35. PostgreSQL: SELECT queries

CREATE SCHEMA ventes;
SET search_path TO ventes;

CREATE TABLE client (
    id      serial PRIMARY KEY,
    nom     varchar(50) NOT NULL,
    ville   varchar(50),
    email   varchar(80) UNIQUE,
    solde   numeric(10,2) NOT NULL DEFAULT 0,
    cree_le timestamp NOT NULL DEFAULT now()
);

CREATE TABLE article (
    id      serial PRIMARY KEY,
    code    varchar(20) NOT NULL UNIQUE,
    libelle text NOT NULL,
    prix    numeric(10,2) NOT NULL CHECK (prix >= 0),
    stock   integer NOT NULL DEFAULT 0,
    actif   boolean NOT NULL DEFAULT true
);

CREATE TABLE commande (
    id        serial PRIMARY KEY,
    client_id integer NOT NULL REFERENCES client (id),
    date_cmd  date NOT NULL DEFAULT current_date,
    etat      varchar(12) NOT NULL DEFAULT 'ouverte'
);

CREATE TABLE ligne_commande (
    commande_id integer NOT NULL REFERENCES commande (id) ON DELETE CASCADE,
    article_id  integer NOT NULL REFERENCES article (id),
    quantite    integer NOT NULL CHECK (quantite > 0),
    prix_unit   numeric(10,2) NOT NULL,
    total       numeric(12,2) GENERATED ALWAYS AS (quantite * prix_unit) STORED,
    PRIMARY KEY (commande_id, article_id)
);

INSERT INTO client (nom, ville, email, solde) VALUES
    ('Alice', 'Oran', '[email protected]', 100.50),
    ('Bob', 'Alger', '[email protected]', -20.00),
    ('Chloé', 'Oran', NULL, 0.00),
    ('David', 'Constantine', '[email protected]', 250.00),
    ('Émile', 'Alger', NULL, 75.25),
    ('Fatima', NULL, '[email protected]', 10.00);

INSERT INTO article (code, libelle, prix, stock, actif) VALUES
    ('ST-01', 'Stylo bille', 1.20, 500, true),
    ('CA-01', 'Cahier 96 pages', 3.50, 200, true),
    ('CL-01', 'Classeur', 6.90, 80, true),
    ('AG-01', 'Agrafeuse', 14.00, 0, false),
    ('GO-01', 'Gomme', 0.80, 150, true),
    ('TR-01', 'Trombones', 2.10, 40, true);

INSERT INTO commande (client_id, date_cmd, etat) VALUES
    (1, '2026-09-01', 'livrée'),
    (1, '2026-09-15', 'ouverte'),
    (2, '2026-09-20', 'livrée'),
    (3, '2026-10-02', 'ouverte'),
    (5, '2026-10-05', 'annulée');

INSERT INTO ligne_commande (commande_id, article_id, quantite, prix_unit) VALUES
    (1, 1, 10, 1.20), (1, 2, 2, 3.50),
    (2, 3, 1, 6.90),
    (3, 1, 100, 1.10), (3, 5, 20, 0.80),
    (4, 2, 5, 3.50), (4, 4, 1, 14.00);

[WITH [RECURSIVE] name [(column, ...)] AS [[NOT] MATERIALIZED] (query) [, ...]]
SELECT [ALL | DISTINCT] item [[AS] alias] [, ...]
    [FROM source [, ...]]
    [WHERE condition]
    [GROUP BY {expr | position | ROLLUP (...) | CUBE (...) | GROUPING SETS (...)} [, ...]]
    [HAVING condition]
    [WINDOW name AS (definition) [, ...]]
    [{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] query]
    [ORDER BY expr [ASC | DESC] [NULLS {FIRST | LAST}] [, ...]]
    [LIMIT {n | ALL}] [OFFSET m [ROW | ROWS]]
    [FETCH {FIRST | NEXT} [n] {ROW | ROWS} {ONLY | WITH TIES}]
    [FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF table [, ...]] [NOWAIT | SKIP LOCKED]]

    [ONLY] [schema.]table [*] [[AS] alias [(column, ...)]]
  | (query) [AS] alias [(column, ...)]
  | (VALUES (...), ...) [AS] alias [(column, ...)]
  | [LATERAL] function(arguments) [WITH ORDINALITY] [[AS] alias [(column, ...)]]
  | LATERAL (query) [AS] alias [(column, ...)]
  | source [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]] JOIN source {ON condition | USING (column, ...)}
  | source NATURAL [INNER | LEFT | RIGHT | FULL] JOIN source
  | source CROSS JOIN source

SELECT nom, solde AS balance, solde * 1.1 marge FROM client WHERE id <= 2 ORDER BY id;

 nom   | balance | marge
-------+---------+---------
 Alice | 100.50  | 110.550
 Bob   | -20.00  | -22.000

SELECT * FROM article WHERE id = 1;
SELECT c.*, 1 AS actif FROM client c WHERE c.id = 1;      -- all columns of c, plus one
SELECT 1 AS a, 'x' AS b, NULL AS c, true AS d;            -- no FROM: one row

SELECT nom AS "Nom du client", solde AS "Solde (DA)" FROM client WHERE id = 1;

 Nom du client | Solde (DA)
---------------+------------
 Alice         | 100.50

