1. Quelle est la difference entre INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN ?
INNER JOIN : retourne uniquement les lignes ayant une correspondance dans les deux tables. LEFT JOIN (LEFT OUTER JOIN) : retourne toutes les lignes de la table gauche, avec les correspondances de la table droite (NULL si pas de correspondance). RIGHT JOIN : inverse du LEFT JOIN, toutes les lignes de droite. FULL JOIN : retourne toutes les lignes des deux tables, avec NULL la ou il n'y a pas de correspondance. CROSS JOIN : produit cartesien, chaque ligne de la premiere table avec chaque ligne de la seconde. En pratique, INNER et LEFT JOIN couvrent 95% des besoins. Toujours specifier la condition de jointure (ON) pour eviter les produits cartesiens accidentels. Les index sur les colonnes de jointure sont critiques pour les performances.
2. Expliquez les fonctions de fenetre (Window Functions).
Les window functions effectuent des calculs sur un ensemble de lignes lies a la ligne courante, sans grouper les resultats. Syntaxe : fonction OVER (PARTITION BY ... ORDER BY ...). Fonctions de classement : ROW_NUMBER() (numero unique), RANK() (rang avec trous), DENSE_RANK() (rang sans trous), NTILE(n) (repartition en n groupes). Fonctions d'agregation : SUM(), AVG(), COUNT() comme window functions conservent toutes les lignes. Fonctions de decalage : LAG(col, n) (valeur n lignes avant), LEAD(col, n) (valeur n lignes apres), FIRST_VALUE(), LAST_VALUE(). Frame : ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW definit la fenetre de calcul. Utilisation : calcul de running totals, comparaison avec la ligne precedente, classement sans sous-requete.
3. Comment optimiser les requetes SQL lentes ?
Etapes : 1) EXPLAIN ANALYZE pour comprendre le plan d'execution. 2) Index : creer des index sur les colonnes de WHERE, JOIN, ORDER BY. Index composite pour les requetes multi-colonnes (l'ordre des colonnes compte). 3) Selectivite : eviter SELECT *, ne selectionner que les colonnes necessaires. 4) Sous-requetes vs JOIN : preferer les JOIN (souvent plus performants). 5) Pagination : OFFSET est lent sur les grandes tables, utiliser le cursor-based pagination (WHERE id > last_id LIMIT n). 6) Eviter les fonctions sur les colonnes indexees dans WHERE (empeche l'utilisation de l'index). 7) Statistiques : ANALYZE pour mettre a jour les statistiques de la table. 8) Partitionnement pour les tres grandes tables.
4. Qu'est-ce que la normalisation et quand denormaliser ?
La normalisation organise les donnees pour eliminer la redondance. 1NF : valeurs atomiques, pas de groupes repetitifs. 2NF : 1NF + chaque colonne non-cle depend de la cle entiere (pas de dependance partielle). 3NF : 2NF + pas de dependance transitive (chaque colonne depend directement de la cle). BCNF : version renforcee de 3NF. La denormalisation reintroduit volontairement de la redondance pour la performance en lecture. Cas de denormalisation : colonnes calculees frequemment, donnees rarement modifiees mais souvent lues, materialized views, tables de reporting. Trade-off : normalisation = integrite des donnees, moins de stockage, mises a jour simples vs denormalisation = lectures plus rapides, requetes plus simples, mais complexite de maintenance.
5. Expliquez les transactions et les niveaux d'isolation.
Une transaction est un groupe d'operations atomiques (ACID). Atomicity : tout ou rien. Consistency : la base reste dans un etat valide. Isolation : les transactions concurrentes n'interferent pas. Durability : les changements sont permanents apres commit. Niveaux d'isolation : READ UNCOMMITTED (dirty reads possibles), READ COMMITTED (par defaut PostgreSQL, lit uniquement les donnees commitees), REPEATABLE READ (les lectures repetees donnent le meme resultat dans la transaction), SERIALIZABLE (isolation maximale, comme si les transactions s'executaient en serie). Plus le niveau est eleve, moins de concurrence mais plus de coherence. PostgreSQL utilise MVCC (Multi-Version Concurrency Control) pour gerer la concurrence sans verrous en lecture.
6. Comment fonctionnent les index et quels types choisir ?
Un index est une structure de donnees qui accelere les recherches. B-Tree (par defaut) : egalite et ranges, le plus polyvalent. Hash : egalite uniquement, plus rapide que B-Tree pour ce cas. GIN (Generalized Inverted Index) : recherche full-text, arrays, JSONB. GiST (Generalized Search Tree) : donnees geometriques, ranges, full-text. BRIN (Block Range Index) : tres grandes tables avec des donnees correlees physiquement (dates chronologiques). Index composite : plusieurs colonnes, l'ordre est crucial (leftmost prefix). Index partiel : WHERE condition, indexe un sous-ensemble (ex: WHERE status = 'active'). Index covering (INCLUDE) : inclut des colonnes supplementaires pour eviter l'acces a la table (index-only scan). Attention : chaque index ralentit les ecritures.
7. Qu'est-ce que les CTE (Common Table Expressions) et les requetes recursives ?
Les CTE (WITH clause) definissent des sous-requetes nommees reutilisables dans la requete principale. Avantages : lisibilite, reutilisation dans la meme requete, et recursivite. Syntaxe : WITH nom AS (SELECT ...) SELECT ... FROM nom. Les CTE recursives resolvent les problemes hierarchiques : WITH RECURSIVE arbre AS (SELECT ... UNION ALL SELECT ... FROM arbre JOIN ...). Le terme non-recursif (ancre) s'execute une fois, le terme recursif s'execute en boucle jusqu'a ce qu'il ne retourne plus de lignes. Cas d'utilisation : arbres de categories, hierarchies organisationnelles, generation de series, parcours de graphes. Attention a la terminaison (LIMIT ou condition d'arret). Les CTE materialisees stockent le resultat, les non-materialisees sont inlinee par l'optimiseur.
8. Expliquez les types de donnees JSON dans PostgreSQL.
PostgreSQL offre deux types : JSON (stockage texte, valide a l'insertion, pas indexable) et JSONB (stockage binaire, plus rapide a lire, indexable avec GIN). JSONB est presque toujours prefere. Operateurs : -> (extraction par cle, retourne JSON), ->> (extraction par cle, retourne texte), @> (contient), ? (cle existe). Fonctions : jsonb_array_elements (explose un array), jsonb_each (parcourt les paires cle-valeur), jsonb_set (modification). Index GIN sur JSONB : CREATE INDEX ON table USING GIN (data) pour rechercher efficacement dans le JSON. Utilisation : donnees semi-structurees, configuration flexible, integrations API. Eviter pour les donnees fortement relationnelles (preferer des colonnes et tables normalisees).
9. Comment gerer la pagination efficacement en SQL ?
OFFSET/LIMIT : simple mais lent sur les grandes tables (la base parcourt toutes les lignes avant l'offset). Cursor-based (keyset pagination) : WHERE id > :last_id ORDER BY id LIMIT :size. Performant (utilise l'index), stable (pas de decalage si des lignes sont ajoutees/supprimees). Inconvenient : pas de saut direct a une page. Implementation : encoder le curseur (dernier id ou combinaison de colonnes) en base64 pour le client. Pour un tri multi-colonnes : WHERE (col1, col2) > (:val1, :val2) (row comparison). Total count : COUNT() separement ou window function COUNT() OVER(). Pour les grandes tables, une estimation (pg_class.reltuples) est plus rapide qu'un count exact. Les frameworks comme GraphQL utilisent le pattern Relay (edges, pageInfo, cursor).
10. Quelles sont les differences entre les sous-requetes, les CTE et les tables temporaires ?
Sous-requetes : requetes imbriquees dans WHERE, FROM ou SELECT. Scalaires (retournent une valeur), de table (retournent un ensemble), correlees (referent la requete externe, executees par ligne). CTE (WITH) : nommees, reutilisables, recursives possibles, scope limite a une requete. L'optimiseur peut inline ou materialiser les CTE. Tables temporaires (CREATE TEMP TABLE) : persistent pour la session, indexables, modifiables avec INSERT/UPDATE/DELETE. Utilisez les sous-requetes pour les cas simples et ponctuels. Les CTE pour la lisibilite et la recursivite. Les tables temporaires pour les resultats intermediaires volumineux necessitant des index ou reutilises dans plusieurs requetes. En PostgreSQL, MATERIALIZED CTE (par defaut avant v12, controlable avec MATERIALIZED/NOT MATERIALIZED depuis v12) affecte les performances.