Aller au contenu
Réussir mon BTS SIO

Tape une notion, une commande ou le nom d'une épreuve.

Par exemple

Chapitre 2131 minDébutant

Exercices SQL corrigés type examen

Par Josselyn C., prof de BTS SIO. Mis à jour le .

À la fin de ce chapitre, tu sauras

  • enchaîner trente requêtes, de la plus simple à la plus difficile ;
  • traiter un cas complet type E7 SLAM ;
  • faire évoluer une base en une heure, comme à l'E6 SLAM ;
  • trouver l'erreur dans une requête fausse.

8 exercices corrigés

Ces exercices SQL corrigés reprennent le format des sujets du BTS SIO : trente requêtes progressives, un cas type E7 SLAM, un cas type E6 SLAM et sept requêtes fausses à corriger. Tous partent de la base d'exemple parc dans son état initial : rejoue son script avant de commencer, et entre deux exercices qui modifient les données.

Série progressive : trente requêtes

Exercice 1 sur 8 Série A, une seule table Réussi À revoir

  1. Les logiciels de la catégorie bureautique, par ordre alphabétique.
  2. Le nombre de postes de chaque type.
  3. Le numéro et le titre des tickets ouverts en octobre 2026, du plus ancien au plus récent.
  4. La durée moyenne, arrondie à la minute, des interventions de plus d'une heure.
  5. Les trois interventions les plus longues : numéro, ticket et durée.
  6. Les tickets dont le titre contient le mot « écran », quelle que soit la casse.
Voir la réponseMasquer la réponse de l'exercice 1
sql
-- 1
SELECT nom FROM logiciel WHERE categorie = 'bureautique' ORDER BY nom;
-- Acrobat Pro, LibreOffice, Microsoft 365

-- 2
SELECT type_poste, COUNT(*) AS nb FROM poste GROUP BY type_poste;
-- fixe : 7, portable : 8

-- 3
SELECT id_ticket, titre FROM ticket
WHERE date_ouverture >= '2026-10-01' AND date_ouverture < '2026-11-01'
ORDER BY date_ouverture;
-- tickets 8 à 14

-- 4
SELECT ROUND(AVG(duree_min)) AS moyenne FROM intervention WHERE duree_min > 60;
-- 145 (6 interventions, 870 minutes)

-- 5
SELECT id_intervention, id_ticket, duree_min FROM intervention
ORDER BY duree_min DESC, id_intervention
LIMIT 3;
-- 11 (ticket 9, 240 min), 12 (ticket 9, 180 min),
-- 13 (ticket 9, 150 min)

-- 6
SELECT id_ticket, titre FROM ticket WHERE titre LIKE '%écran%';
-- tickets 1 et 10, « Écran noir au démarrage »

Pour la question 6, l'interclassement par défaut ignore la casse et les accents : '%écran%' trouve « Écran ». Sous PostgreSQL, il faudrait ILIKE, et un traitement des accents.

Tu avais trouvé ?

Exercice 2 sur 8 Série B, jointures Réussi À revoir

  1. Pour chaque intervention du ticket 5 : le nom du technicien et la durée.
  2. Les postes des employés du service commercial : numéro d'inventaire, nom et prénom de l'utilisateur.
  3. Les logiciels installés sur PC-0010, avec leur éditeur.
  4. Le nombre de tickets ouverts par chaque service, y compris les services sans ticket.
  5. Pour chaque employé, y compris ceux sans poste, le nombre d'installations de logiciels sur l'ensemble de ses postes.
  6. Les interventions sur des tickets demandés par la logistique : numéro du ticket, nom du demandeur, nom du technicien, durée.
Voir la réponseMasquer la réponse de l'exercice 2
sql
-- 1
SELECT e.nom, i.duree_min
FROM intervention i
JOIN employe e ON e.id_employe = i.id_technicien
WHERE i.id_ticket = 5
ORDER BY i.date_intervention;
-- Diallo 90, Mercier 120

-- 2
SELECT p.num_inventaire, e.nom, e.prenom
FROM poste p
JOIN employe e ON e.id_employe = p.id_employe
JOIN service s ON s.id_service = e.id_service
WHERE s.code = 'COMM'
ORDER BY p.num_inventaire;
-- PC-0004 Benali, PC-0005 Roux, PC-0006 Petit, PC-0015 Benali

