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.
En algèbre relationnel et en SQL, afficher la liste des tous les étudiants dont le nom commence par A.
R1 = Restriction(ELEVE, nom COMME 'A%')
R = Projection(R1, nom, prenom)
SELECT nom, prenom
FROM ELEVE
WHERE nom LIKE 'A%';
En algèbre relationnel et en SQL, afficher les noms et prénoms des enseignants qui n'enseignent à aucune classe.
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;
En SQL, affichez le nombre d'étudiants enregistrés en "Terminale S 2".
SELECT classe, COUNT(login)
FROM ELEVE
WHERE classe = 'Terminale S 2' ;
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.
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;
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.
On fera l'hypothèse que tous les étudiants d'une classe ont le même nombre de notes (pas d'absence aux examens).
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;