SQL : De zéro à autonome, le guide ultime pour interroger vos bases de données.

Base de données : ENTREPRISE
│
├── Table : clients
│   ├── Ligne 1 : id=1, nom="Alice", email="alice@mail.com"
│   ├── Ligne 2 : id=2, nom="Bob", email="bob@mail.com"
│   └── ...
│
├── Table : produits
│   ├── Ligne 1 : id=1, nom="Ordinateur", prix=899.99
│   └── ...
│
└── Table : ventes
    └── ...

-- Créer la table clients
CREATE TABLE clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    telephone VARCHAR(20),
    ville VARCHAR(50),
    date_inscription DATE DEFAULT CURRENT_DATE
);

-- Créer la table produits
CREATE TABLE produits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nom VARCHAR(100) NOT NULL,
    categorie VARCHAR(50),
    prix DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    description TEXT
);

-- Créer la table commandes
CREATE TABLE commandes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    produit_id INTEGER NOT NULL,
    quantite INTEGER NOT NULL,
    prix_unitaire DECIMAL(10, 2) NOT NULL,
    montant_total DECIMAL(10, 2) NOT NULL,
    date_commande TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (produit_id) REFERENCES produits(id)
);

-- Insérer des clients
INSERT INTO clients (nom, email, telephone, ville) VALUES
('Alice Dupont', 'alice@mail.com', '0601020304', 'Paris'),
('Bob Martin', 'bob@mail.com', '0605060708', 'Lyon'),
('Charlie Dubois', 'charlie@mail.com', '0609101112', 'Paris'),
('Diana Petit', 'diana@mail.com', '0612131415', 'Marseille'),
('Eve Bernard', 'eve@mail.com', '0616171819', 'Lyon');

-- Insérer des produits
INSERT INTO produits (nom, categorie, prix, stock) VALUES
('MacBook Pro', 'Informatique', 2499.00, 10),
('iPhone 15', 'Téléphonie', 969.00, 25),
('AirPods Pro', 'Audio', 279.00, 50),
('iPad Air', 'Informatique', 699.00, 15),
('Apple Watch', 'Accessoires', 449.00, 30),
('Magic Mouse', 'Accessoires', 89.00, 40),
('MacBook Air', 'Informatique', 1299.00, 12);

-- Insérer des commandes
INSERT INTO commandes (client_id, produit_id, quantite, prix_unitaire, montant_total) VALUES
(1, 1, 1, 2499.00, 2499.00),
(2, 2, 2, 969.00, 1938.00),
(1, 3, 1, 279.00, 279.00),
(3, 4, 1, 699.00, 699.00),
(4, 5, 1, 449.00, 449.00),
(2, 6, 2, 89.00, 178.00),
(5, 7, 1, 1299.00, 1299.00),
(1, 5, 1, 449.00, 449.00);

-- Tout sélectionner
SELECT * FROM clients;

-- Sélectionner des colonnes spécifiques
SELECT nom, email FROM clients;

-- Sélectionner avec alias (renommer)
SELECT nom AS client_nom, email AS client_email FROM clients;

-- Sélectionner des valeurs calculées
SELECT nom, prix, prix * 0.8 AS prix_promo FROM produits;

nom             | email
----------------|------------------
Alice Dupont    | alice@mail.com
Bob Martin      | bob@mail.com
Charlie Dubois  | charlie@mail.com
...

-- Toutes les villes (avec doublons)
SELECT ville FROM clients;
-- Paris, Lyon, Paris, Marseille, Lyon

-- Toutes les villes (sans doublons)
SELECT DISTINCT ville FROM clients;
-- Paris, Lyon, Marseille

-- Toutes les catégories de produits
SELECT DISTINCT categorie FROM produits;

-- Les 3 premiers clients
SELECT * FROM clients LIMIT 3;

-- Les 5 premiers produits
SELECT nom, prix FROM produits LIMIT 5;

-- Pagination : 10 résultats, en commençant au 20ème
SELECT * FROM clients LIMIT 10 OFFSET 20;

-- Clients de Paris
SELECT * FROM clients WHERE ville = 'Paris';

-- Produits de plus de 500€
SELECT nom, prix FROM produits WHERE prix > 500;