SELECT 1 + 1, 'a' || 'b', upper('x'), solde * 2, solde::int FROM client WHERE id = 1;

 1 + 1 | 'a' || 'b' | upper('x') | solde * 2 | solde::int
-------+------------+------------+-----------+------------
 2     | ab         | X          | 201.00    | 101

SELECT nom, solde > 0 AS crediteur, ville IS NULL AS sans_ville, ville = 'Oran' AS oran
FROM client ORDER BY id;

 nom    | crediteur | sans_ville | oran
--------+-----------+------------+------
 Alice  | t         | f          | t
 Bob    | f         | f          | f
 Chloé  | f         | f          | t
 David  | t         | f          | f
 Émile  | t         | f          | f
 Fatima | t         | t          | NULL

SELECT DISTINCT ville FROM client ORDER BY ville;

 ville
-------------
 Alger
 Constantine
 Oran
 NULL

SELECT count(DISTINCT ville) AS villes, count(ville) AS renseignees, count(*) AS clients FROM client;
-- 3 | 5 | 6

SELECT DISTINCT x FROM (VALUES ('a'), ('A'), ('a')) AS v (x) ORDER BY x;     -- A, a

SELECT DISTINCT ON (ville) ville, nom, solde FROM client ORDER BY ville, solde DESC;

 ville       | nom    | solde
-------------+--------+--------
 Alger       | Émile  | 75.25
 Constantine | David  | 250.00
 Oran        | Alice  | 100.50
 NULL        | Fatima | 10.00

VALUES (1, 'a'), (2, 'b');                                   -- columns column1, column2
SELECT * FROM (VALUES (1, 'un'), (2, 'deux')) AS v (n, mot) ORDER BY n;

 n | mot
---+------
 1 | un
 2 | deux

SELECT c.nom, co.id AS commande, co.date_cmd
FROM client c JOIN commande co ON co.client_id = c.id
ORDER BY co.id;

 nom   | commande | date_cmd
-------+----------+------------
 Alice | 1        | 2026-09-01
 Alice | 2        | 2026-09-15
 Bob   | 3        | 2026-09-20
 Chloé | 4        | 2026-10-02
 Émile | 5        | 2026-10-05

SELECT c.nom, co.id AS commande
FROM client c LEFT JOIN commande co ON co.client_id = c.id
ORDER BY c.id, co.id;

 nom    | commande
--------+----------
 Alice  | 1
 Alice  | 2
 Bob    | 3
 Chloé  | 4
 David  | NULL
 Émile  | 5
 Fatima | NULL

-- count without losing clients that have no orders
SELECT c.nom, count(co.id) AS commandes
FROM client c LEFT OUTER JOIN commande co ON co.client_id = c.id
GROUP BY c.id, c.nom ORDER BY c.id;
-- Alice 2, Bob 1, Chloé 1, David 0, Émile 1, Fatima 0

-- all articles, ordered or not
SELECT a.code, l.commande_id
FROM ligne_commande l RIGHT JOIN article a ON a.id = l.article_id
ORDER BY a.id, l.commande_id;
-- … GO-01 | 3, TR-01 | NULL

-- clients without a delivered order: the condition is in ON, the WHERE looks for the absence
SELECT c.id, c.nom
FROM client c LEFT JOIN commande co ON co.client_id = c.id AND co.etat = 'livrée'
WHERE co.id IS NULL AND c.id <= 3;                         -- 3 | Chloé

-- condition in WHERE: only clients that have a delivered order remain
SELECT c.id, c.nom, co.id AS commande
FROM client c LEFT JOIN commande co ON co.client_id = c.id
WHERE co.etat = 'livrée' ORDER BY c.id;                    -- 1 | Alice | 1, 2 | Bob | 3

SELECT c.nom, co.id AS commande
FROM client c FULL JOIN commande co ON co.client_id = c.id AND co.etat = 'livrée'
ORDER BY c.id NULLS LAST, co.id;

 nom    | commande
--------+----------
 Alice  | 1
 Bob    | 3
 Chloé  | NULL
 David  | NULL
 Émile  | NULL
 Fatima | NULL
 NULL   | 2
 NULL   | 4
 NULL   | 5

SELECT count(*) AS n FROM client CROSS JOIN article;      -- 36
SELECT count(*) AS n FROM client, article;                -- 36

SELECT c.nom, co.id
FROM client c, commande co
WHERE co.client_id = c.id AND co.etat = 'ouverte'
ORDER BY co.id;                                            -- Alice | 2, Chloé | 4

SELECT c.nom, a.libelle, l.quantite, l.total
FROM client c
JOIN commande co ON co.client_id = c.id
JOIN ligne_commande l ON l.commande_id = co.id
JOIN article a ON a.id = l.article_id
WHERE co.etat = 'livrée'
ORDER BY c.nom, a.libelle;

 nom   | libelle         | quantite | total
-------+-----------------+----------+--------
 Alice | Cahier 96 pages | 2        | 7.00
 Alice | Stylo bille     | 10       | 12.00
 Bob   | Gomme           | 20       | 16.00
 Bob   | Stylo bille     | 100      | 110.00

CREATE TABLE remise (client_id integer PRIMARY KEY, taux numeric(4,2), id integer);
INSERT INTO remise VALUES (1, 0.10, 100), (4, 0.05, 101), (9, 0.50, 102);

