SQL — Du SELECT à l'Analyse avancée

Votre premier SELECT

Affiche toutes les colonnes et toutes les lignes de la table etudiants (utilise l'étoile *).

Colonnes spécifiques

Affiche uniquement les colonnes nom et note de la table etudiants.

Filtrer avec WHERE

Affiche toutes les colonnes des etudiants dont la note est strictement supérieure à 12 (colonne note).

WHERE avec texte

Affiche tous les etudiants dont la ville est 'Paris' (colonne ville — texte entre apostrophes).

ORDER BY

Affiche tous les etudiants triés du meilleur au moins bon selon leur note (tri décroissant).

LIMIT

Affiche les 5 etudiants ayant les meilleures notes : trie par note décroissante et limite le résultat à 5 lignes.

COUNT

Compte le nombre total d'etudiants. Nomme la colonne du résultat total.

SUM et AVG

Calcule la somme et la moyenne des notes des etudiants. Nomme les colonnes somme et moyenne.

MIN et MAX

Affiche la note la plus basse et la plus haute des etudiants. Nomme les colonnes min_note et max_note.

GROUP BY

Compte le nombre d'etudiants par ville. Affiche la ville et le compte (nommé nb), en groupant par ville.

GROUP BY + AVG

Calcule la note moyenne par ville. Affiche la ville et la moyenne (nommée moyenne), en groupant par ville.

HAVING

Affiche les villes ayant strictement plus de 2 etudiants. Affiche la ville et le compte (nommé nb) ; le filtre s'applique après le regroupement.

LIKE

Affiche les etudiants dont le nom commence par la lettre A (LIKE 'A%').

IN

Affiche les etudiants dont la ville est Paris, Lyon ou Bordeaux (opérateur IN).

BETWEEN

Affiche les etudiants dont la note est comprise entre 10 et 15 inclus (bornes comprises).

IS NULL

Affiche les etudiants sans email renseigné (email IS NULL).

COALESCE

Affiche le nom et l'email de chaque etudiant, en remplaçant les emails NULL par le texte 'non renseigné'. Nomme la colonne du résultat email (COALESCE).

DISTINCT

Affiche la liste des villes sans doublon, triées par ordre alphabétique.

AS - alias

Affichez les colonnes de la table etudiants en les renommant avec des alias (AS) : nom -> "Nom complet", note -> "Score", ville -> "Localisation". Utilise des guillemets doubles pour les alias contenant un espace ou une majuscule.

INSERT

Insère un nouvel etudiant avec les colonnes nom, note, ville : nom 'Emma', note 16, ville 'Nantes'.

UPDATE

Mets la note de l'etudiant nommé 'Alice' à 18.

DELETE

Supprime les etudiants dont la note est strictement inférieure à 5.

CREATE TABLE

Crée une table salles avec 4 colonnes : id (clé primaire auto-incrémentée, SERIAL), nom (texte obligatoire, VARCHAR NOT NULL), batiment (texte), capacite (entier INT).

ALTER TABLE

Ajoute une colonne telephone de type VARCHAR(20) à la table etudiants (ALTER TABLE ... ADD COLUMN).

JOIN INNER

Affiche le nom de chaque étudiant avec le nom de son cours. Joins etudiants → inscriptions (via etudiant_id) → cours (via cours_id) en INNER JOIN. Donne un alias court à chaque table (les lettres sont au choix) puis affiche deux colonnes : le nom de l'étudiant et le nom du cours.

LEFT JOIN

Affiche tous les étudiants et le nom de leurs cours, y compris ceux sans inscription (LEFT JOIN au lieu de INNER JOIN). Mêmes tables et alias au choix que l'exercice précédent ; les colonnes du cours valent NULL si l'étudiant n'a aucune inscription.

Sous-requête

Affiche le nom et la note des etudiants dont la note dépasse la moyenne générale (calcule cette moyenne avec une sous-requête). Colonnes : nom, note.

Sous-requête IN

Affiche le nom des etudiants inscrits au cours 'Python'. Passe par la table inscriptions (etudiant_id, cours_id) et retrouve l'id du cours 'Python' dans la table cours, via une sous-requête avec IN.

CASE WHEN

Affiche nom, note et une colonne mention calculée avec CASE selon la note : ≥16 → 'Excellent', ≥12 → 'Bien', ≥10 → 'Moyen', sinon → 'Insuffisant'. Nomme la colonne mention.

CTE (WITH)

Avec une CTE (WITH), classe les etudiants par note décroissante grâce à une fonction de rang (colonne nommée rang), puis n'affiche que les 3 premiers. Colonnes : nom, note, rang.

Fonctions fenêtre - ROW_NUMBER

Numérote les etudiants à l'intérieur de chaque ville, du meilleur au moins bon, avec une fonction fenêtre. Nomme cette colonne rang_ville. Colonnes : nom, ville, note, rang_ville.

LAG et LEAD

Sur la table ventes (colonnes date_vente, montant), affiche pour chaque vente le montant de la vente précédente et de la suivante, triées par date_vente. Nomme les colonnes vente_precedente et vente_suivante.

SUM cumulatif

Sur la table ventes, calcule le chiffre d'affaires cumulé au fil des dates (somme progressive du montant avec une fonction fenêtre). Colonnes : date_vente, montant, ca_cumule.

PERCENTILE

Calcule la médiane des notes des etudiants avec la fonction PERCENTILE_CONT. Nomme la colonne mediane.

Date functions

Affiche le nom et la date d'inscription des etudiants inscrits il y a moins de 30 jours : date_inscription >= CURRENT_DATE - INTERVAL '30 days'. Colonnes : nom, date_inscription.

EXTRACT

Compte le nombre d'inscriptions par mois et année, en extrayant l'année et le mois de date_inscription. Colonnes : annee, mois, nb. Groupe et trie par annee puis mois.

String functions

Pour les etudiants ayant un email, affiche leur nom en majuscules (colonne nom_maj) et le domaine de leur email — la partie située après le @ (colonne domaine).

CONCAT

Crée une colonne nom_complet en combinant le prénom et le nom, séparés d'un espace. Affiche aussi la ville. Colonnes : nom_complet, ville.

INDEX

Crée un index nommé idx_ville sur la colonne ville de la table etudiants. Un index accélère les requêtes qui filtrent sur cette colonne (la leçon montre comment le vérifier avec EXPLAIN).

VIEW

Crée une vue nommée top_etudiants contenant nom, note, ville des 10 meilleurs etudiants (les 10 meilleures notes). Une fois créée, on peut l'interroger comme une table.

Transactions

Dans une transaction (BEGIN ... COMMIT), insère un étudiant ('Pierre', note 14) puis une inscription qui le relie au cours 1, de façon atomique. Récupère l'id du nouvel étudiant via la séquence de la table (currval).

UNION

Combine avec UNION les etudiants de Paris et ceux de Lyon. Affiche nom, prenom, ville, triés par nom.

INTERSECT et EXCEPT

Avec INTERSECT et EXCEPT, compare deux ensembles d'etudiants : ceux inscrits a la fois en Python (cours 1) ET en SQL (cours 2), puis ceux inscrits en Python mais PAS en SQL.

Corrélation SQL

Affiche les etudiants dont la note dépasse la moyenne des notes de LEUR propre ville (sous-requête corrélée). Colonnes : nom, prenom, note, ville. Trie par ville puis note décroissante.

JSON en SQL

Pour chaque etudiant, construis un objet JSON regroupant son nom, sa ville et sa note, dans une colonne nommée profil_json. Affiche aussi nom, ville, note. Trie par nom et limite à 5 lignes.

ARRAY functions

Affiche le nom des etudiants dont le tableau competences contient 'Python' (utilise un opérateur de recherche dans un tableau).

Requête récursive

Sur la table employes (colonnes id, nom, manager_id), génère la hiérarchie avec WITH RECURSIVE : démarre par les employés sans manager (manager_id IS NULL, niveau 0), puis descend d'un niveau à chaque étape. Colonnes : id, nom, manager_id, niveau.

Performance - EXPLAIN

Écris la requête qui compte le nombre de cours par étudiant (LEFT JOIN inscriptions, GROUP BY étudiant, colonne nb_cours), en ne gardant que ceux ayant plus de 2 cours, triée par nb_cours décroissant puis par nom. La leçon explique comment lire son plan d'exécution avec EXPLAIN ANALYZE.

Données manquantes avancé

Par ville, calcule : le total d'etudiants (total), le nombre avec email (avec_email), le nombre sans email (sans_email) et le pourcentage d'emails renseignés (pct_complet, arrondi à 1 décimale). Astuce : compter une colonne précise ignore ses valeurs NULL. Groupe par ville.

Analyse cohortes

Analyse par cohorte (ville + departement) : nb_etudiants, moyenne (AVG arrondie à 2 décimales), note_min, note_max. Ne garde que les groupes d'au moins 2 etudiants. Trie par moyenne décroissante.

Analyse de funnel — Conversion

Sur la table events (colonnes event_type, user_id), construis un funnel de conversion à 4 étapes : 'page_view' → 'signup' → 'first_order' → 'repeat_order'. Pour chaque étape affiche le libellé (step), le nombre d'utilisateurs distincts (users) et le taux de conversion en % par rapport à l'étape visite (conversion_pct).

Segmentation RFM

Sur la table orders (customer_id, order_date, price, quantity), calcule par client : la récence (jours depuis le dernier order_date), la fréquence (nb de commandes) et le montant (SUM(price*quantity)). Attribue un score NTILE(5) à chaque axe (r_score, f_score, m_score), puis un segment_label (Champions, Fideles, Nouveaux, A risque, Perdus, Autres). Trie par montant décroissant.

Analyse Year-over-Year

Sur la table orders, compare chaque mois de l'année en cours à la même période de l'année précédente : CA (revenue), nombre de commandes (orders) et panier moyen (avg_basket). Calcule la croissance du CA en % (revenue_growth_pct). Trie par mois.

Moving averages et tendances

Sur la table orders, calcule le CA quotidien puis sa moyenne mobile 7 jours (ma_7d) et 30 jours (ma_30d) avec des window frames ROWS. Ajoute une colonne trend valant 'hausse' si la MM7 dépasse la MM30, sinon 'baisse'. Trie par jour.

Metriques SaaS — MRR et churn

Sur la table subscriptions (customer_id, amount, period_start, status = 'active'), calcule par mois : le MRR total (total_mrr), le nouveau MRR (new_mrr), l'expansion (expansion_mrr), le churn (churned_mrr), la variation nette (net_mrr_change = new + expansion − churn − contraction) et le taux de churn en % (churn_rate_pct). Trie par mois.

JSONB — extraire des champs

La table events a une colonne payload de type JSONB (du JSON stocké en base). Pour chaque événement, extrais le navigateur et la plateforme, qui se trouvent dans payload → metadata. Colonnes : id, browser, platform. La leçon montre les opérateurs -> et ->>.

Expressions régulières — valider un format

Sur la table users (colonne email), affiche uniquement les emails au format valide, avec l'opérateur regex ~ : une partie locale, puis @, puis un nom de domaine, puis un point suivi d'une extension d'au moins 2 lettres. La leçon décompose le motif à utiliser.

Agrégats conditionnels — COUNT FILTER

Sur la table events, compte en une seule ligne le nombre d'événements de chaque type, avec un agrégat conditionnel (COUNT ... FILTER). Colonnes : page_views (type 'page_view'), signups (type 'signup'), purchases (type 'purchase').

Écart à la moyenne du groupe (fonction fenêtre)

Sur la table ventes, affiche pour chaque vente son écart au montant moyen de sa catégorie (colonne ecart_moyenne_cat, arrondie à 2 décimales), grâce à une fonction fenêtre par catégorie. Colonnes : produit, categorie, montant, ecart_moyenne_cat. Trie par categorie, puis montant décroissant.

Projet final — Top 5 des produits qui rapportent le plus

Projet final : trouve les 5 produits qui rapportent le plus d'argent. La table order_items contient une ligne par produit commandé (colonnes product_id, quantity, price). Pour chaque produit (regroupe par product_id), calcule deux colonnes : total_quantite = la quantité totale vendue (somme de quantity), et chiffre_affaires = l'argent total généré (somme de quantity × price). Affiche seulement les 5 produits au plus gros chiffre_affaires, du plus grand au plus petit.