Requêtes imbriquées renvoyant plusieurs lignes

SELECT *
FROM Stock
WHERE prod IN ( SELECT prod
FROM Produit);

Le mot-clef "IN" permet ici de sélectionner les tuples prod

appartenant à la sous-requête

296 © Mohamed HADJ KACEM AU : 2021 - 2022

 Exemple : IN
A partir des relations suivantes : Produit (prod, libellé, pu, couleur)
Stock (#prod, #dép, qté)
Dépôt (dép, adr, volume)
Quels sont les produits (prod, libellé, dép, adr) qui sont en rupture de stock ? (sans et
avec imbrication)

SELECT p.prod, p.libellé, d.dép, SELECT p.prod, p.libellé, d.dép, d.Adr

d.Adr
FROM Produit p, Dépôt d
FROM Produit p, Dépôt d, Stock s
WHERE p.prod IN ( SELECT s.prod
WHERE p.prod = s.prod
FROM Stock s
AND s.dép = d.dép
WHERE s.qté <= 0 )
AND s.qte <= 0;
AND p.prod = s.prod
AND s.dép = d.dép ;

297 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice : Les requêtes imbriquées
Magasin (Nummag, adresse, Surface)
Produit (Numprod, Desprod, Couleur, Poids, Qte_stk, Qte_seuil, Prix,
Codmag#)

 Afficher les numéros des produits de même couleur que le produit

numéro 100

SELECT numprod
FROM produit
Where couleur = (SELECT couleur
FROM produit
WHERE numprod = 100);

303 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice : Les requêtes imbriquées
 Afficher les numéros des produits de même couleur qu’un ordinateur
SELECT numprod
FROM produit
Where couleur IN (SELECT couleur
FROM produit
WHERE upper (ltrim(rtrim(desprod))) = 'ORDINATEUR');
 Afficher les numéros des produits dont la quantité est supérieure à la quantité de tout
produit de couleur rouge
SELECT numprod
FROM produit
Where qte_stk > all (SELECT Qte_stk
FROM produit
WHERE upper(couleur)='R'); ou
 SELECT numprod
FROM produit
Where qte_stk > (SELECT MAX(Qte_stk)
FROM produit
WHERE upper(couleur)='R');

304 © Mohamed HADJ KACEM AU : 2021 - 2022

Exemples : Fonctions d'agrégation

Matière Coef Note

Maths 4 15

Sc Nat 3 9

Sc Phy 3 12

Français 2 13

Sc Hum 2 11

Anglais 1 10

Sport 1 12

Résultats (de Mohamed)

306 © Mohamed HADJ KACEM AU : 2021 - 2022

 Exemples : Fonctions d'agrégation
 Quelle est la meilleure note ?  Quelle est la moyenne (pondérée) de
SELECT MAX(Note) Salah ?
FROM Résultats  15 SELECT SUM (Note*Coef) / Sum(Coef)
 Quelle est la plus mauvaise FROM Résultats  12,06
note ?  Dans combien de matières Salah a-t-il
SELECT MIN(Note) eu plus de 12 ?
FROM Résultats 9 SELECT COUNT(*)
 Quelle la somme pondérée des FROM Résultats
notes ? WHERE Note > 12  2
SELECT SUM(Note*Coef)
FROM Résultats  193

307 © Mohamed HADJ KACEM AU : 2021 - 2022

Exemples : Partitionnement des résultats

Matière Coef Note

Maths 4 15

Sc Nat 3 9

Sc Phy 3 12

Français 2 13

Sc Hum 2 11

Anglais 1 10

Sport 1 12

Résultats (de Mohamed)

309 © Mohamed HADJ KACEM AU : 2021 - 2022

Exemples
 Quelle est la note moyenne pour chaque coefficient ? Coef Moyenne
1 11
SELECT coef, Avg(note) as Moyenne
2 12
FROM Résultats 3 10.5
GROUP BY coef; 4 15
 Quels sont les coefficients auxquels participe une seule matière ?
SELECT coef Coef
FROM Résultats 4

GROUP BY coef
HAVING count(*)=1;

310 © Mohamed HADJ KACEM AU : 2021 - 2022

Partitionnement des résultats
Syntaxe
GROUP BY liste_attributs
HAVING condition avec fonction
Cette clause regroupe les résultats par valeur selon la condition

Dans l'ordre, on effectue

1. La sélection SELECT
2. Le partitionnement GROUP BY
3. On retient les partitions intéressantes HAVING
4. On trie avec ORDER BY

311 © Mohamed HADJ KACEM AU : 2021 - 2022

Classification des résultats
 Il est possible de regrouper (classifier) les résultats obtenus permettant d'avoir
ensemble les lignes ayant une même valeur pour la colonne de classification. La
classification permet aussi d'effectuer un calcul tel que la moyenne ou la somme
des valeurs de chaque groupe et de présenter le résultat sous forme d'une ligne
par groupe.
Couleur Qte_stk
 Syntaxe
R 10
SELECT
B 20
FROM
G 20
WHERE
B 35
GROUP BY expression
R 25
HAVING condition
 Exemple
 Afficher la quantité totale de produits par couleur
SELECT couleur, sum(Qte_stk) "Qte totale" La fonction group by
FROM produit
Couleur Qte totale
Group by couleur;
R 35
B 55
G 20

312 © Mohamed HADJ KACEM AU : 2021 - 2022

Exemple
 Afficher le nombre de produits par couleur et désignation
 SELECT desprod, couleur, count(*) "Nombree totale"
FROM produit
Group by desprod, couleur;
 Afficher la quantité totale des produits, supérieure à 100, par couleur.
 SELECT couleur, sum(Qte_stk) "Qte totale"
FROM produit
Group by couleur
Having sum (Qte_stk)>100;
 Afficher les couleurs des produits ayant une quantité totale > 100
 SELECT couleur
FROM produit
Group by couleur
Having sum (Qte_stk)>100;

313 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 REPRESENTATION (Nrep, Titre_rep, Lieu)
 MUSICIEN (Num_Mus, Nom_Mus, #Nrep)
 PROGRAMMER (#Nrep, Date, Tarif)

315 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 1. Donner la liste des titres des représentations
SELECT Titre_rep
FROM REPRESENTATION;
 2. Donner la liste des titres des représentations ayant lieu
au théâtre municipal.
SELECT Titre_rep
FROM REPRESENTATION
WHERE Lieu="théâtre municipal";

316 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 3. Donner la liste des noms des musiciens et des titres
des représentations auxquelles ils participent

SELECT Nom_Mus,Titre_rep
FROM MUSICIEN, REPRESENTATION
WHERE MUSICIEN.Nrep = REPRESENTATION.Nrep;

317 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 4. Donner la liste des titres des représentations, les lieux
et les tarifs pour la journée du 09/12/2021

SELECT Titre_rep, Lieu,Tarif

FROM REPRESENTATION, PROGRAMMER
WHERE PROGRAMMER.Nrep = REPRESENTATION.Nrep
AND date='09/12/2021';

318 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 ETUDIANT (NEtu, Nom, Prénom)
 MATIERE (CodeMat, LibMat, CoefMat)
 EVALUER (#NEtu, #CodeMat, Date, Note)

319 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 1. Quel est le nombre total d'étudiants ?
SELECT COUNT(*)
FROM ETUDIANT;
 2. Quelles sont, parmi l'ensemble des notes, la note la plus
haute et la note la plus basse ?
SELECT MIN(Note), MAX(Note)
FROM EVALUER;

320 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
3. Quelles sont les moyennes de chaque étudiant dans
chacune des matières ?
SELECT ETUDIANT.NEtu, Nom, Prénom, LibMat, CoefMat,
AVG(Note)
FROM EVALUER, MATIERE, ETUDIANT
WHERE EVALUER.CodeMat = MATIERE.CodeMat
AND EVALUER.NEtu = ETUDIANT.NEtu
GROUP BY ETUDIANT.NEtu, Nom, Prénom, LibMat, CoefMat;

321 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 4. Quelles sont les moyennes par matière ?
SELECT LibMat, AVG(Note)
FROM EVALUER, MATIERE
WHERE EVALUER.CodeMat = MATIERE.CodeMat
GROUP BY LibMat;

322 © Mohamed HADJ KACEM AU : 2021 - 2022

Exercice
 5. Quelle est la moyenne générale de chaque étudiant ?
SELECT ETUDIANT.NEtu, Nom, Prénom,
SUM(Note*CoefMat)/SUM(CoefMat)
FROM EVALUER, MATIERE, ETUDIANT
WHERE EVALUER.CodeMat = MATIERE.CodeMat
AND EVALUER.NEtu = ETUDIANT.NEtu
GROUP BY ETUDIANT.NEtu, Nom, Prénom;

323 © Mohamed HADJ KACEM AU : 2021 - 2022