-- Produits à exactement 279€
SELECT * FROM produits WHERE prix = 279.00;

-- Produits à 500€ ou plus
SELECT * FROM produits WHERE prix >= 500;

-- Produits de moins de 1000€
SELECT * FROM produits WHERE prix < 1000;

-- Produits différents de "Accessoires"
SELECT * FROM produits WHERE categorie != 'Accessoires';
-- Ou :
SELECT * FROM produits WHERE categorie <> 'Accessoires';

-- Produits Informatique de plus de 1000€
SELECT * FROM produits 
WHERE categorie = 'Informatique' AND prix > 1000;

-- Produits de Paris OU Lyon
SELECT * FROM clients 
WHERE ville = 'Paris' OR ville = 'Lyon';

-- Produits Informatique OU Audio
SELECT * FROM produits 
WHERE categorie = 'Informatique' OR categorie = 'Audio';

-- Produits entre 200 et 1000€
SELECT * FROM produits 
WHERE prix >= 200 AND prix <= 1000;

-- Clients qui ne sont PAS de Paris
SELECT * FROM clients 
WHERE NOT ville = 'Paris';
-- Équivalent :
SELECT * FROM clients WHERE ville != 'Paris';

-- Produits Informatique de plus de 1000€ OU produits Audio
SELECT * FROM produits 
WHERE (categorie = 'Informatique' AND prix > 1000) 
   OR categorie = 'Audio';

-- Produits entre 200€ et 1000€
SELECT * FROM produits WHERE prix BETWEEN 200 AND 1000;

-- Commandes de mars 2024
SELECT * FROM commandes 
WHERE date_commande BETWEEN '2024-03-01' AND '2024-03-31';

-- Clients de Paris, Lyon ou Marseille
SELECT * FROM clients WHERE ville IN ('Paris', 'Lyon', 'Marseille');

-- Produits Informatique ou Téléphonie
SELECT * FROM produits WHERE categorie IN ('Informatique', 'Téléphonie');

-- Inverse (NOT IN)
SELECT * FROM clients WHERE ville NOT IN ('Paris', 'Lyon');

-- Clients dont le nom commence par "A"
SELECT * FROM clients WHERE nom LIKE 'A%';

-- Clients dont le nom contient "Martin"
SELECT * FROM clients WHERE nom LIKE '%Martin%';

-- Clients dont le nom se termine par "Dupont"
SELECT * FROM clients WHERE nom LIKE '%Dupont';

-- Produits dont le nom contient "Mac"
SELECT * FROM produits WHERE nom LIKE '%Mac%';

-- Email se terminant par "gmail.com"
SELECT * FROM clients WHERE email LIKE '%@gmail.com';

-- _ = un seul caractère
-- Noms de 3 lettres commençant par "B"
SELECT * FROM clients WHERE nom LIKE 'B__';

-- Clients sans téléphone
SELECT * FROM clients WHERE telephone IS NULL;

-- Clients avec un téléphone
SELECT * FROM clients WHERE telephone IS NOT NULL;

-- Produits sans description
SELECT * FROM produits WHERE description IS NULL;

-- Clients triés par nom (A-Z)
SELECT * FROM clients ORDER BY nom;

-- Clients triés par nom décroissant (Z-A)
SELECT * FROM clients ORDER BY nom DESC;

-- Produits triés par prix croissant
SELECT * FROM produits ORDER BY prix;

-- Produits triés par prix décroissant (du plus cher au moins cher)
SELECT * FROM produits ORDER BY prix DESC;