-- 3
SELECT l.nom, l.editeur
FROM poste p
JOIN installation i ON i.id_poste = p.id_poste
JOIN logiciel l     ON l.id_logiciel = i.id_logiciel
WHERE p.num_inventaire = 'PC-0010'
ORDER BY l.nom;
-- Firefox, KeePassXC, Microsoft 365, Wireshark

-- 4
SELECT s.libelle, COUNT(t.id_ticket) AS nb_tickets
FROM service s
LEFT JOIN employe e ON e.id_service = s.id_service
LEFT JOIN ticket t  ON t.id_demandeur = e.id_employe
GROUP BY s.id_service, s.libelle
ORDER BY nb_tickets DESC, s.libelle;
-- Comptabilité 5, Commercial 4, Logistique 4, Direction 1,
-- Informatique 0, Juridique 0

-- 5
SELECT e.nom, COUNT(i.id_logiciel) AS nb_installations
FROM employe e
LEFT JOIN poste p        ON p.id_employe = e.id_employe
LEFT JOIN installation i ON i.id_poste = p.id_poste
GROUP BY e.id_employe, e.nom
ORDER BY nb_installations DESC, e.nom;
-- Benali 6 ; Diallo, Lambert, Mercier, Morel 4 ; ...
-- Rousseau 2 ; Bonnet, Martin 0

-- 6
SELECT t.id_ticket, dem.nom AS demandeur, tech.nom AS technicien, i.duree_min
FROM intervention i
JOIN ticket t     ON t.id_ticket = i.id_ticket
JOIN employe dem  ON dem.id_employe = t.id_demandeur
JOIN service s    ON s.id_service = dem.id_service
JOIN employe tech ON tech.id_employe = i.id_technicien
WHERE s.code = 'LOGI'
ORDER BY i.date_intervention;
-- 6 interventions : ticket 6 (Diallo),
-- ticket 9 (Mercier, Diallo, Lambert), ticket 13 (Mercier, Diallo)

Pour la question 5, les deux LEFT JOIN sont nécessaires : avec un JOIN sur installation, Inès Bonnet et Paul Martin disparaîtraient. Sofiane Benali a 6 installations : Firefox, Microsoft 365 et Odoo sont installés sur chacun de ses deux postes.

Tu avais trouvé ?

Exercice 3 sur 8 Série C, agrégats Réussi À revoir

  1. Le temps total d'intervention consacré aux tickets de chaque service demandeur, du plus grand au plus petit.
  2. Le nombre de tickets ouverts par mois, au format AAAA-MM.
  3. Les marques dont le prix moyen d'achat dépasse 800 €.
  4. Pour chaque catégorie de logiciel : le nombre de logiciels et le nombre total d'installations.
  5. Les techniciens dont la durée moyenne d'intervention dépasse une heure.
  6. Pour chaque priorité, le pourcentage de tickets résolus, avec une décimale.
Voir la réponseMasquer la réponse de l'exercice 3
sql
-- 1
SELECT s.libelle, SUM(i.duree_min) AS minutes
FROM intervention i
JOIN ticket t  ON t.id_ticket = i.id_ticket
JOIN employe e ON e.id_employe = t.id_demandeur
JOIN service s ON s.id_service = e.id_service
GROUP BY s.id_service, s.libelle
ORDER BY minutes DESC;
-- Logistique 735, Commercial 270, Comptabilité 250

-- 2
SELECT DATE_FORMAT(date_ouverture, '%Y-%m') AS mois, COUNT(*) AS nb
FROM ticket
GROUP BY mois
ORDER BY mois;
-- 2026-08 : 1, 2026-09 : 6, 2026-10 : 7

-- 3
SELECT marque, ROUND(AVG(prix_achat), 2) AS prix_moyen
FROM poste
GROUP BY marque
HAVING AVG(prix_achat) > 800;
-- Dell 853.00, Lenovo 1209.00

-- 4
SELECT l.categorie,
       COUNT(DISTINCT l.id_logiciel) AS nb_logiciels,
       COUNT(i.id_poste) AS nb_installations
FROM logiciel l
LEFT JOIN installation i ON i.id_logiciel = l.id_logiciel
GROUP BY l.categorie
ORDER BY l.categorie;
-- bureautique 3 / 14, gestion 2 / 9, navigateur 1 / 14,
-- réseau 1 / 3, sécurité 1 / 4

