Requêtes SQL — Modèles courants

SELECT u.nom, o.total
FROM utilisateurs u
INNER JOIN commandes o ON u.id = o.utilisateur_id
WHERE o.cree_a > '2024-01-01';

SELECT u.nom, COUNT(o.id) AS nb_commandes
FROM utilisateurs u
LEFT JOIN commandes o ON u.id = o.utilisateur_id
  AND o.cree_a > '2024-01-01'
GROUP BY u.id, u.nom;

SELECT nom FROM utilisateurs u
WHERE EXISTS (
    SELECT 1 FROM commandes c
    WHERE c.utilisateur_id = u.id AND c.statut = 'annule'
);

SELECT nom,
    (SELECT COUNT(*) FROM commandes
     WHERE commandes.utilisateur_id = utilisateurs.id) AS nb_commandes
FROM utilisateurs;

SELECT departement, AVG(salaire) AS moy_salaire
FROM employes
GROUP BY departement
HAVING AVG(salaire) > 50000;

SELECT departement,
    COUNT(*) AS total,
    COUNT(CASE WHEN statut = 'actif' THEN 1 END) AS actifs,
    ROUND(100.0 * COUNT(CASE WHEN statut = 'actif' THEN 1 END) / COUNT(*), 1) AS pct_actifs
FROM employes
GROUP BY departement;

WITH commandes_annuelles AS (
    SELECT EXTRACT(YEAR FROM cree_a) AS annee, COUNT(*) AS total
    FROM commandes
    GROUP BY EXTRACT(YEAR FROM cree_a)
)
SELECT annee, total,
    LAG(total) OVER (ORDER BY annee) AS annee_precedente
FROM commandes_annuelles;

WITH RECURSIVE arbo AS (
    SELECT id, nom, parent_id, 1 AS niveau
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    SELECT c.id, c.nom, c.parent_id, a.niveau + 1
    FROM categories c
    INNER JOIN arbo a ON c.parent_id = a.id
)
SELECT * FROM arbo ORDER BY niveau, nom;

-- Concaténer avec séparateur (PostgreSQL)
SELECT nom || ' ' || prenom AS nom_complet FROM utilisateurs;
 
-- COALESCE : retourne la première valeur non-null
SELECT nom,
    COALESCE(telephone, email, 'N/C') AS moyen_contact
FROM utilisateurs;
 
-- NULLIF : retourne NULL si les valeurs sont égales
-- Utile pour éviter les divisions par zéro
SELECT montant,
    ROUND(montant / NULLIF(nb_clients, 0), 2) AS par_client
FROM statistiques;

-- PostgreSQL
SELECT TO_CHAR(date_cree, 'YYYY-MM-DD') AS date_fmt
     , UPPER(nom)                          AS nom_majuscule
     , TRIM(espace_depart)                 AS nom_nettoye
FROM users;
 
-- MySQL
SELECT DATE_FORMAT(date_cree, '%Y-%m-%d') AS date_fmt
     , CONCAT(prénom, ' ', nom)            AS complet
     , TRIM(Espace_depart)                 AS propre
FROM users;

-- Produit cartésien : chaque client × chaque produit
SELECT c.nom, p.nom AS produit
FROM clients c
CROSS JOIN produits p;

-- Trouver les paires d'employés du même département
SELECT e1.nom AS emp1, e2.nom AS emp2, e1.departement
FROM employes e1
INNER JOIN employes e2
    ON e1.departement = e2.departement
   AND e1.id < e2.id;

-- Numéroter chaque ligne dans chaque département
SELECT nom, departement, salaire,
    ROW_NUMBER() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang
FROM employes;

-- Ventes cumulées par jour
SELECT date, ventes_jour,
    SUM(ventes_jour) OVER (ORDER BY date) AS ventes_cumulees
FROM ventes;

-- PostgreSQL (recommandé)
INSERT INTO inventaire (produit_id, quantite)
VALUES (42, 10)
ON CONFLICT (produit_id)
DO UPDATE SET quantite = inventaire.quantite + 10;
 
-- MySQL (alternative)
INSERT INTO inventaire (produit_id, quantite)
VALUES (42, 10)
ON DUPLICATE KEY UPDATE quantite = quantite + 10;
 
-- SQLite (alternative)
INSERT INTO inventaire (produit_id, quantite)
VALUES (42, 10)
ON CONFLICT(produit_id) DO UPDATE SET quantite = excluded.quantite;

-- Ventes par région et par trimestre
SELECT produit,
    SUM(CASE WHEN trimestre = 'T1' THEN montant END) AS t1,
    SUM(CASE WHEN trimestre = 'T2' THEN montant END) AS t2,
    SUM(CASE WHEN trimestre = 'T3' THEN montant END) AS t3,
    SUM(CASE WHEN trimestre = 'T4' THEN montant END) AS t4
FROM ventes
GROUP BY produit;

-- PostgreSQL : dépliage en lignes
SELECT unnest(string_to_array('a,b,c', ',')) AS valeur;
 
-- PostgreSQL : split en colonnes
SELECT (string_to_array('2024-01-15,Paris,500', ','))[1] AS date
     , (string_to_array('2024-01-15,Paris,500', ','))[2] AS ville
     , (string_to_array('2024-01-15,Paris,500', ','))[3] AS montant;
 
-- MySQL : SUBSTRING_INDEX (pas de split natif avant 8.0.31)
SELECT TRIM(SUBSTRING_INDEX('a,b,c', ',', 1)) AS premier;

SELECT nom, departement, salaire,
    RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang_departement
FROM employes;

SELECT date, valeur,
    LAG(valeur) OVER (ORDER BY date) AS valeur_hier,
    valeur - LAG(valeur) OVER (ORDER BY date) AS delta
FROM mesures
ORDER BY date;

SELECT categorie,
    FIRST_VALUE(montant) OVER w AS premier,
    LAST_VALUE(montant) OVER w AS dernier
FROM transactions
WINDOW w AS (PARTITION BY categorie ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);