subqueries

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

Acteur ayant joué dans un film avant 1990

Afficher nom et prénom des acteurs ayant joué un rôle dans un film dont l'année est antérieure à 1990.


select nom, prenom from personne where id_personne in
(
	select J.id_acteur from joue as J where J.id_film in
	( select F.id_film from film as F where annee < 1990 ) 
);

Personnes de même prénom que la personne 16

Afficher les noms des personnes ayant le même prénom que la personne d'identifiant 16.


select   P1.nom  from personne as P1 where prenom = 
( select P2.prenom from personne as P2  where P2.id_personne = 1) ;

Cette requête retourne aussi le nom de la personne d'identifiant 16.Si on veut l'éliminer des résultats, on peut écrire :


select   P1.nom  from personne as P1 where id_personne <> 16 and prenom = 
( select P2.prenom from personne as P2  where P2.id_personne = 1) ;

Acteurs de même prénom que la personne 16

Afficher les noms des acteurs ayant le même prénom que la personne d'identifiant 16. On considère ici qu'un acteur est une personne qui a tenu au moins un rôle (c'est à dire dont l'identifiant se trouve dans la table "joue").


select   P1.nom  from personne as P1 
where P1.prenom = ( select P2.prenom from personne as P2  where P2.id_personne = 1)
and P1.id_personne in (select J.id_acteur from joue as J);

La requête renvoie par exemple Brasseur et Rich qui sont des acteurs mais ne renvoie pas Berri qui n'apparaît pas dans la table "joue".

On remarquera que l'on peut utiliser "=" dans le premier critère car la sous-requête ne peut retourner qu'un seul prénom, mais on utilise in après le and, la seconde sous-requête retournant a priori plusieurs résultats.

Acteurs ayant joué dans un film avec Dany Boon

Afficher les noms et prénoms des acteurs ayant joué dans un film dans lequel Dany Boon jouait un rôle.


select   P.nom, P.prenom from personne as P where P.id_personne in
(
    select J.id_acteur from joue as J where id_film in
    (
        select JJ.id_film from joue as JJ where id_acteur =
        (select PP.id_personne from personne as PP where PP.nom = "Boon" and PP.prenom = "Dany")
    )
)

La requête la plus interne (niveau 4) récupère l'identifiant de Dany Boon. La requête du niveau 3 sélectionne les identifiants des films dans lesquels Dany Boon a joué un rôle. La requête de niveau 2 récupère les identifiants des acteurs ayant joué dans un de ces films. La requête la plus externe récupère enfin les noms et prénoms des acteurs ainsi identifiés.

Films à l'affiche de Ciné Festival

Afficher les titres des films qui ont été à l'affiche du cinéma Ciné Festival.


select F.titre from film as F where  F.id_film in
(
 select S.id_film from seance as S where S.id_cinema =
	(select C.id_cinema from cinema as C where C.nom = "Ciné Festival")
)

Films à l'affiche de tous les cinémas

Afficher les titres des films qui ont été à l'affiche de tous les cinémas de la base.

Première solution


select F.titre from film as F where not exists 
(select S.id_cinema from seance as S where not exists 
   (select * from seance as SS where S.id_cinema = SS.id_cinema and SS.id_film = F.id_film)
)

Autre solution


select F.titre from film as F where F.id_film in
(
    select S.id_film from seance as S 
   group by S.id_film
    having count(distinct S.id_cinema) = (select count(*) from cinema)
)