SELECT * FROM commande JOIN remise USING (client_id) ORDER BY 2;

 client_id | id | date_cmd   | etat    | taux | id
-----------+----+------------+---------+------+-----
 1         | 1  | 2026-09-01 | livrée  | 0.10 | 100
 1         | 2  | 2026-09-15 | ouverte | 0.10 | 100

SELECT client_id, commande.id AS commande, taux
FROM commande FULL JOIN remise USING (client_id)
ORDER BY client_id, commande;

 client_id | commande | taux
-----------+----------+------
 1         | 1        | 0.10
 1         | 2        | 0.10
 2         | 3        | NULL
 3         | 4        | NULL
 4         | NULL     | 0.05
 5         | 5        | NULL
 9         | NULL     | 0.50

CREATE TABLE ville_info (ville varchar(50) PRIMARY KEY, wilaya integer);
INSERT INTO ville_info VALUES ('Oran', 31), ('Alger', 16), ('Annaba', 23);

SELECT ville, nom, wilaya FROM client NATURAL LEFT JOIN ville_info ORDER BY id;

 ville       | nom    | wilaya
-------------+--------+--------
 Oran        | Alice  | 31
 Alger       | Bob    | 16
 Oran        | Chloé  | 31
 Constantine | David  | NULL
 Alger       | Émile  | 16
 NULL        | Fatima | NULL

SELECT a.nom, b.nom AS meme_ville
FROM client a JOIN client b ON a.ville = b.ville AND a.id < b.id
ORDER BY a.id;

 nom   | meme_ville
-------+------------
 Alice | Chloé
 Bob   | Émile

SELECT t.ville, t.total
FROM (SELECT ville, sum(solde) AS total FROM client GROUP BY ville) AS t (ville, total)
WHERE total > 100
ORDER BY 1;

 ville       | total
-------------+--------
 Constantine | 250.00
 Oran        | 100.50

SELECT * FROM article AS q (x, y) WHERE x = 1;

 x | y     | libelle     | prix | stock | actif
---+-------+-------------+------+-------+-------
 1 | ST-01 | Stylo bille | 1.20 | 500   | t

SELECT * FROM (SELECT * FROM remise) AS r (a, b) ORDER BY a;     -- columns a, b, id
SELECT * FROM remise AS r (a, b, c, d);
-- ERROR 42P10: table "r" has 3 columns available but 4 columns specified

SELECT c.nom, d.id, d.date_cmd
FROM client c,
     LATERAL (SELECT id, date_cmd FROM commande co
              WHERE co.client_id = c.id ORDER BY date_cmd DESC LIMIT 1) d
ORDER BY c.id;

 nom   | id | date_cmd
-------+----+------------
 Alice | 2  | 2026-09-15
 Bob   | 3  | 2026-09-20
 Chloé | 4  | 2026-10-02
 Émile | 5  | 2026-10-05

SELECT n, n * n AS carre FROM generate_series(1, 4) AS s (n);

 n | carre
---+-------
 1 | 1
 2 | 4
 3 | 9
 4 | 16

SELECT g FROM generate_series(10, 1, -3) AS g;                               -- 10, 7, 4, 1
SELECT date '2026-10-01' + n AS jour FROM generate_series(0, 2) AS s (n);    -- three dates
SELECT * FROM generate_series(date '2026-10-01', date '2026-10-03', interval '1 day') AS j;
-- 2026-10-01, 2026-10-02, 2026-10-03
SELECT * FROM generate_series(timestamp '2026-10-01 00:00', timestamp '2026-10-01 02:00',
                              interval '1 hour') AS j;
-- 2026-10-01 00:00:00, 2026-10-01 01:00:00, 2026-10-01 02:00:00

SELECT generate_series(1, 3);                                -- column generate_series: 1, 2, 3
SELECT generate_series(1, 3) * 10 AS dizaine;                -- 10, 20, 30
SELECT nom, generate_series(1, 2) AS tour FROM client WHERE id <= 2 ORDER BY nom, tour;

 nom   | tour
-------+------
 Alice | 1
 Alice | 2
 Bob   | 1
 Bob   | 2

SELECT generate_series(1, 3) AS a, generate_series(1, 2) AS b;

 a | b
---+------
 1 | 1
 2 | 2
 3 | NULL

CREATE FUNCTION total_client(cid integer) RETURNS numeric(12,2) AS $$
  SELECT coalesce(sum(l.total), 0)
  FROM commande co JOIN ligne_commande l ON l.commande_id = co.id
  WHERE co.client_id = cid
$$ LANGUAGE sql;

SELECT * FROM total_client(3);                        -- column "total_client": 31.50
SELECT * FROM total_client(3) AS t;                   -- column "t"
SELECT t.montant FROM total_client(3) AS t (montant); -- column "montant"
SELECT c.nom, t FROM client c, total_client(c.id) AS t ORDER BY c.nom;

 nom    | t
--------+--------
 Alice  | 25.90
 Bob    | 126.00
 Chloé  | 31.50
 David  | 0.00
 Fatima | 0.00
 Émile  | 0.00

CREATE FUNCTION commandes_de(cid integer) RETURNS TABLE (num integer, jour date, etat varchar) AS $$
  SELECT id, date_cmd, etat FROM commande WHERE client_id = cid ORDER BY id
$$ LANGUAGE sql;

SELECT * FROM commandes_de(1);

 num | jour       | etat
