🛢️ Bac à sable SQL

La base de données suivante est constituée de plusieurs relations sur les Films. Elle permet de de traiter toute sorte de requêtes, dont certaines avec des jointures complexes.

Parfait pour l'entraînement pour tous : les terminales et PC.

Grand merci à Philippe Rigaux(CNAM) pour ce travail. Vous pouvez retrouver ses cours et les mêmes exercices que sur cette page.

Questions

1. Nom et année de naissance des artistes né́s avant 1950.
2. Titre de tous les drames.
3. Titre des films français sortis depuis 2010.
4. Quels rôles a joué Bruce Willis ?
5. Qui est le réalisateur de Memento ?
6. Quelles sont les notes obtenues par le film Fargo ?
7. Qui a joué le rôle de Chewbacca ?
8. Dans quels films Bruce Willis a-t-il joué́ le role de John McClane ?
9. Nom et prénom des acteurs de Pulp Fiction.
10. Quelles sont les films notés par l'internaute Prénom1 Nom1.
11. Films dont le réalisateur est Tim Burton, et l’un des acteurs Johnny Depp.
12. Titre des films dans lesquels a joué́ Woody Allen. Donner aussi le rôle.
13. Quel metteur en scène a tourné́ dans ses propres films ? Donner le nom, le rôle et le titre des films ?
14. Titre des films de Quentin Tarantino dans lesquels il n’a pas joué.
15. Les films dans lesquels cet artiste a joué.
16. Donner les films de Hitchcock sans James Stewart
17. Dans quels films le réalisateur a-t-il le même pré́nom que l’un des interprètes ? (titre, nom du réalisateur, nom de l’interprète) ? Le réalisateur et l’interprète ne doivent pas être la même personne.
18. Les films sans rôle.
19. Quels acteurs n’ont jamais réalisé de film ?
20. Quelle est la moyenne des notes de Memento ?
21. id, Nom et prénom des réalisateurs, et nombre de films qu’ils ont tournés.
22. Nom et prénom des réalisateurs qui ont tourné au moins deux films.
23. Quels films ont une moyenne des notes supérieure à 7 ?

Voici le schéma relationnel de la base :

