SQL
Structured Query Language
Plan
1. Fonction de groupe
2. Les groupes
3. Données de plusieurs tables
ENSAJ
SQL
Structured Query Language
Plan
1. Fonctions de groupe
2. Les groupes
3. Données de plusieurs tables
ENSAJ
Introduction
les fonctions de groupe opèrent sur des ensembles de lignes afin de renvoyer un
seul résultat par groupe
ENSAJ 3
Type de fonctions de groupe
ENSAJ 4
Type de fonctions de groupe
Fonction Description
AVG ( [DISTINCT| ALL ] n ) Valeur moyenne de n (les valeurs NULL sont
ignorées)
COUNT({*|[DISTINCT|ALL] expr}) Nombre de lignes, où expr prend une valeur
différente de NULL (toutes les lignes sélectionnées
sont comptées avec *, y compris les doublons et
les lignes avec des valeurs NULL)
MAX([DISTINCT|ALL]expr) Valeur maximale de expr (les valeurs NULL sont
ignorées)
MIN([DISTINCT|ALL]expr) Valeur minimale de expr (les valeurs NULL sont
ignorées)
STDDEV([DISTINCT|ALL]x) Ecart-type de n (les valeurs NULL sont ignorées)
SUM([DISTINCT|ALL]n) Somme des valeurs de n (les valeurs NULL sont
ignorées)
VARIANCE([DISTINCT|ALL]x) Variance de n (les valeurs NULL sont ignorées)
ENSAJ 5
Fonction de groupe syntaxe
SELECT [ Colonne, ] fonction_de_groupe ( colonne, …..)
FROM Table
[ WHERE Condition ]
[ ORDER BY Colonne ]
ENSAJ 6
Le schéma de la base Employee
ENSAJ - 2019 7
Utilisation des fonctions AVG et SUM
les fonctions de groupe AVG et SUM sont utilisées sur les données numériques :
Les fonctions MIN et MAX sont utilisé pour les valeurs numérique, les caractères
et les dates :
ENSAJ 8
Utilisation de la fonction COUNT
COUNT (*) renvoie le nombre de ligne dans une table :
La fonction COUNT( expr) renvoie le nombre de lignes avec des valeurs non
nulles pour expr:
ENSAJ 9
Fonctions de groupe et valeurs NULL
les fonctions de groupe ignorent les valeurs nulles dans la colonnes :
La fonction NVL( colonne) force les fonctions de groupe à inclure les valeurs
nulles
ENSAJ 10
SQL
Structured Query Language
Plan
1. Fonctions de groupe
2. Les groupes
3. Données de plusieurs tables
ENSAJ
Créer des groupes de données
ENSAJ 12
Syntaxe
SELECT [ Colonne, ] fonction_de_groupe ( colonne, …..)
FROM Table
[ WHERE Condition ]
GROUP BY Colonne
[ ORDER BY Colonne ]
On peut utiliser la clause GROUP BY afin de diviser les lignes
d'une table en groupes
ENSAJ 13
Utiliser la clause GROUP BY
Toutes les colonnes de la liste SELECT qui ne sont pas incluses dans des
fonctions de groupe doivent figurer dans la clause GROUP BY.
ENSAJ 14
Utiliser la clause GROUP BY
La colonne dans GROUP BY ne doit pas nécessairement figurer dans la clause
SELECT :
ENSAJ 15
Regrouper sur plusieurs colonnes
ENSAJ 16
Regrouper sur plusieurs colonnes
La fonction SUM est donc appliquée à la colonne de salaire pour tous les ID de
poste de chaque groupe de numéros de département.
ENSAJ 17
Restreindre les résultats des groupes
ENSAJ 18
Restreindre les résultats des groupes
Pour restreindre les résultats dans un groupe on utilise la clause HAVING.
Le serveur oracle exécute cette clause de la façon suivantes :
1. Les lignes sont regroupées.
2. La fonction de groupe est appliquée
3. Les groupes qui correspondent à la clause HAVING s’affichent
ENSAJ 19
Utilisation de HAVING
Afficher le numéro et le salaire maximal des départements dont le salaire
maximal est supérieur à 10 000 $.
ENSAJ 20
SQL
Structured Query Language
Plan
1. Fonction de groupe
2. Les groupes
3. Données de plusieurs tables
ENSAJ
Données sur plusieurs tables
Afficher les colonnes de plusieurs tables ( jointure )
ENSAJ 22
Types de jointure
Il existe plusieurs types de jointure:
1. Jointure naturelle
2. Clause USING
3. Jointure croisée
ENSAJ 23
Jointure naturelle
Utiliser la clause NATURAL JOIN.
Cette clause se base sur les colonnes des deux tables portant le
mêmes nom.
Elle sélectionne les lignes des deux tables dont la valeurs sont
identiques dans toutes les colonnes qui correspondent.
Si les colonnes portant le même nom présentent des types de
donnés différents, une erreur est renvoyée.
ENSAJ 24
Le schéma de la base Employee
ENSAJ - 2019 25
Jointure Naturelle
La table LOCATIONS est jointe à la table DEPARTMENT par la colonne
LOCATION_ID, qui est la seule colonne portant le même nom dans les deux tables..
ENSAJ 26
Jointure avec USING
Si plusieurs colonnes portent le même nom, la clause NATURAL
JOIN peu être modifiée avec la clause USING.
Cette clause permet de designer les colonnes qui doivent être
utilisées pour une équijointure.
il faut pas utilisé des alias dans les tables de référence
ENSAJ 27
Le schéma de la base Employee
ENSAJ - 2019 28
Jointure avec USING
ENSAJ 29
Jointure avec USING
Les jointures naturelles utilisent toutes les colonnes dont les noms
et les types de données correspondent pour joindre les tables.
La clause USING vous permet de n'indiquer que les colonnes qui
doivent être utilisées pour une équijointure
ENSAJ 30
Différencier les noms de colonne
Utiliser les préfixes avant les noms de colonnes pour différencier
les noms de colonnes présent dans plusieurs tables.
Utiliser des alias de colonnes pour distinguer les colonnes qui
présentent des noms identiques, mais qui résident dans des tables
différentes
N’utiliser pas des alias sur les colonnes identifiées dans la clause
USING et indiquées ailleurs dans l’instruction SQL
ENSAJ - 2019 31
Jointure avec ON
la condition de la jointure naturelle est une équijointure de toutes
les colonnes portants le même nom
Utiliser la clause ON pour indiquer des conditions arbitraires ou
pour désigner les colonnes à joindre
La condition de jointure et différente et distincte des autres
conditions de recherche
La clause ON facilite la lecture du code
ENSAJ - 2019 32
Le schéma de la base Employee
ENSAJ - 2019 33
Auto-jointure avec ON
ENSAJ - 2019 34
Auto-jointure avec ON
ENSAJ - 2019 35
Appliquer des conditions supplémentaires avec ON
ENSAJ - 2019 36
Jointure sur plusieurs tables avec ON
ENSAJ - 2019 37
Produit cartésien
Un produit cartésien de deux tables est la combinaison de toutes les
lignes de la première table avec toutes les lignes de la deuxième table
Un produit cartésien est obtenu Lorsqu'une condition de jointure
n'est pas valide ou est totalement omise
Un produit cartésien tend à générer un grand nombre de lignes et le
résultat est rarement exploitable.
Les produits cartésiens sont utiles pour certains tests lorsque vous
devez générer un grand nombre de lignes afin de simuler une quantité
importante de données
ENSAJ - 2019 38
Produit cartésien ( exemple)
ENSAJ - 2019 39
Produit cartésien ( avec cross join)
ENSAJ - 2019 40
EXERCICE
On considère que la structure des tables d’une base de données pour les ressources humaines
d’une entreprise est la suivante :
Employe(idEmp, nom, prenom, salaire, date_embauche, commission, #idPoste, #idDepartement
#idManager) Poste(idPoste, intitule)
departement(idDepartement, nom_departement, localisation)
Ecrire le code SQL permettant d’ afficher :
- le nom et le salaire des employés qui gagnent plus de 12 000 DH
- Ecrire une requête SQL permettant de déterminer le salaire le plus élevé, le salaire le plus
faible, la somme des salaires et le salaire moyen de tous les employés.
- Ecrire une requête SQL permettant d'afficher le salaire minimum, le salaire maximum, la
somme des salaires et le salaire moyen pour chaque type de poste.
- Ecrire une interrogation SQL permettant d'afficher le nombre de personnes qui occupent le
même poste.
Ecrire une requête SQL permettant d’afficher le nom, l’intitulé de poste et le salaire de tous les
employés dont le poste est intitulé «commercial» et dont le salaire n'est pas égal à 2 500 DH, 3
500 DH ou 7 000 DH.
ENSAJ - 2019 41
