Query Data with SQL in PostgreSQL

Database used: PostgreSQL (pgAdmin) Dataset: FAA (Federal Aviation Administration) flight on-time performance statistics — flights of major US airlines, January.

Table of Contents

1. Understand the relational model

• 1.1 Introduction to SQL
• 1.2 Structure of a relational database
• 1.3 Using pgAdmin
• 1.4 Introduction to Joins

2. SELECT your data

• 2.1 Introduction to SELECT to retrieve data
• 2.2 Return specific fields
• 2.3 Column aliases (AS)
• 2.4 Return distinct values (DISTINCT)

3. Limit your results

• 3.1 Introduction to the WHERE keyword
• 3.2 Specify criteria (comparison operators)
• 3.3 Implementation of pattern matching (LIKE)
• 3.4 Handling NULL values
• 3.5 Combine criteria (AND, OR, IN, NOT IN)
• 3.6 Operator precedence

4. Present and aggregate results

• 4.1 Sort results (ORDER BY)
• 4.2 Aggregation functions
• 4.3 Group results (GROUP BY)
• 4.4 Filtering aggregates (HAVING)

5. General Summary
6. SQL Commands Quick Reference

1. Understand the relational model

1.1 Introduction to SQL

SQL is a powerful and flexible tool for database administration, data management and preparation for analysis. Unlike general-purpose programming languages ​​like JavaScript or Python, SQL is a special-purpose language: its sole purpose is to interact with data.

What is a database?

A database is a container that helps logically organize data. A physical example is the card catalog in a library: each card represents a book, organized by category and containing additional information on each item.

SQL and ANSI

SQL, or Structured Query Language, is a platform compliant with the ANSI (American National Standards Institute) national standard. This standard ensures that SQL can be used consistently across different database platforms:

• Oracle — adds PL/SQL as a proprietary extension
• Microsoft SQL Server — adds MDX language
• PostgreSQL — ANSI compliant with additional features

ANSI compliance is a major advantage: once SQL is mastered on PostgreSQL, the transition to other platforms is easier. Even non-SQL query languages ​​generally have a close relationship with SQL.

In this course, the emphasis is on ANSI-compliant instructions that can be used on all platforms.

PostgreSQL

PostgreSQL is the database platform used throughout this course. Its main advantages:

• ANSI compliant
• Open-source and free – Adopted by many startups and businesses of all sizes due to its low operating cost and technical features
• Includes a graphical interface called pgAdmin, the main tool used in this course

1.2 Structure of a relational database

Understanding the relational database model is an important part of learning SQL. This model is based on three fundamental concepts.

Tables, columns and rows

Keys

keys allow you to connect information between different tables.

Primary Key: A field that uniquely identifies each record in a table.

Foreign Key: A field in a table that references the primary key of another table. This is what allows the tables to be linked together.

Illustrative example:

people table:

Table address:

In this example:

• PersonID is the primary key of the people table
• AddressID is the primary key of the address table
• PersonID in the address table is a foreign key that references the people table

Using this relationship, we can determine that Bob Clark (PersonID 125) lives at 8 Mesa Ave.

Importance of database design

Although database design is not the main focus of this course, it is important to remember that database design is crucial: it will determine what questions you and other users can ask of the data.

1.3 Using pgAdmin

pgAdmin is the GUI provided with PostgreSQL. It allows you to explore the structure of a database and write SQL queries.

Browsing the airline database

As part of this course, a dataset from the FAA (Federal Aviation Administration) has been loaded. It contains flight punctuality statistics for major US airlines during the month of January.

The structure of this database:

• Database: airlines
• Table: performance
• Number of columns: 19 (flight date, marketing carrier, flight number, origin, destination, performance statistics, etc.)
• Number of rows: 599,013 records

Basic operations in pgAdmin

To view data from a table:

1. Right click on the performance table
2. Select Scripts → SELECT Script
3. pgAdmin automatically generates the code to select all records
4. Click on the Execute icon (play icon) to execute

