L2 TD SQL
TD SQL
Corrigé
Exercice n°1
Schéma relationnel de la base de données :
ETUDAINT (numetu, nom, prenom, datenaiss, rue, cp, ville)
MATIERE (codemat, libelle, coef)
EPREUVE (numepreuve, datepreuve, lieu, codemat#)
NOTATION (numetu#, numepreuve#, note)
Question n° 1
Liste des noms les étudiants, classée par ordre alphabétique inverse.
Correction
SELECT nom
FROM ETUDIANT
ORDER BY nom DESC;
Question n° 2
Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ?
Correction
SELECT MIN(Note), MAX(Note) FROM notation;
Question n° 3
Quel est le nombre total des étudiants ayant passé plus que deux épreuves ?
ABIDI Najah 1
L2 TD SQL
Correction
SELECT COUNT(numetu), COUNT(numepreuve)
fROM ETUDIANT join NOTAION USING(NUMETU)
GROUP BY numetu
Having COUNT(numepreuve)>2;
Question n° 4
Nom et prénom des étudiants domiciliés à Lyon et qui n’ont passé aucune épreuve ?
Correction
SELECT nom, prenom
FROM ETUDIANT
WHERE ville='Lyon'
And numetu not in (select numetu from NOTATION);
Question n° 5
Liste des notes supérieures ou égales à 10 de la matière « bases de données ».
Correction
SELECT note
FROM NOTATION join EPRUVE using (numepreuve) join MATIERE using(codmat)
WHERE note>=10 and libelle=’bases de données’;
Question n° 6
Liste des épreuves de l’année 2016.
ABIDI Najah 2
L2 TD SQL
Correction
SELECT numepreuve
FROM EPREUVE
WHERE datepreuve BETWEEN '01/01/2016' AND '31/12/2016';
Question n° 7
Nom, prénom et ville des étudiants dont la ville contient la chaîne "ll".
Correction
SELECT nom, prenom, ville
FROM ETUDIANT
WHERE ville LIKE '%ll%' ;
Question n° 8
Prénoms des étudiants de nom Dupont, Durand ou Martin et habitent la même ville que
« Max »
Correction
SELECT prenom
FROM ETUDIANT
WHERE nom IN ('Dupont', 'Durand', 'Martin')
And ville =(select VILLE from ETUDIANT WHERE NOM=’MAX’);
Question n° 9
Somme des coefficients de toutes les matières.
Correction
SELECT SUM(coef)
FROM MATIERE ;
ABIDI Najah 3
L2 TD SQL
Question n° 10
Nombre total d'épreuves par matière
Correction
SELECT COUNT(numepruve)
FROM EPREUVE
GROUP BY codmat;
Question n° 11
Nombre de notes indéterminées (NULL) pour chaque matière
Correction
SELECT Count(epreuve) AS Nombre de notes indéterminées
FROM NOTATION join EPREUVE USING(numepreuve)
Group by codmat
HAVING Note Is Null;
Question n° 12
Liste des épreuves (numéro, date et lieu) incluant le libellé de la matière.
Correction
SELECT numepreuve, datepreuve, lieu, libelle
FROM EPREUVE join MATIERE USING(codemat);
Question n° 13
Liste des notes en précisant pour chacune le nom et le prénom de l'étudiant qui l'a obtenue.
Correction
SELECT nom, prenom, note
FROM ETUDIANT join NOTATION USING(numetu);
ABIDI Najah 4
L2 TD SQL
Question n° 14
Liste des notes en précisant pour chacune le nom et le prénom de l'étudiant qui l'a obtenue et
le libellé de la matière concernée.
Correction
SELECT nom, prenom, note, libelle
FROM ETUDIANT join NOTATION USING(numetu) join EPREUVE USING(numepreuve)
join MATIERE using(codemat)
Question n° 15
Moyennes des notes de chaque étudiant (indiquer le nom et le prénom).
Correction
SELECT nom, prenom, AVG(note)
FROM ETUDIANT join NOTATION USING(numetu)
GROUP BY nom, prenom ;
Question n° 16
Moyennes des notes de chaque étudiant (indiquer le nom et le prénom), classées de la
meilleure à la moins bonne.
Correction
SELECT nom, prenom, AVG(note) AS moyenne
FROM ETUDIANT join NOTATION USING(numetu)
GROUP BY nom, prenom
ORDER BY AVG(note)DESC ;
Question n° 17
Moyennes des notes pour les matières (indiquer le libellé) comportant plus de 2 épreuves.
ABIDI Najah 5
L2 TD SQL
Correction
SELECT libelle, AVG(note)
FROM MATIERE JOIN EPREUVE USING(codemat) JOIN NOTATION USING(numepreuve)
GROUP BY libelle
HAVING COUNT(DISTINCT numepreuve)>2 ;
Question n° 18
Moyennes des notes obtenues aux épreuves (indiquer le numéro d'épreuve) où moins de 6
étudiants ont été notés.
Correction
SELECT enumepreuve, AVG(note)
FROM EPREUVE join NOTATION USING(numepreuve)
GROUP BY numepreuve
HAVING COUNT(numetu)<6 ;
Question n° 19
Noms des étudiants ayant obtenu la note 12
Correction
SELECT nom
FROM ETUDIANT join NOTATION USING(numetu)
where note=12;
OU
Requête imbriquée (la sous requête renvoie au plus 1 tuple : utilisez =, <, >, <>)
SELECT nom
FROM ETUDIANT
WHERE numetu =
ABIDI Najah 6
L2 TD SQL
(SELECT numetu FROM NOTATION WHERE note =12);
OU
Requête imbriquée (la sous requête renvoie 1 ou plusieurs tuples:utilisez [NOT] IN,ALL,
ANY )
Noms des étudiants qui ont obtenu la note 12
SELECT nom
FROM ETUDIANT
WHERE numetu IN
(SELECT numetu FROM NOTATION WHERE note =12);
Question n° 20
Noms des étudiants n’ayant pas obtenu la note 12
Correction
SELECT nom
FROM ETUDIANT join NOTATION USING(numetu)
where note<>12;
OU
SELECT nom
FROM ETUDIANT
WHERE numetu <> ALL
(SELECT numetu FROM NOTATION WHERE note =12);
OU
SELECT nom
FROM ETUDIANT
WHERE numetu Not IN
(SELECT numetu FROM NOTATION WHERE note =12);
ABIDI Najah 7
L2 TD SQL
Noms des étudiants dont la note n’est pas renseignée
SELECT nom
FROM ETUDIANT
WHERE numetu = ANY
(SELECT numetu FROM NOTATION WHERE note IS NULL);
Notez que les clauses = ANY et <> ANY correspondent respectivement à IN et à NOT IN.
Question n° 21
Afficher la moyenne des notes la plus élevée (on veut afficher le numéro et le nom de
l’étudiant)
Correction
Select numetud,nom, avg(note)
From notation join etudiant using(numetu)
Group by numetud, nom
Having avg(note) >=all (select avg(note) from notation
group by numetud);
Question n° 22
Afficher les noms des étudiants qui ont passé toutes les épreuves
Correction
SELECT numetu, nom, count(numepruve)
FROM ETUDIANT join NOTATION USING(numetu)
GROUP BY numetu, nom
Having count(numepreuve)= (SELECT count(numepreuve) FROM EPREUVE);
ABIDI Najah 8
L2 TD SQL
Exercice n°2
Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France
97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne :
EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)
COUREUR(NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)
PAYS(CodePays, NomPays)
TYPE_ETAPE(CodeType, LibelléType)
ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType*)
PARTICIPER(NuméroCoureur*, NuméroEtape*, TempsRéalisé)
ATTRIBUER_BONIFICATION(NuméroEtape*, km, Rang, NbSecondes, NuméroCoureur*)
Correction de l'exercice n°2
1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des coureurs) ?
SELECT NumeeroCoureur, NomCoureur, NomPays
FROM EQUIPE join COUREUR using (CodeEquipe) join PAYS using(CodePays)
WHERE NomEquipe=’FESTINA’ ;
2 - Quel est le nombre de kilomètres total du Tour de France 97 ?
SELECT SUM(Nbkm) FROM ETAPE;
ABIDI Najah 9
L2 TD SQL
3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?
SELECT SUM(Nbkm)
FROM ETAPE join TYPE_ETAPE using (CodeType)
WHERE LibelleeType=’HAUTE MONTAGNE’ ;
4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?
SELECT NomCoureur FROM COUREUR
WHERE NumeeroCoureur NOT IN (SELECT NumeeroCoureur FROM
ATTRIBUER_BONIFICATION) ;
5 - Quels sont les noms des coureurs qui ont participé́ à toutes les étapes ?
SELECT NomCoureur
FROM PARTICIPER join COUREUR using(NumeeroCoureur )
GROUP BY NumeroCoureur, NomCoureur
HAVING COUNT(NumeroEtape) = (SELECT COUNT(NumeroEtape)
FROM ETAPE) ;
6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des
coureurs) à l'issue des 13 premières étapes sans considérer les bonifications ?
SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsReealisee) AS Total FROM
PARTICIPER join COUREUR using( NumeroCoureur)
WHERE NumeeroEtape<=13
GROUP BY NumeroCoureur, NomCoureur, CodeEquipe, CodePays
ORDER BY Total;
ABIDI Najah 10
L2 TD SQL
7- quel est le(s) coureur(s)(code, nom )ayant le total de bonification le moins élevé?
Select NumeroCoureur, NomCoureur, sum(NbSecondes)
From ATTRIBUER_BONIFICATION join COUREUR using(NumeroCoureur)
Group by NumeroCoureur, NomCoureur
Having sum(NbSecondes) <= all (Select sum(NbSecondes)
From ATTRIBUER_BONIFICATION
Group by NumeroCoureur);
ABIDI Najah 11
