[SGBD_SQL] [2019-2020]
Lesson 1: SELECT *
family_members
id name
1 Dave
2 Mary
3 Pickles
gender
male
female
male
species num_books_read
human 200
human 180
dog
0
SELECT * FROM family_members;
Result:
id name
1 Dave
2 Mary
3 Pickles
gender
male
female
male
species num_books_read
human 200
human 180
dog
0
Lesson 2: SELECT specific columns
Current tables:
family_members
id name
1 Dave
2 Mary
3 Pickles
gender
male
female
male
species
human
human
dog
num_books_read
200
180
0
SELECT * FROM family_members WHERE num_books_read > 0
Result:
id name gender species num_books_read
1 Dave male
human 200
2 Mary female human 180
SELECT*FROM family_members WHERE num_books_read >=180;
Result:
SELECT name, num_books_read FROM family_members;
Result:
name num_books_read
Dave
200
Mary
180
Pickles 0
Lesson 3: WHERE ... =, <, <=….AND, OR …
SELECT * FROM family_members WHERE species = 'human';
Result:
id name gender species num_books_read
1 Dave male
human 200
2 Mary female human 180
[Circuits à diodes]
id name gender species num_books_read
1 Dave male
human 200
2 Mary female human 180
Current tables:
friends_of_pickles
id
1
2
3
4
5
6
7
name
Dave
Mary
Fry
Leela
Odie
Jumpy
Sneakers
gender
male
female
male
female
male
male
male
species
human
human
cat
cat
dog
dog
dog
height_cm
180
160
30
25
40
35
55
Page 1
[SGBD_SQL] [2019-2020]
SELECT * FROM friends_of_pickles WHERE height_cm > 25 AND species = 'cat';
Result:
id name gender species height_cm
3 Fry
male
cat
30
SELECT * FROM friends_of_pickles WHERE height_cm > 25 OR species = 'cat';
Result:
id
1
2
3
4
5
6
7
name
Dave
Mary
Fry
Leela
Odie
Jumpy
Sneakers
gender
male
female
male
female
male
male
male
species
human
human
cat
cat
dog
dog
dog
height_cm
180
160
30
25
40
35
55
Lesson 4: IN
SELECT * FROM friends_of_pickles WHERE species IN ('cat', 'human');
Result:
id name gender species height_cm
1 Dave male
human 180
2 Mary female human 160
3 Fry
male
cat
30
4 Leela female cat
25
SELECT * FROM friends_of_pickles WHERE species NOT IN ('cat', 'dog');
Result:
id name gender species height_cm
1 Dave male
human 180
2 Mary female human 160
[Circuits à diodes]
Lesson 9: DISTINCT
SELECT DISTINCT gender, species FROM friends_of_pickles WHERE height_cm <
100;
Result:
gender
male
female
male
species
cat
cat
dog
Lesson 10: ORDER BY
SELECT * FROM friends_of_pickles ORDER BY name;
Result:
id
1
3
6
4
2
5
7
name
Dave
Fry
Jumpy
Leela
Mary
Odie
Sneakers
gender
male
male
male
female
female
male
male
species
human
cat
dog
cat
human
dog
dog
height_cm
180
30
35
25
160
40
55
SELECT * FROM friends_of_pickles ORDER BY height_cm;
Result:
id
4
3
6
5
7
2
1
name
Leela
Fry
Jumpy
Odie
Sneakers
Mary
Dave
gender
female
male
male
male
male
female
male
species
cat
cat
dog
dog
dog
human
human
height_cm
25
30
35
40
55
160
180
Page 2
[SGBD_SQL] [2019-2020]
SELECT * FROM friends_of_pickles ORDER BY height_cm DESC;
Result:
id
1
2
7
5
6
3
4
name
Dave
Mary
Sneakers
Odie
Jumpy
Fry
Leela
gender
male
female
male
male
male
male
female
species
human
human
dog
dog
dog
cat
cat
height_cm
180
160
55
40
35
30
25
Lesson 11: LIMIT # of returned rows
SELECT * FROM friends_of_pickles ORDER BY height_cm LIMIT 2;
Result:
id name gender species height_cm
4 Leela female cat
25
3 Fry
male
cat
30
SELECT * FROM friends_of_pickles ORDER BY height_cm DESC LIMIT 3;
Result:
id
name
1 Dave
2 Mary
7 Sneakers
gender
male
female
male
species
human
human
dog
height_cm
180
160
55
Lesson 12: COUNT(*)
SELECT COUNT(*) FROM friends_of_pickles;
Result:
Lesson 14: SUM: somme
SELECT SUM(num_legs) FROM family_members;
Result:
SUM(num_legs)
8
Lesson 15: AVG : moyenne
SELECT AVG(num_legs) FROM family_members;
Result:
AVG(num_legs)
2.6666666666666665
Lesson 16: MAX and MIN
SELECT MIN(num_legs) FROM family_members;
Result:
MIN(num_legs)
2
Lesson 17: GROUP BY
SELECT COUNT(*), species FROM friends_of_pickles GROUP BY
species;
Result:
COUNT(*) species
2
cat
3
dog
2
human
COUNT(*)
7
Lesson 13: COUNT(*) ... WHERE
SELECT COUNT(*) FROM friends_of_pickles WHERE species = 'human';
Result:
COUNT(*)
2
[Circuits à diodes]
Page 3
EXERCICE 1 : Les requêtes SELECT - Base de données ETUDIANT
Objectif: Écrire des requêtes SELECT comportant la projection, le filtrage et le regroupement des données.
Soit le schéma relationnel suivant:
Travail à faire:
Écrire les requêtes suivantes en SQL :
1. Afficher la liste des étudiants triés par ordre croissant de date de naissance.
2. Afficher tous les étudiants inscrits à M1 et tous les étudiants inscrits à M2.
3. Afficher les matricules des étudiants qui ont passé l'examen du cours 002.
4. Afficher les matricules de tous les étudiants qui ont passé l'examen du cours 001 et de tous les étudiants qui ont
passé l'examen du cours 002.
5. Afficher le matricule, code, note /20 et note /40 de tous les examens classés par ordre croissant de matricule et
de code.
6. Trouver la moyenne de notes de cours 002.
7. Compter les examens passés par un étudiant (exemple avec matricule 'e1')
8. Compter le nombre d'étudiants qui ont passé l'examen du cours 002.
9. Calculer la moyenne des notes d'un étudiant (exemple avec matricule 'e1').
10. Compter les examens passés par chaque étudiant.
11. Calculer la moyenne des notes pour chaque étudiant.
12. La même au dessus, mais afficher seulement les étudiants (et leurs moyennes) dont la moyenne est >= 15.
13. Trouver la moyenne de notes de chaque cours.
Correction :
/Auteur: CHAOULID
//Copyright: Exelib.net
use ETUDIANTS
go
[Circuits à diodes]
Page 4
--Q1 select *from Etudiant order by DateNaissance
--Q2 select * from Etudiant where Niveau='M1' or Niveau='M2'
--Q3 select Matricule from Examen where Code='002'
--Q4 select Matricule from Examen where Code='001' or Code='002'
--Q5 select Matricule,Code,Note as "Note sur 20",Note*2 as "Note sur 40" from Examen order by
Matricule,Code
--Q6 select AVG(Note) as "Moyenne de notes" from Examen where Code='002'
--Q7 select COUNT(Code) as "Nombre d'examens" from Examen where Matricule='e1'
--Q8 select COUNT(Matricule) as "Nombre d'étudiants" from Examen where Code='002'
--Q9 select AVG(Note) as "Moyenne de notes" from Examen where Matricule='e1'
--Q10 select Matricule,COUNT(Code) as "Nombre d'examens" from Examen group by Matricule
--Q11 select Matricule,AVG(Note) as "Moyenne de notes" from Examen group by Matricule
--Q12 select Matricule,AVG(Note) as "Moyenne de notes" from Examen group by Matricule
having AVG(Note)>=15
--Q13 select Code,AVG(Note) as "Moyenne de notes" from Examen group by Code
EXERCICE 2 : STAGIAIRE
L’objectif de cet exercice est d’interroger une base de données de gestion des notes en utilisant des
requêtes SELECT Mono-Table. Les éléments à traiter sont :
les sélections,
les projections,
le tri,
les groupements,
les fonctions d’agrégats et les opérateurs.
Avant de commencer
Pour cet exercice, utilisez la base de données gestion-notes-stagiaires dont le schéma relationnel est le
suivant :
[Circuits à diodes]
Page 5
Schéma Base de données Stagiaires
Travail à faire:
Écrivez les requêtes permettant d'afficher :
1. La liste des stagiaires ;
2. La liste des examens ;
3. Les numéros de tous les stagiaires ;
4. Les numéros des examens munis de la date de réalisation ;
5. La liste des stagiaires triée par nom dans un ordre décroissant ;
6. La liste des examens réalisés dans les salles 'A2' ou 'A3';
7. La liste des examens pratiques ;
8. La liste précédente triée par date de passation de l'examen ;
9. La liste des examens triée par salle dans un ordre croissant et par date dans un ordre décroissant ;
10. Les numéros et les notes des examens passé par le stagiaire 'S01';
11. Les numéros et les notes des examens passé par le stagiaire 'S01' et dont la note est supérieure ou
égale à 15 ;
12. Les stagiaires dont le nom contient la lettre 'u' ;
13. Les prénoms des stagiaires dont le prénom se termine par la lettre 'm' ;
14. Les prénoms des stagiaires dont le prénom se termine par la lettre 'm' ou 'd' ;
[Circuits à diodes]
Page 6
15. Les noms et prénoms des stagiaires dont le nom se termine par la lettre 'i' et le prénom par 'm' ;
16. Les noms des stagiaires dont la deuxième lettre est 'a' ;
17. [3 méthodes] Les noms des stagiaires dont la deuxième lettre n'est pas 'a' ;
18. La liste des examens pratiques réalisés dans une salle commençant par la lettre 'A';
19. Toutes les salles dont on a réalisé au moins un examen ;
20. La liste précédentes sans doublons ;
21. Pour chaque examen, la première et la dernière note ;
22. Pour l'examen 'E05', la première et la dernière note ;
23. Pour chaque examens, l'écart entre la première et la dernière note ;
24. Le nombre d'examens pratiques ;
25. La date du premier examen effectué ;
26. Le nombre de stagiaires dont le nom contient 'b' ou 's' ;
27. Pour chaque stagiaires la meilleure note dans tous les examens ;
28. Pour chaque date enregistrée dans la base de données le nombre d'examens ;
29. Pour chaque salle le nombre d'examens réalisés ;
30. Le nombre d'examens réalisés dans la salle 'B5';
31. Toutes les salles dont on a effectué au moins deux examens ;
32. Toutes les salles dont on a effectué exactement 3 examens ;
33. Le nombre d'examens réalisés dans les salle commençant par la lettre 'A' ;
34. Pour chaque salle commençant par la lettre 'A', le nombre d'examens ;
35. Les salles qui commence par 'A' et dont on a effectué deux examens.
Correction exercice 2
//Q1 select * from Stagiaires;
//Q5 select * from Stagiaires order by NumS DESC;
//Q6 select * from Examens where Salle='A2' or Salle='A3';
select * from Examens where Salle in ('A2', 'A3');
//Q7 select * from Examens where TypeE='P';
//Q8 select * from Examens where TypeE='P' order by Date;
//Q9 select * from Examens order by Salle, Date DESC;
//Q11 select NumS, NumE, Note from PasserExamen where NumS='S01' and Note
>= 15;
//Q12 select * from Stagiaires where NomS like '%u%';
//Q13 select distinct PrenomS from Stagiaires where PrenomS like '%m';
[Circuits à diodes]
Page 7
//Q14 select PrenomS from Stagiaires where PrenomS like '%m' or PrenomS
like '%d' ;
select PrenomS from Stagiaires where PrenomS like '%[dm]' ;
//Q16 select NomS from Stagiaires where NomS like '_a%';
//Q17 select NomS from Stagiaires where NomS not like '_a%';
select NomS from Stagiaires where NomS like '_[b-zB-Z ]%';
select NomS from Stagiaires where NomS like '_[^a]%';
//Q19
select Salle from Examens
//Q20 select distinct Salle from Examens
//Q21 select NumE, max(Note) 'Premiere Note', min(Note) 'Derniere Note'
from PasserExamen group by NumE;
//Q22 select NumE, max(Note) as 'Premiere Note', min(Note) 'Derniere Note'
from PasserExamen where NumE='E05' group by NumE ;
//Q23 select
by NumE;
NumE, max(Note) -
//Q24 select count(*)
TypeE='P';
min(Note) 'Ecart' from PasserExamen group
[Nombre Examen Pratique] from Examens where
//Q25 select min(Date) as 'Date 1er Examen' from Examens;
//Q26 select count(*) 'Nb stagiaire..' from Stagiaires where NomS like
'%[bs]%';
//Q27 select NumS, max(Note) from PasserExamen group by NumS;
//Q28 select Date, count(*) as 'Nb Examen' from Examens group by Date;
//Q29 select Salle, count(*) as 'Nb Examen' from Examens group by Salle;
//Q30 select count(*) as 'Nb Examen' from Examens where Salle='B5';
//Q31 select Salle, count(*) as 'Nb Examen' from Examens group by Salle
having count(*) > 1;
//Q32 select Salle, count(*) as 'Nb Examen' from Examens group by Salle
having count(*) = 3;
//Q33 select count(*) as 'Nb Examen' from Examens where Salle like 'A%';
//Q34 select Salle, count(*) as 'Nb Examen' from Examens where Salle like
'A%' group by Salle;
[Circuits à diodes]
Page 8
//Q35 select Salle, count(*) as 'Nb Examen' from Examens where Salle like
'A%' group by Salle having count(*) = 2 ;
EXERCICE 3 : VUE SQL
Objectif: Créer et utiliser des Vues SQL.
Soit le modèle relationnel suivant :
Modèle Relationnel : ELEVES
Travail à faire:
1. Créer les tables en précisant les clés primaires et étrangères.
2. Créer les contraintes qui imposent que l’âge de l’élève soit entre 17 et 23 et que la note du contrôle soit entre 0
et 20.
3. Insérer des lignes dans les tables.
4. Afficher le nombre de contrôles passés par élève en mathématiques.
5. Créer une vue V_Moyenne_Matière qui permet d’afficher la moyenne de chaque élève (Id,Nom,Prénom) Par
matière (NomMat, Coef).
6. Créer une vue V_Moyenne_Générale qui permet d’afficher la moyenne générale de chaque élève
(Id,Nom,Prénom).
Correction EXERCICE 3
//Q1 create database ELEVES Go
use ELEVES
create table Eleve(Id_Eleve int primary key,Nom varchar(30),Prenom
varchar(30),Age int)
create table Matiere(Code_Matiere varchar(30) primary key,Nom_Matiere
varchar(30),Coef_Matiere int)
create table Controle(Id_Eleve int foreign key references
Eleve(Id_Eleve),Code_Matiere varchar(30) foreign key references
[Circuits à diodes]
Page 9
Matiere(Code_Matiere),Date_Controle Date,Note float,primary key
(Id_Eleve,Code_Matiere,Date_Controle))
drop table Controle
//Q2 alter table Eleve add constraint age_eleve check(Age between 17 and
23)
alter table Controle add constraint note_eleve check(Note between 0 and 20)
//Q3 insert into Eleve values(1,'NOM1','PRENOM1',18)
insert into Eleve values(2,'NOM2','PRENOM2',18)
insert into Eleve values(3,'NOM3','PRENOM3',20)
insert into Matiere values('M1','MATIERE1',2)
insert into Matiere values('M2','MATIERE2',3)
insert into Matiere values('M3','MATIERE3',4)
insert into Controle values(1,'M1','1/2/2016',15)
insert into Controle values(1,'M2','1/2/2016',14)
insert into Controle values(1,'M3','1/2/2016',9)
insert into Controle values(1,'M1','1/5/2016',13)
insert into Controle values(2,'M1','1/5/2016',13)
//Q4 select c.Id_Eleve,COUNT(m.Code_Matiere) as "Nombre de controles"
from Controle c inner join Matiere m on c.Code_Matiere=m.Code_Matiere
where m.Nom_Matiere='MATH' group by Id_Eleve
//Q5 create view V_Moyenne_Matiere as
select
e.Id_Eleve,e.Nom,e.Prenom,m.Nom_Matiere,m.Coef_Matiere,SUM(c.Note)/COUNT(c.
Note) as "Moyenne_Matiere" from Controle c inner join Eleve e on
c.Id_Eleve=e.Id_Eleve inner join Matiere m on c.Code_Matiere=m.Code_Matiere
group by Id_Eleve,e.Nom,e.Prenom,m.Nom_Matiere,m.Coef_Matiere
select * from V_Moyenne_Matiere
//Q6 create view V_Moyenne_Genrale as select
Id_Eleve,Nom,Prenom,SUM(Coef_Matiere*Moyenne_Matiere)/(select
[Circuits à diodes]
Page 10
SUM(Coef_Matiere) from Matiere) as "Moyenne Générale" from
V_Moyenne_Matiere group by Id_Eleve,Nom,Prenom
select * from V_Moyenne_Genrale
EXERCICE 4 :
Objectif: Créer et utiliser des Vues SQL.
Soit le schéma relationnel :
Modèle Relationnel : EMPLOYES
1. Créer la base de données EMPLOYES.
2. Créez une vue V_EMP contenant : le matricule, le nom, le numéro de département, la somme de la commission
et du salaire nommé GAINS, le lieu du département.
3. Sélectionnez les lignes de V_EMP dont le salaire total est supérieur à 10.000
4. Essayez de mettre à jour le nom de l'employé de matricule 1 à travers la vue V_EMP.
5. Créez une vue V_EMP10 qui ne contienne que les employés du département 10 de la table EMP (n'utilisez pas
l'option CHECK pour cette création). Insérez dans cette vue un employé qui appartient au département 20.
Essayez ensuite de retrouver cet employé au moyen de la vue V_EMP10 puis au moyen de la table EMP.
6. Détruisez cette vue VEMP10 et recréez-la avec l'option CHECK.
7. Essayez d'insérer un employé pour le département 30. Que se passe-t-il ? Essayez de modifier le département
d'un employé visualisé à l'aide de cette vue.
8. Afficher la liste des matricules, noms et salaires des employés avec le pourcentage par rapport au total des
salaires de leur département (utilisez une vue qui fournira le total des salaires).
Correction EXERCICE 4
//Q1 create database EMPLOYES
[Circuits à diodes]
Page 11
Go use EMPLOYES
create table EMP(matricule varchar(30) primary key,Nom varchar(30),Prenom
varchar(30),Salaire float,Commission float, Num_Dept int foreign key
references DEPT(Num_Dept))
create table DEPT(Num_Dept int primary key,Nom_Dept varchar(30),Ville_Dept
varchar(30))
//Q2 create view V_EMP as select
e.matricule,e.Nom,e.Prenom,e.Num_Dept,e.Salaire+ISNULL(e.Commission,0) as
"GAINS",d.Ville_Dept
from EMP e inner join DEPT d on e.Num_Dept=d.Num_Dept
select * from V_EMP
//Q3 select * from V_EMP where GAINS>10000
//Q4 update V_EMP set Nom='NOM11' where matricule=1
//Q5 create view V_EMP10 as select * from EMP where Num_Dept=10
insert into V_EMP10 values(6,'NOM6','PRENOM6',6000,400,20)
select * from V_EMP10 select * from EMP
//Q6 drop view V_EMP10
create view V_EMP10 as
select * from EMP where Num_Dept=1 with check option
//Q7 select * from V_EMP10
insert into V_EMP10 values(7,'NOM7','PRENOM7',7000,400,30)
update V_EMP10 set Num_Dept=20 where matricule=2
//Q8 create view V_SOMME_SALAIRES_DEPT as select Num_Dept,SUM(Salaire) as
"Somme_Salaires"
from EMP group by Num_Dept
select * from V_SOMME_SALAIRES_DEPT
select
e.matricule,e.Nom,e.Prenom,e.Salaire,cast((e.Salaire*100)/s.Somme_Salaires
as varchar) +'%' as "Pourcentage"
from EMP e inner join V_SOMME_SALAIRES_DEPT s on e.Num_Dept=s.Num_Dept
[Circuits à diodes]
Page 12
