SQL : Basics Flashcards

Question 1

Q

Qu’est-ce que SQL et à quoi sert-il ?

Answer

A

SQL (Structured Query Language) est le langage standardisé pour communiquer avec les bases de données relationnelles.

Il permet de créer, lire, modifier et supprimer des données de manière structurée.

Analogie : SQL est comme un bibliothécaire expert qui comprend vos demandes et trouve exactement les livres dont vous avez besoin.

Exemple d’utilisation :

SELECT nom, email FROM utilisateurs WHERE age > 18;

Pourquoi : SQL est universel et fonctionne sur MySQL, PostgreSQL, SQL Server, Oracle, etc.

Question 2

Q

Quel est le mnémonique pour retenir l’ordre d’exécution SQL ?

Answer

A

Mnémonique : “Franck Va Où ? Groupé Hier, Seul, Ou Limité”

FROM : Table source
Virtual (WHERE) : Filtrage des lignes
Où ? (GROUP BY) : Regroupement
Groupé
Hier (HAVING) : Filtrage des groupes
Seul (SELECT) : Sélection finale
Ou (ORDER BY) : Tri
Limité (LIMIT) : Limitation

Pourquoi c’est important : Comprendre l’ordre d’exécution aide à écrire des requêtes optimisées et à comprendre pourquoi certaines requêtes ne fonctionnent pas.

Question 3

Q

Quelle est la différence entre WHERE et HAVING ?

Answer

A

Tableau comparatif :

Exemple WHERE :

SELECT * FROM employes WHERE salaire > 50000;

Exemple HAVING :

SELECT dept_id, AVG(salaire) as moyenne
FROM employes
GROUP BY dept_id
HAVING AVG(salaire) > 50000;

Règle d’or : Utilisez WHERE quand vous pouvez, HAVING quand vous devez filtrer sur des agrégations.

Question 4

Q

Quels sont les 5 types de JOIN et leurs différences ?

Answer

A

Les 5 types de JOIN :

1. INNER JOIN : Seulement les correspondances dans les DEUX tables (intersection)

2. LEFT JOIN : Toutes les lignes de GAUCHE + correspondances de droite

3. RIGHT JOIN : Toutes les lignes de DROITE + correspondances de gauche

4. FULL OUTER JOIN : TOUTES les lignes des deux tables

5. CROSS JOIN : Produit cartésien (chaque ligne × chaque ligne)

Exemple visuel :
```
– INNER : employés AVEC département
SELECT e.nom, d.nom_dept
FROM employes e INNER JOIN departements d ON e.dept_id = d.id;

– LEFT : TOUS les employés (même sans département)
SELECT e.nom, d.nom_dept
FROM employes e LEFT JOIN departements d ON e.dept_id = d.id;
~~~

Question 5

Q

Que signifie ACID dans les transactions SQL ?

Answer

A

ACID garantit la fiabilité des transactions :

Atomicité : Tout ou rien (pas d’état intermédiaire)
Cohérence : Les contraintes sont toujours respectées
Isolation : Les transactions concurrentes ne s’interfèrent pas
Durabilité : Une fois validée, la transaction est permanente

Mnémonique : “Atomic Coins Isolate Durably” (Les pièces atomiques s’isolent durablement)

Exemple concret :

BEGIN TRANSACTION;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = solde + 100 WHERE id = 2;
COMMIT;  -- Les DEUX opérations réussissent ou les DEUX échouent

Pourquoi : ACID est essentiel pour les applications critiques (banque, e-commerce) où la cohérence des données est vitale.

Question 6

Q

Comment créer une table avec toutes les contraintes principales ?

Answer

A

Exemple complet avec toutes les contraintes :

CREATE TABLE employes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18 AND age <= 65),
    salaire DECIMAL(10,2) CHECK (salaire > 0),
    dept_id INT,
    est_actif BOOLEAN DEFAULT TRUE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dept_id) REFERENCES departements(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Contraintes utilisées :
- PRIMARY KEY : Identifiant unique
- NOT NULL : Valeur obligatoire
- UNIQUE : Pas de doublons
- CHECK : Validation personnalisée
- FOREIGN KEY : Relation entre tables
- DEFAULT : Valeur par défaut

Pourquoi : Les contraintes empêchent les données invalides à la source.

Question 7

Q

Quel est le problème avec ce code et comment le corriger ?

Answer

A

Problème : Piège N+1 Query (performances catastrophiques)

❌ Mauvais :

SELECT * FROM departements;
foreach (dept) {
    SELECT * FROM employes WHERE dept_id = dept.id;
}
-- 1 + N requêtes (si 100 départements = 101 requêtes !)

✅ Correct :

SELECT d.nom_dept, e.nom, e.salaire
FROM departements d
LEFT JOIN employes e ON d.id = e.dept_id
ORDER BY d.id, e.nom;
-- 1 seule requête !

Impact : La version corrigée peut être 100x à 1000x plus rapide. Chaque requête a un coût (latence réseau, parsing SQL).

Question 8

Q

Comment créer un index et quand l’utiliser ?

Answer

A

Créer un index :

```
– Index simple
CREATE INDEX idx_nom ON employes(nom);

– Index composite
CREATE INDEX idx_dept_salaire ON employes(dept_id, salaire);

