1. Quelle est la différence 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 première table avec chaque ligne de la seconde. En pratique, INNER et LEFT JOIN couvrent 95% des besoins. Toujours spécifier la condition de jointure (ON) pour éviter 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 à la ligne courante, sans grouper les résultats. Syntaxe : fonction OVER (PARTITION BY ... ORDER BY ...). Fonctions de classement : ROW_NUMBER() (numéro unique), RANK() (rang avec trous), DENSE_RANK() (rang sans trous), NTILE(n) (répartition en n groupes). Fonctions d'agrégation : 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 après), FIRST_VALUE(), LAST_VALUE(). Frame : ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW définit la fenetre de calcul. Utilisation : calcul de running totals, comparaison avec la ligne précédente, classement sans sous-requete.
3. Comment optimiser les requêtes SQL lentes ?
Étapes : 1) EXPLAIN ANALYZE pour comprendre le plan d'exécution. 2) Index : créer des index sur les colonnes de WHERE, JOIN, ORDER BY. Index composite pour les requêtes multi-colonnes (l'ordre des colonnes compte). 3) Selectivite : éviter SELECT *, ne sélectionner que les colonnes nécessaires. 4) Sous-requetes vs JOIN : préférer 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) Éviter les fonctions sur les colonnes indexees dans WHERE (empêche l'utilisation de l'index). 7) Statistiques : ANALYZE pour mettre à jour les statistiques de la table. 8) Partitionnement pour les très grandes tables.
4. Qu'est-ce que la normalisation et quand denormaliser ?
La normalisation organise les données pour eliminer la redondance. 1NF : valeurs atomiques, pas de groupes repetitifs. 2NF : 1NF + chaque colonne non-cle dépend de la cle entière (pas de dépendance partielle). 3NF : 2NF + pas de dépendance transitive (chaque colonne dépend directement de la cle). BCNF : version renforcee de 3NF. La dénormalisation reintroduit volontairement de la redondance pour la performance en lecture. Cas de dénormalisation : colonnes calculées fréquemment, données rarement modifiées mais souvent lues, materialized views, tables de reporting. Trade-off : normalisation = intégrité des données, moins de stockage, mises à jour simples vs dénormalisation = lectures plus rapides, requêtes plus simples, mais complexité de maintenance.
5. Expliquez les transactions et les niveaux d'isolation.
Une transaction est un groupe d'opérations atomiques (ACID). Atomicity : tout ou rien. Consistency : la base reste dans un état valide. Isolation : les transactions concurrentes n'interferent pas. Durability : les changements sont permanents après commit. Niveaux d'isolation : READ UNCOMMITTED (dirty reads possibles), READ COMMITTED (par défaut PostgreSQL, lit uniquement les données commitees), REPEATABLE READ (les lectures repetees donnent le même résultat dans la transaction), SERIALIZABLE (isolation maximale, comme si les transactions s'executaient en série). Plus le niveau est eleve, moins de concurrence mais plus de cohérence. PostgreSQL utilise MVCC (Multi-Version Concurrency Control) pour gérer la concurrence sans verrous en lecture.
6. Comment fonctionnent les index et quels types choisir ?
Un index est une structure de données qui accélère les recherches. B-Tree (par défaut) : 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) : données geometriques, ranges, full-text. BRIN (Block Range Index) : très grandes tables avec des données 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 supplémentaires pour éviter l'accès à la table (index-only scan). Attention : chaque index ralentit les écritures.
7. Qu'est-ce que les CTE (Common Table Expressions) et les requêtes récursives ?
Les CTE (WITH clause) définissent des sous-requetes nommées réutilisables dans la requête principale. Avantages : lisibilité, réutilisation dans la même requête, et récursivité. Syntaxe : WITH nom AS (SELECT ...) SELECT ... FROM nom. Les CTE récursives resolvent les problèmes hierarchiques : WITH RÉCURSIVE arbre AS (SELECT ... UNION ALL SELECT ... FROM arbre JOIN ...). Le terme non-recursif (ancre) s'execute une fois, le terme récursif s'execute en boucle jusqu'à ce qu'il ne retourne plus de lignes. Cas d'utilisation : arbres de catégories, hierarchies organisationnelles, génération de séries, parcours de graphes. Attention à la terminaison (LIMIT ou condition d'arrêt). Les CTE materialisees stockent le résultat, les non-materialisees sont inlinee par l'optimiseur.
8. Expliquez les types de données JSON dans PostgreSQL.
PostgreSQL offre deux types : JSON (stockage texte, valide à l'insertion, pas indexable) et JSONB (stockage binaire, plus rapide à lire, indexable avec GIN). JSONB est presque toujours préfère. Opérateurs : -> (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 : données semi-structurees, configuration flexible, intégrations API. Éviter pour les données fortement relationnelles (préférer des colonnes et tables normalisees).
9. Comment gérer 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 à une page. Implémentation : 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() séparément 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 différences entre les sous-requetes, les CTE et les tables temporaires ?
Sous-requetes : requêtes imbriquées dans WHERE, FROM ou SELECT. Scalaires (retournent une valeur), de table (retournent un ensemble), correlees (referent la requête externe, exécutées par ligne). CTE (WITH) : nommées, réutilisables, récursives possibles, scope limite à une requête. 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 lisibilité et la récursivité. Les tables temporaires pour les résultats intermédiaires volumineux nécessitant des index ou reutilises dans plusieurs requêtes. En PostgreSQL, MATERIALIZED CTE (par défaut avant v12, controlable avec MATERIALIZED/NOT MATERIALIZED depuis v12) affecte les performances.