Questions scolaires
[30 min]
Soit le schéma relationnel suivant gérant le fonctionnement d'une école et les notes des élèves.
CLASSE(#intitule)
MATIERE(#intitule)
ELEVE(#login, nom, prenom, age, classe=>CLASSE)
ENSEIGNANT(#login, nom, prenom, mail, matiere=>MATIERE)
ENSEIGNE(#enseignant=> ENSEIGNANT, #classe=>CLASSE)
NOTE(#eleve=>ELEVE, #enseignant=>ENSEIGNANT, #date_examen, note)
Contrainte : un enseignant ne peut mettre une note à un élève que si celui-ci se trouve dans une classe dans laquelle il enseigne.
NB : Nous n'utiliserons pas de sous-requêtes.
Question
En algèbre relationnel et en SQL, afficher la liste des tous les étudiants dont le nom commence par A.
Solution
R1 = Restriction(ELEVE, nom COMME 'A%')
R = Projection(R1, nom, prenom)
SELECT nom, prenom
FROM ELEVE
WHERE nom LIKE 'A%';
Question
En algèbre relationnel et en SQL, afficher les noms et prénoms des enseignants qui n'enseignent à aucune classe.
Solution
R1 = JointureExterneGauche(ENSEIGNANT, ENSEIGNE, login=enseignant)
R2 = Restriction(R1, classe = NULL)
R = Projection(R2, nom, prenom)
SELECT nom, prenom
FROM ENSEIGNANT LEFT JOIN ENSEIGNE on login=enseignant
WHERE classe IS NULL;
Question
En SQL, affichez le nombre d'étudiants enregistrés en "Terminale S 2".
Solution
SELECT classe, COUNT(login)
FROM ELEVE
WHERE classe = 'Terminale S 2' ;
Question
En SQL, affichez les logins, noms, prénoms, classes et moyennes des élèves en cours de "Mathématiques", par ordre décroissant de moyenne, à condition qu'ils aient au minimum 2 notes dans cette matière.
Solution
SELECT EL.login, EL.nom, EL.prenom, EL.classe, AVG(note) AS moyenne
FROM ELEVE EL, NOTE, ENSEIGNANT EN
WHERE EL.login = eleve
AND enseignant = EN.login
AND matiere = 'Mathématiques'
GROUP BY EL.login, EL.nom, EL.prenom, EL.classe
HAVING COUNT(note) > 1
ORDER BY moyenne DESC;
Question
En SQL, à des fins de statistiques, nous souhaitons rechercher par enseignant et par classe, les classes qui n'ont pas la moyenne générale, et afficher pour celles-ci : le nom, prénom et mail de l'enseignant en question, la matière enseignée, la classe, la moyenne d'âge des étudiants avec les extrêmes (minimum et maximum), la moyenne générale de la classe avec les valeurs extrêmes, ainsi que le nombre d'étudiants présents dans cette classe ; le tout classé par ordre alphabétique de classe, puis de nom et de prénom de l'enseignant.
Indice
On fera l'hypothèse que tous les étudiants d'une classe ont le même nombre de notes (pas d'absence aux examens).
Solution
SELECT EL.classe, ENA.nom, ENA.prenom, ENA.mail, ENA.matiere, AVG(EL.age), MIN(EL.age), MAX(EL.age), COUNT(distinct EL.login), MIN(note), MAX(note), AVG(note)
FROM ELEVE EL, NOTE N, ENSEIGNE EN, ENSEIGNANT ENA
WHERE EL.login = N.eleve
AND EL.classe = EN.classe
AND EN.enseignant = ENA.login
AND N.enseignant = ENA.login
GROUP BY ENA.login, ENA.nom, ENA.prenom, ENA.mail, EL.classe, ENA.matiere
HAVING AVG(note) <= 10
ORDER BY EL.classe, ENA.nom, ENA.prenom;