1.4 Introduction to Joins

joins allow you to combine records and data from multiple tables. They are the basis of database theory and define the relationships between tables.

All joins are based on keys.

Inner Join

An INNER JOIN returns all rows from two or more tables that satisfy the join condition. The joined fields must exist and match in both tables.

Venn diagram representation: Only the intersection of the two sets.

Example:

customers table:

Table orders:

Result of an INNER JOIN on CustomerID:

• John Smith (121) does not appear — he never placed an order
• Estella Dodd (124) appears twice — she has two orders
• Column CustomerID appears twice (retrieved from both tables)

Left Outer Join

A LEFT JOIN returns all records from the left table, as well as matching records from the right table. If no matches exist in the table on the right, the columns in that table contain NULL values.

Representation: The whole set A, plus the intersection with B.

Example: If we want the list of all customers, whether they have placed an order or not:

• John Smith (121) appears with NULL columns for orders — he never ordered
• All other customers appear with their order information if it exists

Good practice: The LEFT OUTER JOIN is by far the most used in practice. It is generally easier to understand and interpret than a RIGHT OUTER JOIN. It is strongly recommended to mainly use LEFT OUTER JOINs and reserve the RIGHT OUTER JOIN for cases where it is absolutely necessary.

Right Outer Join

A RIGHT JOIN returns all records from the right table, as well as matching records from the left table. If no matches exist in the table on the left, the columns in that table contain NULL values.

Example: If we added an order without a valid CustomerID in the orders table, it will still appear with NULL values ​​for the customers columns.

Full Outer Join

A FULL JOIN returns all records in the left and right tables, whether there is a match or not. Unmatched sides contain NULL values.

Representation: The complete union of the two sets.

Example: A FULL JOIN on customers and orders would return:

• Customers without orders (NULL on the orders side)
• Orders without corresponding customers (NULL on customers side)
• All normal matches

Summary of join types

2. SELECT your data

2.1 Introduction to SELECT to retrieve data

When you want to retrieve information from a database, you query the database. These SQL statements are commonly called queries.

SQL code formatting

Although SQL does not have strict formatting requirements, it is useful to use consistent formatting to:

• Make your code easier to read as queries become more complex
• Helping others interpret your code

Recommended conventions:

• SQL keywords are generally in UPPER CASE (e.g.: SELECT, FROM, WHERE)
• Identifiers (table and column names) are in lowercase
• Each statement ends with a semicolon (;)

Best practice: Although PostgreSQL often accepts statements without a semicolon, it is strongly recommended to end all SQL statements with a semicolon. This becomes essential when several instructions are written in the same window.

The SELECT keyword

The most fundamental keyword in SQL is SELECT. It allows you to retrieve selected data from a database. You can even use it without a table:

SELECT 2 + 2;

Which simply returns 4.

Basic structure of a SELECT query

SELECT colonne1, colonne2
  FROM nom_de_table;

• After SELECT: list of column names of interest
• After FROM: name of the table containing these columns

The wildcard asterisk (*) and LIMIT

To see all columns in a table:

SELECT *
  FROM performance;

This query returns the 19 columns and 599,013 rows of the performance table. Using * in production is considered bad practice: on large databases, these queries can run very slowly.

To limit the number of rows returned — useful for preliminary exploration of a dataset:

SELECT *
  FROM performance
 LIMIT 12;

PostgreSQL returns only the first 12 rows, which is useful for examining the table structure.

2.2 Return specific fields

Explicit notation consists of listing the specific columns that you want to return. This makes the code:

• More readable and usable for others
• Easier to debug
• Better performance on large databases

SELECT mkt_carrier,
       mkt_carrier_fl_num,
       origin
  FROM performance;

This query returns only the three columns specified for all records in the performance table.