-----+------------+---------
 1   | 2026-09-01 | livrée
 2   | 2026-09-15 | ouverte

CREATE FUNCTION articles_actifs() RETURNS SETOF article AS $$
  SELECT * FROM article WHERE actif ORDER BY id
$$ LANGUAGE sql;

SELECT code, prix FROM articles_actifs() WHERE prix > 2;     -- CA-01 3.50, CL-01 6.90, TR-01 2.10

SELECT * FROM unnest(ARRAY['rouge', 'vert']) WITH ORDINALITY AS c (couleur, rang);
-- rouge | 1 ; vert | 2
SELECT c.nom, v FROM client c, unnest(ARRAY[c.solde, c.solde * 2]) AS v
WHERE c.id <= 2 ORDER BY c.nom, v;
-- Alice 100.50, Alice 201.00, Bob -40.00, Bob -20.00
SELECT jour, n FROM generate_series(1, 3) WITH ORDINALITY AS g (jour, n);
SELECT * FROM regexp_split_to_table('a,b;c', '[,;]') WITH ORDINALITY AS t (morceau, n);
-- a | 1 ; b | 2 ; c | 3
SELECT * FROM jsonb_each('{"b": 2, "a": 1}') AS e;           -- a | 1 ; b | 2 (keys sorted)
SELECT * FROM json_each_text('{"b": 2, "a": "x"}') AS e (k, v);   -- b | 2 ; a | x
SELECT c.nom, e.key FROM client c, jsonb_each(jsonb_build_object('id', c.id)) AS e
WHERE c.id = 1;                                               -- Alice | id

SELECT nom, solde FROM client WHERE solde >= 10 AND solde <> 75.25 ORDER BY id;
-- Alice 100.50, David 250.00, Fatima 10.00

SELECT libelle FROM article WHERE actif IS TRUE AND stock > 100 ORDER BY id;
-- Stylo bille, Cahier 96 pages, Gomme

SELECT id FROM commande WHERE date_cmd BETWEEN date '2026-09-01' AND date '2026-09-30' ORDER BY id;   -- 1, 2, 3
SELECT id FROM commande WHERE date_cmd > date '2026-10-05' - interval '1 month' ORDER BY id;          -- 2, 3, 4, 5
SELECT id FROM commande WHERE extract(month FROM date_cmd) = 9 ORDER BY id;                           -- 1, 2, 3

SELECT NULL = NULL AS a, NULL IS NULL AS b, 1 = NULL AS c, NULL AND false AS d,
       NULL OR true AS e, NOT NULL AS f, 1 IN (2, NULL) AS g, 1 NOT IN (2, NULL) AS h;

 a    | b | c    | d | e | f    | g    | h
------+---+------+---+---+------+------+------
 NULL | t | NULL | f | t | NULL | NULL | NULL

SELECT nom FROM client WHERE ville <> 'Oran' ORDER BY id;                -- Bob, David, Émile
SELECT nom FROM client WHERE ville IS DISTINCT FROM 'Oran' ORDER BY id;  -- Bob, David, Émile, Fatima
SELECT nom FROM client WHERE ville IS NULL;                              -- Fatima

SELECT 'a' = 'A' AS casse, 'e' = 'é' AS accent, 'a' = 'a ' AS espace, 'a' < 'B' AS ordre;

 casse | accent | espace | ordre
-------+--------+--------+-------
 f     | f      | t      | f

SELECT nom FROM client WHERE nom = 'emile';                              -- no rows
SELECT nom FROM client WHERE nom = 'Émile';                              -- Émile

SELECT nom FROM client WHERE nom LIKE 'A%';                 -- Alice
SELECT nom FROM client WHERE nom LIKE 'a%';                 -- no rows
SELECT nom FROM client WHERE nom LIKE '_o_';                -- Bob
SELECT nom FROM client WHERE nom ILIKE 'ALI%';              -- Alice
SELECT nom FROM client WHERE nom ILIKE 'emile';             -- Émile
SELECT nom FROM client WHERE nom ~ '^[A-C]' ORDER BY id;    -- Alice, Bob, Chloé
SELECT nom FROM client WHERE nom ~* '^[a-c]' ORDER BY id;   -- Alice, Bob, Chloé
SELECT nom FROM client WHERE nom ~* '^e';                   -- no rows
SELECT nom FROM client WHERE nom !~ 'e$' ORDER BY id;       -- Bob, Chloé, David, Fatima
SELECT nom FROM client WHERE nom !~~* 'b%' ORDER BY id;     -- Alice, Chloé, David, Émile, Fatima

CREATE TABLE fichier (nom text);
INSERT INTO fichier VALUES ('rapport_2026.pdf'), ('rapport-2026.pdf'), ('100%.txt'), ('Rapport_final.PDF');

SELECT nom FROM fichier WHERE nom ILIKE 'rapport\_%' ORDER BY nom;           -- Rapport_final.PDF, rapport_2026.pdf
SELECT nom FROM fichier WHERE nom LIKE 'rapport#_%' ESCAPE '#' ORDER BY nom; -- rapport_2026.pdf
SELECT nom FROM fichier WHERE nom LIKE '100\%%';                             -- 100%.txt
SELECT nom FROM fichier WHERE nom ILIKE '%.pdf' ORDER BY nom;                -- 3 rows, including Rapport_final.PDF
SELECT nom FROM fichier WHERE nom LIKE '%.pdf' ORDER BY nom;                 -- rapport-2026.pdf, rapport_2026.pdf
SELECT nom FROM fichier WHERE nom ~ '^rapport' ORDER BY nom;                 -- rapport-2026.pdf, rapport_2026.pdf

