SQL : Best Practices Flashcards

Question 1

Q

Quelles sont les bonnes pratiques SQL et pourquoi sont-elles importantes ?

Answer

A

Les bonnes pratiques SQL sont un ensemble de règles et conventions qui garantissent que votre code est lisible, performant, sécurisé, et maintenable.

Analogie : Comme les règles de la route : elles garantissent que tout le monde se comprend, que le code est rapide et sûr, et qu’on évite les accidents (bugs, failles, lenteur).

Les 4 piliers :
- Lisible : facile à comprendre pour l’équipe
- Performant : rapide même sur grandes tables
- Sécurisé : protégé contre injections SQL
- Maintenable : facile à modifier et faire évoluer

Impact : Un code SQL professionnel est 100x plus rapide et infiniment plus sûr qu’un code amateur.

Question 2

Q

Quel est le mnémonique pour retenir les bonnes pratiques SQL ?

Answer

A

Mnémonique : SECURISE

Sécurité : Requêtes paramétrées (jamais de concaténation)
Explicite : Noms clairs, alias compréhensibles
Contraintes : NOT NULL, CHECK, FOREIGN KEY
Utiliser les index : Analysez avec EXPLAIN
Requêtes courtes : Transactions minimales
Intégrité : Normalisation appropriée
SELECT précis : Jamais SELECT *
Erreurs gérées : Validation, transactions, rollback

Phrase : “Un SQL SECURISE est un SQL professionnel”

Application : Vérifiez ces 8 points avant chaque requête importante !

Question 3

Q

Qu’est-ce qu’une injection SQL et comment s’en protéger ?

Answer

A

Une injection SQL est une attaque où l’utilisateur insère du code SQL malveillant dans une entrée pour manipuler la base de données.

Exemple d’attaque :
```
– Code vulnérable
query = “SELECT * FROM users WHERE username = ‘” + username + “’”

– Si username = “admin’ OR ‘1’=’1”
– Requête : SELECT * FROM users WHERE username = ‘admin’ OR ‘1’=’1’
– Retourne TOUS les utilisateurs !
```

Protection (requêtes paramétrées) :
```
– Java
PreparedStatement stmt = conn.prepareStatement(
“SELECT * FROM users WHERE username = ?”
);
stmt.setString(1, username);

– Python
cursor.execute(“SELECT * FROM users WHERE username = %s”, (username,))

– PHP PDO
$stmt = $pdo->prepare(“SELECT * FROM users WHERE username = :user”);
$stmt->execute([‘user’ => $username]);
```

Pourquoi : Les injections permettent de voler/supprimer toutes vos données. Les requêtes paramétrées sont NON NÉGOCIABLES.

Question 4

Q

Quelles sont les conventions de nommage SQL recommandées ?

Answer

A

Conventions cohérentes pour lisibilité et maintenance :

Tables :
- Pluriel ou singulier (choisir 1 convention)
- snake_case : employes, commandes_clients
- ❌ Éviter : tbl_employes, emp, data

Colonnes :
- Descriptives : date_embauche, salaire_annuel
- Booléens : est_actif, a_valide, peut_modifier
- Timestamps : created_at, updated_at
- FK : {table}_id (ex: departement_id)

Contraintes et index :

pk_{table}                    -- pk_employes
fk_{source}_{dest}_{col}      -- fk_employes_departements_dept_id
idx_{table}_{colonnes}        -- idx_employes_nom_prenom
uq_{table}_{colonne}          -- uq_employes_email
ck_{table}_{col}_{desc}       -- ck_employes_salaire_positif

Pourquoi : Conventions cohérentes = compréhension immédiate sans documentation. Gagne des heures sur gros projets.

Question 5

Q

Pourquoi éviter SELECT * en production ?

Answer

A

Problèmes avec SELECT * :

❌ Mauvais :

SELECT * FROM employes WHERE dept_id = 5;
-- Récupère 30 colonnes alors que vous en utilisez 3
-- Gaspille bande passante et mémoire
-- Fragile si schéma change (nouvelle colonne)