-- Commandes triées par date (plus récentes d'abord)
SELECT * FROM commandes ORDER BY date_commande DESC;

-- Clients triés par ville, puis par nom
SELECT * FROM clients ORDER BY ville, nom;

-- Produits triés par catégorie (A-Z), puis par prix (décroissant)
SELECT * FROM produits ORDER BY categorie ASC, prix DESC;

-- Commandes triées par client, puis par date
SELECT * FROM commandes ORDER BY client_id, date_commande DESC;

-- Produits triés par prix avec TVA (20%)
SELECT nom, prix, prix * 1.20 AS prix_ttc 
FROM produits 
ORDER BY prix_ttc DESC;

-- Commandes triées par montant total
SELECT * FROM commandes ORDER BY montant_total DESC;

-- Nombre total de clients
SELECT COUNT(*) FROM clients;

-- Nombre de clients avec un téléphone
SELECT COUNT(telephone) FROM clients;

-- Nombre de clients distincts ayant commandé
SELECT COUNT(DISTINCT client_id) FROM commandes;

-- Nombre de produits par catégorie
SELECT categorie, COUNT(*) AS nombre_produits 
FROM produits 
GROUP BY categorie;

-- Montant total de toutes les commandes
SELECT SUM(montant_total) FROM commandes;

-- Quantité totale vendue
SELECT SUM(quantite) FROM commandes;

-- Chiffre d'affaires par client
SELECT client_id, SUM(montant_total) AS ca_total 
FROM commandes 
GROUP BY client_id;

-- Prix moyen des produits
SELECT AVG(prix) FROM produits;

-- Panier moyen (montant moyen des commandes)
SELECT AVG(montant_total) FROM commandes;

-- Prix moyen par catégorie
SELECT categorie, AVG(prix) AS prix_moyen 
FROM produits 
GROUP BY categorie;

-- Produit le moins cher
SELECT MIN(prix) FROM produits;

-- Produit le plus cher
SELECT MAX(prix) FROM produits;

-- Prix min et max par catégorie
SELECT 
    categorie, 
    MIN(prix) AS prix_min, 
    MAX(prix) AS prix_max 
FROM produits 
GROUP BY categorie;

-- Première et dernière commande
SELECT 
    MIN(date_commande) AS premiere_commande,
    MAX(date_commande) AS derniere_commande
FROM commandes;

-- Nombre de clients par ville
SELECT ville, COUNT(*) AS nb_clients 
FROM clients 
GROUP BY ville;

-- Résultat :
-- Paris     | 2
-- Lyon      | 2
-- Marseille | 1

-- Chiffre d'affaires par client
SELECT 
    client_id, 
    COUNT(*) AS nb_commandes,
    SUM(montant_total) AS ca_total 
FROM commandes 
GROUP BY client_id;

-- Stock total par catégorie
SELECT 
    categorie, 
    SUM(stock) AS stock_total,
    COUNT(*) AS nb_produits
FROM produits 
GROUP BY categorie;

-- Ventes par mois
SELECT 
    strftime('%Y-%m', date_commande) AS mois,
    COUNT(*) AS nb_ventes,
    SUM(montant_total) AS ca_mois
FROM commandes 
GROUP BY strftime('%Y-%m', date_commande)
ORDER BY mois;

-- Villes avec plus de 1 client
SELECT ville, COUNT(*) AS nb_clients 
FROM clients 
GROUP BY ville 
HAVING COUNT(*) > 1;

-- Clients ayant dépensé plus de 1000€
SELECT 
    client_id, 
    SUM(montant_total) AS ca_total 
FROM commandes 
GROUP BY client_id 
HAVING SUM(montant_total) > 1000;

-- Catégories avec un prix moyen > 500€
SELECT 
    categorie, 
    AVG(prix) AS prix_moyen 
FROM produits 
GROUP BY categorie 
HAVING AVG(prix) > 500;

-- Produits vendus plus de 2 fois
SELECT 
    produit_id, 
    COUNT(*) AS nb_ventes 
FROM commandes 
GROUP BY produit_id 
HAVING COUNT(*) > 2;

-- WHERE : filtre AVANT regroupement
SELECT categorie, COUNT(*) 
FROM produits 
WHERE prix > 100  -- Filtre les produits individuels
GROUP BY categorie;

-- HAVING : filtre APRÈS regroupement
SELECT categorie, COUNT(*) 
FROM produits 
GROUP BY categorie 
HAVING COUNT(*) > 2;  -- Filtre les groupes

-- Commandes avec nom du client
SELECT 
    commandes.id,
    clients.nom AS client,
    commandes.montant_total,
    commandes.date_commande
FROM commandes
INNER JOIN clients ON commandes.client_id = clients.id;

-- Avec alias (plus court)
SELECT 
    c.id,
    cl.nom AS client,
    c.montant_total,
    c.date_commande
FROM commandes c
INNER JOIN clients cl ON c.client_id = cl.id;

-- Commandes avec client ET produit
SELECT 
    cm.id,
    cl.nom AS client,
    p.nom AS produit,
    cm.quantite,
    cm.montant_total
FROM commandes cm
INNER JOIN clients cl ON cm.client_id = cl.id
INNER JOIN produits p ON cm.produit_id = p.id;

-- Résultat :
-- id | client        | produit      | quantite | montant_total
-- 1  | Alice Dupont  | MacBook Pro  | 1        | 2499.00
-- 2  | Bob Martin    | iPhone 15    | 2        | 1938.00
-- ...

Commandes          Clients
client_id=1   →   id=1 (Alice)
client_id=2   →   id=2 (Bob)
client_id=1   →   id=1 (Alice)

-- Tous les clients avec leurs commandes (même ceux sans commande)
SELECT 
    cl.nom,
    cl.email,
    COUNT(cm.id) AS nb_commandes,
    COALESCE(SUM(cm.montant_total), 0) AS ca_total
FROM clients cl
LEFT JOIN commandes cm ON cl.id = cm.client_id
GROUP BY cl.id;

-- Résultat :
-- Alice Dupont  | alice@mail.com | 3 | 3227.00
-- Bob Martin    | bob@mail.com   | 2 | 2116.00
-- Charlie       | ...            | 1 | 699.00
-- Diana         | ...            | 1 | 449.00
-- Eve           | ...            | 1 | 1299.00

-- Clients sans commande
SELECT 
    cl.nom,
    cl.email
FROM clients cl
LEFT JOIN commandes cm ON cl.id = cm.client_id
WHERE cm.id IS NULL;

Clients (TOUS)     Commandes
Alice      →       3 commandes
Bob        →       2 commandes
Charlie    →       1 commande
Diana      →       NULL (pas de commande)

-- Équivalent avec RIGHT JOIN (peu utilisé)
SELECT cl.nom, COUNT(cm.id) 
FROM commandes cm
RIGHT JOIN clients cl ON cm.client_id = cl.id
GROUP BY cl.id;

-- Préférez LEFT JOIN :
SELECT cl.nom, COUNT(cm.id) 
FROM clients cl
LEFT JOIN commandes cm ON cl.id = cm.client_id
GROUP BY cl.id;

-- PostgreSQL/MySQL seulement
SELECT 
    cl.nom AS client,
    p.nom AS produit
FROM clients cl
FULL OUTER JOIN produits p ON 1=1;  -- Produit cartésien

-- Toutes les combinaisons client-produit
SELECT 
    cl.nom AS client,
    p.nom AS produit
FROM clients
CROSS JOIN produits;

-- Si 5 clients et 7 produits = 35 résultats

-- Exemple : Table employés avec manager_id
-- Trouver les employés et leur manager

-- Créons d'abord la table
CREATE TABLE employes (
    id INTEGER PRIMARY KEY,
    nom VARCHAR(100),
    manager_id INTEGER
);

INSERT INTO employes VALUES
(1, 'Alice', NULL),      -- PDG
(2, 'Bob', 1),           -- Manager sous Alice
(3, 'Charlie', 1),       -- Manager sous Alice
(4, 'Diana', 2),         -- Employé sous Bob
(5, 'Eve', 2);           -- Employé sous Bob

-- Auto-jointure pour voir employé et son manager
SELECT 
    e.nom AS employe,
    m.nom AS manager
FROM employes e
LEFT JOIN employes m ON e.manager_id = m.id;

-- Résultat :
-- Alice   | NULL
-- Bob     | Alice
-- Charlie | Alice
-- Diana   | Bob
-- Eve     | Bob

-- Produits plus chers que le prix moyen
SELECT nom, prix 
FROM produits 
WHERE prix > (SELECT AVG(prix) FROM produits);

-- Clients ayant commandé le produit le plus cher
SELECT nom 
FROM clients 
WHERE id IN (
    SELECT client_id 
    FROM commandes 
    WHERE produit_id = (
        SELECT id FROM produits ORDER BY prix DESC LIMIT 1
    )
);

-- Produits jamais commandés
SELECT nom 
FROM produits 
WHERE id NOT IN (
    SELECT DISTINCT produit_id FROM commandes
);

-- Clients avec leur nombre de commandes
SELECT 
    nom,
    email,
    (SELECT COUNT(*) FROM commandes WHERE client_id = clients.id) AS nb_commandes
FROM clients;

-- Produits avec quantité vendue
SELECT 
    nom,
    prix,
    stock,
    (SELECT COALESCE(SUM(quantite), 0) 
     FROM commandes 
     WHERE produit_id = produits.id) AS quantite_vendue
FROM produits;

-- Top 3 clients par CA
SELECT * 
FROM (
    SELECT 
        client_id,
        SUM(montant_total) AS ca_total
    FROM commandes
    GROUP BY client_id
    ORDER BY ca_total DESC
    LIMIT 3
) AS top_clients;

-- Ventes par mois avec cumul
SELECT 
    mois,
    ca_mois,
    (SELECT SUM(ca_mois) 
     FROM ventes_mensuelles vm2 
     WHERE vm2.mois <= vm1.mois) AS ca_cumule
FROM (
    SELECT 
        strftime('%Y-%m', date_commande) AS mois,
        SUM(montant_total) AS ca_mois
    FROM commandes
    GROUP BY strftime('%Y-%m', date_commande)
) vm1;

-- Clients ayant au moins une commande
SELECT nom 
FROM clients c
WHERE EXISTS (
    SELECT 1 FROM commandes WHERE client_id = c.id
);

-- Clients sans commande
SELECT nom 
FROM clients c
WHERE NOT EXISTS (
    SELECT 1 FROM commandes WHERE client_id = c.id
);

-- Produits qui ont été commandés
SELECT nom 
FROM produits p
WHERE EXISTS (
    SELECT 1 FROM commandes WHERE produit_id = p.id
);

-- Concaténer
SELECT nom || ' - ' || email AS info FROM clients;

-- Majuscules / Minuscules
SELECT UPPER(nom), LOWER(email) FROM clients;

-- Longueur
SELECT nom, LENGTH(nom) AS longueur FROM clients;

-- Sous-chaîne
SELECT SUBSTR(email, 1, 5) AS debut_email FROM clients;

-- Remplacer
SELECT REPLACE(telephone, '06', '07') FROM clients;

-- Supprimer espaces
SELECT TRIM('  texte  ');  -- 'texte'
SELECT LTRIM('  texte');   -- 'texte  '
SELECT RTRIM('texte  ');   -- '  texte'

-- Arrondi
SELECT ROUND(prix, 0) FROM produits;  -- Entier
SELECT ROUND(3.14159, 2);  -- 3.14

-- Plafond / Plancher
SELECT CEIL(3.2);   -- 4
SELECT FLOOR(3.9);  -- 3

-- Valeur absolue
SELECT ABS(-10);  -- 10

-- Puissance
SELECT POWER(2, 3);  -- 8

-- Racine carrée
SELECT SQRT(16);  -- 4

-- Aléatoire
SELECT RANDOM();  -- Nombre aléatoire

-- Date actuelle
SELECT DATE('now');        -- 2024-11-29
SELECT DATETIME('now');    -- 2024-11-29 15:30:45
SELECT TIME('now');        -- 15:30:45

-- Extraire des parties
SELECT 
    strftime('%Y', date_commande) AS annee,
    strftime('%m', date_commande) AS mois,
    strftime('%d', date_commande) AS jour
FROM commandes;

-- Calculs de dates
SELECT DATE('now', '+7 days');     -- Dans 7 jours
SELECT DATE('now', '-1 month');    -- Il y a 1 mois
SELECT DATE('now', '+1 year');     -- Dans 1 an

-- Différence entre dates (en jours)
SELECT julianday('now') - julianday(date_inscription) AS jours_depuis_inscription
FROM clients;

-- Age d'une commande
SELECT 
    id,
    date_commande,
    julianday('now') - julianday(date_commande) AS jours_depuis_commande
FROM commandes;

-- CASE (comme if/else)
SELECT 
    nom,
    prix,
    CASE 
        WHEN prix < 100 THEN 'Bon marché'
        WHEN prix < 500 THEN 'Moyen'
        WHEN prix < 1000 THEN 'Cher'
        ELSE 'Très cher'
    END AS gamme_prix
FROM produits;

-- CASE avec agrégations
SELECT 
    SUM(CASE WHEN prix < 500 THEN 1 ELSE 0 END) AS nb_pas_cher,
    SUM(CASE WHEN prix >= 500 THEN 1 ELSE 0 END) AS nb_cher
FROM produits;

-- COALESCE (première valeur non NULL)
SELECT 
    nom,
    COALESCE(telephone, 'Non renseigné') AS tel
FROM clients;

-- NULLIF (retourne NULL si égal)
SELECT NULLIF(stock, 0) FROM produits;  -- NULL si stock = 0

-- Insérer une ligne
INSERT INTO clients (nom, email, ville) 
VALUES ('Nouveau Client', 'nouveau@mail.com', 'Paris');

-- Insérer plusieurs lignes
INSERT INTO clients (nom, email, ville) VALUES
('Client 1', 'c1@mail.com', 'Lyon'),
('Client 2', 'c2@mail.com', 'Paris'),
('Client 3', 'c3@mail.com', 'Marseille');

-- Insérer depuis une autre table
INSERT INTO clients_archive 
SELECT * FROM clients WHERE date_inscription < '2023-01-01';

-- Insérer et récupérer l'ID (SQLite)
INSERT INTO clients (nom, email) VALUES ('Test', 'test@mail.com');
SELECT last_insert_rowid();

-- Modifier un client
UPDATE clients 
SET telephone = '0601020304' 
WHERE id = 1;

-- Modifier plusieurs colonnes
UPDATE clients 
SET 
    telephone = '0605060708',
    ville = 'Lyon'
WHERE id = 2;

-- Augmenter tous les prix de 10%
UPDATE produits 
SET prix = prix * 1.10;

-- Augmenter seulement une catégorie
UPDATE produits 
SET prix = prix * 1.05 
WHERE categorie = 'Informatique';

-- Mettre à jour selon une condition
UPDATE produits 
SET stock = stock - 1 
WHERE id = 3 AND stock > 0;

-- ⚠️ ATTENTION : Oublier WHERE modifie TOUTES les lignes !
UPDATE clients SET ville = 'Paris';  -- DANGER !

-- Supprimer un client
DELETE FROM clients WHERE id = 10;

-- Supprimer selon condition
DELETE FROM commandes WHERE date_commande < '2023-01-01';

-- Supprimer tout (DANGER !)
DELETE FROM clients;  -- Supprime TOUS les clients

-- Mieux : TRUNCATE (plus rapide, remet compteur à 0)
-- SQLite n'a pas TRUNCATE, utiliser :
DELETE FROM clients;
DELETE FROM sqlite_sequence WHERE name='clients';  -- Reset auto-increment

-- Commencer une transaction
BEGIN TRANSACTION;

-- Opérations
UPDATE produits SET stock = stock - 1 WHERE id = 1;
INSERT INTO commandes (client_id, produit_id, quantite, prix_unitaire, montant_total) 
VALUES (1, 1, 1, 2499.00, 2499.00);

-- Si tout OK : valider
COMMIT;

-- Si erreur : annuler
ROLLBACK;

BEGIN TRANSACTION;

-- Vérifier le stock
SELECT stock FROM produits WHERE id = 1;  -- Si < 1, erreur

-- Si OK :
UPDATE produits SET stock = stock - 1 WHERE id = 1;
INSERT INTO commandes (...) VALUES (...);
COMMIT;

-- Si KO :
ROLLBACK;

-- Table avec contraintes
CREATE TABLE utilisateurs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age INTEGER CHECK (age >= 18),
    solde DECIMAL(10,2) DEFAULT 0.00 CHECK (solde >= 0),
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actif BOOLEAN DEFAULT 1
);

