B05 - SQL - interroger et modifier les données

B05 - SQL : interroger (SELECT) et modifier (INSERT, UPDATE, DELETE)

Objectif

Écrire des requêtes de la plus simple à la plus complexe : filtres, tris, jointures, agrégats, sous-requêtes, UNION, auto-jointures - puis modifier les données sans casse. Exercices d’origine : Révision SQL B1 (MySQL) et Révision SQL B2 (SQL Server).

Bases d'entraînement

Northwind et Pubs (Microsoft) : github.com/microsoft/sql-server-samples → samples/databases/northwind-pubs (script instnwnd.sql). Version MySQL : github.com/busynovadad/northwind-MySQL.

---

1. La structure d’un SELECT

SELECT   colonnes, agrégats           -- 5. quoi afficher
FROM     table                        -- 1. d'où
JOIN     autre_table ON condition     -- 1. (avec quoi)
WHERE    condition sur les lignes     -- 2. filtrer les lignes
GROUP BY colonnes                     -- 3. regrouper
HAVING   condition sur les groupes    -- 4. filtrer les groupes
ORDER BY colonne DESC;                -- 6. trier

flowchart LR
  F["1. FROM / JOIN"] --> W["2. WHERE"] --> G["3. GROUP BY"] --> H["4. HAVING"] --> S["5. SELECT"] --> O["6. ORDER BY"] --> T["7. TOP / LIMIT"]

C'est l' ordre d'exécution réel. D'où : on ne peut pas utiliser un alias du SELECT dans le WHERE (il n'existe pas encore), mais on peut dans le ORDER BY.

2. Requêtes simples

SELECT * FROM Products;                                       -- tout (à éviter en production)
SELECT ProductID, ProductName, UnitPrice FROM Products;       -- colonnes choisies
SELECT ProductName AS Nom, UnitPrice AS Prix FROM Products;   -- alias
SELECT DISTINCT Country FROM Customers;                       -- sans doublons
SELECT TOP 5 ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC;   -- T-SQL
-- MariaDB : ... ORDER BY UnitPrice DESC LIMIT 5;

Filtrer avec WHERE

Opérateur	Exemple
=, <>, <, >, <=, >=	WHERE UnitPrice > 100
AND, OR, NOT	WHERE Country = 'France' AND City = 'Nantes'
BETWEEN … AND …	WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
IN (…)	WHERE Country IN ('UK', 'USA')
LIKE (% = n’importe quoi, _ = 1 caractère)	WHERE CompanyName LIKE 'A%'
IS NULL / IS NOT NULL	WHERE Region IS NULL - jamais = NULL !

3. Les jointures

flowchart LR
  subgraph INNER["INNER JOIN"]
    i["Seulement les lignes<br/>qui ont une correspondance<br/>des DEUX côtés"]
  end
  subgraph LEFT["LEFT JOIN"]
    l["TOUTES les lignes de GAUCHE<br/>+ correspondances à droite<br/>(NULL si aucune)"]
  end
  subgraph RIGHT["RIGHT JOIN"]
    r["TOUTES les lignes de DROITE<br/>+ correspondances à gauche"]
  end
  subgraph FULL["FULL OUTER JOIN"]
    f["Tout, des deux côtés"]
  end

-- INNER : commandes avec le nom du client
SELECT o.OrderID, c.CompanyName
FROM Orders AS o
INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID;
 
-- 3 tables
SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Employees e ON o.EmployeeID = e.EmployeeID;
 
-- LEFT + IS NULL = « ce qui n'a PAS de correspondance »
-- Clients qui n'ont JAMAIS commandé :
SELECT c.CompanyName
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;
 
-- Métiers (Pubs) attribués à personne :
SELECT j.job_desc
FROM jobs j
LEFT JOIN employee e ON e.job_id = j.job_id
WHERE e.emp_id IS NULL;

A RIGHT JOIN B = B LEFT JOIN A. Prends l'habitude d'écrire toujours des LEFT JOIN en mettant la table « principale » à gauche : c'est plus lisible.

Auto-jointure (une table jointe à elle-même)

-- Chaque employé avec le nom de son manager (Northwind)
SELECT e.FirstName + ' ' + e.LastName AS Employe,
       m.FirstName + ' ' + m.LastName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ReportsTo = m.EmployeeID;   -- LEFT : garder le grand patron (sans chef)

4. Agrégats et regroupements

Fonction	Résultat
COUNT(*)	Nombre de lignes
COUNT(colonne)	Nombre de valeurs non NULL
COUNT(DISTINCT colonne)	Nombre de valeurs différentes
SUM, AVG, MIN, MAX	Somme, moyenne, minimum, maximum

SELECT COUNT(DISTINCT Country) FROM Customers;                 -- nombre de pays
 
SELECT Country, COUNT(*) AS NbClients                          -- clients par pays
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 5                                            -- seulement les pays > 5 clients
ORDER BY NbClients DESC;
 
-- Montant de chaque commande
SELECT OrderID, SUM(UnitPrice * Quantity * (1 - Discount)) AS Montant
FROM [Order Details]
GROUP BY OrderID;
 
-- Nombre de commandes par client, y compris ceux à 0
SELECT c.CompanyName, COUNT(o.OrderID) AS NbCommandes          -- COUNT(o.OrderID) et pas COUNT(*) !
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

Règles du GROUP BY

1. Toute colonne du SELECT qui n’est pas dans un agrégat doit être dans le GROUP BY.
2. WHERE filtre les lignes avant regroupement ; HAVING filtre les groupes après.
3. Avec un LEFT JOIN, compte une colonne de la table de droite (COUNT(o.OrderID)), sinon les « zéro » valent 1.
4. ISNULL(SUM(...), 0) (T-SQL) / IFNULL (MariaDB) / COALESCE (standard) pour afficher 0 au lieu de NULL.

5. Sous-requêtes

-- IN : produits des catégories Condiments et Seafood
SELECT ProductName FROM Products
WHERE CategoryID IN (SELECT CategoryID FROM Categories
                     WHERE CategoryName IN ('Condiments', 'Seafood'));
 
-- NOT IN : clients sans commande (⚠ NOT IN échoue si la sous-requête renvoie un NULL → préférer NOT EXISTS)
SELECT CompanyName FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE CustomerID IS NOT NULL);
 