-- 5
SELECT id_technicien, ROUND(AVG(duree_min), 1) AS moyenne
FROM intervention
GROUP BY id_technicien
HAVING AVG(duree_min) > 60;
-- 10 : 127.5, 12 : 64.3

-- 6
SELECT priorite,
       COUNT(*) AS nb,
       ROUND(100 * SUM(CASE WHEN statut = 'resolu' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolus
FROM ticket
GROUP BY priorite
ORDER BY pct_resolus DESC;
-- haute 75.0, normale 60.0, critique 50.0, basse 33.3

Question 1 : la Direction n'apparaît pas, son seul ticket n'a pas d'intervention (jointure interne). Question 2 : MySQL accepte l'alias mois dans le GROUP BY ; la norme demanderait de répéter l'expression. Question 4 : COUNT(DISTINCT l.id_logiciel) est indispensable, chaque logiciel étant répété autant de fois qu'il a d'installations.

Tu avais trouvé ?

Exercice 4 sur 8 Série D, sous-requêtes et ensembles Réussi À revoir

  1. Les employés qui n'ont jamais ouvert de ticket, avec NOT EXISTS.
  2. Le ou les postes les plus anciens.
  3. Les tickets dont la durée totale d'intervention dépasse la durée moyenne par ticket.
  4. Les logiciels installés sur tous les postes du service informatique.
  5. Les employés qui ont un poste mais n'ont jamais ouvert de ticket, avec un opérateur ensembliste.
  6. Pour chaque service, l'employé embauché le plus anciennement.
Voir la réponseMasquer la réponse de l'exercice 4
sql
-- 1
SELECT e.nom FROM employe e
WHERE NOT EXISTS (SELECT 1 FROM ticket t WHERE t.id_demandeur = e.id_employe)
ORDER BY e.nom;
-- Bonnet, Diallo, Lambert, Mercier

-- 2
SELECT num_inventaire, date_achat FROM poste
WHERE date_achat = (SELECT MIN(date_achat) FROM poste)
ORDER BY num_inventaire;
-- PC-0001, PC-0003, PC-0013 (14 février 2019)

-- 3
WITH par_ticket AS (
  SELECT id_ticket, SUM(duree_min) AS total FROM intervention GROUP BY id_ticket
)
SELECT id_ticket, total FROM par_ticket
WHERE total > (SELECT AVG(total) FROM par_ticket)
ORDER BY total DESC;
-- 9 (570), 5 (210), 13 (150) ; la moyenne est de 114,1 minutes

-- 4
SELECT l.nom FROM logiciel l
WHERE NOT EXISTS (
  SELECT 1 FROM poste p
  JOIN employe e ON e.id_employe = p.id_employe
  JOIN service s ON s.id_service = e.id_service
  WHERE s.code = 'INFO'
    AND NOT EXISTS (SELECT 1 FROM installation i
                    WHERE i.id_poste = p.id_poste AND i.id_logiciel = l.id_logiciel)
)
ORDER BY l.nom;
-- Firefox, KeePassXC, Microsoft 365, Wireshark

-- 5
SELECT id_employe FROM poste WHERE id_employe IS NOT NULL
EXCEPT
SELECT id_demandeur FROM ticket
ORDER BY id_employe;
-- 10, 11, 12 (Mercier, Lambert, Diallo)

-- 6
SELECT s.libelle, e.nom, e.date_embauche
FROM employe e
JOIN service s ON s.id_service = e.id_service
WHERE e.date_embauche = (SELECT MIN(e2.date_embauche)
                         FROM employe e2
                         WHERE e2.id_service = e.id_service)
ORDER BY e.date_embauche;
-- Direction Garnier (2012), Logistique Faure (2013),
-- Comptabilité Morel (2014), Commercial Benali (2015),
-- Informatique Mercier (2016)

Question 4 : une division relationnelle, « il n'existe pas de poste informatique qui n'ait pas ce logiciel ». Question 5 : sans le IS NOT NULL, la première partie renverrait un NULL pour les postes en stock, qui ne gênerait pas EXCEPT mais apparaîtrait dans le résultat.

Tu avais trouvé ?

Exercice 5 sur 8 Série E, mises à jour et fenêtres Réussi À revoir

  1. Ajoute une licence aux logiciels payants auxquels il reste au plus une licence disponible.
  2. Supprime les postes en stock achetés avant 2020. Que deviennent leurs installations ?
  3. Classe les services selon leur nombre de tickets, avec RANK().
  4. Pour chaque ticket résolu, affiche le délai de résolution en minutes et son rang, du plus rapide au plus lent.
  5. Pour les interventions des tickets 5 et 9, affiche la part (en %, une décimale) de chaque intervention dans le temps total de son ticket.
  6. Crée, pour chaque poste de plus de 6 ans au 28 octobre 2026, un ticket « Renouvellement à planifier », de priorité basse, demandé par Antoine Mercier (10) et rattaché au poste.
Voir la réponseMasquer la réponse de l'exercice 5
sql
-- 1
UPDATE logiciel
SET nb_licences = nb_licences + 1
WHERE nb_licences IS NOT NULL
  AND nb_licences - (SELECT COUNT(*) FROM installation i
                     WHERE i.id_logiciel = logiciel.id_logiciel) <= 1;
-- 2 lignes : Sage 100 Comptabilité (4 licences) et Odoo (9 licences)

-- 2
DELETE FROM poste
WHERE id_employe IS NULL AND date_achat < '2020-01-01';
-- 1 ligne : PC-0013 ;
-- ses 2 installations sont supprimées par la cascade

-- 3
SELECT s.libelle, COUNT(*) AS nb_tickets,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS rang
FROM ticket t
JOIN employe e ON e.id_employe = t.id_demandeur
JOIN service s ON s.id_service = e.id_service
GROUP BY s.id_service, s.libelle
ORDER BY rang, s.libelle;
-- Comptabilité 5 (1), Commercial 4 (2), Logistique 4 (2),
-- Direction 1 (4)

-- 4
SELECT id_ticket,
       TIMESTAMPDIFF(MINUTE, date_ouverture, date_cloture) AS delai,
       RANK() OVER (ORDER BY TIMESTAMPDIFF(MINUTE, date_ouverture, date_cloture)) AS rang
FROM ticket
WHERE statut = 'resolu'
ORDER BY rang;
-- 3 (25 min), 8 (28), 10 (75), 1 (148),
-- 2 (1830), 9 (2065), 5 (2980), 4 (4740)

-- 5
SELECT id_ticket, id_intervention, duree_min,
       ROUND(100 * duree_min / SUM(duree_min) OVER (PARTITION BY id_ticket), 1) AS part_pct
FROM intervention
WHERE id_ticket IN (5, 9)
ORDER BY id_ticket, date_intervention;
-- ticket 5 : 42.9 et 57.1 ; ticket 9 : 42.1, 31.6 et 26.3

-- 6
INSERT INTO ticket (titre, date_ouverture, priorite, statut, id_demandeur, id_poste)
SELECT 'Renouvellement à planifier', '2026-10-28 09:00:00', 'basse', 'ouvert', 10, id_poste
FROM poste
WHERE date_achat < '2020-10-28';
-- 6 tickets, pour PC-0001, PC-0003, PC-0007,
-- PC-0008, PC-0009 et PC-0013

Question 1 : la sous-requête corrélée compte les installations de chaque logiciel. Elle lit installation et non logiciel, donc pas d'erreur 1093. Question 6 : « plus de 6 ans au 28 octobre 2026 » signifie un achat avant le 28 octobre 2020.

Tu avais trouvé ?

Cas type E7 SLAM : un portail pour les responsables

Exercice 6 sur 8 Cas Sorbier, dossier « portail de suivi » Réussi À revoir

La DSI de Sorbier Logistique ouvre aux responsables de service un portail web de suivi des tickets. Le portail, développé en PHP, tourne sur le serveur 10.0.2.15.

  1. Écris la vue v_suivi_service qui donne, pour chaque ticket : son numéro, son titre, son statut, sa priorité, sa date d'ouverture, le nom du demandeur, le code de son service et le temps total d'intervention en minutes (0 si aucune intervention).

  2. Avec cette vue, écris la requête qui affiche les tickets non résolus du service LOGI, des plus urgents aux moins urgents (critique, haute, normale, basse), puis du plus ancien au plus récent.

  3. Crée le compte du portail et donne-lui le strict nécessaire pour la question 2.

  4. Les responsables veulent aussi pouvoir modifier la priorité d'un ticket, et rien d'autre. La vue convient-elle ? Propose une solution.

  5. Le développeur a écrit cette requête pour compter les tickets non résolus par service ; elle échoue. Corrige-la.

    sql
    SELECT code_service, COUNT(*) AS nb
FROM v_suivi_service
WHERE statut <> 'resolu' AND COUNT(*) > 1
GROUP BY code_service;

  6. On trouve dans la base le trigger suivant. Que fait-il ? Quel défaut a-t-il ?

    sql
    CREATE TRIGGER trg_intervention_statut
AFTER INSERT ON intervention
FOR EACH ROW
  UPDATE ticket SET statut = 'en_cours';

  7. Le portail filtre les tickets par code de service avec $pdo->query("SELECT * FROM v_suivi_service WHERE code_service = '" . $_GET['service'] . "'"). Explique le risque et corrige.

Voir la réponseMasquer la réponse de l'exercice 6

1. Une jointure externe vers intervention garde les tickets sans intervention ; COALESCE remplace leur somme NULL par 0.

sql
CREATE VIEW v_suivi_service AS
SELECT t.id_ticket, t.titre, t.statut, t.priorite, t.date_ouverture,
       e.nom AS demandeur,
       s.code AS code_service,
       COALESCE(SUM(i.duree_min), 0) AS minutes
FROM ticket t
JOIN employe e           ON e.id_employe = t.id_demandeur
JOIN service s           ON s.id_service = e.id_service
LEFT JOIN intervention i ON i.id_ticket  = t.id_ticket
GROUP BY t.id_ticket, t.titre, t.statut, t.priorite, t.date_ouverture, e.nom, s.code;

2.

sql
SELECT id_ticket, titre, priorite, date_ouverture, demandeur, minutes
FROM v_suivi_service
WHERE code_service = 'LOGI' AND statut <> 'resolu'
ORDER BY CASE priorite WHEN 'critique' THEN 1 WHEN 'haute' THEN 2
                       WHEN 'normale' THEN 3 ELSE 4 END,
         date_ouverture;
id_tickettitreprioritedate_ouverturedemandeurminutes
13Lenteurs réseau à l'entrepôtcritique2026-10-26 07:45:00Faure150
6Ventilateur bruyantbasse2026-09-22 15:40:00Girard15
12Clavier cassébasse2026-10-21 13:00:00Rousseau0

3.

sql
CREATE USER 'portail'@'10.0.2.15' IDENTIFIED BY 'Phrase-de-passe-du-portail-2026';
GRANT SELECT ON parc.v_suivi_service TO 'portail'@'10.0.2.15';

Aucun droit sur les tables : la vue s'exécute avec les droits de son créateur.

4. Non : la vue contient GROUP BY et SUM, elle n'est pas modifiable. Deux solutions :

  • un privilège de colonne, GRANT UPDATE (priorite) ON parc.ticket TO 'portail'@'10.0.2.15';, complété par GRANT SELECT (id_ticket) ON parc.ticket pour que le WHERE id_ticket = ? de l'UPDATE soit autorisé ;
  • mieux, une procédure changer_priorite(p_id_ticket, p_priorite) qui vérifie la valeur et trace le changement, avec GRANT EXECUTE ON PROCEDURE parc.changer_priorite TO 'portail'@'10.0.2.15';. Le portail n'a alors aucun droit d'écriture direct.

5. Une condition sur un agrégat ne peut pas être dans le WHERE (erreur 1111) :

sql
SELECT code_service, COUNT(*) AS nb
FROM v_suivi_service
WHERE statut <> 'resolu'
GROUP BY code_service
HAVING COUNT(*) > 1;
code_servicenb
LOGI3

6. À chaque intervention ajoutée, le trigger passe tous les tickets de la base au statut en_cours, y compris les tickets résolus : l'UPDATE n'a pas de WHERE. Correction : ne modifier que le ticket concerné, et seulement s'il est ouvert.

sql
CREATE TRIGGER trg_intervention_statut
AFTER INSERT ON intervention
FOR EACH ROW
  UPDATE ticket SET statut = 'en_cours'
  WHERE id_ticket = NEW.id_ticket AND statut = 'ouvert';

7. La valeur de $_GET['service'] est concaténée dans la requête : un attaquant peut y glisser du SQL (injection), par exemple ' UNION SELECT ... pour lire d'autres données. Le compte restreint à la vue limite les dégâts, mais la faille reste. Correction :

php
$stmt = $pdo->prepare('SELECT * FROM v_suivi_service WHERE code_service = ?');
$stmt->execute([$_GET['service'] ?? '']);
$tickets = $stmt->fetchAll();

Tu avais trouvé ?

Cas type E6 SLAM : faire évoluer la base en une heure

Exercice 7 sur 8 Gérer les prêts de matériel Réussi À revoir

Expression du besoin : « Le service informatique prête des portables aux salariés en déplacement. Il veut savoir quel poste est prêté à qui, depuis quand, la date de retour prévue, et la date de retour effective. Il veut aussi la liste des prêts en retard et des portables disponibles. »

  1. Écris le CREATE TABLE de la table pret, avec ses contraintes.
  2. Enregistre le prêt du portable en stock PC-0014 à Inès Bonnet (9), du 28 octobre au 4 novembre 2026.
  3. Écris la requête des prêts en retard au 5 novembre 2026 : poste, emprunteur, date de retour prévue, nombre de jours de retard.
  4. Écris la requête des portables disponibles : en stock, et sans prêt en cours.
  5. Comment empêcher qu'un même poste soit prêté deux fois en même temps ?
Voir la réponseMasquer la réponse de l'exercice 7

1.

sql
CREATE TABLE pret (
  id_pret            INT AUTO_INCREMENT PRIMARY KEY,
  id_poste           INT  NOT NULL,
  id_employe         INT  NOT NULL,
  date_debut         DATE NOT NULL,
  date_retour_prevue DATE NOT NULL,
  date_retour        DATE,                  -- NULL tant que le poste n'est pas rendu
  CONSTRAINT ck_pret_prevue CHECK (date_retour_prevue >= date_debut),
  CONSTRAINT ck_pret_retour CHECK (date_retour IS NULL OR date_retour >= date_debut),
  CONSTRAINT fk_pret_poste   FOREIGN KEY (id_poste)   REFERENCES poste (id_poste),
  CONSTRAINT fk_pret_employe FOREIGN KEY (id_employe) REFERENCES employe (id_employe)
);

2.

sql
INSERT INTO pret (id_poste, id_employe, date_debut, date_retour_prevue)
VALUES (14, 9, '2026-10-28', '2026-11-04');

3.

sql
SELECT p.num_inventaire, e.nom, e.prenom, pr.date_retour_prevue,
       DATEDIFF('2026-11-05', pr.date_retour_prevue) AS jours_de_retard
FROM pret pr
JOIN poste p   ON p.id_poste   = pr.id_poste
JOIN employe e ON e.id_employe = pr.id_employe
WHERE pr.date_retour IS NULL
  AND pr.date_retour_prevue < '2026-11-05'
ORDER BY jours_de_retard DESC;

Avec le prêt de la question 2, elle renvoie PC-0014, Bonnet Inès, 2026-11-04, 1 jour de retard. Dans l'application, '2026-11-05' devient CURDATE().

4.

sql
SELECT p.num_inventaire, p.marque, p.modele
FROM poste p
WHERE p.type_poste = 'portable'
  AND p.id_employe IS NULL
  AND NOT EXISTS (SELECT 1 FROM pret pr
                  WHERE pr.id_poste = p.id_poste AND pr.date_retour IS NULL)
ORDER BY p.num_inventaire;

5. Un UNIQUE (id_poste) simple interdirait tout historique : un poste ne pourrait être prêté qu'une fois dans sa vie. La règle « un seul prêt en cours par poste » s'exprime avec un index unique sur une expression (MySQL 8.0.13 et plus) :

sql
CREATE UNIQUE INDEX uq_pret_en_cours
  ON pret ((CASE WHEN date_retour IS NULL THEN id_poste END));

Pour un prêt en cours, l'expression vaut l'identifiant du poste : deux prêts en cours du même poste font un doublon, refusé. Pour un prêt rendu, elle vaut NULL, et un index unique accepte plusieurs NULL : l'historique ne gêne pas. C'est la meilleure solution, parce qu'elle est déclarative et que le moteur la fait respecter même quand deux saisies arrivent au même moment. PostgreSQL écrit un index unique partiel : CREATE UNIQUE INDEX uq_pret_en_cours ON pret (id_poste) WHERE date_retour IS NULL;. MariaDB, qui n'a pas d'index fonctionnel, passe par une colonne générée indexée UNIQUE.

Deux autres solutions, moins sûres seules :

  • dans l'application, une transaction qui verrouille la ligne du poste (SELECT ... FROM poste WHERE id_poste = ? FOR UPDATE), vérifie l'absence de prêt en cours, puis insère ;
  • dans la base, un trigger BEFORE INSERT (et BEFORE UPDATE) sur pret qui lève une erreur avec SIGNAL s'il existe déjà un prêt sans date_retour pour ce poste.

Le trigger protège tous les accès, y compris les saisies directes en SQL, mais pas deux saisies simultanées : les deux lisent l'absence de prêt avant que l'autre n'ait validé, et passent toutes les deux (la décision sur une lecture périmée). Il lui faut le verrou de la transaction, alors que l'index unique n'en a pas besoin.

Tu avais trouvé ?

Trouve l'erreur

Exercice 8 sur 8 Sept requêtes fausses Réussi À revoir

Chaque requête contient une erreur : une erreur de syntaxe, un message de MySQL ou un résultat faux sans message. Trouve-la et corrige-la.

sql
-- 1. Nombre d'employés par service
SELECT id_service, nom, COUNT(*) AS effectif FROM employe GROUP BY id_service;

-- 2. Tickets non clos
SELECT * FROM ticket WHERE date_cloture = NULL;

-- 3. Effectif de chaque service, Juridique compris
SELECT s.libelle, COUNT(*) AS effectif
FROM service s LEFT JOIN employe e ON e.id_service = s.id_service
GROUP BY s.id_service, s.libelle;

-- 4. Tickets non résolus de priorité haute ou critique
SELECT titre FROM ticket
WHERE priorite = 'haute' OR priorite = 'critique' AND statut <> 'resolu';

-- 5. Les portables et leur utilisateur
SELECT e.nom, p.num_inventaire FROM employe e, poste p WHERE p.type_poste = 'portable';

-- 6. Supprimer les installations des logiciels de gestion
DELETE FROM installation
WHERE id_logiciel = (SELECT id_logiciel FROM logiciel WHERE categorie = 'gestion');

-- 7. Techniciens qui ont travaillé plus de 300 minutes
SELECT id_technicien, SUM(duree_min) AS total FROM intervention
WHERE total > 300 GROUP BY id_technicien;
Voir la réponseMasquer la réponse de l'exercice 8
  1. nom n'est ni regroupé ni agrégé, et un service compte plusieurs noms : erreur 1055. La question ne demande pas les noms : retire nom du SELECT. (Pour la liste des noms, ce serait GROUP_CONCAT(nom).)
  2. = NULL ne renvoie jamais rien : WHERE date_cloture IS NULL.
  3. Pas de message, mais Juridique affiche 1 au lieu de 0 : COUNT(*) compte la ligne complétée par NULL. Écris COUNT(e.id_employe).
  4. Pas de message, mais les tickets résolus de priorité haute apparaissent : le AND est évalué avant le OR. Écris WHERE priorite IN ('haute', 'critique') AND statut <> 'resolu'.
  5. Aucune condition de jointure : chaque portable est associé à chacun des 14 employés (112 lignes). Écris FROM poste p JOIN employe e ON e.id_employe = p.id_employe WHERE p.type_poste = 'portable'.
  6. La catégorie gestion contient deux logiciels : la sous-requête renvoie deux lignes, erreur 1242. Écris WHERE id_logiciel IN (SELECT ...).
  7. L'alias total n'existe pas dans le WHERE, et un filtre sur une somme se fait après le regroupement : GROUP BY id_technicien HAVING SUM(duree_min) > 300. Résultat : techniciens 10 (510) et 12 (450).

Tu avais trouvé ?

Chapitre terminé !

Fais les 8 exercices avant de passer à la suite : c'est en t'entraînant que tout se fixe.

chapitres
–
de série
–
du cours
–
Chapitre suivant : Aide-mémoire : les commandes SQLRevoir le sommaire du cours