Beware of field name errors: If a column name is misspelled or does not exist in the table, PostgreSQL returns an error message. PostgreSQL often attempts to suggest the correct field name to help with diagnosis.

-- Exemple qui génère une erreur
SELECT mkt_carrier,
       flt_num,    -- nom incorrect, devrait être mkt_carrier_fl_num
       origin
  FROM performance;

2.3 Column aliases (AS)

Column names in a database are not always user-friendly. PostgreSQL makes it easy to assign an alias to a column name using the AS keyword.

The syntax is: original_column AS new_name

SELECT mkt_carrier          AS airline,
       mkt_carrier_fl_num   AS flight,
       origin
  FROM performance;

The results are the same, but the column names in the result set are now airline and flight — much more user-friendly.

Formatting convention: the “river”

In the example above, notice the alignment of the SQL keywords:

• Keywords (SELECT, FROM) are right aligned
• Identifiers (column names) are left aligned

This formatting creates what is known in typography as a river in the middle of the code, allowing the reader to easily scan the code and separate SQL keywords from specific implementation details.

Reminder: There are no strict rules for formatting. The main goal should be consistency and readability.

2.4 Return distinct values ​​(DISTINCT)

The keyword DISTINCT returns unique values ​​in a column, that is, even if a value appears more than once, it is only returned once.

The keyword DISTINCT is placed immediately after SELECT and before specifying the fields.

Example with a single column

Let’s imagine a students table with the following data:

-- Retourne 8 lignes (toutes les valeurs)
SELECT first_name
  FROM students;

-- Retourne 5 lignes (valeurs distinctes uniquement)
SELECT DISTINCT first_name
  FROM students;

The query result with DISTINCT: Katie, Amy, Jason, Alec, Shannon.

Application on airline database

-- Retourne 599 013 enregistrements (un par vol)
SELECT mkt_carrier
  FROM performance;

-- Retourne seulement 10 enregistrements (10 compagnies aériennes uniques)
SELECT DISTINCT mkt_carrier
  FROM performance;

The result shows that there are 10 unique airlines listed as marketing carriers in the performance table.

DISTINCT on multiple columns

DISTINCT can also be used to find unique combinations. For example, to obtain the list of cities served by each airline in terms of departures:

-- Retourne une ligne par vol (avec doublons pour chaque combinaison airline/origin)
SELECT mkt_carrier,
       origin
  FROM performance;

-- Retourne une ligne par combinaison unique airline + ville de départ
SELECT DISTINCT mkt_carrier,
                origin
  FROM performance;

The result is a list of cities served by each airline for outbound flights.

Summary: DISTINCT can be used to return unique values ​​when only one column is listed in the SELECT clause, or to return distinct combinations when more than one column is listed.

3. Limit your results

3.1 Introduction to the WHERE keyword

Until now, queries returned either all columns or specific columns, but for all records. Most of the time, we want to return only certain records according to specified criteria.

The WHERE clause allows you to specify these filter criteria. Everything we do in SQL is a modification of the basic SELECT query.

General structure with WHERE

SELECT colonne1, colonne2
  FROM nom_de_table
 WHERE colonne = valeur;

The WHERE clause is composed of the WHERE keyword and the limitation criteria.

Basic example

SELECT first_name,
       last_name
  FROM person
 WHERE first_name = 'Shelby';

This query only returns records where the first name is ‘Shelby’.

Important: The WHERE criterion is case-sensitive. WHERE first_name = 'shelby' would not return the same result as WHERE first_name = 'Shelby'. This can be a problem in databases that store information in all uppercase, all lowercase, or resulting from inconsistent entry by several people.

3.2 Specifying criteria (comparison operators)

In addition to equality, SQL provides a variety of comparison operators.

Comparison operator table

| Operator | Description | Application | |----------|--------||-------------| | = | Equal to | All data types | | <> or != | Different from | All data types | | < | Less than | Numeric, integer, date | | > | Greater than | Numeric, integer, date | | <= | Less than or equal to | Numeric, integer, date | | >= | Greater than or equal to | Numeric, integer, date |

