Exercice 28 / 28Difficile
Synthèse : visiteur top par CA distribué
À toi
On cherche le visiteur dont la valeur totale des échantillons distribués est maximale. Cela combine plusieurs notions : jointure 4 tables, agrégation, comparaison à un MAX via sous-requête sur table dérivée.
Affiche le matricule, le nom et la valeur totale (arrondie à 2 décimales, alias valeur_totale) du ou des visiteurs ayant la plus grande valeur totale d'échantillons distribués.
Les règles à respecter
- On calcule pour chaque visiteur la somme de prix_ht × quantite
- On garde celui (ou ceux) dont la somme est égale à la somme maximale
- On affiche matricule, nom, valeur_totale (arrondie à 2 décimales)
Les tables utiles
- visiteur (matricule, nom)
- visite (id_visite, #matricule)
- ligne_visite (#id_visite, #code_medicament, quantite)
- medicament (code, prix_ht)
Clé primaire soulignée, clé étrangère précédée de #. Base GSB, toutes ses tables sont disponibles.
Sous-requêtes, UNION et EXCEPT
Les exemples de ces mémos portent sur une petite médiathèque, pas sur la base GSB des exercices : livre (id, titre, annee, prix, genre, #auteur_id), auteur (id, nom, pays), adherent (id, nom, ville, inscription), emprunt (id, #livre_id, #adherent_id, date_emprunt, date_retour).
Une requête dans une requête
Une sous-requête, entre parenthèses, fournit une valeur ou une liste à la requête principale.
-- les livres plus chers que la moyenne
SELECT titre, prix FROM livre
WHERE prix > (SELECT AVG(prix) FROM livre);
Une sous-requête qui renvoie une seule valeur se compare avec =, >, <… ; une qui renvoie une liste s'utilise avec IN ou NOT IN :
-- les auteurs dont un livre a été emprunté
SELECT nom FROM auteur
WHERE id IN (SELECT l.auteur_id FROM livre l JOIN emprunt e ON e.livre_id = l.id);
EXISTS
EXISTS (sous-requête) est vrai si la sous-requête renvoie au moins une ligne. Elle peut faire référence à la ligne de la requête principale :
SELECT nom FROM adherent ad
WHERE NOT EXISTS (SELECT 1 FROM emprunt e WHERE e.adherent_id = ad.id);
Attention : NOT IN avec une sous-requête qui contient un NULL ne renvoie rien ; NOT EXISTS n'a pas ce piège.
UNION et EXCEPT
UNIONmet bout à bout deux résultats de mêmes colonnes, sans doublon (UNION ALLles garde) ;EXCEPTgarde les lignes du premier résultat absentes du second.
SELECT ville FROM adherent
UNION
SELECT pays FROM auteur;
Tu es bloqué ?
Ouvre les indices un par un, le corrigé en dernier.
Indice 1
Étapes : 1) joindre 4 tables, 2) calculer la somme par visiteur, 3) garder celui dont la somme est égale au MAX des sommes.
Indice 2
Le MAX d'un agrégat se fait via une sous-requête sur table dérivée (on calcule toutes les sommes, on prend le MAX).
Indice 3
Structure : ... GROUP BY ... HAVING SUM(...) = (SELECT MAX(t) FROM (SELECT SUM(...) AS t FROM ... GROUP BY ...) AS sub) ;
Voir le corrigé
Connecte-toi pour voir le corrigé
C'est gratuit, avec ton compte Google : un clic, et tu reviens ici. Ta progression te suit ensuite sur tous tes appareils.
Le site garde ton nom et ton email, rien d'autre. Tes données
Pour revoir la notion : chapitre 9 du cours SQL, Sous-requêtes SQL : IN, EXISTS, NOT EXISTS.
Connecte-toi pour faire cet exercice
C'est gratuit, avec ton compte Google : un clic, et tu reviens ici. Ta progression te suit ensuite sur tous tes appareils.
Le site garde ton nom et ton email, rien d'autre. Tes données