SQL Partie 3 : (LID : Langage d'interrogation de données)

1. SQL Partie 3 :(LID : Langage d'interrogation de données) Réfs : Chap 4.7, 4.8, 4.9 p 109-118

2. Requêtes sur un seul tableau I/ Requêtes sans restrictions = Projection Dans ce cas, on affichera tous les enregistrements, c'est-dire toutes les lignes du tableau. • L'instruction Select permet de préciser les colonnes du tableau que l'on souhaite afficher Select colonne1, colonne2,… Ex : select nom, prenom • On peut rencontrer également l'écriture suivante (plus longue) : Select nomTableau.colonne1, nomTableau.colonne2,… Ex : select client.nom, client.prenom Il est parfois indispensable d'y avoir recours lorsqu'une colonne porte le même nom dans deux tableaux différents de la base de données.

3. Si on souhaite afficher toutes les colonnes du tableau, on peut utiliser : Select *

4. Exemple 1 : Afficher toutes les informations sur les employés de l'entreprise Select * From employe Exemple 2 : Afficher le nom des différents services et leur lieu d'implantation Select nomService,lieu From service

5. L'instruction select distinct Cette instruction s'utilise à la place de select lorsqu'on ne souhaite pas afficher plusieurs fois des valeurs identiques. Exemple 3 : Afficher les différentes fonctions occupées dans l'entreprise Select distinct fonction From employe

6. II/ Requêtes avec restrictions = Selection Dans ce cas, seules les lignes vérifiant une ou plusieurs conditions seront affichées. C'est l'instruction where qui permet de préciser la première condition à respecter. En cas de plusieurs conditions, on complète avec l'instruction and Ex : where condition 1 and condition 2 and condition 3…

7. Exemple 4 : Afficher les noms des salariés qui occupent la fonction de commercial Select nomEmploye From employe Where fonction="commercial" Exemple 5 : Afficher les noms des salariés embauchés avant 1990 Select nomEmploye From employe Where dateEmbauche<"1990/01/01" Ou : Select nomEmploye From employe Where year(dateEmbauche)<1990

8. Exemple 6 : Afficher les noms des salariés dont la rémunération brute (salaire+commission) dépasse 40 000 € Select nomEmploye From employe Where salaire+commission>40000

9. Lorsqu'une condition porte sur un intervalle de dates ou de valeur, on peut utiliser l'instruction Between borneInf and borneSup Exemple 7 : Afficher le nom des salariés embauchés au cours de l'année 2005 Select nomEmploye From employe Where dateEmbauche between "2005/01/01" and "2005/12/31" Ou Select nomEmploye From employe Where year(dateEmbauche)=2005

10. Une condition peut comporter un calcul sur une ou plusieurs colonnes Exemple 8 : Afficher les noms des salariés dont la commission représente plus de la moitié du salaire Select nomEmploye From employe Where commission>salaire/2

11. Une condition peut tester si la valeur d'une colonne prend des valeurs isolées. Dans ce cas, on utilise l'instruction IN (valeur1, valeur2,…) Exemple 9 : Afficher les noms et fonctions des salariés qui sont soit commercial soit ingénieur Select nomEmploye, fonction From employe Where fonction in ("commercial","ingénieur")

12. Condition avec l'instruction like Cette instruction peut s'utiliser dans le cas suivant : Exemple 10 : Afficher les noms des salariés dont le nom commence par la lettre M Select nomEmploye From employe Where nomEmploye like "M%"

13. Requêtes avec plusieurs conditions (And ou OR) • Si plusieurs condition doivent être vérifiées simultanément, on utilise AND • Si au-moins une des deux conditions doivent être vérifiées, on utilise OR Exemple 11 : Afficher les noms des salariés du service 30, ayant un salaire brut annuel d'au-moins 25 000 € Select nomEmploye From employe Where numServEmploye=30 And salaire>=25000

14. Exemple 12 : Afficher les noms des salariés qui sont soit commercial travaillant dans le service 20, soit ingénieur Select nomEmploye From employe Where (fonction="commercial" and numServEmploye=20) Or fonction="ingénieur" Exemple 13 : Afficher les noms des salariés qui sont soit commercial ou ingénieur, et travaillant dans le service 20 Select nomEmploye From employe Where (fonction="commercial" or fonction="ingénieur") And numServEmploye=20

15. Conditions avec is null ou is not null Cette condition s'utilise lorsque certaines lignes d'une colonne ne comportent aucune valeur. Attention : zéro est considéré comme une valeur. Dans quel tableau et colonne de la base support, existe-il des lignes non renseignées ?

16. Exemple 14 : Afficher les noms des salariés qui ont un supérieur hiérarchique. Select nomEmploye From employe Where matSupérieur is not null

17. III/ Requêtes avec tris (ORDER BY) Les résultats d'une requête peuvent être affichés selon un ordre croissant ou décroissant sur un ou plusieurs critères. Les critères de tri doivent être précisés dans l'instruction ORDER BY suivi de la mention DESC si le tri est dans l'ordre décroissant.

18. Exemple 15 : Afficher la liste des salariés, classés par fonction et pour chaque fonction, par salaire décroissant Select nomEmploye, fonction,salaire From employe Order by salaire desc

19. Tri des résultats sur une colonne calculée Exemple 16 : Afficher la liste des salariés touchant une commission, classée par rémunération brute décroissante Select nomEmploye, fonction,salaire+commission From employe Where commission is not null Order by 3 desc

20. Requêtes sur plusieurs tableaux Exemple 17 : Afficher toutes les informations sur les salariés avec le lieu de travail Analyse : Le lieu de travail figure dans la table "Service" ; il est donc nécessaire d'utiliser les deux tableaux de la base. Règle : dans le cas où plusieurs tableaux doivent être utilisés, il faut obligatoirement définir une ou plusieurs jointures, c'est-à-dire préciser ce qui permet de relier les tableaux entre eux (voir modèle relationnel).

21. Pour relier un ou plusieurs tableaux, on utilise la clause Where, comme si il s'agissait d'une condition. Dans l'exemple, c'est le numéro de service qui permet de relier les deux tableaux utilisés. On aura donc : Where employe.NumServEmploye=service.NumService Rmq : le nom du tableau précède le nom de la colonne Select employe.*,lieu From employe,service Where employe.NumServEmploye=service.NumService

22. Cas particulier : tableau relié avec lui-même Exemple : Indiquer pour chaque salarié, le nom de son supérieur Analyse : toutes les informations sont dans la table Employe, mais le nom du supérieur n'apparait pas directement. Dans ce cas, il faut créer un deuxième tableau employe qu'on utilisera pour récupérer le nom du supérieur. Il faudra donner un nom (alias) à ce deuxième tableau pour le distinguer du premier, par exemple sup Les deux tableaux employe et sup seront reliés par le numéro de matricule. La jointure sera donc : employe.matSuperieur=sup.mat

23. Requêtes imbriquées Une condition peut faire appel à une sous requête, à condition que cette sous requête renvoie un résultat unique. Exemple 18 : Donner la liste des employés occupant la même fonction que le salarié nommé "dupond" Méthode : il faut d'abord créer la sous requête et la tester, puis compléter la requête principale. Ici la sous-requête consiste à afficher la fonction du salarié "dupond"

24. Select nomEmploye, fonction From employe Where fonction=(Select fonction From employe Where nomEmploye="dupont")

25. Les fonctions statistiques du SQL Ces fonctions sont prévues pour s'appliquer sur l'une des colonnes d'un tableau à l'exception de la fonction count(*) qui permet de compter les lignes d'un tableau. Le résultat renvoyé est toujours une valeur unique. Il existe 5 fonctions statistiques en SQL : Sum(nomColonne) : effectue la somme des valeurs d'une colonne Avg(nomColonne) : calcule la moyenne des valeurs d'une colonne Min(nomColonne) : renvoie la valeur minimale d'une colonne Max(nomColonne) : renvoie la valeur maximale d'une colonne Count(*) : compte le nombre de lignes ou d'enregistrements d'un tableau Count(nomColonne) : compte le nombre de lignes non vides d'une Colonne.

26. Réalisez les exemples suivants : Exemple 1 : calculer le salaire moyen des employés. Select avg(salaire) From employe Exemple 2 : trouver la date d’embauche du salarié le plus ancien. Select min(year(dateEmbauche)) From employe Exemple 3: Calculer l’ancienneté du salarié le plus ancien. select max(year(curdate())-year(dateEmbauche)) from employe

27. Exemple 4: Calculer la masse salariale de cette entreprise.(= somme de tous les salaires annuels de l’entreprise) Select sum(salaire) Fromemploye Exemple 5: Calculer l’effectif du service 20. Select count(*) Fromemploye WherenumServEmploye=20

28. Exemple 6: Trouver le nom de l’employé qui a le salaire le plus élevé. Select nomEmploye Fromemploye Where salaire=(Select max(salaire) Fromemploye)

29. L'instruction group by Cette instruction permet de réaliser en une seule requête, ce qui nécessiterait plusieurs requêtes sans utilisation de group by. Exemple : On souhaite afficher le nombre de salariés par service

30. Sans instruction group by, il faudrait réaliser plusieurs requêtes du même type que la suivante : Select count(*) From employe Where numServEmploye=20

31. Group by va permettre de parcourir le tableau "employe" en regroupant chaque ligne ou enregistrement par numéro de service si on décide de faire un regroupement par numéro de service. Group by réalise en quelque sortes des paquets d'enregistrements regroupés à l'aide d'un critère ; il suffira alors de compter le nombre d'enregistrements de chaque paquet grâce à l'instruction count(*).

32. Essayer : Select count(*) Fromemploye Group by numServEmploye Cette requête fonctionne, mais on ne sait pas à quels services correspondent les nombres affichés. Il est possible de placer dans l'instruction select, le nom de la colonne ou des colonnes ayant servi au regroupement à l'exclusion de toute autre.

33. Ce qui nous donne : Select numServEmploye, count(*) Fromemploye Group by numServEmploye Exercice 1 : Améliorons encore en donnant un intitulé approprié à la deuxième colonne du tableau résultat. Select numServEmploye, count(*) as "effectif" From employe Group by numServEmploye

34. Exercice 2 : Afin d'améliorer encore le résultat, on souhaiterait afficher également le nom du service en plus du numéro pour une meilleure interprétation. Cette fois, il faudra également utiliser le tableau "service" car lui seul contient le nom des services. Select numServEmploye, nomService, count(*) as "effectif" From employe, service where numServemploye=numService Group by numServEmploye, nomService

35. Exercice 3 : Afficher le nombre de salariés par lieu géographique Select numServEmploye,nomService, count(*) as "effectif" From employe, service where numServemploye=numService Group by numServEmploye,nomService Exercice 4 : Afficher le salaire moyen par service On utilisera la fonction avg(NomColonne) et la fonction round() pour arrondir à deux décimales le salaire moyen Exercice 5 : Trier le tableau précédent par salaire moyen croissant Astuce : il faut utiliser comme critère de tri l'intitulé crée avec l'instruction AS

36. L'instruction Having L'instruction Having complète l'instruction group by en offrant la possibilité d'introduire une condition portant sur chaque paquet d'enregistrements, une fois ceux ci déjà réalisés par le group by. L'instruction having est suivie d'une condition formulée uniquement grâce à une fonction statistique ou portant sur un champ de regroupement utilisé dans le group by ou sur l'intitulé du champ crée à l'aide de AS dans le select. Exemples de conditions : • having count(*)>=2 • having NomService like "*v" • having Salaire_moyen>20 000

37. Exemple 1 : Que va afficher la requête suivante ? Select nomService, count(*) as "Nombre de salariés" From employe, service Where numServEmploye=numservice Group by nomService, numServEmploye Having count(*)>=2 Cette requête affiche l'effectif des services ayant au moins deux salariés.

38. Exemple 2 : Que va afficher la requête suivante ? Select nomService, count(*) as "Nombre de salariés" From employe, service Where numServEmploye=numservice Group by nomService, numServEmploye Having NomService like "v*" Cette requête affiche l'effectif des services dont l'intitulé commence par la lettre v.

39. Exemple 3 : Compléter la requête pour qu'elle affiche le salaire moyen de chaque service lorsqu'il dépasse 20 000 €. Select nomService, round(avg(salaire),2) as "Salaire_moyen" From employe, service Where numServEmploye=numService Group by numServEmploye, nomService

40. Comment savoir si il faut utiliser l'instruction having ? Si la condition porte sur chaque paquet déjà regroupé, il faut utiliser having. Si la condition porte sur les lignes d'enregistrements d'un tableau avant d'effectuer les regroupements, il faut toujours utiliser where.

41. Exemple : Que fait la requête suivante ? Select nomService, round(avg(salaire),2) as "Moyenne des hauts salaires" From employe, service Where numServEmploye=numService And salaire>20000 Group by numService, nomService Elle calcule par service, la moyenne des salaires supérieurs à 20 000 € (Moyenne des hauts salaires)

42. Requêtes avec group by dans une instruction from Modifier la requête suivante pour obtenir le nombre moyen de salariés par service : Select NumServEmploye, count(*) as "effectif" From employe Group by numServEmploye

43. Corrigé : select avg(effectif) as effectif_moyen from (Select count(*) as effectif From employe Group by numServEmploye)