✅ Bon :

SELECT id, nom, prenom FROM employes WHERE dept_id = 5;
-- Seulement 3 colonnes nécessaires
-- 10x moins de données transférées
-- Code explicite et robuste

Impact :
- Performances : 10x plus lent (transfert données inutiles)
- Mémoire : Consomme plus de RAM
- Maintenance : Code fragile aux changements de schéma
- Index : Peut empêcher l’usage d’index couvrants

Règle : Listez TOUJOURS explicitement les colonnes nécessaires.

Question 6

Q

Qu’est-ce que le problème N+1 queries et comment le résoudre ?

Answer

A

Problème N+1 queries : Exécuter 1 requête pour récupérer N entités, puis N requêtes pour récupérer leurs relations = 1 + N requêtes totales.

❌ Mauvais (N+1) :
```
– 1 requête : récupérer départements
SELECT * FROM departements; – 1 requête

– N requêtes : pour chaque département
foreach (dept) {
SELECT * FROM employes WHERE dept_id = dept.id; – N requêtes
}
– Total : 1 + 100 = 101 requêtes (si 100 départements)
– Temps : 10 secondes
```

✅ Bon (1 requête avec JOIN) :

SELECT d.nom_dept, e.id, e.nom, e.prenom
FROM departements d
LEFT JOIN employes e ON d.id = e.dept_id
ORDER BY d.id, e.nom;
-- Total : 1 requête
-- Temps : 0.1 seconde (100x plus rapide)

Pourquoi : Chaque requête a un coût (latence réseau). 101 requêtes = 1000x plus lent qu’une seule.

Question 7

Q

Quelles sont les bonnes pratiques pour les transactions SQL ?

Answer

A

Bonnes pratiques transactions :

1. Transactions COURTES :
```
❌ Mauvais : transaction longue (40 secondes)
BEGIN;
SELECT …; – Lecture
– traitement app (10s) + interaction user (30s)
UPDATE …;
COMMIT;

✅ Bon : transaction courte (0.01 seconde)
– Lecture AVANT transaction
SELECT …;
– traitement + interaction
BEGIN;
UPDATE …; – Seulement l’écriture
COMMIT;
```

2. Niveau d’isolation approprié :
- READ COMMITTED : par défaut, la plupart des cas
- SERIALIZABLE : opérations critiques (banque, réservations)

3. Éviter deadlocks (ordre cohérent) :

-- TOUJOURS modifier ressources dans le MÊME ordre
BEGIN;
UPDATE comptes WHERE id = 1;  -- Ordre : 1, 2
UPDATE comptes WHERE id = 2;
COMMIT;

Pourquoi : Transactions longues bloquent utilisateurs et causent deadlocks. Transactions courtes = disponibilité.

Question 8

Q

Quelle est la différence entre normalisation et dénormalisation ?

Answer

A

Tableau comparatif :

Exemple normalisation (3NF) :

employes: id, nom, dept_id
departements: id, nom_dept, ville
-- Pas de redondance

Exemple dénormalisation :

employes: id, nom, dept_id, nom_dept, ville_dept
-- Redondance volontaire pour performances
-- Utilisé pour data warehousing, rapports

Règle : Normalisez par défaut, dénormalisez seulement si mesures de performance le justifient.

Question 9

Q

Comment gérer correctement les valeurs NULL en SQL ?

Answer

A

Règles essentielles pour NULL :

1. NULL n’est PAS égal à NULL :

SELECT NULL = NULL;   -- Résultat : NULL (pas TRUE)
SELECT NULL != NULL;  -- Résultat : NULL (pas TRUE)

2. Comparaisons correctes :
```
❌ Mauvais
SELECT * FROM employes WHERE manager_id = NULL; – 0 lignes !
SELECT * FROM employes WHERE manager_id != NULL; – 0 lignes !

✅ Bon
SELECT * FROM employes WHERE manager_id IS NULL;
SELECT * FROM employes WHERE manager_id IS NOT NULL;
```

