Page 1

Rfs : Chap 4.7, 4.8, 4.9 p 109-118

Page 2

Requtes sur un seul tableau I/ Requtes sans restrictions = Projection Dans ce cas, on affichera tous les enregistrements, c'est-dire toutes les lignes du tableau. L'instruction Select permet de prciser 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 mme nom dans deux tableaux diffrents de la base de donnes.

Page 3

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

Page 4

Exemple 1 : Afficher toutes les informations sur les employs de l'entreprise Select * From employe Exemple 2 : Afficher le nom des diffrents services et leur lieu d'implantation Select nomService,lieu From service

Page 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 diffrentes fonctions occupes dans l'entreprise Select distinct fonction From employe

Page 6

II/ Requtes avec restrictions = Selection Dans ce cas, seules les lignes vrifiant une ou plusieurs conditions seront affiches. C'est l'instruction where qui permet de prciser la premire condition respecter. En cas de plusieurs conditions, on complte avec l'instruction and Ex : where condition 1 and condition 2 and condition 3

Page 7

Exemple 4 : Afficher les noms des salaris qui occupent la fonction de commercial Select nomEmploye From employe Where fonction="commercial" Exemple 5 : Afficher les noms des salaris embauchs avant 1990 Select nomEmploye From employe Where dateEmbauche40000

Page 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 salaris embauchs au cours de l'anne 2005 Select nomEmploye From employe Where dateEmbauche between "2005/01/01" and "2005/12/31" Ou Select nomEmploye From employe Where year(dateEmbauche)=2005

Page 10

Une condition peut comporter un calcul sur une ou plusieurs colonnes Exemple 8 : Afficher les noms des salaris dont la commission reprsente plus de la moiti du salaire Select nomEmploye From employe Where commission>salaire/2

Page 11

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

Page 12

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

Page 13

Requtes avec plusieurs conditions (And ou OR) Si plusieurs condition doivent tre vrifies simultanment, on utilise AND Si au-moins une des deux conditions doivent tre vrifies, on utilise OR Exemple 11 : Afficher les noms des salaris du service 30, ayant un salaire brut annuel d'au-moins 25 000 Select nomEmploye From employe Where numServEmploye=30 And salaire>=25000

Page 14

Exemple 12 : Afficher les noms des salaris qui sont soit commercial travaillant dans le service 20, soit ingnieur Select nomEmploye From employe Where (fonction="commercial" and numServEmploye=20) Or fonction="ingnieur" Exemple 13 : Afficher les noms des salaris qui sont soit commercial ou ingnieur, et travaillant dans le service 20 Select nomEmploye From employe Where (fonction="commercial" or fonction="ingnieur") And numServEmploye=20

Page 15

Conditions avec is null ou is not null Cette condition s'utilise lorsque certaines lignes d'une colonne ne comportent aucune valeur. Attention : zro est considr comme une valeur. Dans quel tableau et colonne de la base support, existe-il des lignes non renseignes ?

Page 16

Exemple 14 : Afficher les noms des salaris qui ont un suprieur hirarchique. Select nomEmploye From employe Where matSuprieur is not null

Page 17

III/ Requtes avec tris (ORDER BY) Les rsultats d'une requte peuvent tre affichs selon un ordre croissant ou dcroissant sur un ou plusieurs critres. Les critres de tri doivent tre prciss dans l'instruction ORDER BY suivi de la mention DESC si le tri est dans l'ordre dcroissant.

Page 18

Exemple 15 : Afficher la liste des salaris, classs par fonction et pour chaque fonction, par salaire dcroissant Select nomEmploye, fonction,salaire From employe Order by salaire desc

Page 19

Tri des rsultats sur une colonne calcule Exemple 16 : Afficher la liste des salaris touchant une commission, classe par rmunration brute dcroissante Select nomEmploye, fonction,salaire+commission From employe Where commission is not null Order by 3 desc

Page 20

Requtes sur plusieurs tableaux Exemple 17 : Afficher toutes les informations sur les salaris avec le lieu de travail Analyse : Le lieu de travail figure dans la table "Service" ; il est donc ncessaire d'utiliser les deux tableaux de la base. Rgle : dans le cas o plusieurs tableaux doivent tre utiliss, il faut obligatoirement dfinir une ou plusieurs jointures, c'est--dire prciser ce qui permet de relier les tableaux entre eux (voir modle relationnel).

Page 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 numro de service qui permet de relier les deux tableaux utiliss. On aura donc : Where employe.NumServEmploye=service.NumService Rmq : le nom du tableau prcde le nom de la colonne Select employe.*,lieu From employe,service Where employe.NumServEmploye=service.NumService

Page 22