These operators tell PostgreSQL to compare the specified field to a specified value.

Combine multiple criteria with AND

Multiple criteria can be combined using the AND keyword. There is no limit to the number of criteria that can be combined.

Common issue: Multiple states can have cities with the same name.

-- Ambiguïté : retourne toutes les villes nommées Louisville (plusieurs états)
SELECT city,
       state,
       population
  FROM city_population
 WHERE city = 'Louisville';

-- Précis : retourne uniquement Louisville, Kentucky
SELECT city,
       state,
       population
  FROM city_population
 WHERE city = 'Louisville'
   AND state = 'Kentucky';

Application on the flight database

-- Tous les vols partant de Chicago O'Hare (code aéroport : ORD)
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       dest
  FROM performance
 WHERE origin = 'ORD';

-- Tous les vols ayant Chicago O'Hare comme destination
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       dest
  FROM performance
 WHERE dest = 'ORD';

-- Vols de Bozeman, Montana (BZN) vers Chicago O'Hare (ORD)
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       dest
  FROM performance
 WHERE dest = 'ORD'
   AND origin = 'BZN';

3.3 Implementation of pattern matching (LIKE)

Relational operators require a specific comparison value. The LIKE keyword is a logical operator that allows you to find records where a field matches a specific pattern.

General syntax

WHERE nom_champ LIKE 'pattern'

Wildcards (wildcards)

There are two wildcards that can be used with LIKE:

Basic Examples

-- Équivalent à WHERE first_name = 'Shelby'
SELECT first_name, last_name
  FROM person
 WHERE first_name LIKE 'Shelby';

-- Villes dont le nom commence par 'Fort'
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin_city_name
  FROM performance
 WHERE origin_city_name LIKE 'Fort%';

Advanced examples with wildcards

-- Villes uniques commençant par 'Fort'
SELECT DISTINCT origin_city_name
  FROM performance
 WHERE origin_city_name LIKE 'Fort%';
-- Résultat : 5 villes (Fort Myers, Fort Lauderdale, Fort Smith, etc.)

-- Villes en Floride (se terminent par 'FL')
SELECT DISTINCT origin_city_name
  FROM performance
 WHERE origin_city_name LIKE '%FL';

-- Villes commençant par 'New' et se terminant par 'LA' (Louisiane)
SELECT DISTINCT origin_city_name
  FROM performance
 WHERE origin_city_name LIKE 'New%LA';
-- Résultat : New Orleans, Louisiana

-- Villes au Kansas (KS) avec exactement 4 lettres dans leur nom
-- (le format de la colonne est "Ville, État")
SELECT DISTINCT origin_city_name
  FROM performance
 WHERE origin_city_name LIKE '____, KS';
-- Résultat : Hays, Kansas (4 lettres)

-- Toutes les villes avec exactement 4 lettres dans leur nom
SELECT DISTINCT origin_city_name
  FROM performance
 WHERE origin_city_name LIKE '____, %';
-- Résultat : 11 villes avec un nom de 4 lettres

Using NOT LIKE

To find records that do not match the pattern:

-- Villes dont le nom NE commence PAS par 'Fort'
SELECT DISTINCT origin_city_name
  FROM performance
 WHERE origin_city_name NOT LIKE 'Fort%';

Advanced pattern matching: regular expressions

The type of pattern matching performed by LIKE is known as fuzzy-matching. It allows processing less than perfect data.

PostgreSQL also allows the use of regular expressions for more complex patterns, allowing you to validate entries, find input errors or search for useful patterns in the data. This is an advanced feature that is beyond the scope of this course, but it exists and can be useful.

3.4 Handling NULL values

PostgreSQL has two criteria statements specifically designed to handle NULL values.

What is a NULL value?

NULL is a special character in SQL and relational databases. Unlike some programming languages ​​where null is equivalent to 0, in SQL:

• NULL is not a specific value like zero or blank space
• NULL is a flag — it indicates that a field has a missing or unknown value

IS NULL and IS NOT NULL

-- Vols annulés (ceux qui ont une valeur dans cancellation_code)
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       cancellation_code
  FROM performance
 WHERE cancellation_code IS NOT NULL;
-- Résultat : 18 740 vols annulés pour diverses raisons

-- Vols opérés normalement (pas annulés)
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       cancellation_code
  FROM performance
 WHERE cancellation_code IS NULL;
-- Résultat : plus de 582 000 vols opérés normalement en janvier

Warning: IS NULL and IS NOT NULL are very useful for diagnosing problems when analyzing a dataset. In particular, arithmetic operations involving a NULL value always return NULL. NULL values ​​can have unintended consequences on your analysis or results, so it is advisable to check them.

3.5 Combine criteria (AND, OR, IN, NOT IN)

The AND keyword

The AND keyword is a logical operator that means all conditions must be true. If a row matches both conditions specified, it will be included.

-- Vols vers Chicago O'Hare depuis Bozeman, Montana
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       dest
  FROM performance
 WHERE dest = 'ORD'
   AND origin = 'BZN';

The OR keyword

The keyword OR differs from AND: with OR, only one of the conditions is true for the line to be included.

-- Étudiants nommés Jimmy, Brenna ou Elmo
SELECT first_name
  FROM students
 WHERE first_name = 'Jimmy'
    OR first_name = 'Brenna'
    OR first_name = 'Elmo';

The IN keyword

The keyword IN is a practical shortcut when you want to compare a field to a list of values. It effectively replaces several OR conditions with =.

-- Équivalent à la requête OR ci-dessus, mais plus concis
SELECT first_name
  FROM students
 WHERE first_name IN ('Jimmy', 'Brenna', 'Elmo');

Corresponding values ​​are separated by commas and enclosed in parentheses.

Limitation: We cannot use IN to search for several patterns with LIKE. It would be necessary to add several LIKE statements and combine them with the OR keyword.

The NOT IN keyword

IN has a complementary keyword, NOT IN, which returns records whose value does not match any of the values ​​in the list.

-- Tous les étudiants SAUF Jimmy, Brenna et Elmo
SELECT first_name
  FROM students
 WHERE first_name NOT IN ('Jimmy', 'Brenna', 'Elmo');

3.6 Operator precedence

When using logical operators to combine multiple criteria, it is important to understand the concept of operator precedence.

Operator precedence determines the order in which operations are performed in the query.

Important rule in PostgreSQL

By default, AND has higher precedence than OR.

This means that an AND statement will be evaluated before an OR statement, regardless of the order in which they are listed in the WHERE clause.

Example of precedence problem

-- Attention : cette requête est ambiguë !
-- PostgreSQL l'interprète comme :
-- (origin = 'ORD' AND mkt_carrier = 'AA') OR mkt_carrier = 'UA'
SELECT *
  FROM performance
 WHERE origin = 'ORD'
   AND mkt_carrier = 'AA'
    OR mkt_carrier = 'UA';

-- Ce qu'on voulait probablement (avec parenthèses pour clarifier) :
-- origin = 'ORD' ET (mkt_carrier = 'AA' OU mkt_carrier = 'UA')
SELECT *
  FROM performance
 WHERE origin = 'ORD'
   AND (mkt_carrier = 'AA' OR mkt_carrier = 'UA');

Best practice: use parentheses

Always use parentheses to make the desired order of operations explicit when combining multiple logical operators. This avoids ambiguities and unexpected results.

-- Exemple avec parenthèses bien placées
SELECT fl_date,
       mkt_carrier    AS airline,
       mkt_carrier_fl_num AS flight,
       origin,
       dest
  FROM performance
 WHERE (origin = 'ORD' OR origin = 'ATL')
   AND (dest = 'BZN' OR dest = 'DEN')
   AND mkt_carrier IS NOT NULL;