• Film (idFilm, titre, année, genre, résumé, #idRéalisateur, #codePays)

• Pays (code, nom, langue)

• Artiste (idArtiste, nom, prénom, annéeNaiss)

• Rôle (#idFilm, #idActeur, nomRôle)

• Internaute (email, nom, prénom, codePays)

• Notation (#email, #idFilm, note)

En souligné, la ou les attributs qui forme(nt) la clé primaire. Les clés étrangères sont précédées d'un #.

Ecrire un requête SQL, puis l'exécuter

Votre code SQL
Exécuter

Solutions

SELECT nom, annéeNaiss
FROM Artiste
WHERE annéeNaiss < 1950

SELECT titre
FROM Film
WHERE genre = 'Drama'

SELECT titre
FROM Film
WHERE année >= 2010 AND codePays = 'FR'

Ici il faut une jointure entre les relations Film et Artiste. DISTINCT sert à supprimer les doublons.

SELECT DISTINCT nomRôle
FROM Rôle
JOIN Artiste
ON Rôle.idActeur = Artiste.idArtiste
WHERE nom = 'Willis' AND prénom = 'Bruce'

SELECT nom, prénom
FROM Artiste
JOIN Film
ON Artiste.idArtiste = Film.idRéalisateur
WHERE titre = 'Memento'

SELECT note
FROM Notation
NATURAL JOIN Film
WHERE titre = 'Fargo'

 SELECT DISTINCT nom, prénom
FROM Artiste
JOIN Rôle
ON Artiste.idArtiste = Rôle.idActeur
WHERE nomRôle = 'Chewbacca'

SELECT titre
FROM Film
NATURAL JOIN Rôle
JOIN Artiste
ON Artiste.idArtiste = Rôle.idActeur
WHERE nomRôle = 'John McClane' AND  nom = 'Willis' AND prénom = 'Bruce'

SELECT nom, prénom
FROM Artiste
JOIN Rôle
ON Artiste.idArtiste = Rôle.idActeur
NATURAL JOIN Film
WHERE titre = 'Pulp Fiction'

SELECT titre
FROM Notation AS n
NATURAL JOIN Internaute
JOIN Film AS f
ON f.idFilm = n.idFilm
WHERE prénom = 'Prénom1' AND nom = 'Nom1'

SELECT titre
FROM Film
JOIN Artiste as Realisateur
ON  Film.idRéalisateur = Realisateur.idArtiste
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON Rôle.idActeur = Acteur.idArtiste
WHERE Realisateur.nom = "Burton" AND  Realisateur.prénom = "Tim" AND Acteur.nom = "Depp" AND Acteur.prénom = "Johnny"

SELECT titre, nomRôle
FROM Film
NATURAL JOIN Rôle
JOIN Artiste
ON Rôle.idActeur = Artiste.idArtiste
WHERE nom = "Allen" AND  prénom = "Woody"

SELECT titre, nomRôle
FROM Film
JOIN Artiste as Realisateur
ON  Film.idRéalisateur = Realisateur.idArtiste
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON Rôle.idActeur = Acteur.idArtiste
WHERE Realisateur.idArtiste = Acteur.idArtiste

-- Les films de Quentin Tarantino
SELECT titre
FROM Film
JOIN Artiste as Realisateur
ON  Film.idRéalisateur = Realisateur.idArtiste
WHERE Realisateur.nom = "Tarantino" AND Realisateur.prénom = "Quentin"

-- Avec EXCEPT, on retire les films où le réalisateur joue un rôle cf. question précédente
EXCEPT
SELECT titre
FROM Film
JOIN Artiste as Realisateur
ON  Film.idRéalisateur = Realisateur.idArtiste
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON Rôle.idActeur = Acteur.idArtiste
WHERE Realisateur.idArtiste = Acteur.idArtiste

SELECT titre
FROM Film
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON  Rôle.idActeur = Acteur.idArtiste
WHERE nom = "Tarantino" AND prénom = "Quentin"

-- La relation des films de Hitchcock
SELECT titre
FROM Film
JOIN Artiste 
ON  idRéalisateur = idArtiste
WHERE nom = "Hitchcock"

-- en excluant
EXCEPT

-- Les films de Hitchcock avec James Stewart
SELECT titre
FROM Film
JOIN Artiste as Realisateur
ON  Film.idRéalisateur = Realisateur.idArtiste
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON Rôle.idActeur = Acteur.idArtiste
WHERE Realisateur.nom = "Hitchcock"  AND Acteur.nom = "Stewart" AND Acteur.prénom = "James"

SELECT titre, Realisateur.nom, Acteur.nom
FROM Film
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON Rôle.idActeur = Acteur.idArtiste
JOIN Artiste as Realisateur
ON  Film.idRéalisateur = Realisateur.idArtiste
WHERE Realisateur.prénom = Acteur.prénom AND Realisateur.nom <> Acteur.nom

-- Tout les films
SELECT titre
FROM Film

-- enxcluant ceux qui apparraisent dans la relation Rôle
EXCEPT
SELECT titre
 FROM Film
NATURAL JOIN Rôle    

-- les noms, prénoms de tous les acteurs
SELECT nom, prénom
FROM Film
NATURAL JOIN Rôle
JOIN Artiste as Acteur
ON  Rôle.idActeur = Acteur.idArtiste
GROUP BY idArtiste

-- en excluant
EXCEPT 

-- les réalisateurs
SELECT nom, prénom
FROM Film
JOIN Artiste
ON idRéalisateur  = idArtiste

SELECT AVG(note) AS "note moyenne"
FROM Notation AS n
JOIN Film AS f
ON f.idFilm = n.idFilm
WHERE titre = "Memento"

SELECT nom, prénom, COUNT(idFilm) AS "nombre film"
FROM Artiste
JOIN Film
ON Artiste.idArtiste = Film.idRéalisateur
GROUP BY idRéalisateur

-- en reprenant la question précédente avec la clause HAVING
SELECT nom, prénom, COUNT(idFilm) AS "nombre film"
FROM Artiste
JOIN Film
ON Artiste.idArtiste = Film.idRéalisateur
GROUP BY idRéalisateur
HAVING COUNT(idFilm) >= 2

SELECT titre, AVG(note) AS "note moyenne"
FROM Film AS f
NATURAL JOIN  Notation AS n
GROUP BY idFilm
HAVING AVG(note) > 7

Les relations de l'exercice:

A charger avant d'exécuter les requêtes.

Cliquer sur Exécuter ci-dessous
Exécuter