SELECT nom FROM client WHERE nom COLLATE "C" = 'alice';                     -- no rows
SELECT nom FROM client WHERE nom = 'alice' COLLATE utf8mb4_general_ci;      -- Alice
SELECT nom FROM client WHERE lower(nom) = 'alice';                          -- Alice

SELECT nom FROM client WHERE solde = 'abc';
-- ERROR 22P02: invalid input syntax for type numeric: "abc"

SELECT ville, count(*) AS n, sum(solde) AS total, avg(solde) AS moyenne,
       min(solde) AS mini, max(solde) AS maxi
FROM client GROUP BY ville ORDER BY ville;

 ville       | n | total  | moyenne              | mini   | maxi
-------------+---+--------+----------------------+--------+--------
 Alger       | 2 | 55.25  | 27.6250000000000000  | -20.00 | 75.25
 Constantine | 1 | 250.00 | 250.0000000000000000 | 250.00 | 250.00
 Oran        | 2 | 100.50 | 50.2500000000000000  | 0.00   | 100.50
 NULL        | 1 | 10.00  | 10.0000000000000000  | 10.00  | 10.00

SELECT ville, sum(solde) AS total FROM client
WHERE solde > 0                    -- filters the rows, before grouping
GROUP BY ville
HAVING sum(solde) > 50             -- filters the groups
ORDER BY total DESC;

 ville       | total
-------------+--------
 Constantine | 250.00
 Oran        | 100.50
 Alger       | 75.25

-- revenue per client
SELECT c.nom, count(DISTINCT co.id) AS commandes, sum(l.total) AS montant
FROM client c
JOIN commande co ON co.client_id = c.id
JOIN ligne_commande l ON l.commande_id = co.id
GROUP BY c.id, c.nom
ORDER BY montant DESC;

 nom   | commandes | montant
-------+-----------+---------
 Bob   | 1         | 126.00
 Chloé | 1         | 31.50
 Alice | 2         | 25.90

SELECT extract(month FROM date_cmd) AS mois, count(*) AS n
FROM commande GROUP BY extract(month FROM date_cmd) ORDER BY mois;      -- 9 | 3, 10 | 2

SELECT extract(month FROM date_cmd) AS mois, count(*) AS n
FROM commande GROUP BY mois ORDER BY mois;                              -- alias

SELECT to_char(date_cmd, 'YYYY-MM') AS mois, count(*) AS n
FROM commande GROUP BY 1 ORDER BY 1;                                    -- position: 2026-09 | 3, 2026-10 | 2

SELECT ville, nom FROM client GROUP BY ville;
-- ERROR 42803: 'nom' isn't in GROUP BY

SELECT c.id, c.nom, c.ville, count(o.id) AS commandes
FROM client c LEFT JOIN commande o ON o.client_id = c.id
GROUP BY c.id
ORDER BY c.nom;

SELECT count(*) AS c, sum(solde) AS s, avg(solde) AS a FROM client WHERE id > 100;   -- 0 | NULL | NULL
SELECT count(*) AS n FROM client HAVING count(*) > 100;                              -- no rows

SELECT ville, string_agg(nom, ', ' ORDER BY nom) AS noms FROM client GROUP BY ville ORDER BY ville;

 ville       | noms
-------------+--------------
 Alger       | Bob, Émile
 Constantine | David
 Oran        | Alice, Chloé
 NULL        | Fatima

SELECT ville,
       count(*) FILTER (WHERE solde > 0) AS crediteurs,
       sum(solde) FILTER (WHERE solde > 50) AS gros
FROM client GROUP BY ville ORDER BY ville;

 ville       | crediteurs | gros
-------------+------------+--------
 Alger       | 1          | 75.25
 Constantine | 1          | 250.00
 Oran        | 1          | 100.50
 NULL        | 1          | NULL

SELECT ville, array_agg(id ORDER BY id) AS ids FROM client GROUP BY ville ORDER BY ville;

 ville       | ids
-------------+-------
 Alger       | {2,5}
 Constantine | {4}
 Oran        | {1,3}
 NULL        | {6}

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY solde) AS mediane,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY solde) AS mediane_d,
       rank(100) WITHIN GROUP (ORDER BY solde DESC) AS rang_de_100
FROM client;

 mediane | mediane_d | rang_de_100
---------+-----------+-------------
 42.625  | 10.00     | 3

SELECT ville, etat, count(*) AS n
FROM client c JOIN commande co ON co.client_id = c.id
GROUP BY ROLLUP (ville, etat)
ORDER BY ville NULLS LAST, etat NULLS LAST;

 ville | etat    | n
-------+---------+---
 Alger | annulée | 1
 Alger | livrée  | 1
 Alger | NULL    | 2
 Oran  | livrée  | 1
 Oran  | ouverte | 2
 Oran  | NULL    | 3
 NULL  | NULL    | 5

SELECT ville, count(*) AS n, grouping(ville) AS g FROM client GROUP BY ROLLUP (ville) ORDER BY g, ville;

 ville       | n | g
