Base de données Avancées SupNum
Jointures et Groupe de Fonctions
Moussa Demba
1
Le Group by
SQL> SELECT * FROM Paie;
ID NAME JOBCODE STARTDATE SALARY BONUS
1111 Toto Titi CI 15-JAN-97 45000 1000
2222 Jojo Dada IN 25-SEP-92 40000 1500
3333 Soso Tata AP 05-FEB-00 25000 500
4444 Tata Titi CM 03-JUL-97 42000 2000
5555 Tutu Jojo CI 30-OCT-92 50000 2000
6666 Baba Bubu IN 18-AUG-94 48000 2000
7777 Toto Jojo CI 05-NOV-99 45000 3000
8888 Papa Nono IN 12-DEC-98 45000 2000
2
Partitionnement: Group By
• Principe
– partitionnement horizontal d'une relation, selon les valeurs
d'un attribut ou groupe d'attributs qui est spécifié dans la
clause GROUP BY
– la relation est (logiquement) fragmentée en groupes de
lignes, où tous les tuples de chaque groupe ont la même
valeur pour l'attribut (ou le groupe d'attributs) de
partitionnement
• Restrictions sur les groupes
– application possible d'un critère de restriction sur les
groupes obtenus
– clause HAVING
3
Exemple: Afficher le nombre d’employés par code de travail (jobcode) ?
ID NAME JOBCODE STARTDATE SALARY BONUS
1111 Toto Titi CI 15-JAN-97 45000 1000
2222 Jojo Dada IN 25-SEP-92 40000 1500
3333 Soso Tata AP 05-FEB-00 25000 500
4444 Tata Titi CM 03-JUL-97 42000 2000
5555 Tutu Jojo CI 30-OCT-92 50000 2000
6666 Baba Bubu IN 18-AUG-94 48000 2000
7777 Toto Jojo CI 05-NOV-99 45000 3000
8888 Papa Nono IN 12-DEC-98 45000 2000
JOBCODE COUNT(ID) Il faut utiliser une fonction qui permet
----- ----------- de regrouper les lignes par jobcode et puis
AP 1
les compter !
CI 3
CM 1
IN 3 4
Group functions
SELECT colonne(s)
FROM tables
WHERE condition
Dans cet exemple, on affiche le nombre d’employés par
GROUP BY colonne(s) code de travail (jobcode). Pour ce faire on a besoin de
HAVING condition regrouper les employés par jobcode. Pour grouper par
ORDER BY colonne(s); jobcode, on a besoin de selectionner le jobcode.
On a mis count(ID) pour compter le nombre d’employés
SQL> SELECT jobcode, count(ID)
FROM Paie
GROUP BY jobcode;
SQL> SELECT jobcode, count(*)
JOBCODE COUNT(ID) FROM Paie
----- ----------- GROUP BY jobcode;
AP 1
CI 3 JOBCODE COUNT(*)
CM 1 ----- ---------
AP 1
IN 3
CI 3
CM 1
IN 3
5
Group functions
ID NAME JOBCODE STARTDATE SALARY BONUS
1111 Toto Titi CI 15-JAN-97 45000 1000
2222 Jojo Dada IN 25-SEP-92 40000 1500
3333 Soso Tata AP 05-FEB-00 25000 500
4444 Tata Titi CM 03-JUL-97 42000 2000
5555 Tutu Jojo CI 30-OCT-92 50000 2000
6666 Baba Bubu IN 18-AUG-94 48000 2000
7777 Toto Jojo CI 05-NOV-99 45000
8888 Papa Nono IN 12-DEC-98 45000 2000
SQL> SELECT jobcode, COUNT(name)
FROM Paie
WHERE salary <= 45000
GROUP BY jobcode; Contrairement à l’exemple précédent,
ici on considère seulement les
employés dont les salaires sont <=
JOBCODE COUNT(NAME) 45000. Donc les employés 5555 et
------ --------- 6666 sont exclus.
AP 1
CI 2
CM 1
IN 2 6
Group functions
SQL> SELECT jobcode, COUNT(name)
FROM Paie
WHERE salary <= 45000
GROUP BY jobcode
ORDER BY jobcode desc; Par ordre decroissant de jobcode.
JOBCODE COUNT(NAME)
------ -----------
IN 2
CM 1
CI 2
AP 1
SQL> SELECT jobcode, COUNT(name)
FROM Paie
WHERE salary <= 45000
GROUP BY jobcode Par ordre croissant de count(name)
ORDER BY COUNT(name);
JOBCODE COUNT(NAME)
------ -----------
AP 1
CM 1
CI 2
7
IN 2
Group functions
ID NAME JOBCODE
1111 Toto Titi CI
2222 Jojo Dada IN
3333 Soso Tata AP
SQL> SELECT jobcode, COUNT(name)
FROM Paie 4444 Tata Titi CM
WHERE jobcode != 'IN' 5555 Tutu Jojo CI
GROUP BY jobcode; 6666 Baba Bubu IN
7777 Toto Jojo CI
JOBCODE COUNT(NAME)
----- ----------- 8888 Papa Nono IN
AP 1
CI 3
CM 1
on affiche le nombre d’employés par code de
travail (jobcode) excepté de ceux du code ‘IN’.
8
Group functions
SQL> SELECT jobcode, MIN(salary), MAX(salary)
FROM Paie
GROUP BY jobcode; Affiche pour chaque code de travail
(jobcode) le plus petit et le plus grand
salaires.
JOBCODE MIN(SALARY) MAX(SALARY)
----- ----------- -----------
AP 25000 25000 JOBCODE SALARY
CI 45000 50000
CI 45000
CM 42000 42000
IN 40000 48000 IN 40000
AP 25000
SQL> SELECT jobcode, AVG(salary) CM 42000
FROM Paie
CI 50000
GROUP BY jobcode;
IN 48000
JOBCODE AVG(SALARY) CI 45000
-- ---------
IN 45000
AP 25000
CI 46666.667
Donne le salaire moyen
CM 42000
pour chaque jobcode.
IN 44333.333
9
" … uniquement si ce jobcode concerne plus d’un employé"
SQL> SELECT jobcode, AVG(salary)
FROM Paie JOBCODE SALARY
GROUP BY jobcode; CI 45000
HAVING COUNT(*)>1
IN 40000
JOBCODE AVG(SALARY) AP 25000
-- --------- CM 42000
CI 46666.667
CI 50000
IN 44333.333
IN 48000
CI 45000
SQL> SELECT jobcode, ROUND(AVG(salary),2) IN 45000
FROM Paie
GROUP BY jobcode;
HAVING COUNT(*)>1
JOBCODE AVG(SALARY)
-- ---------
CI 46666.667
IN 44333.333
arrondi à 2 décimales
10
Les jointures internes : NATURAL et INNER JOIN
On considere les 3 tables R1, R2 et R3:
SELECT * FROM R1 NATURAL JOIN R2 ; OU BIEN SELECT * FROM R1 INNER JOIN R2 USING(B);
NATURAL JOIN est basée La colonne de jointure, ici B,
sur les noms des colonnes n’est pas dupliquée
SELECT * FROM R1 NATURAL JOIN R3 ; OU BIEN SELECT * FROM R1 INNER JOIN R3 USING(A, B);
SELECT * FROM R1 INNER JOIN R3 USING(B);
Attention, la colonne A est dupliquée
Car ce n’est pas une colonne de jointure
11
Les jointures internes : NATURAL et INNER JOIN (suite)
On considere les 3 tables
R1, R2 et R3:
SELECT * FROM R1 INNER JOIN R2 ON R1.B=R2.B;
La colonne de jointure, ici B,
est dupliquée
SELECT * FROM R1 INNER JOIN R3 ON R1.B=R3.B;
Quel est le resultat de cette requete:
SELECT * FROM R1 INNER JOIN R2 USING(B) INNER JOIN R3 USING(B);
??? 12
Les jointures externes : Left et Right JOIN
On considere les 3 tables R1, R2 et R3:
SELECT A, R1.B, C FROM R1 LEFT JOIN R2 ON R1.B=R2.B ;
R1
SELECT A, R1.B, C FROM R1 LEFT JOIN R2 ON USING(B) ;
SELECT A, R1.B, C FROM R1 RIGHT JOIN R2 ON USING(B) ;
13
R2
Les jointures externes : Left et Right JOIN
On considere les 3 tables R1, R2 et R3:
SELECT A,R1.B,C FROM R1 RIGHT JOIN R2 ON USING(B) ;
SELECT * FROM R1 RIGHT JOIN R2 USING(B) WHERE A IS NOT NULL ;
14
Important
POUR EXECUTER UN FICHIER .SQL DEPUIS L’INVITE DE COMMANDE DOS:
On suppose qu’on a un fichier [Link] qui contient les commandes SQL:
d:/id2/[Link]
- Oracle: sql> @d:/id2/[Link];
- MySQL: mysql> source d:/id2/[Link];
La BD dans [Link] sera créée dans la BD locale.
15
Les opérateurs ANY, SOME et ALL
PAIE
ID NAME JOBCODE STARTDATE SALARY BONUS
1111 Toto Titi CI 15-JAN-97 45000 1000
2222 Jojo Dada IN 25-SEP-92 40000 1500
ANY : au moins 1 ...
SOME : au moins 1 ... 3333 Soso Tata AP 05-FEB-00 25000 500
ALL : tout ... 4444 Tata Titi CM 03-JUL-97 42000 2000
5555 Tutu Jojo CI 30-OCT-92 50000 2000
6666 Baba Bubu IN 18-AUG-94 48000 2000
SQL> SELECT NAME 7777 Toto Jojo CI 05-NOV-99 45000
FROM PAIE 8888 Papa Nono IN 12-DEC-98 45000 2000
WHERE JOBCODE= ANY (SELECT JOBCODE FROM
PAIE WHERE JOBCODE IN (‘CI', ‘IN')
);
Remarques :
- l'opérateur ANY est équivalent à l'opérateur SOME.
- X > ANY (Ensemble)
retourne vrai si X est > que n'importe quelle valeur de l'ensemble.
16
PAIE
Les opérateurs ANY, SOME et ALL ID NAME JOBCODE STARTDATE SALARY BONUS
1111 Toto Titi CI 15-JAN-97 45000 1000
2222 Jojo Dada IN 25-SEP-92 40000 1500
ANY : au moins 1 ...
SOME : au moins 1 ... 3333 Soso Tata AP 05-FEB-00 25000 500
ALL : tout ... 4444 Tata Titi CM 03-JUL-97 42000 2000
5555 Tutu Jojo CI 30-OCT-92 50000 2000
6666 Baba Bubu IN 18-AUG-94 48000 2000
7777 Toto Jojo CI 05-NOV-99 45000
8888 Papa Nono IN 12-DEC-98 45000 2000
SQL> SELECT NAME,SALARY
FROM PAIE
WHERE SALARY> ANY (SELECT SALARYFROM
PAIE WHERE JOBCODE LIKE ‘IN‘;
);
17
Les opérateurs ANY, SOME et ALL
Sélectionnez les paiements dont les salaires sont differents de 45.000, 40.000 et 48.000
SQL> SELECT *
FROM PAIE
WHERE SALARY != ALL (45.000, 40.000,48.000); !=ALL est equivalent a NOT IN
=ANY est équivalent à IN
X =ANY ( A , B , C ) est la même chose que X IN ( A , B , C )
18
Resultats
Les opérateurs ANY, SOME et ALL
ALL: Le résultat est vrai si toutes les valeurs répondent à la comparaison
SELECT NUMAD FROM RESULTATS
WHERE NOTE >ALL
(SELECT NOTE FROM RESULTATS WHERE CODE_COURS ='KEG') ;
On cherche un numero pour lequel sa note
est superieure a toutes les notes du cours KEG
SELECT NUMAD FROM RESULTATS
WHERE NOTE >ANY
(SELECT NOTE FROM RESULTATS WHERE CODE_COURS ='KEG') ;
On cherche un numero pour lequel sa note est Donne le resultat
superieure a au moins une des notes du cours KEG
19
UNION
C’est une commande qui permet de concaténer les résultats de 2 requêtes ou plus. Pour l’utiliser il est nécessaire
que chacune des requêtes à concaténer retournes le même nombre de colonnes, avec les mêmes types de données et
dans le même ordre.
A savoir : par défaut, les enregistrements exactement identiques ne seront pas répétés dans les résultats.
Pour effectuer une union dans laquelle même les lignes dupliquées sont affichées il faut plutôt utiliser la
commande UNION ALL.
La syntaxe pour unir les résultats de 2 tableaux sans afficher les doublons est la suivante:
SELECT * FROM A …
UNION
SELECT * FROM B …
20
21
Afficher les pilotes qui sont de paris ou nice.
mysql> SELECT * FROM PILOTE WHERE VILLE='PARIS' UNION SELECT * FROM PILOTE WHERE VILLE='NICE';
Attention:
Pour utiliser la clause UNION, les expressions SELECT Sans duplications des enregistrements
doivent avoir le meme nombre de colonnes
UNION ALL avec duplications des enregistrements, s’il en existe
22
Le prédicat EXISTS
Il permet de tester si le résultat (tuples) d'une sous-requête est vide ou non
La clause EXISTS est suivie d'une sous-requête entre parenthèses, et prend la valeur vrai s'il existe au
moins une ligne satisfaisant les conditions de la sous-requête.
Pour tester si une valeur se trouve dans le résultat d'une requête, on utilise IN.
Soit B une liste de valeurs renvoyées par une requête. Pour tester si une condition (par
exemple A < B) est vraie pour:
toutes les valeurs de B, on utilise ALL,
au moins une valeur de B, on utilise ANY.
EXISTS vérifie si la sous-requête contient au moins une ligne.
23
Le prédicat EXISTS
Select R1.*
From R1, R2
Where R1.b=R2.b;
Select * Select *
From R1 From R1
Where R1.b IN (Select R2.b From R2); Where EXISTS (Select * From R2 Where R1.b=R2.b);
Soit E le resultat de la sous-requete:
b IN E : retrourne true si bE et false sinon b NOT IN E : retrourne true si bE et false sinon
EXISTS E: retrourne true si E n’est pas vide et NOT EXISTS E: retrourne true si E est vide et
false sinon false sinon
24
1. Liste des pilotes pilotant le meme avion que 2. Liste des pilotes GAGNANT plus que tous les
le pilote numero 4. pilotes nicois.
SELECT [Link],[Link] SELECT PLNUM,PLNOM,PLPRENOM,SALAIRE
FROM PILOTE P, VOL V FROM PILOTE
WHERE [Link]=[Link] AND WHERE SALAIRE>ALL(SELECT SALAIRE FROM PILOTE
[Link] IN (SELECT AVNUM FROM VOL WHERE VILLE='NICE');
WHERE PLNUM=4);
3. Liste des pilotes ayant le MEME SALAIRE que le pilote ‘PENAULD’. 4. Liste des pilotes ayant la MEME VILLE et
le MEME SALAIRE que le pilote ‘PENAULD’.
SELECT PLNUM,PLNOM,PLPRENOM,SALAIRE SELECT PLNUM,PLNOM,PLPRENOM,SALAIRE
FROM PILOTE FROM PILOTE
WHERE SALAIRE=(SELECT SALAIRE FROM PILOTE WHERE (VILLE, SALAIRE)=(SELECT VILLE,SALAIRE
WHERE PLNOM=PENAULD'); FROM
PILOTE
WHERE PLNOM='PENAULD‘
);
25
5. Liste des avions qui n’ont jamais volé.
SELECT AVNUM, AVNOM
FROM AVION
WHERE NOT EXISTS (SELECT * FROM VOL
WHERE [Link]=[Link]);
6. Afficher les 3 pilotes les mieux payés par ordre decroissant des salaires.
SELECT SALAIRE
FROM PILOTE
ORDER BY SALAIRE DESC LIMIT 3;
26
7. Le numero et le nom de l’avion qui a volé le plus.
SELECT AVNUM, AVNOM
FROM AVION NATURAL JOIN VOL
GROUP BY [Link]
HAVING COUNT(*)>=ALL (SELECT COUNT(*) FROM VOL GROUP BY AVNUM );
8. Créer une autre table pilote2 qui a la même structure que la table pilote.
CREATE TABLE PILOTE2 LIKE PILOTE;
9. Insérer dans la table pilote2 tous les pilotes qui n’ont jamais volé, en utilisant une sous-requête;
INSERT INTO PILOTE2
SELECT * FROM PILOTE
WHERE NOT EXISTS (SELECT * FROM VOL WHERE [Link]=[Link]);
27
10. Créer une autre table pilote3 qui contient les memes données que la table pilote, en utilisant une sous-requête;
CREATE TABLE PILOTE3 AS SELECT * FROM PILOTE;
11. Surpimer tous les pilotes de la table pilote3 qui sont dans la table pilote2, en utilisant une sous-requête;
DELETE FROM PILOTE3
WHERE EXISTS (SELECT 1 FROM PILOTE2 WHERE [Link]=[Link]);
28
12. Que fait cette requete ?
UPDATE PILOTE3 SET salaire=(SELECT MAX(salaire) FROM PILOTE) WHERE plnum=9;
Elle attribute au pilote numero 9, le salaire le plus grand.
12. Que fait cette requete ?
DELETE FROM PILOTE3
WHERE not exists (SELECT 1 FROM PILOTE3 WHERE [Link]=[Link]);
Elle supprime les pilotes qui n’ont jamais vole’.
29
