04-SQL Agregation Groupement [PDF]
12/01/20 Bases de données SQL - Agrégation et groupement EL Moukhtar ZEMMOURI ENSAM – Meknès Version – 2020 / 2021 E.
23 0 411KB
![04-SQL Agregation Groupement [PDF]](https://vdoc.tips/img/200x200/04-sql-agregation-groupement.jpg)
- Author / Uploaded
- redwane ouberri
Datei wird geladen, bitte warten...
Zitiervorschau
12/01/20
Bases de données SQL - Agrégation et groupement
EL Moukhtar ZEMMOURI ENSAM – Meknès Version – 2020 / 2021
E. Zemmouri, ENSAM - Meknès
2
12/01/20
Introduction
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
dnum
dnom
ville
dir
101
HASSAN
Ingénieur
1200
10
10
Production
Meknes
101
102
AMAL
Ingénieur
1000
10
20
Ventes
Casa
100
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
Table DEP
•
Le salaire min, le salaire max et la moyenne des salaires des employés?
•
Le nombre d’employés ingénieurs?
•
La moyenne des salaires par ville? Ou par profession ?
•
è Opérateurs d’agrégation et groupement 3
E. Zemmouri, ENSAM - Meknès
Opérateurs d’agrégation • Des opérateurs qui s’appliquent sur un ensemble de valeurs (numériques, chaines, …) et qui produisent une valeur agrégée comme résultat. • Ces opérateurs sont utilisés pour résumer ou «agréger» les valeurs d’une colonne d'une relation è opérateurs d'agrégation.
E. Zemmouri, ENSAM - Meknès
4
12/01/20
Opérateurs d’agrégation • Les opérateurs standard : o
SUM
o
MIN
o
MAX
o
AVG
o
COUNT
o
…
5
E. Zemmouri, ENSAM - Meknès
Opérateurs d’agrégation
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
La somme des salaires des employés? SELECT SUM(sal) FROM emp
E. Zemmouri, ENSAM - Meknès
SUM(sal) 7100
6
12/01/20
Opérateurs d’agrégation
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Le salaire min, le salaire max et la moyenne des salaires des employés? SELECT MIN(sal), MAX(SAL), AVG(sal) FROM emp
MIN(sal)
MAX(sal)
AVG(sal)
800
1200
1014.28
7
E. Zemmouri, ENSAM - Meknès
Opérateurs d’agrégation
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Le nombre d’employés ingénieurs? SELECT COUNT(*) FROM emp
COUNT(*) 3
WHERE prof=‘Ingénieur’ E. Zemmouri, ENSAM - Meknès
8
12/01/20
Opérateurs d’agrégation
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Le nombre de professions? Attention à la répétition ! SELECT COUNT(prof)
SELECT COUNT(DISTINCT prof)
FROM emp
FROM emp
9
E. Zemmouri, ENSAM - Meknès
Opérateurs d’agrégation
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Le salaire moyen des ingénieur? SELECT AVG(sal) FROM emp
AVG(sal) 1100
WHERE prof=‘Ingénieur’ E. Zemmouri, ENSAM - Meknès
10
12/01/20
Groupement
11
E. Zemmouri, ENSAM - Meknès
Groupement
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Le salaire moyen par profession ?
E. Zemmouri, ENSAM - Meknès
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
105
ADIL
Ingénieur
1100
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
106
ZAID
Technicien
800
20 12
12/01/20
Groupement • Regrouper les tuples d’une table en sous groupes avant d’appliquer un opérateur d’agrégation sur chaque sous groupe. • Le groupement se fait selon la valeur d’une colonne (ou plusieurs colonnes)
13
E. Zemmouri, ENSAM - Meknès
Groupement
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Le salaire moyen par profession ? SELECT prof, AVG(sal) FROM emp GROUP BY prof
E. Zemmouri, ENSAM - Meknès
prof
AVG(sal)
Ingénieur
1100
Manager
1000
Technicien
933.33 14
12/01/20
Groupement
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
dnum
dnom
ville
dir
101
HASSAN
Ingénieur
1200
10
10
Production
Meknes
101
102
AMAL
Ingénieur
1000
10
20
Ventes
Casa
100
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Table DEP
La somme et la moyenne des salaires par ville ? SELECT ville, SUM(sal), AVG(sal) FROM emp NATURAL JOIN dep
ville
SUM(sal)
AVG(sal)
Casa
2900
966.66
Meknes
4200
1050
GROUP BY ville 15
E. Zemmouri, ENSAM - Meknès
Groupement
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
dnum
dnom
ville
dir
101
HASSAN
Ingénieur
1200
10
10
Production
Meknes
101
102
AMAL
Ingénieur
1000
10
20
Ventes
Casa
100
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Table DEP
La moyenne des salaires par prof dans chaque ville ?
ville
prof
AVG(sal)
Casa
Manager
1000
Casa
Technicien
950
FROM emp NATURAL JOIN dep
Meknes
Ingénieur
1100
GROUP BY ville, prof
Meknes
Technicien
900
SELECT ville, prof, AVG(sal)
E. Zemmouri, ENSAM - Meknès
16
12/01/20
Sélection dans un groupement
17
E. Zemmouri, ENSAM - Meknès
Sélection après groupement
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
Les professions dont la moyenne des salaires est supérieure à 1000 ?
E. Zemmouri, ENSAM - Meknès
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
105
ADIL
Ingénieur
1100
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
106
ZAID
Technicien
800
20 18
12/01/20
Sélection après groupement • La clause HAVING permet la sélection, après groupement, de certains groupes selon une condition qui dépend du groupe dans son ensemble, plutôt que des tuples individuels.
19
E. Zemmouri, ENSAM - Meknès
Sélection après groupement
Table EMP
enum
enom
prof
sal
dnum
100
ALI
Manager
1000
20
101
HASSAN
Ingénieur
1200
10
102
AMAL
Ingénieur
1000
10
103
AMINA
Technicien
1100
20
104
AMINE
Technicien
900
10
105
ADIL
Ingénieur
1100
10
106
ZAID
Technicien
800
20
•
prof
AVG(sal)
Ingénieur
1100
Manager
1000
Technicien
933.33
Les professions dont la moyenne des salaires est supérieure à 1000 ? SELECT prof FROM emp GROUP BY prof HAVING AVG(sal) >= 1000
E. Zemmouri, ENSAM - Meknès
prof Ingénieur Manager 20
12/01/20
Questions
21
E. Zemmouri, ENSAM - Meknès
EMP (enum, enom, prof, sal, dnum*) DEP (dnum, dnom, ville, dir*)
Question
• Donner les villes où la moyenne des salaires des ingénieurs est supérieurs à 1000.
E. Zemmouri, ENSAM - Meknès
22
12/01/20
EMP (enum, enom, prof, sal, dnum*) DEP (dnum, dnom, ville, dir*)
Question
• Donner la liste des employés qui ont un salaire supérieur à la moyenne des salaires de tous les employés.
23
E. Zemmouri, ENSAM - Meknès
EMP (enum, enom, prof, sal, dnum*) DEP (dnum, dnom, ville, dir*)
Question
• Donner la liste des employés qui ont le salaire max dans leurs départements.
E. Zemmouri, ENSAM - Meknès
24
12/01/20
EMP (enum, enom, prof, sal, dnum*) DEP (dnum, dnom, ville, dir*)
Question
• Donner la liste des employés qui ont un salaire supérieur au salaire moyen dans leurs villes.
25
E. Zemmouri, ENSAM - Meknès
Bases de données Agrégation et groupement
EL Moukhtar ZEMMOURI ENSAM – Meknès Version – 2020 / 2021