-------------+---+---
 Alger       | 2 | 0
 Constantine | 1 | 0
 Oran        | 2 | 0
 NULL        | 1 | 0
 NULL        | 6 | 1

SELECT nom, solde FROM client ORDER BY solde DESC, nom;                   -- David, Alice, Émile, Fatima, Chloé, Bob
SELECT nom, solde * 2 AS double FROM client ORDER BY double DESC LIMIT 2; -- alias
SELECT nom, solde FROM client ORDER BY 2 DESC LIMIT 2;                    -- position
SELECT nom FROM client ORDER BY length(nom), nom;                         -- expression
SELECT ville, count(*) AS n FROM client GROUP BY ville ORDER BY count(*) DESC, ville;   -- aggregate

SELECT nom, ville FROM client ORDER BY ville, nom;

 nom    | ville
--------+-------------
 Bob    | Alger
 Émile  | Alger
 David  | Constantine
 Alice  | Oran
 Chloé  | Oran
 Fatima | NULL

SELECT nom, ville FROM client ORDER BY ville NULLS FIRST, nom;        -- Fatima first
SELECT nom, ville FROM client ORDER BY ville DESC NULLS LAST, nom;    -- Alice, Chloé, David, Bob, Émile, Fatima

SELECT nom FROM client ORDER BY nom;                     -- Alice, Bob, Chloé, David, Fatima, Émile
SELECT x FROM (VALUES ('b'), ('B'), ('a'), ('A'), ('é'), ('e')) AS v (x) ORDER BY x;
-- A, B, a, b, e, é

SELECT id FROM client ORDER BY id LIMIT 2;                -- 1, 2
SELECT id FROM client ORDER BY id LIMIT 2 OFFSET 3;       -- 4, 5
SELECT id FROM client ORDER BY id OFFSET 4;               -- 5, 6   (OFFSET alone)
SELECT id FROM client ORDER BY id OFFSET 1 LIMIT 2;       -- 2, 3   (OFFSET before LIMIT)
SELECT id FROM client ORDER BY id LIMIT ALL OFFSET 5;     -- 6
SELECT id FROM client ORDER BY id LIMIT 0;                -- no rows

PREPARE page (int, int) AS SELECT id FROM client ORDER BY id LIMIT $1 OFFSET $2;
EXECUTE page(2, 1);                                       -- 2, 3

SELECT id FROM client ORDER BY id FETCH FIRST 2 ROWS ONLY;                    -- 1, 2
SELECT id FROM client ORDER BY id OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;       -- 3, 4
SELECT id FROM client ORDER BY id FETCH FIRST ROW ONLY;                       -- 1

SELECT ville, nom FROM client ORDER BY ville FETCH FIRST 1 ROWS WITH TIES;

 ville | nom
-------+-------
 Alger | Bob
 Alger | Émile

-- scalar, uncorrelated
SELECT nom, solde FROM client WHERE solde > (SELECT avg(solde) FROM client) ORDER BY id;
-- Alice 100.50, David 250.00, Émile 75.25

-- scalar, correlated, in the list
SELECT nom, (SELECT count(*) FROM commande co WHERE co.client_id = c.id) AS commandes,
            (SELECT max(date_cmd) FROM commande co WHERE co.client_id = c.id) AS derniere
FROM client c ORDER BY c.id;

 nom    | commandes | derniere
--------+-----------+------------
 Alice  | 2         | 2026-09-15
 Bob    | 1         | 2026-09-20
 Chloé  | 1         | 2026-10-02
 David  | 0         | NULL
 Émile  | 1         | 2026-10-05
 Fatima | 0         | NULL

SELECT nom FROM client WHERE id IN (SELECT client_id FROM commande WHERE etat = 'livrée') ORDER BY id;
-- Alice, Bob

SELECT nom FROM client c
WHERE EXISTS (SELECT 1 FROM commande co WHERE co.client_id = c.id AND co.etat = 'ouverte') ORDER BY id;
-- Alice, Chloé

SELECT nom FROM client c
WHERE NOT EXISTS (SELECT 1 FROM commande co WHERE co.client_id = c.id) ORDER BY id;
-- David, Fatima

SELECT libelle, prix FROM article WHERE prix >= ALL (SELECT prix FROM article);     -- Agrafeuse 14.00
SELECT libelle, prix FROM article
WHERE prix > ANY (SELECT prix_unit FROM ligne_commande WHERE commande_id = 4) ORDER BY id;
-- Classeur 6.90, Agrafeuse 14.00

SELECT nom FROM client WHERE (id, ville) IN (SELECT client_id, 'Oran' FROM commande) ORDER BY id;
-- Alice, Chloé

-- the biggest line of each article
SELECT a.libelle, l.quantite
FROM article a JOIN ligne_commande l ON l.article_id = a.id
WHERE l.quantite = (SELECT max(l2.quantite) FROM ligne_commande l2 WHERE l2.article_id = a.id)
ORDER BY a.id;

query {UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] query [...]
    [ORDER BY ...] [LIMIT n] [OFFSET m]

