SQL reference

Essential syntax

Référence rapide pour retrouver les structures utilisées dans les exercises : filtres, jointures, agrégations, VAT, marge et impayés.

Quick reference

SQL patterns used most often in the exercises

Use this page to find syntax quickly without interrupting your practice. Every block can be copied directly.

Query a table

SELECT colonne1, colonne2
FROM table;

Filter

SELECT *
FROM factures
WHERE montant_ttc > 5000;

Sort

SELECT *
FROM factures
ORDER BY montant_ttc DESC;

Join

SELECT c.nom_client, f.montant_ttc
FROM clients c
JOIN factures f ON c.id_client = f.id_client;

Aggregation

SELECT c.nom_client, SUM(f.montant_ht) AS ca_ht
FROM clients c
JOIN factures f ON c.id_client = f.id_client
GROUP BY c.nom_client;

Filter after aggregation

SELECT c.nom_client, SUM(f.montant_ttc) AS total_ttc
FROM clients c
JOIN factures f ON c.id_client = f.id_client
GROUP BY c.nom_client
HAVING total_ttc > 10000;

NULL values

SELECT COALESCE(SUM(r.montant_regle), 0) AS montant_regle
FROM reglements r;

Net VAT payable

SELECT
(SELECT SUM(tva) FROM factures) -
(SELECT SUM(tva_deductible) FROM factures_achats)
AS tva_a_decaisser;

Gross margin

SELECT p.nom_produit,
SUM(l.quantite * l.prix_unitaire_ht)
- SUM(l.quantite * p.cout_achat_ht) AS marge
FROM produits p
JOIN lignes_ventes l ON p.id_produit = l.id_produit
GROUP BY p.nom_produit;