3. NULL dans calculs (utiliser COALESCE) :
```
SELECT 10 + NULL; – NULL (contamination)

SELECT salaire + COALESCE(prime, 0) as total FROM employes;
– Remplace NULL par 0
```

4. Contraintes NOT NULL par défaut :

CREATE TABLE employes (
    nom VARCHAR(100) NOT NULL,      -- Obligatoire
    telephone VARCHAR(20)            -- Optionnel (NULL OK)
);

Pourquoi : NULL mal géré = bugs subtils (totaux incorrects, filtres cassés).

Question 10

Q

Pourquoi utiliser DECIMAL au lieu de FLOAT pour l’argent ?

Answer

A

Problème avec FLOAT/DOUBLE :

❌ Mauvais (erreurs d’arrondi) :
```
CREATE TABLE produits (prix FLOAT);
INSERT INTO produits VALUES (19.99);
SELECT prix FROM produits;
– Résultat : 19.989999… ou 19.990001
– Imprécis pour l’argent !

SELECT SUM(prix) FROM produits;
– Erreurs s’accumulent : 100.00 peut devenir 99.97 ou 100.03
```

✅ Bon (précision exacte) :
```
CREATE TABLE produits (prix DECIMAL(10,2));
INSERT INTO produits VALUES (19.99);
SELECT prix FROM produits;
– Résultat : 19.99 (précis)

SELECT SUM(prix) FROM produits;
– Résultat exact, pas d’erreurs d’arrondi
```

Types recommandés :
- DECIMAL(10,2) : Argent (10 chiffres total, 2 après virgule)
- FLOAT/DOUBLE : Calculs scientifiques seulement

Pourquoi : FLOAT est approximatif (précision ~7 chiffres). DECIMAL est exact. Pour l’argent, l’exactitude est CRITIQUE.

Question 11

Q

Quelles contraintes d’intégrité utiliser et pourquoi ?

Answer

A

Les 5 contraintes essentielles :

1. PRIMARY KEY (identifiant unique) :

id INT PRIMARY KEY AUTO_INCREMENT

2. FOREIGN KEY (intégrité référentielle) :

FOREIGN KEY (dept_id) REFERENCES departements(id)
    ON DELETE CASCADE      -- Supprimer parent → supprimer enfants
    ON UPDATE CASCADE      -- Modifier parent → modifier enfants
-- Ou RESTRICT, SET NULL selon besoin

3. UNIQUE (unicité) :

email VARCHAR(255) UNIQUE

4. NOT NULL (valeur obligatoire) :

nom VARCHAR(100) NOT NULL

5. CHECK (validation métier) :

CHECK (age >= 18 AND age <= 65)
CHECK (salaire > 0)
CHECK (date_fin > date_debut)

Pourquoi : Contraintes = première ligne de défense contre données invalides. 100x plus fiables que validation dans le code (peut être contournée).

Question 12

Q

Quelles sont les bonnes pratiques pour les migrations de schéma ?

Answer

A

Principes essentiels des migrations :

1. Une migration = un fichier versionné

V001_create_users_table.sql
V002_add_email_to_users.sql
V003_create_index_users_email.sql

2. Chaque migration a UP et DOWN :
```
– UP : applique le changement
CREATE TABLE users (…);

– DOWN : annule le changement (rollback)
DROP TABLE users;
```

3. Migrations IMMUABLES :

❌ JAMAIS modifier une migration déjà appliquée en prod
✅ Créez une NOUVELLE migration pour corriger

4. Zero-downtime (renommer colonne) :

-- Étape 1 : Ajouter nouvelle colonne
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);
-- Étape 2 : Copier données
UPDATE users SET full_name = name;
-- Étape 3 : Déployer code (utilise full_name)
-- Étape 4 : Supprimer ancienne (migration ultérieure)
ALTER TABLE users DROP COLUMN name;

