Sous-requêtes avec la base etude.sqlite

Les exercices de cette page portent sur la base etude.sqlite dont une présentation est donnée ici.

Etudiant ayant eu des notes supérieures à la moyenne des notes

  1. Afficher les identifiants des étudiants ayant eu au moins une note supérieure à la moyenne des notes.
  2. Afficher maintenant les noms et prénoms de ces étudiants.

Identifiant des étudiants ayant eu au moins une note supérieure à la moyenne des notes.


SELECT distinct id_etudiant FROM  aSuivi where note >
(select avg(note) from aSuivi);

Nom et prénom des étudiants ayant eu au moins une note supérieure à la moyenne des notes.

On dispose déjà des identifiants avec la requête précédente. On peut utiliser cette requête précédente en requête interne : 

select nom, prenom from personne where id_personne in
(
SELECT distinct id_etudiant FROM  aSuivi where note >
(select avg(note) from aSuivi)
);

Personnes plus âgées que Adèle Gemlamorte

Afficher les noms et prénoms des personnes plus âgées que Adèle Gemlamorte. 


select nom, prenom from personne where anneeNaissance <
( select anneeNaissance from personne where nom = "Gemlamorte" and prenom = "Adèle"  );

Étudiant ayant eu au moins une note supérieure à 17

  1. Afficher la liste des identifiants des étudiants ayant eu au moins une note supérieure à 17.
  2. Afficher la liste de leurs noms et prénoms.

Les identifiants


select distinct A1.id_etudiant from aSuivi as A1 where 
exists (select * from aSuivi as A2 where note > 17 and A2.id_etudiant =  A1.id_etudiant)

Les noms et prénoms


select nom, prenom from personne where id_personne in
(
select distinct A1.id_etudiant from aSuivi as A1 where 
exists (select * from aSuivi as A2 where note > 17 and A2.id_etudiant =  A1.id_etudiant)
) ;

Enseignant donnant au moins deux cours

  1. Afficher la liste des enseignants encadrant au moins deux cours.
  2. Afficher leurs noms et prénoms.

Les identifiants


select distinct C1.id_enseignant from cours as C1 where 
( select count(id_cours) from cours as C2 where C1.id_enseignant = C2.id_enseignant) > 1;

Les noms et prénoms


select nom, prenom from personne where id_personne in
(
select distinct C1.id_enseignant from cours as C1 where 
(select count(id_cours) from cours as C2 where C1.id_enseignant = C2.id_enseignant) > 1
);

Liste des cours dans lesquels au moins un étudiant n'a pas la moyenne

Afficher la liste des cours (par leur intitulé) pour lesquels au moins un étudiant a une note inférieure à 10. 


select intitule from cours where id_cours in
(
select distinct A1.id_cours from aSuivi as A1 where
10 > ( select min(note) from aSuivi as A2 where A1.id_cours = A2.id_cours )
);

Liste des étudiants ayant suivi tous les cours commençant par j

Afficher la liste des étudiants ayant suivi tous les cours dont l'intitulé commence par j.

Les identifiants 


select distinct A1.id_etudiant from aSuivi as A1 where not exists
(
select C.id_cours from cours as C 
where C.intitule like 'j%' and not exists
   (
    select A2.id_cours from aSuivi as A2 
    where  C.id_cours = A2.id_cours and A2.id_etudiant = A1.id_etudiant 
   )
)
La requête la plus interne sélectionne les lignes de aSuivi correspondant à un cours d'identifiant C.id_cours (et donc commençant par j) et suivi par l'étudiant d'identifiant A1.id_etudiant.
La requête du niveau au-dessus sélectionne donc les lignes de la table cours telles que l'attribut id_cours commence par j et telles que l'étudiant d'identifiant A1.id_etudiant n'ait pas suivi ce cours.
La requête la plus externe sélectionne donc les étudiants pour lesquels il n'existe pas de cours commençant par j qu'ils ne suivent pas : ce sont donc les étudiants qui ont suivi tous les cours dont l'intitulé commence par j.

Leurs noms 


select nom, prenom from personne where id_personne in
(
select distinct A1.id_etudiant from aSuivi as A1 where not exists
	(
	select C.id_cours from cours as C 
	where C.intitule like 'j%' and not exists
	   (
		select A2.id_cours from aSuivi as A2 
		where  C.id_cours = A2.id_cours and A2.id_etudiant = A1.id_etudiant 
	   )
	)
)