-- Table avec clés étrangères et CASCADE
CREATE TABLE commentaires (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    utilisateur_id INTEGER NOT NULL,
    texte TEXT NOT NULL,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) 
        ON DELETE CASCADE  -- Si utilisateur supprimé, ses commentaires aussi
        ON UPDATE CASCADE  -- Si ID utilisateur change, ça suit
);

-- Ajouter une colonne
ALTER TABLE clients ADD COLUMN date_naissance DATE;

-- Renommer une colonne (SQLite 3.25+)
ALTER TABLE clients RENAME COLUMN telephone TO tel;

-- Renommer une table
ALTER TABLE clients RENAME TO customers;

-- Supprimer une colonne (pas toutes les versions SQLite)
-- En SQLite, il faut recréer la table sans la colonne

-- Supprimer une table
DROP TABLE IF EXISTS clients_archive;

-- Supprimer plusieurs tables
DROP TABLE IF EXISTS table1, table2, table3;

-- Index simple
CREATE INDEX idx_clients_email ON clients(email);

-- Index composite
CREATE INDEX idx_commandes_client_date ON commandes(client_id, date_commande);

-- Index unique
CREATE UNIQUE INDEX idx_clients_email_unique ON clients(email);

-- Supprimer un index
DROP INDEX IF EXISTS idx_clients_email;

