GROUP BY : Pour partitionner l'ensemble des lignes en groupes
Toutes les lignes d un groupe ont la même valeur pour les attributs de
groupement.
Dans la clause SELECT : uniquement des attributs de groupement
et des fonctions d agrégation évaluées sur chaque groupe.
groupe 1
groupe 2
groupe 3
groupe 4
52
Exemple
Attributs de groupement
SELECT Continent,
AVG(Population) "Pop moyenne",
SUM(Surface) "Surface totale",
COUNT(*) "Nb pays"
FROM Pays
GROUP BY Continent;
Pop Surface Nb
Continent
moyenne totale pays
Europe 6,33 440 4
Amérique 266 9373 1
Une ligne par groupe
53
VOL
Exemple VOLNUM VILLEDEP VILLEARR
100 NICE TOULOUSE
101 NICE LYON
102 TOULOUSE LYON
103 NICE TOULOUSE
104 TOULOUSE LYON
105 NICE LYON
106 TOULOUSE LYON
SELECT VILLEDEP,COUNT(*)
FROM VOL
GROUP BY VILLEDEP; VILLEDEP COUNT(*)
NICE 4
TOULOUSE 3
SELECT VILLEDEP,VILLEARR,COUNT(*)
FROM VOL VILLEDEP VILLEARR COUNT(*)
GROUP BY VILLEDEP,VILLEARR; NICE TOULOUSE 2
NICE LYON 2
TOULOUSE LYON 3
54
Conditions sur les sous-groupes
HAVING : Pour sélectionner certains groupes (et non des lignes).
Ordre d exécution :
Sélection des lignes en fonction de la clause WHERE
Groupement des lignes restantes en groupes disjoints en fonction des
attributs du GROUP BY
Sélection des groupes en fonction de la clause HAVING
groupe 1
groupe 2
groupe 3
groupe 4
55
Exemple
SELECT Continent,
AVG(Population) "Pop moyenne",
SUM(Surface) "Surface totale",
COUNT(*) "Nb pays"
FROM Pays
GROUP BY Continent
HAVING COUNT(*) > 2;
Pop Surface Nb
Continent
moyenne totale pays
Europe 6,33 440 4
Une ligne par groupe sélectionné
56
Exercices
Donner les villes et le nombre de fournisseurs
dans chaque ville.
Donner les produits et le prix moyen des produits
est supérieur à 10).
57
Exercices
Donner pour chaque couple (cnom, pnom), le nombre
de commandes.
Donner les produits fournis par au moins 2
fournisseurs avec un coût supérieur à 10
58
Produit cartésien
Toutes les combinaisons possibles entre AVNUM et PLNUM.
AVION PILOTE
AVNUM AVNOM PLNUM PLNOM AVNUM PLNUM
1 A300 1 MIRANDA 1 1
2 A310 2 LETHANH 2 1
3 B707 3 TALADOIRE 3 1
1 2
2 2
3 2
SELECT AVNUM,PLNUM 1 3
FROM AVION CROSS JOIN PILOTE; 2 3
3 3
59
Jointure
T1 T2
A1 A2 A3 B1 B2 B3
Equi-Jointure (égalité) Théta-Jointure (comparaison)
SELECT * SELECT *
FROM T1 JOIN T2 ON A1=B1; FROM T1 JOIN T2 ON A1<B2;
60
Jointure naturelle
Egalité sur tous les attributs communs + projection
T1 T2 T1 T2
A B C A D E A B C A B D
FROM T1 JOIN T2 ON T1.A=T2.A; FROM T1 JOIN T2 ON T1.A=T2.A
AND T1.B=T2.B;
Utilisation de la notation pointée
Obligatoire pour les attributs présents dans plusieurs relations
61
T1 T2
A1 A2 A3 B1 B2 B3
1 a 4
Jointures externes 2 2
1 e 2
2 3
3 e 1 3 a 1
4 c 1 4 b 3
5 e 3
Jointures internes (inner join) : A1 A2 A3 B1 B2 B3
1 a 4 3 a 1
SELECT * 3 e 1 1 e 2
3 e 1 5 e 3
FROM T1 JOIN T2 ON T1.A2=T2.B2;
Ne conserve que les tuples de la 1ère relation qui
joignent avec la 2ème relation.
A1 A2 A3 B1 B2 B3
1 a 4 3 a 1
2 2
3 e 1 1 e 2
Jointures externes (outer join) : 3 e 1 5 e 3
4 c 1
SELECT *
FROM T1 LEFT JOIN T2 ON T1.A2=T2.B2;
Tous les tuples de T1
A1 A2 A3 B1 B2 B3
3 e 1 1 e 2
2 3
SELECT * 1 a 4 3 a 1
FROM T1 RIGHT JOIN T2 ON T1.A2=T2.B2; 4 b 3
3 e 1 5 e 3
Tous les tuples de T2
64
JO
Jointures externes Annee Lieu Pays
1896 Athènes Grèce
1900 Paris
1904 St Louis USA
1908 Londres Royaume-Uni
SELECT Annee,Lieu,Pays,Capitale
FROM JO J LEFT JOIN Pays P ON [Link]=[Link];
Annee Lieu Pays Capitale
1896 Athènes Grèce
1900 Paris
1904 St Louis USA Washington
1908 Londres Royaume-Uni Londres
65
Auto-Jointure
Jointure sur la même table (alias obligatoires)
SELECT T1.A, T1.B, T2.C, T2.B
FROM T T1 JOIN T T2 ON T1.A=T2.A;
T T1.A T1.B T2.A T2.B
A B C 1 a 4 a
1 a 4 2 2
2 2 3 e 1 e
3 e 1 4 c 1 c
4 c 1
66
Auto-Jointure
SELECT [Link],[Link],[Link],[Link]
FROM Pays P1 JOIN Pays P2
ON [Link] > [Link];
[Link] [Link] [Link] [Link]
Autriche 8 Irlande 4
Autriche 8 Suisse 7
Suisse 7 Irlande 4
USA 266 Irlande 4
USA 266 Autriche 8
USA 266 Suisse 7
67
Exercices
Donner le nom, le fournisseur et le prix des
produits commandés par Jean.
Donner le nom des fournisseurs de Dijon qui
fournissent du Boudes.
68
Exercices
Quels sont les fournisseurs qui habitent dans la
même ville (fnom1, fnom2, ville) ?
Donner le nom de tous les produits, leur prix, le
nom de leur fournisseur et la ville du
fournisseur.
69