LE LANGAGE SQL
Requêtes d'interrogation de données : fonctions statistiques et group by
Soit le modèle relationnel suivant :
CLIENT (num_cli, nom_cli, pren_cli, rue_cli, cp_cli, ville_cli, credit_autorisé)
COMLIVRE (num_comm, date_comm, qte, #no_volume, #num_emp, #num_cli)
EMPLOYE (num_emp, nom_emp, pren_emp, fonction, date_emb, salaire, prime)
La prime est facultative.
VOLUME (no_volume, titre, prix, #code_categ)
CATLIVRE (code_categ, lib_categ)
Soit l'extrait de la relation EMPLOYE :
|
num_emp |
nom_emp |
pren_emp |
fonction |
date_emb |
salaire |
prime |
|
1000 |
Vilier |
Paul |
vendeur |
01/12/92 |
8000 |
1500 |
|
1010 |
Audinet |
Elizabeth |
secrétaire |
01/06/93 |
7500 |
|
|
1020 |
Bontemps |
Frédérique |
secrétaire |
01/08/94 |
7500 |
|
|
1030 |
Guillaume |
Estelle |
vendeur |
01/10/93 |
7800 |
2000 |
|
1040 |
Chevalier |
Patrick |
comptable |
01/08/93 |
9000 |
|
|
1050 |
Receveur |
Philippe |
vendeur |
01/06/92 |
8600 |
1500 |
|
1060 |
Perrin |
Christian |
directeur |
01/01/92 |
15000 |
|
Exemples :
Quel est le salaire le plus élevé dans la société ?
SELECT MAX(salaire) as moyenne
FROM EMPLOYE;
A combien s'élèvent au total les salaires annuels des employés ?
SELECT SUM(salaire)*12
FROM EMPLOYE;
Quel est le nombre de fonctions existant dans la société ?
SELECT COUNT(DISTINCT fonction)
FROM EMPLOYE;
Dans les exemples précédents, les fonctions statistiques sont appliquées à la totalité des lignes d'une table. Ils peuvent bien sûr s'appliquer à un sous-ensemble des lignes d'une table en utilisant la clause WHERE.
Exemples d'utilisation de fonctions statistiques avec la clause WHERE :
Combien y-a-t-il de volumes de catégorie Finances ?
SELECT COUNT(*)
FROM VOLUME
WHERE code_categ IN
(SELECT code_categ
FROM CATEGORIE
WHERE lib_categ = 'Finances');
Lorsqu'on utilise des fonctions statistiques, on ne peut pas y introduire des interrogations individuelles.
Ainsi pour obtenir le nom de l'employé qui a le salaire le plus élevé, on ne peut pas écrire la requête suivante :
SELECT nom_emp,
MAX(salaire)
FROM
Employe;
Par contre, on peut calculer un résultat dans une sous-requête à l'aide d'une fonction statistique et tester ce résultat dans la requête de niveau supérieur.
SELECT nom_emp
FROM Employe
WHERE salaire =
( SELECT MAX(salaire)
FROM Employe);
Quels sont les noms des employés dont le salaire est supérieur ou égal au salaire moyen ?
SELECT nom_emp
FROM Employe
WHERE salaire >=
( SELECT AVG(salaire)
FROM Employe);
Le regroupement va permettre d'appliquer les fonctions statistiques non plus sur un seul ensemble de ligne mais par ensemble de lignes vérifiant un même critère c'est à dire par ensemble de lignes dont l'attribut (ou les attributs) de groupement a (ou ont) la même valeur.
Quel est le salaire moyen des employés ?
SELECT AVG(salaire) as Salaire_moyen
FROM EMPLOYE;
La moyenne est calculée sur les valeurs de salaire de toutes les lignes de la table Employe.
Tuple résultat :
|
Salaire_moyen |
|
9057,14 |
Quel est le salaire moyen des employés par fonction ?
SELECT fonction, AVG(salaire)
FROM EMPLOYE
GROUP BYfonction;
La moyenne est calculée pour chaque ensemble de lignes, les lignes étant regroupées pour les mêmes valeurs de l'attribut fonction.
Tuples résultats :
|
Fonction |
AVG(salaire) |
|
vendeur |
8133,33 |
|
secrétaire |
7500 |
|
comptable |
9000 |
|
directeur |
15000 |
Quel est le nombre de livres commandés par client (numéro client) ?
SELECT num_cli, COUNT(*)
FROM comlivre
GROUP BY num_cli
Quel est le nombre de livres commandés par client (numéro, nom et prénom du client) ?
SELECT nom_cli, pren_cli, COUNT(*)
FROM comlivre c
INNER JOIN client cl ON c.num_cli = cl.num_cli
GROUP BY c.num_cli
Règles :
1. ce qui figure dans le GROUP BY ne doit pas obligatoirement figurer dans le SELECT mais quand il s'agit d'un SELECT de 1er niveau, cela s'impose car sinon le résultat n'a pas de sens. Cela n'a pas de sens d'écrire :
SELECT AVG(salaire)
FROM EMPLOYE
GROUP BY fonction;
Ici on ne sait pas à quelle fonction se rapporte chacune des moyennes affichées.
Si un critère de restriction doit être exprimé non pas sur chaque ligne d'une table mais sur chaque ensemble de lignes produit par un regroupement, la clause à utiliser n'est pas WHERE mais HAVING.
Exemple :
Quel est le salaire moyen des employés par fonction qui compte plus d'un employé ?
SELECT fonction, AVG(salaire)
FROM Employe
GROUP BYfonction
HAVING count(*) > 1;
Quels sont les numéros des clients qui ont acheté pour plus de 100 euros de livres ? (on précisera le montant total des livres achetés)
SELECT c.num_cli, SUM(prix * qte)
FROM comlivre c
INNER JOIN volume v
ON c.no_volume = v.no_volume
GROUP c.num_cli
HAVING SUM(prix * qte) > 100 ;
1. Quel est le prix minimum des livres?
SELECT MIN(prix)
FROM VOLUME;
2. Quel est le titre du livre le moins cher ?
SELECT titre
FROM VOLUME
WHERE prix =
(SELECT MIN(prix) FROM VOLUME)
3. Quel est le prix moyen des livres traitant d'informatique ?
SELECT AVG(prix)
FROM VOLUME
WHERE code_categ IN
(SELECT code_categ FROM CATEGORIE WHERE lib_categ='Informatique')
4. Quel est le nombre de villes où résident nos clients ?
SELECT COUNT(DISTINCT ville_cli)
FROM CLIENT
5. Quel est le nombre de clients par ville ?
SELECT ville_cli, COUNT(*)
FROM CLIENT
GROUP BY ville_cli
6. Quel est le nombre de commandes passées par chaque client ?
SELECT num_cli, COUNT(*)
FROM COMLIVRE
GROUP BY num_cli
7. Quels sont tous les numéros de clients ayant commandé plus de 10 volumes ?
SELECT num_cli
FROM COM_LIVRE
GROUP BY num_cli
HAVING COUNT(*) > 10
8. Quel est le prix minimum des volumes par code catégorie pour les catégories comptant moins de 10 volumes ?
SELECT code_categ, MIN(prix)
FROM VOLUME
GROUP BY code_categ
HAVING count(*) < 10
9. Réécrire la même requête avec l'affichage par libellé catégorie en supposant l’unicité des libellés.
SELECT lib_categ, MIN(prix)
FROM CATEGORIE, VOLUME
WHERE CATEGORIE.code_categ = VOLUME.code_categ
GROUP BY lib_categ
HAVING count(*) < 10