Bases de données – ING1
TD 8 : Jointures et requêtes complexes
Nous réutilisons le schéma relationnel du dernier TD sur les films, cinéma et projection :
– Individu(num_ind, nom, prenom)
– Jouer(num_ind, num_film, role)
– Film(num_film, num_ind, titre, genre, annee)
– Projection(num_cine, num_film, pdate)
– Cinéma(num_cine, nom, adresse)
En utilisant des fonctions d’agrégat et des jointures, exprimez les requêtes suivantes
sous la forme d’une commande SQL que vous testerez sur votre base de données.
1. Quel est le nom et le prénom des acteurs et le nombre de films dans lesquels ils ont
joué ?
2. Quel est le nom et le prénom des acteurs et le nombre de films dans lesquels ils ont
joué pour les acteurs ayant joué dans strictement plus d’un film?
3. Quel est le nom et le prénom des acteurs et le nombre de drames dans lesquels ils ont
joué ?
4. Quel est le nombre moyen de projections de l’ensemble des cinémas ?
5. Quels sont les cinémas qui ont projeté tous les films ?
6. Quels sont les acteurs qui ont joué dans un nombre de films supérieur à la moyenne des
acteurs ?
7. On veut obtenir le nom et le prénom de tous les individus classés du meilleur acteur au
plus mauvais en nombre de films.
8. Quel est le nombre de films réalisés par les réalisateurs, dont on désire connaître le nom
et le prénom, ayant réalisé au moins un film du même genre que l'un des films réalisés
par David Cronenberg ?
Corrigé
1. Quel est le nom et le prénom des acteurs et le nombre de films dans lesquels ils ont joué ?
select nom, prenom, count(num_film)
from individu i, jouer j
where i.num_ind = j.num_ind
group by nom,prenom;
2. Quel est le nom et le prénom des acteurs et le nombre de films dans lesquels ils ont joué pour les
acteurs ayant joué dans strictement plus d’un film?
select nom, prenom, count(num_film)
from individu i, jouer j
where i.num_ind = j.num_ind
group by nom,prenom
having count(num_film) > 1;
3. Quel est le nom et le prénom des acteurs et le nombre de drames dans lesquels ils ont joué ?
select nom, prenom, count(j.num_film)
from individu i, jouer j, film f
where i.num_ind = j.num_ind
and j.num_film = f.num_film
and genre = 'Drame'
group by nom,prenom;
4. Quel est le nombre moyen de projections de l’ensemble des cinémas ?
select n1/n2
from (select count(*) as n1 from projection), (select count(*) as n2 from cinema) ;
5. Quels sont les cinémas qui ont projeté tous les films ?
Reformulation : Donner les cinémas donc le nombre de films projetés (différents) est
égal au nombre total des films.
Attention : s'il n'y a pas distinct dans count, le résultat est faux
select [Link]
from cinema c, projection p
where c.num_cine = p.num_cine
group by [Link]
having count(distinct p.num_film) = (select count(*) from film);
6. Quels sont les acteurs qui ont joué dans un nombre de films supérieur à la moyenne des
acteurs ?
select [Link], [Link], COUNT(distinct j.num_film)
from individu a, jouer j
where a.num_ind = j.num_ind
group by [Link], [Link]
having COUNT(distinct j.num_film) >
(select AVG(COUNT(distinct j.num_film))
from individu a, jouer j
where a.num_ind = j.num_ind
group by a.num_ind );
7. On veut obtenir le nom et le prénom de tous les individus classés du meilleur acteur au
plus mauvais en nombre de films.
select [Link], [Link], COUNT(j.num_film)
from individu i left join jouer j on i.num_ind = j.num_ind
group by [Link], [Link]
order by COUNT(distinct j.num_film) DESC;
8. Quel est le nombre de films réalisés par les réalisateurs, dont on désire connaître le nom
et le prénom, ayant réalisé au moins un film du même genre que l'un des films réalisés
par David Cronenberg ?
select nom, prenom, count(num_film)
from film f, individu i
where f.num_ind = i.num_ind
and genre IN (select genre
from film f, individu i
where f.num_ind = i.num_ind
and nom = 'Cronenberg'
and prenom = 'David')
group by i.num_ind, nom, prenom;