SELECT ville FROM client UNION SELECT ville FROM ville_info ORDER BY ville;
-- Alger, Annaba, Constantine, Oran, NULL
SELECT ville FROM client INTERSECT SELECT ville FROM ville_info ORDER BY ville;
-- Alger, Oran
SELECT ville FROM client EXCEPT SELECT ville FROM ville_info ORDER BY ville;
-- Constantine, NULL
SELECT ville FROM client EXCEPT ALL SELECT ville FROM ville_info ORDER BY ville;
-- Alger, Constantine, Oran, NULL

SELECT 1 AS n UNION ALL SELECT 2 INTERSECT SELECT 2;          -- 1, 2
(SELECT 1 AS n UNION ALL SELECT 2) INTERSECT SELECT 2;        -- 2

(SELECT nom FROM client ORDER BY solde DESC LIMIT 1)
UNION ALL
(SELECT nom FROM client ORDER BY solde LIMIT 1);              -- David, Bob

SELECT 1 UNION SELECT '2';                                    -- 1, 2 (integer)
SELECT 1 UNION SELECT 'x';                                    -- 22P02
SELECT 1 UNION SELECT '99999999999';                          -- 22003 (integer)
SELECT '1.5' UNION SELECT 1 UNION SELECT 2.5;                 -- 22P02 ('1.5' read as an integer)
SELECT INTERVAL '1 day' UNION SELECT '24:00:00';              -- 1 day (interval)
VALUES (DATE '2026-01-02'), ('2026-03-04');                   -- two dates

WITH total_cmd AS (
    SELECT commande_id, sum(total) AS montant FROM ligne_commande GROUP BY commande_id
), par_client AS (
    SELECT co.client_id, sum(t.montant) AS montant
    FROM commande co JOIN total_cmd t ON t.commande_id = co.id
    GROUP BY co.client_id
)
SELECT c.nom, p.montant
FROM client c JOIN par_client p ON p.client_id = c.id
ORDER BY p.montant DESC;

 nom   | montant
-------+---------
 Bob   | 126.00
 Chloé | 31.50
 Alice | 25.90

WITH RECURSIVE r (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM r WHERE n < 5
)
SELECT n FROM r ORDER BY n;                                   -- 1, 2, 3, 4, 5

CREATE TABLE categorie (
    id        integer PRIMARY KEY,
    parent_id integer REFERENCES categorie (id),
    nom       text NOT NULL
);
INSERT INTO categorie VALUES
    (1, NULL, 'Papeterie'), (2, 1, 'Écriture'), (3, 1, 'Classement'), (4, 2, 'Stylos'), (5, 2, 'Crayons');

WITH RECURSIVE arbre (id, nom, niveau, chemin) AS (
    SELECT id, nom, 0, nom FROM categorie WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.nom, a.niveau + 1, a.chemin || ' > ' || c.nom
    FROM categorie c JOIN arbre a ON c.parent_id = a.id
)
SELECT id, niveau, chemin FROM arbre ORDER BY chemin;

 id | niveau | chemin
----+--------+--------------------------------
 1  | 0      | Papeterie
 3  | 1      | Papeterie > Classement
 2  | 1      | Papeterie > Écriture
 5  | 2      | Papeterie > Écriture > Crayons
 4  | 2      | Papeterie > Écriture > Stylos

function(...) [FILTER (WHERE condition)] OVER (
    [PARTITION BY expr [, ...]]
    [ORDER BY expr [ASC | DESC] [, ...]]
    [{ROWS | RANGE} BETWEEN bound AND bound]
)
function(...) OVER window_name          -- with WINDOW name AS (...)

SELECT nom, ville, solde,
       row_number() OVER (ORDER BY solde DESC) AS rang,
       rank() OVER (PARTITION BY ville ORDER BY solde DESC) AS rang_ville
FROM client ORDER BY rang;

 nom    | ville       | solde  | rang | rang_ville
--------+-------------+--------+------+------------
 David  | Constantine | 250.00 | 1    | 1
 Alice  | Oran        | 100.50 | 2    | 1
 Émile  | Alger       | 75.25  | 3    | 1
 Fatima | NULL        | 10.00  | 4    | 1
 Chloé  | Oran        | 0.00   | 5    | 2
 Bob    | Alger       | -20.00 | 6    | 2

SELECT etat, id, rank() OVER w AS r, dense_rank() OVER w AS dr, row_number() OVER w AS rn
FROM commande WINDOW w AS (ORDER BY etat) ORDER BY id;

 etat    | id | r | dr | rn
---------+----+---+----+----
 livrée  | 1  | 2 | 2  | 2
 ouverte | 2  | 4 | 3  | 4
 livrée  | 3  | 2 | 2  | 3
 ouverte | 4  | 4 | 3  | 5
 annulée | 5  | 1 | 1  | 1

SELECT co.id, t.montant,
       sum(t.montant) OVER (ORDER BY co.date_cmd) AS cumul,
       round(100 * t.montant / sum(t.montant) OVER (), 1) AS part
FROM commande co
JOIN (SELECT commande_id, sum(total) AS montant FROM ligne_commande GROUP BY commande_id) t
  ON t.commande_id = co.id
ORDER BY co.date_cmd;

 id | montant | cumul  | part
----+---------+--------+------
 1  | 19.00   | 19.00  | 10.4
 2  | 6.90    | 25.90  | 3.8
 3  | 126.00  | 151.90 | 68.7
 4  | 31.50   | 183.40 | 17.2