-- Voir les index
SELECT * FROM sqlite_master WHERE type = 'index';

-- Combiner deux requêtes (sans doublons)
SELECT nom, email FROM clients
UNION
SELECT nom, email FROM fournisseurs;

-- Avec doublons (UNION ALL)
SELECT ville FROM clients
UNION ALL
SELECT ville FROM fournisseurs;

-- Différence : UNION élimine doublons, UNION ALL garde tout

-- Sans CTE (difficile à lire)
SELECT * FROM (
    SELECT client_id, SUM(montant_total) AS ca
    FROM commandes
    GROUP BY client_id
) WHERE ca > 1000;

-- Avec CTE (plus clair)
WITH ca_clients AS (
    SELECT 
        client_id, 
        SUM(montant_total) AS ca_total
    FROM commandes
    GROUP BY client_id
)
SELECT 
    c.nom,
    ca.ca_total
FROM ca_clients ca
JOIN clients c ON ca.client_id = c.id
WHERE ca.ca_total > 1000;

-- CTE multiples
WITH 
    ventes_2024 AS (
        SELECT * FROM commandes 
        WHERE strftime('%Y', date_commande) = '2024'
    ),
    top_clients AS (
        SELECT client_id, SUM(montant_total) AS ca
        FROM ventes_2024
        GROUP BY client_id
        ORDER BY ca DESC
        LIMIT 10
    )