4. Present and aggregate results

4.1 Sort results (ORDER BY)

When running a query against a Postgres database, the database returns results in a seemingly random order — in reality, the data is returned in the order it is stored in the database. However, we can sort these results with the keyword ORDER BY.

Sort ascending and descending

-- Tri par prénom (ascendant par défaut)
SELECT name,
       state
  FROM customers
 ORDER BY name;

-- Tri par prénom explicitement ascendant
SELECT name,
       state
  FROM customers
 ORDER BY name ASC;

-- Tri par prénom descendant
SELECT name,
       state
  FROM customers
 ORDER BY name DESC;

By default, if no direction is specified, the order is ascending (ASC).

Sort by multiple columns

PostgreSQL allows sorting by more than one column. Sorting is done first on the first column, then on the next for rows with the same value in the first column.

-- Trier d'abord par état, puis par nom dans chaque état
SELECT name,
       state
  FROM customers
 ORDER BY state, name;

-- Trier les états en descendant, les noms en ascendant
SELECT name,
       state
  FROM customers
 ORDER BY state DESC, name ASC;

Shortcut by column position

SQL also allows columns in ORDER BY to be referenced by their position number in the SELECT clause.

-- name est colonne 1, state est colonne 2
-- ORDER BY 2, 1 signifie : trier d'abord par state (col 2), puis par name (col 1)
SELECT name,
       state
  FROM customers
 ORDER BY 2, 1;

Warning: Although practical, this method can harm the readability and interpretability of the code. Use with discretion.

4.2 Aggregation functions

SQL provides a set of functions called aggregate functions. An aggregate function performs a calculation on a set of values ​​to return a single value.

The main aggregation functions

To use an aggregate function, it is included in the SELECT clause.

Examples of using aggregate functions

-- Compter le nombre total de vols dans la table
SELECT COUNT(*)
  FROM performance;
-- Résultat : 599 013

-- Calculer le retard de départ total (en minutes)
SELECT SUM(dep_delay)
  FROM performance;

-- Calculer le retard moyen au départ
SELECT AVG(dep_delay)
  FROM performance;
-- Résultat : ~10.43 minutes

-- Retard minimal enregistré
SELECT MIN(dep_delay)
  FROM performance;

-- Retard maximal enregistré
SELECT MAX(dep_delay)
  FROM performance;

Count with a WHERE filter

Aggregation functions can be combined with a WHERE clause:

-- Nombre de vols avec un retard au départ supérieur à 0
SELECT COUNT(*)
  FROM performance
 WHERE dep_delay > 0;

-- Nombre de vols partis EN AVANCE (retard négatif)
SELECT COUNT(*)
  FROM performance
 WHERE dep_delay < 0;
-- Résultat : 364 265 vols ont une valeur de retard négative
--           (ils ont décollé avant leur heure prévue)

-- Nombre de vols partis EXACTEMENT à l'heure
SELECT COUNT(*)
  FROM performance
 WHERE dep_delay = 0;
-- Résultat : 22 985 enregistrements (le cas le moins fréquent)

4.3 Group results (GROUP BY)

The keyword GROUP BY allows you to use aggregation functions to calculate values ​​grouped by category.

Calculate average per group

-- Retard moyen au départ groupé par ville de départ
SELECT origin_city_name,
       AVG(dep_delay)
  FROM performance
 GROUP BY origin_city_name;
-- Exemple : Aberdeen, South Dakota — retard moyen ~32.5 minutes

Important rule: all non-aggregated fields must be in GROUP BY

Critical rule: If an aggregated field is used in addition to other fields in the SELECT clause, all other fields must be listed in the GROUP BY clause. Otherwise, PostgreSQL does not know how to group the results and will return an error.