SELECT id, avg(id) OVER (ORDER BY date_cmd ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moy_glissante
FROM commande ORDER BY date_cmd;
-- 1.5, 2.0, 3.0, 4.0, 4.5 (with sixteen decimals)

SELECT id, count(*) FILTER (WHERE etat = 'livrée') OVER (ORDER BY id) AS livrees
FROM commande ORDER BY id;                                    -- 1, 1, 2, 2, 2

SELECT ville, count(*) AS n, rank() OVER (ORDER BY count(*) DESC) AS rang
FROM client GROUP BY ville ORDER BY rang, ville;
-- Alger 2 1, Oran 2 1, Constantine 1 3, NULL 1 3

SELECT id, date_cmd,
       lag(date_cmd) OVER (ORDER BY date_cmd) AS precedente,
       date_cmd - lag(date_cmd) OVER (ORDER BY date_cmd) AS jours,
       lead(id, 1, 0) OVER (ORDER BY date_cmd) AS suivante
FROM commande ORDER BY date_cmd;

 id | date_cmd   | precedente | jours | suivante
----+------------+------------+-------+----------
 1  | 2026-09-01 | NULL       | NULL  | 2
 2  | 2026-09-15 | 2026-09-01 | 14    | 3
 3  | 2026-09-20 | 2026-09-15 | 5     | 4
 4  | 2026-10-02 | 2026-09-20 | 12    | 5
 5  | 2026-10-05 | 2026-10-02 | 3     | 0

SELECT code, prix,
       first_value(code) OVER w AS moins_cher,
       last_value(code) OVER (ORDER BY prix ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS plus_cher,
       nth_value(code, 2) OVER w AS deuxieme
FROM article WINDOW w AS (ORDER BY prix) ORDER BY prix;

 code  | prix  | moins_cher | plus_cher | deuxieme
-------+-------+------------+-----------+----------
 GO-01 | 0.80  | GO-01      | AG-01     | NULL
 ST-01 | 1.20  | GO-01      | AG-01     | ST-01
 TR-01 | 2.10  | GO-01      | AG-01     | ST-01
 CA-01 | 3.50  | GO-01      | AG-01     | ST-01
 CL-01 | 6.90  | GO-01      | AG-01     | ST-01
 AG-01 | 14.00 | GO-01      | AG-01     | ST-01

SELECT ville, nom, solde
FROM (SELECT ville, nom, solde,
             row_number() OVER (PARTITION BY ville ORDER BY solde DESC) AS rn
      FROM client) x
WHERE rn = 1
ORDER BY ville;

 ville       | nom    | solde
-------------+--------+--------
 Alger       | Émile  | 75.25
 Constantine | David  | 250.00
 Oran        | Alice  | 100.50
 NULL        | Fatima | 10.00

SELECT ... FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF table [, ...]] [NOWAIT | SKIP LOCKED]

BEGIN;
SELECT id, stock FROM article WHERE id = 1 FOR UPDATE;      -- 1 | 500: the row is held
UPDATE article SET stock = stock - 10 WHERE id = 1;
COMMIT;

BEGIN;
SELECT id FROM commande WHERE etat = 'ouverte' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- process the returned order, then:
UPDATE commande SET etat = 'livrée' WHERE id = 2;
COMMIT;

CREATE VIEW v_ca AS
SELECT c.id, c.nom, sum(l.total) AS ca
FROM client c
JOIN commande co ON co.client_id = c.id
JOIN ligne_commande l ON l.commande_id = co.id
GROUP BY c.id, c.nom;

SELECT nom, ca, rank() OVER (ORDER BY ca DESC) AS rang FROM v_ca ORDER BY rang;

 nom   | ca     | rang
-------+--------+------
 Bob   | 126.00 | 1
 Chloé | 31.50  | 2
 Alice | 25.90  | 3

SELECT ville, nom, solde FROM client
QUALIFY row_number() OVER (PARTITION BY ville ORDER BY solde DESC) = 1
ORDER BY ville;
-- same result as "First of each group" (35.10)

CREATE FULLTEXT INDEX ft_lib ON article (libelle);
SELECT code, libelle FROM article WHERE MATCH (libelle) AGAINST ('pages');    -- CA-01 | Cahier 96 pages
SELECT code, libelle FROM article WHERE MATCH (libelle) AGAINST ('cahier');   -- no rows: "Cahier"

EXPLAIN SELECT c.nom, co.id FROM client c JOIN commande co ON co.client_id = c.id WHERE c.ville = 'Oran';

 id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra
----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------
 1  | SIMPLE      | c     | ALL  | PRIMARY,email | NULL | NULL    | NULL | 6    | Using where
 1  | SIMPLE      | co    | ALL  | PRIMARY       | NULL | NULL    | NULL | 5    | Using join buffer (hash join)

EXPLAIN SELECT * FROM client WHERE id = 1;

 id | select_type | table  | type   | possible_keys | key     | key_len | ref   | rows | Extra
----+-------------+--------+--------+---------------+---------+---------+-------+------+-------
 1  | SIMPLE      | client | eq_ref | PRIMARY,email | PRIMARY | NULL    | const | 1    |

EXPLAIN ANALYZE SELECT * FROM client WHERE ville = 'Oran';

-> Project  (actual time=0.010..0.011 rows=2 loops=1)
    -> Table scan on client with pushed-down filter  (rows=6)  (actual time=0.009..0.009 rows=2 loops=1)