SELECT 
    c.nom,
    tc.ca
FROM top_clients tc
JOIN clients c ON tc.client_id = c.id;

-- Classement (RANK)
SELECT 
    nom,
    prix,
    RANK() OVER (ORDER BY prix DESC) AS rang
FROM produits;

-- Numéro de ligne
SELECT 
    nom,
    prix,
    ROW_NUMBER() OVER (ORDER BY prix DESC) AS numero
FROM produits;

-- Partition (classement par catégorie)
SELECT 
    nom,
    categorie,
    prix,
    RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang_categorie
FROM produits;

-- Cumul (running total)
SELECT 
    date_commande,
    montant_total,
    SUM(montant_total) OVER (ORDER BY date_commande) AS ca_cumule
FROM commandes;

-- LAG / LEAD (valeur précédente/suivante)
SELECT 
    date_commande,
    montant_total,
    LAG(montant_total) OVER (ORDER BY date_commande) AS vente_precedente
FROM commandes;

-- Exemple : Hiérarchie d'employés
WITH RECURSIVE hierarchie AS (
    -- Base : PDG
    SELECT id, nom, manager_id, 1 AS niveau
    FROM employes
    WHERE manager_id IS NULL

    UNION ALL

    -- Récursion : employés sous les managers
    SELECT e.id, e.nom, e.manager_id, h.niveau + 1
    FROM employes e
    JOIN hierarchie h ON e.manager_id = h.id
)
SELECT * FROM hierarchie ORDER BY niveau, nom;

