Université de 8 mai 45 Guelma Département : Informatique L2 Module : Bases de données
Corrigé Série TD N°02
Exercice 1
Requêtes SQL
1
SELECT Count( SELECT Num_C
FROM Animal A
GROUP BY Num_C
HAVING Count(Num_C) = SELECT Capacite
FROM Cage C
WHERE A.Num_C = C.Num_C )
2 3 4
SELECT Espece SELECT Nom_A SELECT Nom_A, Count(Nom_A) as Nbr
FROM Animal FROM Entretenir FROM Attraper
GROUP BY Espece GROUP BY Nom_A GROUP BY Nom_A
HAVING Count(Espece)>=3 HAVING Count(Code)>=1 ORDER BY Nbr
ORDER BY Espece DESC
LIMIT 5
5
SELECT Nom, Prenom
FROM Entretenir E, Gardien G
WHERE ([Link] = [Link]) and Nom_A IN SELECT Nom_A
FROM Entretenir E1, Gardien G1
WHERE ([Link] = [Link]) and ([Link] = « Mohamed »)
6 7
SELECT Sum(Nbr) /count(Num_C) SELECT Espese, Count(Nom_A)
FROM SELECT Num_C, Count(Code) AS Nbr FROM Animal
FROM Entretenir GROUP BY Espese
GROUP BY Num_C
Dr. Brahim FAROU - 2019/2020-
Université de 8 mai 45 Guelma Département : Informatique L2 Module : Bases de données
Exercice 2
ETUDIANT (Num_E, Nom_E, Prenom_E, wilaya_E, Date_Naiss, Niveau, spécialité)
MODULE (Code, libellé, crédit, Coefficient)
ENSEIGNANT (Num_En, Nom_En, Prenom_En, wilaya_En, Salle, Grade)
ETUDIER (Num_E, Num_En, Code, Note_TD,Note_TP,Note_Examen)
A) Créer en SQL (LDD) les tables ETUDIANT, MODULE et ETUDIER
Create table Etudiant
(
Num_E integer PRIMARY KEY,
Nom_E chaine[30] NOT NULL,
Prenom_E chaine[30] NOT NULL,
wilaya_E chaine[30] NOT NULL,
Date_Naiss date,
Niveau integer NOT NULL CHECK IN [L1,L2,L3,M1,M2],
Spécialité chaine[30] NOT NULL
)
Create table Module
(
Code integer PRIMARY KEY,
Libellé chaine[50] NOT NULL,
Crédit integer NOT NULL,
Coefficient integer NOT NULL
)
Create table ETUDIER
(
Num_E integer REFERENCES Etudiant,
Num_En integer REFERENCES Enseignant,
Code integer REFERENCES module,
Note_TD integer CHECK IN [0..20] DEFAULT 0,
Note_TP integer CHECK IN [0..20],
Note_Examen integer CHECK IN [0..20]
PRIMARY KEY (Num_E, Num_En, Code)
)
B) Exprimer les requêtes suivantes en SQL
1) Afficher la liste des Étudiants en deuxième année licence informatique par ordre alphabétique
Select *
From Etudiant E
Where (niveau =L2) and (spécialité= informatique)
Order by Nom_E, Prenom_E
2) Quels sont les étudiants qui ont eu une note d’examen supérieure à 13 dans le module BDD
Select Nom_E, Prenom_E
From Etudiant E, Etudier ET
Where (E.Num_E=ET.Num_E) and (Note_Examen >= 13) and (Code=BDD)
3) Afficher la liste des étudiants (nom, prénom) par niveau et spécialité
Select Nom_E, Prenom_E
From Etudiant
Dr. Brahim FAROU - 2019/2020-
Université de 8 mai 45 Guelma Département : Informatique L2 Module : Bases de données
Order by Niveau, spécialité
4) Afficher la liste des étudiants qui habitent dans la même wilaya que l’enseignant FAROU
Select Nom_E, Prenom_E
From Etudiant
Where wilaya_E IN(Select wilaya_En
From Enseignant
Where Nom_En = ‘FAROU’)
5) Quels sont les modules enseignés par Mr. HALLACI Samir
Select code, libellee
From Module M, Enseignqant En, Etudier E
Where ([Link]=[Link]) and (En.Num_En=E.Num_En)
and (En.Nom_En= ‘HALLACI’) and ([Link]=’Samir’)
6) Quels sont les enseignants qui enseignent les modules enseignés par FAROU
Select Nom_En
From Enseigant En, Etudier E
Where (En.num_En= E.num_En) and
code IN(select code
From etudier E, enseignant En
Where (E.Num_En = En.num_En) and (E.Nom_En=’FAROU’))
7) Quels sont les enseignants de grade MCB qui enseignent les étudiants de première année
Select Nom_E
From Enseignant En, Etudier E, etudiant Et
Where (En.num_En = E.Num_En) and (Et.Num_E = E.Num_E)
and ([Link]= ‘MCB’) and (niveau = L1)
8) Combien d’étudiants ont eu une note strictement supérieure à 13 dans le module BDD
Select count(*)
From Etudier
Where (code=’BDD’) and (Note_Examen>=13)
9) Calculer la moyenne en BDD des étudiants de deuxième année licence informatique
Select nom_E, (Note_TD+Note_TP+Note_Examen)/3 as moyenne
From Etudier E, Etudiant Et
Where (E.num_E = Et.Num_E) and (Niveau = L2) and (spécialité=’informatique’)
10) Quels sont les étudiants qui ont eu la meilleure note d’examen de BDD
Select Nom_E
From Etudiant Et, Etudier E
Where Et.Num_E= E.Num_E and
Note_Examen = (select MAX(Note_Examen)
From Etiduer
Where code=BDD)
Dr. Brahim FAROU - 2019/2020-