27 Apr 2017 Maria Alcaraz NULL Values and the GROUP BY Clause 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. The best way to master GROUP BY and NULL in SQL is through practice. I recommend the SQL Practice track at LearnSQL. Read more 15 Mar 2017 Maria Alcaraz Understanding the Use of NULL in SQL Three-Valued Logic 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 24 Jan 2017 Maria Alcaraz Performing Calculations on Date- and Time-Related Values 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 13 Dec 2016 Maria Alcaraz Correlated Subquery in SQL: A Beginner’s Guide 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 23 Jun 2016 Maria Alcaraz Your First Steps With the Geography Data Type 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 19 May 2016 Maria Alcaraz Pivot Tables in PostgreSQL Using the Crosstab Function 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