Correction du TP 2 de Bases de Données

Si vous trouver des erreus dans cette correction ou que vous bloquez sur une requête, vous pouvez m'écrire à loris.marchal@ens-lyon.fr

(petite mise en garde: les chaînes de caractères des champs Etudiants.ville et Salles.adresse sont mal formées, éviter donc de les afficher, et s'il vous faut le faire, afficher les en fin de ligne (sinon vous risquer de voir disparaître des colonnes). Ceci est dû à une mauvaise création de la base de données.)

partie 2: Sélection


Question 1.
SELECT *
FROM Etudiants
WHERE ville = 'grenoble';

Question 2.
SELECT *
FROM Etudiants
WHERE ville = 'paris' AND prenom = 'louis';

Question 3.
SELECT *
FROM Horaires
WHERE jour = 'lundi' AND heure < 12;


Question 4.
SELECT *
FROM Etudiants
WHERE ville = 'paris' AND prenom = 'louis';


partie 3: Projection


Question 1.
SELECT nom_cours
FROM Cours;


Question 2.
SELECT nom,prenom
FROM Etudiants;


Question 3.
SELECT nom_cours
FROM Horaires
WHERE jour = 'mardi';


partie 4: Produit Cartésien, Jointure


Question 1.
SELECT Etudiants.num_etudiant, nom, prenom, Notes.num_etudiant, nom_cours, valeur
FROM Etudiants,Notes;


Question .
SELECT nom, nom_cours
FROM Etudiants, Notes
WHERE Etudiants.num_etudiant = Notes.num_etudiant ;


Question 3.
SELECT Cours.nom_cours
FROM Cours, Salles
WHERE Cours.num_salle = Salles.num_salle AND Salles.adresse = 'Ampère nord';


Question 4.
SELECT Etudiants.prenom, Etudiants.nom
FROM Etudiants, Notes, Cours
WHERE Etudiants.num_etudiant = Notes.num_etudiant
AND Cours.nom_cours = Notes.nom_cours
AND Cours.pre_requis = 'aucun'
AND Notes.valeur IS NOT NULL;


Question 5.
SELECT Salles.num_salle, Etudiants.nom, Cours.nom_cours, Horaires.jour, Notes.valeur
FROM Salles,Etudiants,Horaires,Cours,Notes
WHERE Etudiants.num_etudiant = Notes.num_etudiant
AND Notes.nom_cours = Cours.nom_cours
AND Notes.nom_cours = Horaires.nom_cours
AND Cours.num_salle = Salles.num_salle
AND Etudiants.nom = 'DEBUSSY'
AND Horaires.jour = 'mardi'
AND Notes.valeur IS NULL;


2. Groupement


Question 1.
SELECT Notes.nom_cours, COUNT(Notes.num_etudiant)
FROM Notes
WHERE Notes.valeur IS NULL
GROUP BY Notes.nom_cours;


Question 2
première solution en protégant le AVG(..) avec des quotes.
SELECT Etudiants.nom, AVG(Notes.valeur)
FROM Notes,Etudiants
WHERE Notes.num_etudiant = Etudiants.num_etudiant
AND Notes.valeur IS NOT NULL
GROUP BY Notes.num_etudiant;
ORDER BY 'AVG(Notes.valeur)' DESC;


Question 2.
deuxième solution en utilisant un alias ("moyenne") pour AVG(..)
SELECT Etudiants.nom, AVG(Notes.valeur) AS moyenne
FROM Notes,Etudiants
WHERE Notes.num_etudiant = Etudiants.num_etudiant
AND Notes.valeur IS NOT NULL
GROUP BY Notes.num_etudiant;
ORDER BY moyenne DESC;


Question 3.
en utilisant l'opérateur HAVING : permet de rajouter une condition sur le résultat d'un groupement
SELECT Etudiants.nom, AVG(Notes.valeur)
FROM Notes,Etudiants
WHERE Notes.num_etudiant = Etudiants.num_etudiant
AND Notes.valeur IS NOT NULL
GROUP BY Notes.num_etudiant
HAVING AVG(Notes.valeur) > 16;


Question 3 bis
en utilisant l'imbrication de requêtes : on crée une première requête, ici entre les parenthèses, qui nous renvoie une table à laquelle on donne un nom (TABLE_TEMPORAIRE) puis on utilise cette table comme une table classique SELECT nom, moyenne
FROM
(SELECT Etudiants.nom, AVG(Notes.valeur) AS moyenne
FROM Notes,Etudiants
WHERE Notes.num_etudiant = Etudiants.num_etudiant
AND Notes.valeur IS NOT NULL
GROUP BY Notes.num_etudiant
) AS TABLE_TEMPORAIRE
WHERE moyenne > 16;


partie 5 : Union, Différence


Question 1. Pour faire la différence entre deux tables, il faut utiliser l'opérateur NOT IN.
SELECT nom_cours
FROM Cours
WHERE nom_cours NOT IN
(SELECT nom_cours
FROM Notes
WHERE valeur IS NULL);


Question 2. Pour faire l'union de deux tables, on utilise l'opérateur... UNION .
(SELECT nom_cours FROM cours WHERE nom_cours NOT IN
(SELECT nom_cours FROM horaires))
UNION
(SELECT nom_cours FROM cours WHERE nom_cours NOT IN
(SELECT nom_cours FROM notes WHERE valeur IS NULL));


Question 2. Deuxième solution pour l'union : utiliser un ou logique (OR) dans la clause "WHERE".
SELECT nom_cours
FROM Cours
WHERE nom_cours NOT IN
(SELECT nom_cours
FROM Horaires)
OR nom_cours NOT IN
(SELECT nom_cours
FROM Notes
WHERE valeur IS NULL);


partie 7 : Requêtes complexes


Question 1.
Il faut ici utiliser deux copies de la table Horaires. Pour les différencier, on leur donne des noms: H1 et H2.
SELECT H1.nom_cours, H1.jour, H1.heure, H2.nom_cours, H2.jour, H2.heure
FROM Horaires H1, Horaires H2
WHERE H1.jour = H2.jour
AND H1.heure = H2.heure
AND H1.nom_cours < H2.nom_cours;


Question 2.

ensemble A:
SELECT Notes.num_etudiant, Cours.pre_requis
FROM Notes,Cours
WHERE Notes.nom_cours = Cours.nom_cours
AND Cours.pre_requis <> 'aucun';

ensemble B
SELECT num_etudiant, nom_cours
FROM Notes
WHERE valeur IS NOT NULL;

réponse (ensemble C = A - B)
SELECT num_etudiant, pre_requis
FROM Notes, Cours
WHERE Notes.nom_cours = Cours.nom_cours
AND Cours.pre_requis <> 'aucun'
AND (num_etudiant,pre_requis) NOT IN
(SELECT num_etudiant, nom_cours
FROM Notes
WHERE valeur IS NOT NULL);


Question 3.

Première requête: nom des cours avec des étudiants non inscrits
SELECT DISTINCT nom_cours
FROM Notes
WHERE valeur IS NOT NULL;

réponse = nom des cours n'étant pas dans la requête précédente
SELECT nom_cours
FROM Cours
WHERE nom_cours NOT IN
(SELECT DISTINCT nom_cours
FROM Notes
WHERE valeur IS NOT NULL);