NULL Values and the GROUP BY Clause by Maria Alcaraz 27 Apr 2017 We've already covered how to use the GROUP BY clause and some aggregation functions like SUM(), AVG(), MAX(), MIN(), COUNT(). In this article, we will explain how the GROUP BY clause works when NULL values are involved. We will also explain about using NULLs with the ORDER BY clause. In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. For more details, check out Wikipedia's explanation of NULL in SQL. Read more Understanding the Use of NULL in SQL Three-Valued Logic by Maria Alcaraz 15 Mar 2017 NULLs are necessary in relational databases, and learning to use them is fundamental to SQL success. However, NULLs should also be handled with care, as we explain in this post. In relational databases, we don’t always have a value to put in a column. For example, suppose we have a table called “persons” that has “first_name”, “last_name”, “birth_date” and “marriage_date” columns. What value will we store in the “marriage_date” column for single persons? Read more Performing Calculations on Date- and Time-Related Values by Maria Alcaraz 24 Jan 2017 Relational databases support several date and time data types. In this article, we'll look at several arithmetic operations we can do on these types. These operations are logical and understandable, even for the beginning SQL coder. Let's first briefly explain the main data types used for dates and times. Keep in mind that data types may differ by database engine, so check your database documentation for specifics before you start working with them. Read more Correlated Subquery in SQL: A Beginner’s Guide by Maria Alcaraz 13 Dec 2016 Sometimes, using a SQL correlated subquery is the only way to solve a statement. But these subqueries can be very slow. In this post, we’ll talk about when to use a correlated subquery, why, and how to do it. Subqueries are an important resource for increasing the expressive power of SQL. If you haven’t read our previous article, subqueries are simply a SELECT statement inside another SELECT. We can use them in different places inside a SELECT, such as in the WHERE, HAVING, or FROM clauses. Read more Your First Steps With the Geography Data Type by Maria Alcaraz 23 Jun 2016 Geographical applications are everywhere: GPS and sat nav systems, maps, get-a-taxi apps, real estate portals, etc. Behind each of them is a spatial database storing geographical data, and supporting spatial queries. In this article, we will introduce PostGIS, the main open-source spatial database manager. PostGIS is a spatial database extension for the PostgreSQL relational database. It adds support for geographic objects, allowing location queries to be run in SQL. PostGIS adds two main data types to PostgreSQL: geography and geometry. Read more Pivot Tables in PostgreSQL Using the Crosstab Function by Maria Alcaraz 19 May 2016 Some years ago, when PostgreSQL version 8.3 was released, a new extension called tablefunc was introduced. This extension provides a really interesting set of functions. One of them is the crosstab function, which is used for pivot table creation. That's what we'll cover in this article. The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we'll define the pivot table we want. Read more