TD SQL serie 2 correction.doc

Liste de tous les étudiants, classée par ordre alphabétique inverse. Haut du ... Nom, prénom et ville des étudiants dont la ville contient la chaîne "ll". Haut du ...


un extrait du document



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(*)