-- EXISTS : fournisseurs ayant au moins un produit en rupture
SELECT s.CompanyName FROM Suppliers s
WHERE EXISTS (SELECT 1 FROM Products p
              WHERE p.SupplierID = s.SupplierID AND p.UnitsInStock = 0);
 
-- Sous-requête scalaire : produits plus chers que la moyenne
SELECT ProductName, UnitPrice FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

6. UNION

SELECT City FROM Customers
UNION                -- fusionne et SUPPRIME les doublons (trie → plus lent)
SELECT City FROM Suppliers;
 
SELECT City FROM Customers
UNION ALL            -- garde tout, doublons compris (plus rapide)
SELECT City FROM Suppliers;

Les deux requêtes doivent avoir le même nombre de colonnes, de types compatibles.

7. Modifier les données (DML)

INSERT INTO Salles (nom_salle, etage) VALUES (N'B201', 2), (N'B202', 2);
INSERT INTO Archives (id, nom) SELECT id, nom FROM Clients WHERE actif = 0;   -- copier depuis une requête
 
UPDATE Postes SET adresse_ip = '192.168.10.50' WHERE id_poste = 7;
UPDATE Products SET UnitPrice = UnitPrice * 1.05 WHERE CategoryID = 2;        -- +5 %
 
DELETE FROM Postes WHERE date_achat < '2018-01-01';

UPDATE ou DELETE sans WHERE = toutes les lignes. Réflexe : écrire d'abord le SELECT avec le même WHERE, vérifier le résultat, puis transformer en UPDATE/DELETE. Ou travailler dans une transaction :

BEGIN TRANSACTION;
DELETE FROM Postes WHERE id_salle = 3;
SELECT @@ROWCOUNT;        -- combien de lignes touchées ?
ROLLBACK;                 -- (ou COMMIT si c'est bon)

8. Exercices corrigés

Art-2-Rue (fiche B02 - Modélisation Merise et UML)

Script complet (création + données + requêtes) : scripts/bdd/art2rue_creation_et_requetes.sql.

1. Annuaire des lieux trié par quartier

Correction

SELECT adresse, quartier FROM Lieux ORDER BY quartier;

2. Œuvres créées après le 01/01/2023 avec le pseudonyme de l’artiste

Correction

SELECT o.titre, o.date_creation, a.pseudonyme
FROM Oeuvres o
JOIN Oeuvre_Artiste oa ON oa.id_oeuvre = o.id_oeuvre
JOIN Artistes a ON a.id_artiste = oa.id_artiste
WHERE o.date_creation > '2023-01-01';

3. Nombre d’œuvres par technique, y compris les techniques jamais utilisées

Correction

SELECT t.nom, COUNT(ot.id_oeuvre) AS nb_oeuvres
FROM Techniques t
LEFT JOIN Oeuvre_Technique ot ON ot.id_technique = t.id_technique
GROUP BY t.id_technique, t.nom;

4. Photographes ayant pris des œuvres de « C215 » dans le quartier « Centre »

Correction

SELECT DISTINCT b.nom, b.prenom
FROM Benevoles b
JOIN Photos p ON p.id_benevole = b.id_benevole
JOIN Oeuvres o ON o.id_oeuvre = p.id_oeuvre
JOIN Lieux l ON l.id_lieu = o.id_lieu
JOIN Oeuvre_Artiste oa ON oa.id_oeuvre = o.id_oeuvre
JOIN Artistes a ON a.id_artiste = oa.id_artiste
WHERE a.pseudonyme = N'C215' AND l.quartier = N'Centre';

Northwind

5. L’employé qui a généré le plus de chiffre d’affaires

Correction

SELECT TOP 1 e.FirstName, e.LastName,
       SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS CA
FROM Employees e
JOIN Orders o ON o.EmployeeID = e.EmployeeID
JOIN [Order Details] od ON od.OrderID = o.OrderID
GROUP BY e.EmployeeID, e.FirstName, e.LastName
ORDER BY CA DESC;

6. Catégories dont au moins un produit dépasse 100 $

Correction

SELECT CategoryName FROM Categories c
WHERE EXISTS (SELECT 1 FROM Products p WHERE p.CategoryID = c.CategoryID AND p.UnitPrice > 100);

7. Clients dont le total commandé dépasse 20 000 $

Correction

SELECT c.CompanyName, SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS Total
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
GROUP BY c.CustomerID, c.CompanyName
HAVING SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) > 20000
ORDER BY Total DESC;

Voir aussi

• B04 - SQL - créer la structure (DDL) · B06 - SQL Server - installation et outils · B07 - MariaDB et MySQL
• Retour : 00 - Index général