Pourquoi : Migrations versionnées = traçabilité, réversibilité, cohérence entre environnements.

Question 13

Q

Comment écrire des requêtes SQL lisibles et maintenables ?

Answer

A

Bonnes pratiques de formatage :

❌ Mauvais (illisible) :

SELECT e.id,e.nom,d.nom_dept FROM employes e INNER JOIN departements d ON e.dept_id=d.id WHERE e.salaire>50000 ORDER BY e.nom;

✅ Bon (lisible) :

SELECT
    e.id,
    e.nom,
    e.prenom,
    d.nom_dept,
    e.salaire_annuel
FROM employes e
INNER JOIN departements d ON e.dept_id = d.id
WHERE e.salaire_annuel > 50000
  AND e.est_actif = TRUE
ORDER BY e.nom, e.prenom;

Règles :
- 1 clause par ligne (SELECT, FROM, WHERE, ORDER BY)
- Indentation pour sous-clauses
- Alias clairs (e = employes, d = departements)
- Majuscules pour mots-clés SQL (convention)
- Commentaires pour requêtes complexes

Pourquoi : Requête bien formatée = compréhension en 5 secondes au lieu de 5 minutes. Sur milliers de requêtes = productivité vs confusion.

Question 14

Q

Comment éviter les deadlocks dans les transactions SQL ?

Answer

A

Deadlock = deux transactions s’attendent mutuellement.

❌ Mauvais (risque de deadlock) :
```
– Transaction 1
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
– pause
UPDATE comptes SET solde = solde + 100 WHERE id = 2;
COMMIT;

– Transaction 2 (parallèle)
BEGIN;
UPDATE comptes SET solde = solde - 50 WHERE id = 2; – Ordre inversé !
– pause
UPDATE comptes SET solde = solde + 50 WHERE id = 1;
COMMIT;
– DEADLOCK ! Chaque transaction attend l’autre
```

✅ Bon (même ordre = pas de deadlock) :
```
– TOUJOURS modifier ressources dans le MÊME ordre
– Transaction 1
BEGIN;
UPDATE comptes WHERE id = 1; – Ordre : 1, 2
UPDATE comptes WHERE id = 2;
COMMIT;

– Transaction 2
BEGIN;
UPDATE comptes WHERE id = 1; – Ordre : 1, 2 (identique)
UPDATE comptes WHERE id = 2;
COMMIT;
– Pas de deadlock : ordre cohérent
```

Autres bonnes pratiques :
- Transactions courtes
- Timeout sur verrous
- Retry automatique en cas de deadlock

Pourquoi : Deadlocks bloquent application. Ordre cohérent = évite le problème à la source.

Question 15

Q

Quels sont les anti-patterns SQL à éviter absolument ?

Answer

A

Top 5 anti-patterns SQL :

1. Entity-Attribute-Value (EAV)
```
❌ Mauvais : table générique
eav: entite_id, attribut, valeur
– Requêtes horribles, pas de contraintes, lent

✅ Bon : tables spécifiques
employes: id, nom, age, email
```

2. Concaténation SQL (injections)
```
❌ DANGER
query = “SELECT * FROM users WHERE name = ‘” + name + “’”

✅ Requêtes paramétrées
query = “SELECT * FROM users WHERE name = ?”
```

3. FLOAT pour l’argent

❌ Mauvais : prix FLOAT (erreurs arrondi)
✅ Bon : prix DECIMAL(10,2) (précis)

4. Colonnes multiples pour listes

❌ Mauvais : tel1, tel2, tel3
✅ Bon : table telephones séparée

5. SELECT * en production

❌ Mauvais : SELECT * (lent, fragile)
✅ Bon : SELECT id, nom, email (précis)

Pourquoi : Anti-patterns = code complexe, lent, bugué, et vulnérable. Évitez-les dès la conception !

SQL : Best Practices Flashcards

(15 cards)