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);

1. Regroupement

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.

1.1. La clause GROUP BY

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 BY fonction;

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) ?

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

Quel est le nombre de livres commandés par client (numéro, nom et  prénom du client) ?

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

Règle :

 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 1^er 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;

On ne sait pas à quelle fonction se rapporte chacune des moyennes affichées.

1.2. La clause HAVING

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

HAVINGcount(*) > 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)

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

……………………………………………………………………………………………………………

2. Exercices d'application :

1. Quel est le prix minimum des livres?

2. Quel est le titre du livre le moins cher ?

3. Quel est le prix moyen des livres traitant d'informatique ?

4. Quel est le nombre de villes où résident nos clients ?

5. Quel est le nombre de clients par ville ?

6. Quel est le nombre de commandes passées par chaque client ?

7. Quels sont tous les numéros de clients ayant commandé plus de 10 volumes ?

8. Quel est le prix minimum des volumes par code catégorie pour les catégories comptant moins de 10 volumes ?

9. Réécrire la même requête avec l'affichage par libellé catégorie en supposant l’unicité des libellés.