Informatique TD SQL
TD SQL
Corrige
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.
Correction
SELECT *
FROM ETUDIANT
ORDER BY nom DESC ;
Question n° 3
Libellé et coefficient (exprimé en pourcentage) de chaque matière.
Correction
SELECT libelle, coef*100
FROM MATIERE ;
Question n° 4
Nom et prénom de chaque étudiant.
Correction
SELECT nom, prenom
FROM ETUDIANT ;
DEUG MIAS 1/
Informatique TD SQL
Question n° 5
Nom et prénom des étudiants domiciliés à Lyon.
Correction
SELECT nom, prenom
FROM ETUDIANT
WHERE ville='Lyon' ;
Question n° 6
Liste des notes supérieures ou égales à 10.
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.
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".
Correction
SELECT nom, prenom, ville
FROM ETUDIANT
WHERE ville LIKE '%ll%' ;
Question n° 9
Prénoms des étudiants de nom Dupont, Durand ou Martin.
Correction
SELECT prenom
FROM ETUDIANT
WHERE nom IN ('Dupont', 'Durand', 'Martin') ;
DEUG MIAS 2/
Informatique TD SQL
Question n° 10
Somme des coefficients de toutes les matières.
Correction
SELECT SUM(coef)
FROM MATIERE ;
Question n° 11
Nombre total d'épreuves.
Correction
SELECT COUNT(*)
FROM EPREUVE ;
Question n° 12
Nombre de notes indéterminées (NULL).
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.
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
DEUG MIAS 3/
Informatique TD SQL
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, 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.
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).
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.
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.
Correction
SELECT libelle, AVG(note)
FROM MATIERE AS m, EPREUVE AS e, NOTATION AS n
DEUG MIAS 4/
Informatique TD SQL
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.
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(*)<6 ;
Requête imbriquée (la sous requête renvoie au plus 1 tuple : utilisez =, <, >, <>)
SELECT nom
FROM ETUDIANT
WHERE ETUDIANT.numetu =
(SELECT NOTATION.numetu FROM NOTATION WHERE NOTATION.note =12);
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 ETUDIANT.numetu IN
(SELECT NOTATION.numetu FROM NOTATION WHERE NOTATION.note =12);
Noms des étudiants n’ayant pas obtenu la note 12
SELECT nom
FROM ETUDIANT
WHERE ETUDIANT.numetu <> ALL
(SELECT NOTATION.numetu FROM NOTATION WHERE NOTATION.note =12);
Noms des étudiants dont la note n’est pas renseignée
SELECT nom
FROM ETUDIANT
WHERE ETUDIANT.numetu = ANY
(SELECT NOTATION.numetu FROM NOTATION WHERE NOTATION.note IS NULL);
Notez que les clauses = ANY et <> ANY correspondent respectivement à IN et à NOT IN.
DEUG MIAS 5/