Cas particulier : tableau reli avec lui-mme Exemple : Indiquer pour chaque salari, le nom de son suprieur Analyse : toutes les informations sont dans la table Employe, mais le nom du suprieur n'apparait pas directement. Dans ce cas, il faut crer un deuxime tableau employe qu'on utilisera pour rcuprer le nom du suprieur. Il faudra donner un nom (alias) ce deuxime tableau pour le distinguer du premier, par exemple sup Les deux tableaux employe et sup seront relis par le numro de matricule. La jointure sera donc : employe.matSuperieur=sup.mat

Page 23

Requtes imbriques Une condition peut faire appel une sous requte, condition que cette sous requte renvoie un rsultat unique. Exemple 18 : Donner la liste des employs occupant la mme fonction que le salari nomm "dupond" Mthode : il faut d'abord crer la sous requte et la tester, puis complter la requte principale. Ici la sous-requte consiste afficher la fonction du salari "dupond"

Page 24

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

Page 25

Les fonctions statistiques du SQL Ces fonctions sont prvues 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 rsultat 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.

Page 26

Ralisez les exemples suivants : Exemple 1 : calculer le salaire moyen des employs. Select avg(salaire) From employe Exemple 2 : trouver la date dembauche du salari le plus ancien. Select min(year(dateEmbauche)) From employe Exemple 3: Calculer lanciennet du salari le plus ancien. select max(year(curdate())-year(dateEmbauche)) from employe

Page 27

Exemple 4: Calculer la masse salariale de cette entreprise.(= somme de tous les salaires annuels de lentreprise) Select sum(salaire) From employe Exemple 5: Calculer leffectif du service 20. Select count(*) From employe Where numServEmploye=20

Page 28

Exemple 6: Trouver le nom de lemploy qui a le salaire le plus lev. Select nomEmploye From employe Where salaire=(Select max(salaire) From employe)

Page 29

L'instruction group by Cette instruction permet de raliser en une seule requte, ce qui ncessiterait plusieurs requtes sans utilisation de group by. Exemple : On souhaite afficher le nombre de salaris par service

Page 30

Sans instruction group by, il faudrait raliser plusieurs requtes du mme type que la suivante : Select count(*) From employe Where numServEmploye=20

Page 31

Group by va permettre de parcourir le tableau "employe" en regroupant chaque ligne ou enregistrement par numro de service si on dcide de faire un regroupement par numro de service. Group by ralise en quelque sortes des paquets d'enregistrements regroups l'aide d'un critre ; il suffira alors de compter le nombre d'enregistrements de chaque paquet grce l'instruction count(*).

Page 32

Essayer : Select count(*) From employe Group by numServEmploye Cette requte fonctionne, mais on ne sait pas quels services correspondent les nombres affichs. 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.

Page 33

Ce qui nous donne : Select numServEmploye, count(*) From employe Group by numServEmploye Exercice 1 : Amliorons encore en donnant un intitul appropri la deuxime colonne du tableau rsultat. Select numServEmploye, count(*) as "effectif" From employe Group by numServEmploye

Page 34

Exercice 2 : Afin d'amliorer encore le rsultat, on souhaiterait afficher galement le nom du service en plus du numro pour une meilleure interprtation. 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

Page 35

Exercice 3 : Afficher le nombre de salaris par lieu gographique 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 dcimales le salaire moyen Exercice 5 : Trier le tableau prcdent par salaire moyen croissant Astuce : il faut utiliser comme critre de tri l'intitul cre avec l'instruction AS

Page 36 20 000">

L'instruction Having L'instruction Having complte l'instruction group by en offrant la possibilit d'introduire une condition portant sur chaque paquet d'enregistrements, une fois ceux ci dj raliss par le group by. L'instruction having est suivie d'une condition formule uniquement grce une fonction statistique ou portant sur un champ de regroupement utilis dans le group by ou sur l'intitul du champ cre l'aide de AS dans le select. Exemples de conditions : having count(*)>=2 having NomService like "*v" having Salaire_moyen>20 000

Page 37 =2 Cette requte affiche l'effectif des services ayant au moins deux salaris.">

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

Page 38

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

Page 39

Exemple 3 : Complter la requte pour qu'elle affiche le salaire moyen de chaque service lorsqu'il dpasse 20 000. Select nomService, round(avg(salaire),2) as "Salaire_moyen" From employe, service Where numServEmploye=numService Group by numServEmploye, nomService

Page 40

Comment savoir si il faut utiliser l'instruction having ? Si la condition porte sur chaque paquet dj 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.

Page 41 20000 Group by numService, nomService Elle calcule par service, la moyenne des salaires suprieurs 20 000 (Moyenne des hauts salaires)">

Exemple : Que fait la requte 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 suprieurs 20 000 (Moyenne des hauts salaires)

Page 42

Requtes avec group by dans une instruction from Modifier la requte suivante pour obtenir le nombre moyen de salaris par service : Select NumServEmploye, count(*) as "effectif" From employe Group by numServEmploye

Page 43

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