– Index unique
CREATE UNIQUE INDEX idx_email ON employes(email);
```

✅ Utilisez un index quand :
- Colonnes dans WHERE fréquemment
- Colonnes dans JOIN
- Colonnes dans ORDER BY
- Tables avec > 1000 lignes

❌ Évitez un index quand :
- Petites tables (< 1000 lignes)
- Colonnes rarement recherchées
- Colonnes avec peu de valeurs distinctes (ex: sexe)
- Beaucoup d’INSERT/UPDATE/DELETE

Pourquoi : Les index accélèrent les lectures mais ralentissent les écritures.

Question 9

Q

Qu’est-ce qu’une Window Function et comment l’utiliser ?

Answer

A

Window Function = calculs sur un ensemble de lignes liées à la ligne actuelle, SANS GROUP BY

Contrairement à GROUP BY qui réduit les lignes, les window functions gardent toutes les lignes.

Exemple avec ROW_NUMBER :

SELECT
    nom,
    dept_id,
    salaire,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salaire DESC) as rang
FROM employes;
-- Résultat : chaque employé garde sa ligne + son rang dans son département

Fonctions courantes :
- ROW_NUMBER() : Numérotation unique
- RANK() : Classement avec ex-aequo
- LAG() / LEAD() : Lignes précédentes/suivantes
- SUM() / AVG() OVER : Cumuls et moyennes glissantes

Pourquoi : Permet des analyses sophistiquées impossibles avec GROUP BY seul.

Question 10

Q

Quelle est la différence entre une vue normale et une vue matérialisée ?

Answer

A

Tableau comparatif :

Vue normale :

CREATE VIEW employes_actifs AS
SELECT * FROM employes WHERE est_actif = TRUE;
-- Exécute la requête à chaque SELECT

Vue matérialisée (PostgreSQL) :
```
CREATE MATERIALIZED VIEW stats_dept AS
SELECT dept_id, COUNT(*) FROM employes GROUP BY dept_id;
REFRESH MATERIALIZED VIEW stats_dept;
– Stocke les résultats, doit être rafraîchie
~~~

Question 11

Q

Comment optimiser cette requête lente ?

Answer

A

Requête lente à optimiser :

❌ Avant (lent) :

SELECT * FROM employes WHERE UPPER(nom) = 'DUPONT';
-- Problème : fonction sur colonne indexée empêche l'usage de l'index

✅ Après (rapide) :
```
– Option 1 : Éviter la fonction
SELECT id, nom, salaire FROM employes WHERE nom = ‘Dupont’;

– Option 2 : Index fonctionnel (certains SGBD)
CREATE INDEX idx_nom_upper ON employes(UPPER(nom));
SELECT id, nom, salaire FROM employes WHERE UPPER(nom) = ‘DUPONT’;
```

Autres optimisations courantes :
- SELECT seulement les colonnes nécessaires (pas SELECT *)
- Utiliser WHERE au lieu de HAVING quand possible
- Ajouter LIMIT si vous n’avez pas besoin de toutes les lignes
- Utiliser EXPLAIN pour analyser le plan d’exécution

Question 12

Q

Qu’est-ce qu’une CTE et pourquoi l’utiliser ?

Answer

A

CTE (Common Table Expression) = requête temporaire nommée, plus lisible que les sous-requêtes

Syntaxe de base :

WITH employes_seniors AS (
    SELECT * FROM employes
    WHERE YEAR(CURRENT_DATE) - YEAR(date_embauche) > 10
)
SELECT nom, salaire FROM employes_seniors WHERE salaire > 60000;

Avantages :
- ✅ Plus lisible que les sous-requêtes imbriquées
- ✅ Peut être réutilisé plusieurs fois dans la requête
- ✅ Permet les CTE récursifs (hiérarchies, graphes)

CTE multiple :

WITH
    stats AS (SELECT dept_id, AVG(salaire) FROM employes GROUP BY dept_id),
    dept AS (SELECT id, nom_dept FROM departements)
SELECT * FROM stats JOIN dept ON stats.dept_id = dept.id;

Pourquoi : Code SQL maintenable et organisé, essentiel pour les requêtes complexes.

Question 13

Q

Que va afficher cette requête avec GROUP BY ?

Answer

A

Code :

SELECT dept_id, COUNT(*) as nb, AVG(salaire) as moy
FROM employes
WHERE salaire > 40000
GROUP BY dept_id
HAVING COUNT(*) > 2
ORDER BY moy DESC;

Résultat : La requête affiche les départements ayant plus de 2 employés gagnant plus de 40000€, avec le nombre d’employés et le salaire moyen, triés par salaire moyen décroissant.

Explication étape par étape :
1. WHERE filtre les employés avec salaire > 40000
2. GROUP BY regroupe par département
3. COUNT(*) compte les employés par département
4. AVG(salaire) calcule la moyenne par département
5. HAVING garde seulement les départements avec > 2 employés
6. ORDER BY trie par salaire moyen décroissant

Ordre d’exécution : FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Question 14

Q

Quelle est la différence entre RANK() et DENSE_RANK() ?

Answer

A

Différence principale : le traitement des ex-aequo

RANK() : Saute les rangs en cas d’égalité
DENSE_RANK() : Ne saute PAS les rangs

Exemple concret :

SELECT
    nom,
    salaire,
    RANK() OVER (ORDER BY salaire DESC) as rang,
    DENSE_RANK() OVER (ORDER BY salaire DESC) as rang_dense
FROM employes;

Résultat si salaires : 5000, 4000, 4000, 3000

Pourquoi : RANK saute au rang 4 après deux 2èmes places, DENSE_RANK continue à 3.

nom | salaire | RANK | DENSE_RANK |
|—–|———|——|————|
| Alice | 5000 | 1 | 1 |
| Bob | 4000 | 2 | 2 |
| Claire | 4000 | 2 | 2 |
| David | 3000 | 4 | 3 |

SQL : Basics Flashcards

(14 cards)