TD SQL serie 2 correction.doc

TD SQL
Corrige Haut du formulaire
Bas du formulaire
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 de tous les étudiants.
Correction
SELECT *
FROM ETUDIANT ;
Question n° 2
Liste de tous les étudiants, classée par ordre alphabétique inverse.
Haut du formulaire
Bas du formulaire
Haut du formulaire
Bas du formulaire
Correction
SELECT *
FROM ETUDIANT
ORDER BY nom DESC ;
Question n° 3
Libellé et coefficient (exprimé en pourcentage) de chaque matière.
Haut du formulaire
Bas du formulaire
Haut du formulaire
Bas du formulaire
Correction
SELECT libelle, coef*100
FROM MATIERE ;
Question n° 4
Nom et prénom de chaque étudiant.
Haut du formulaire
Bas du formulaire
Correction
SELECT nom, prenom
FROM ETUDIANT ;
Question n° 5
Nom et prénom des étudiants domiciliés à Lyon.
Haut du formulaire
Bas du formulaire
Correction
SELECT nom, prenom
FROM ETUDIANT
WHERE ville='Lyon' ;
Question n° 6
Liste des notes supérieures ou égales à 10.
Haut du formulaire
Bas du formulaire
Correction
SELECT note
FROM NOTATION
WHERE note>=10 ;
Question n° 7
Liste des épreuves dont la date se situe entre le 1er janvier et le 30 juin
2004.
Haut du formulaire
Bas du formulaire
Correction
SELECT *
FROM EPREUVE
WHERE datepreuve BETWEEN '2004-01-01' AND '2004-06-30' ;
Question n° 8
Nom, prénom et ville des étudiants dont la ville contient la chaîne "ll".
Haut du formulaire
Bas du formulaire
Correction
SELECT nom, prenom, ville
FROM ETUDIANT
WHERE ville LIKE '%ll%' ;
Question n° 9
Prénoms des étudiants de nom Dupont, Durand ou Martin.
Haut du formulaire
Bas du formulaire
Correction
SELECT prenom
FROM ETUDIANT
WHERE nom IN ('Dupont', 'Durand', 'Martin') ;
Question n° 10
Somme des coefficients de toutes les matières.
Haut du formulaire
Bas du formulaire
Correction
SELECT SUM(coef)
FROM MATIERE ;
Question n° 11
Nombre total d'épreuves.
Haut du formulaire
Bas du formulaire
Correction
SELECT COUNT(*)
FROM EPREUVE ;
Question n° 12
Nombre de notes indéterminées (NULL).
Haut du formulaire
Bas du formulaire
Correction
SELECT Count(NOTATION.numetu) AS [Nombre de notes indéterminées]
FROM NOTATION
HAVING (((NOTATION.Note) Is Null));
Question n° 13
Liste des épreuves (numéro, date et lieu) incluant le libellé de la
matière.
Haut du formulaire
Bas du formulaire
Correction
SELECT numepreuve, datepreuve, lieu, libelle
FROM EPREUVE, MATIERE
WHERE EPREUVE.codemat=MATIERE.codemat ;
Question n° 14
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, NOTATION
WHERE ETUDIANT.numetu=NOTATION.numetu ;
Question n° 15
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.
Haut du formulaire
Bas du formulaire
Correction
SELECT nom, prenom, note, libelle
FROM ETUDIANT, NOTATION, EPREUVE, MATIERE
WHERE ETUDIANT.numetu=NOTATION.numetu
AND NOTATION.numepreuve=EPREUVE.numepreuve
AND EPREUVE.codemat=MATIERE.codemat
Question n° 16
Nom et prénom des étudiants qui ont obtenu au moins une note égale à 20.
Haut du formulaire
Bas du formulaire
SELECT DISTINCT nom, prenom
FROM ETUDIANT, NOTATION
WHERE ETUDIANT.numetu=NOTATION.numetu
AND note=20 ;
Question n° 17
Moyennes des notes de chaque étudiant (indiquer le nom et le prénom).
Haut du formulaire
Bas du formulaire
Correction
SELECT nom, prenom, AVG(note)
FROM ETUDIANT, NOTATION
WHERE ETUDIANT.numetu=NOTATION.numetu
GROUP BY nom, prenom ;
Question n° 18
Moyennes des notes de chaque étudiant (indiquer le nom et le prénom),
classées de la meilleure à la moins bonne.
Haut du formulaire
Bas du formulaire
Correction
SELECT nom, prenom, AVG(note) AS moyenne
FROM ETUDIANT, NOTATION
WHERE ETUDIANT.numetu=NOTATION.numetu
GROUP BY nom, prenom
ORDER BY AVG(NOTATION.note)DESC ;
Question n° 19
Moyennes des notes pour les matières (indiquer le libellé) comportant plus
d'une épreuve.
Haut du formulaire
Bas du formulaire
Correction
SELECT libelle, AVG(note)
FROM MATIERE AS m, EPREUVE AS e, NOTATION AS n
WHERE m.codemat=e.codemat
AND e.numepreuve=n.numepreuve
GROUP BY libelle
HAVING COUNT(DISTINCT e.numepreuve)>1 ;
Question n° 20
Moyennes des notes obtenues aux épreuves (indiquer le numéro d'épreuve) où
moins de 6 étudiants ont été notés.
Haut du formulaire
Bas du formulaire
Correction
SELECT e.numepreuve, AVG(note)
FROM EPREUVE AS e, NOTATION AS n
WHERE e.numepreuve=n.numepreuve
AND note IS NOT NULL
GROUP BY e.numepreuve
HAVING COUNT(*)