-- ERREUR : origin n'est pas dans GROUP BY
SELECT origin_city_name,
       origin,           -- manquant dans GROUP BY !
       AVG(dep_delay)
  FROM performance
 GROUP BY origin_city_name;

-- CORRECT : les deux champs non-agrégés sont dans GROUP BY
SELECT origin_city_name,
       origin,
       AVG(dep_delay)
  FROM performance
 GROUP BY origin_city_name,
          origin;

Combine GROUP BY and ORDER BY

-- Aéroports avec les plus longs retards moyens (du plus long au plus court)
SELECT origin_city_name,
       origin,
       AVG(dep_delay)
  FROM performance
 GROUP BY origin_city_name,
          origin
 ORDER BY AVG(dep_delay) DESC;
-- Résultat :
-- 1. Santa Maria, Californie : ~103 minutes de retard moyen
-- 2. Clarksburg, Virginie-Occidentale : ~69.9 minutes de retard moyen

-- Aéroports avec les retards moyens les plus courts
SELECT origin_city_name,
       origin,
       AVG(dep_delay)
  FROM performance
 GROUP BY origin_city_name,
          origin
 ORDER BY AVG(dep_delay) ASC;
-- Résultat :
-- 1. Yakutat, Alaska : -9.25 minutes (partent en moyenne 9.25 min en avance)
-- 2. Petersburg, Alaska : légèrement moins de -9 minutes

4.4 Filter aggregates (HAVING)

The HAVING keyword is similar to the WHERE keyword, but with one fundamental difference:

Why not use WHERE on aggregates?

WHERE is applied before calculating aggregates. This means that we cannot use WHERE to filter on values ​​calculated by aggregation functions. This is where HAVING comes in.

General syntax

SELECT colonne_groupe,
       AGGREGATE_FUNCTION(colonne)
  FROM table
 GROUP BY colonne_groupe
HAVING AGGREGATE_FUNCTION(colonne) opérateur valeur;

HAVING Examples

-- Villes avec un retard moyen au départ supérieur à 120 minutes (2 heures)
SELECT origin_city_name,
       AVG(dep_delay)
  FROM performance
 GROUP BY origin_city_name
HAVING AVG(dep_delay) > 120;
-- Résultat : 27 aéroports avec un retard moyen supérieur à 2 heures

-- Villes où la moyenne de retard dépasse 19 dans un tableau d'élèves et niveaux
SELECT grade_level,
       AVG(age)
  FROM students
 GROUP BY grade_level
HAVING AVG(age) < 19;
-- Résultat : seulement les niveaux scolaires où l'âge moyen est inférieur à 19
-- (freshmen : 15, juniors : 17)

Combine multiple aggregates and HAVING

It is possible to combine several aggregation functions in the same query, and to apply multiple HAVING criteria:

-- Villes avec un retard moyen supérieur à 90 minutes ET plus de 30 vols retardés
SELECT origin_city_name,
       AVG(dep_delay),
       COUNT(*)
  FROM performance
 WHERE dep_delay > 0      -- ne compter que les vols retardés
 GROUP BY origin_city_name
HAVING AVG(dep_delay) > 90
   AND COUNT(*) > 30
 ORDER BY AVG(dep_delay) DESC;
-- Résultat : 12 villes avec plus de 30 vols retardés et un retard moyen > 90 min
-- Exemple : Bakersfield, Californie — retard moyen ~104 min, 42 vols retardés

Note: WHERE is evaluated before aggregation (here, we only consider delayed flights), while HAVING is evaluated after aggregation (we filter the resulting groups).

5. General Summary

This course covers the fundamentals of SQL applied to PostgreSQL. Here are the key points to remember:

Module 1 — The relational model

• A database is a logical data organization container
• SQL is an ANSI-compliant special purpose language
• PostgreSQL is open-source, ANSI compliant, and includes pgAdmin as a GUI
• Relational databases use tables, columns and rows
• primary keys uniquely identify each record
• foreign keys allow tables to be linked together
• The four types of joints: INNER, LEFT, RIGHT, FULL

