Cours avec des exemples SQL exercices corrigés langage SQL

SQL : any et all

• <attribut> <comparateur> ALL <liste> permet de vérfier si toutes les valeurs de la liste satisfont la condition.

Produit le plus léger

SELECT p.NP
FROM P p
WHERE p.Poids <= ALL ( SELECT p2.Poids
                                           FROM P p2 )

• ANY permet de vérifier si au moins une valeur de la liste satisfait la condition.

SQL : agrégation

• Les fonctions COUNT(), SUM(), MIN(), MAX(), AVG() permettent d'agréger les données.

• Résultat : une ligne par partition (ou par relation si pas de partition).

Moyenne des salaires

SELECT AVG(Salary) FROM Employee

Produit le plus léger

SELECT p.NP FROM P p
WHERE p.Poids = ( SELECT MIN(p2.Poids) FROM P p2 )

• La requête SELECT SSN, MAX(Salary) FROM Employee est illégale : pourquoi ?

SQL : group by

• La clause GROUP BY <attributs> permet de partitionner une relation.

• Il y aura une partition par combinaison de valeurs des attributs.

SELECT A1, ..., An, AGG(An+1), ..., AGG(Am)
FROM ... WHERE ...
GROUP BY A1, ..., An
HAVING condition

• Les attributs du SELECT (A1, ..., An) doivent être des clés de groupement.
• La condition du HAVING porte sur les partitions, donc sur les attributs A1, ..., An et sur des agrégations.

SELECT A1, ..., An, AGG(An+1), ..., AGG(Am)
FROM ... WHERE ...
GROUP BY A1, ..., An
HAVING condition

Ordre intuitif d'évaluation :

1. Evaluation du FROM... WHERE ...
2. Partitionnement selon les attributs du GROUP BY
3. Application de la condition du HAVING
4. Evaluation du SELECT

Moyenne des salaires par département

SELECT DNo, AVG(Salary)
FROM Employee
GROUP BY DNo

   

Moyenne des salaires pour les départements de moins de 3 employés

SELECT DNo, AVG(Salary)
FROM Employee
GROUP BY DNo
HAVING count(*) < 3

SQL : exercices

• Donner le nombre d'usines approvisionnées par le fournisseur 1.

• Pour chaque produit livré à une usine, donner le numéro du produit, celui de l'usine et la quantité totale livrée.

• Donner le numéro des fournisseurs qui ont vendu plus de 100 produits.

• Pour chaque fournisseur de Londres qui vend au moins 3 produits différents, donner le numéro du fournisseur et la quantité de produits vendus.

-------------------------------------------------------------------------------------------------------

Correction SQL

-------------------------------------------------------------------------------------------------------
 

• Donner le nombre d'usines approvisionnées par le fournisseur 1

SELECT COUNT(distinct l.NU)
FROM PUF l
WHERE l.NF = 1

• Pour chaque produit livré à une usine, donner le numéro du produit, celui de l'usine et la quantité totale livrée

SELECT l.NP, l.NU, SUM(l.Quantite)
FROM PUF l
GROUP BY l.NP, l.NU

• Donner le numéro des fournisseurs qui ont vendu plus de 100 produits

SELECT l.NF
FROM PUF l
GROUP BY l.NF
HAVING SUM(l.Quantite) > 100

• Pour chaque fournisseur de Londres qui vend au moins 3 produits différents, donner le numéro du fournisseur et la quantité de produits vendus

SELECT l.NF, SUM(l.Quantité)
FROM PUF l, F f
WHERE l.NF = f.NF and f.VilleF='Londres'
GROUP BY l.NF
HAVING COUNT(distinct l.NP) >= 3