-- Voir le plan d'exécution
EXPLAIN QUERY PLAN
SELECT c.nom, cm.montant_total
FROM clients c
JOIN commandes cm ON c.id = cm.client_id
WHERE c.ville = 'Paris';

-- Si vous voyez "SCAN TABLE" → mauvais (lit toute la table)
-- Si vous voyez "SEARCH TABLE USING INDEX" → bon (utilise un index)

-- Avant (lent)
SELECT * FROM clients WHERE email = 'alice@mail.com';

-- Créer un index
CREATE INDEX idx_clients_email ON clients(email);

-- Après : 100x plus rapide

-- ❌ Mauvais (ramène tout)
SELECT * FROM produits;

-- ✅ Bon (seulement ce qu'on utilise)
SELECT id, nom, prix FROM produits;

-- ❌ Lent (empêche l'utilisation d'index)
SELECT * FROM clients WHERE UPPER(nom) = 'ALICE';

-- ✅ Rapide (utilise l'index)
SELECT * FROM clients WHERE nom = 'Alice';

-- ❌ Plus lent
SELECT * FROM clients 
WHERE id IN (SELECT client_id FROM commandes);

-- ✅ Plus rapide
SELECT * FROM clients c
WHERE EXISTS (SELECT 1 FROM commandes WHERE client_id = c.id);

-- Ne jamais charger 1 million de lignes d'un coup
SELECT * FROM commandes ORDER BY date_commande DESC LIMIT 100;

-- ❌ ERREUR si quantite = 0
SELECT montant_total / quantite FROM commandes;

-- ✅ Sécurisé
SELECT 
    CASE 
        WHEN quantite = 0 THEN NULL 
        ELSE montant_total / quantite 
    END AS prix_unitaire
FROM commandes;

-- ❌ Ne fonctionne pas
SELECT * FROM clients WHERE telephone = NULL;

-- ✅ Correct
SELECT * FROM clients WHERE telephone IS NULL;

-- ❌ DANGER : modifie TOUTES les lignes
UPDATE produits SET prix = 999;

-- ✅ Toujours un WHERE
UPDATE produits SET prix = 999 WHERE id = 1;

# Python - NE JAMAIS FAIRE ÇA
email = input("Email : ")
query = f"SELECT * FROM clients WHERE email = '{email}'"
# Si email = "'; DROP TABLE clients; --" → BOOM

# Python - TOUJOURS faire ça
email = input("Email : ")
cursor.execute("SELECT * FROM clients WHERE email = ?", (email,))

// PHP (PDO)
$stmt = $pdo->prepare("SELECT * FROM clients WHERE email = ?");
$stmt->execute([$email]);

// Node.js (parameterized query)
db.query("SELECT * FROM clients WHERE email = ?", [email], callback);

-- Créer un utilisateur (MySQL/PostgreSQL)
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

-- Donner des droits
GRANT SELECT, INSERT, UPDATE ON entreprise.* TO 'app_user'@'localhost';

-- Retirer des droits
REVOKE DELETE ON entreprise.* FROM 'app_user'@'localhost';

-- Voir les droits
SHOW GRANTS FOR 'app_user'@'localhost';

-- ❌ JAMAIS ça
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    password VARCHAR(50)  -- EN CLAIR = DANGER
);

-- ✅ Toujours hasher
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    password_hash VARCHAR(255)  -- Hash bcrypt/argon2
);

import bcrypt

# Hasher
password = "mon_password"
hashed = bcrypt.hashpw(password.encode(), bcrypt.gensalt())

# Vérifier
bcrypt.checkpw(password.encode(), hashed)  # True/False

-- 1. Lister tous les clients de Paris
-- Votre réponse :


-- 2. Produits de plus de 500€, triés par prix décroissant
-- Votre réponse :


-- 3. Nombre total de commandes
-- Votre réponse :


-- 4. Clients dont le nom commence par "A"
-- Votre réponse :


-- 5. Prix moyen des produits de catégorie "Informatique"
-- Votre réponse :

-- 1. Liste des commandes avec nom du client et du produit


-- 2. Clients qui n'ont jamais commandé


-- 3. Top 3 produits les plus vendus (quantité)


-- 4. Chiffre d'affaires par ville


-- 5. Clients ayant dépensé plus de 1000€

-- 1. Produits jamais commandés


-- 2. Client ayant la commande la plus récente


-- 3. Produits avec stock inférieur à la moyenne


-- 4. Mois avec le meilleur CA


-- 5. Clients ayant commandé au moins 3 produits différents