Module 2 — SELECT

• SELECT * returns all columns (not recommended in production)
• LIMIT limits the number of rows returned
• Explicit notation lists the specific columns desired
• AS assigns aliases to columns to make them more user-friendly
• DISTINCT only returns unique values or combinations

Module 3 — Filtering with WHERE

• WHERE filters records according to specified criteria
• The comparison operators: =, <>, <, >, <=, >=
• AND combines criteria (all conditions must be true)
• OR combines criteria (at least one condition must be true)
• LIKE allows pattern matching with wildcards % and _
• IS NULL / IS NOT NULL handle missing values
• IN / NOT IN filter according to a list of values
• Use parentheses to clarify operator precedence

Module 4 — Presentation and aggregation

• ORDER BY sorts results into ASC (default) or DESC
• Aggregation functions: COUNT, SUM, AVG, MIN, MAX
• GROUP BY groups data for aggregation calculations
• All non-aggregated fields in SELECT must be in GROUP BY
• HAVING filters groups/aggregates (unlike WHERE which filters rows)

6. SQL Commands Quick Reference

Basic structure of a query

SELECT colonne1,
       colonne2,
       AGGREGATE_FUNCTION(colonne3)
  FROM nom_de_table
 WHERE critères_de_filtre_sur_lignes
 GROUP BY colonne1, colonne2
HAVING critères_de_filtre_sur_agrégats
 ORDER BY colonne1 ASC, colonne2 DESC
 LIMIT n;

Order of execution of an SQL query

Although the query is written in the order above, PostgreSQL executes it in the following order:

1. FROM — identify the source table
2. WHERE — filter individual rows
3. GROUP BY — group filtered rows
4. HAVING — filter groups
5. SELECT — select columns and calculate aggregates
6. ORDER BY — sort results
7. LIMIT — limit the number of results returned

Keyword summary table

Complete query examples

-- 1. Tous les vols annulés avec leur code d'annulation
SELECT fl_date,
       mkt_carrier          AS airline,
       mkt_carrier_fl_num   AS flight,
       origin,
       dest,
       cancellation_code
  FROM performance
 WHERE cancellation_code IS NOT NULL
 ORDER BY fl_date;

-- 2. Compagnies aériennes uniques desservant Chicago O'Hare au départ
SELECT DISTINCT mkt_carrier AS airline
  FROM performance
 WHERE origin = 'ORD'
 ORDER BY airline;

-- 3. Top 10 des aéroports avec les retards moyens les plus importants
SELECT origin_city_name,
       origin,
       AVG(dep_delay)       AS avg_delay_minutes,
       COUNT(*)             AS total_flights
  FROM performance
 GROUP BY origin_city_name,
          origin
 ORDER BY avg_delay_minutes DESC
 LIMIT 10;

-- 4. Aéroports avec plus de 30 vols retardés de plus de 90 minutes en moyenne
SELECT origin_city_name,
       origin,
       AVG(dep_delay)   AS avg_delay_minutes,
       COUNT(*)         AS delayed_flights
  FROM performance
 WHERE dep_delay > 0
 GROUP BY origin_city_name,
          origin
HAVING AVG(dep_delay) > 90
   AND COUNT(*) > 30
 ORDER BY avg_delay_minutes DESC;

-- 5. Statistiques globales de retard pour l'ensemble du jeu de données
SELECT COUNT(*)          AS total_flights,
       AVG(dep_delay)    AS avg_departure_delay,
       MIN(dep_delay)    AS min_departure_delay,
       MAX(dep_delay)    AS max_departure_delay,
       SUM(dep_delay)    AS total_delay_minutes
  FROM performance;

Search Terms

query · data · sql · postgresql · databases · keyword · database · join · select · combine · group · aggregates · aggregation · column · columns · criteria · functions · having · null · order · outer · relational · sort · aggregate