Logo de kxs.frCours d'informatique pour le lycée et la prépa

IMDb

Introduction

L'IMDb est la plus grosse base de donnée mondiale sur les films, séries et animations. Il est possible de récupérer (légalement) une partie limitée leur base de données.

Même si elle n'est pas complète, la base de donnée reste conséquente avec 1Go compressée et plusieurs dixaine de millions de lignes. La taille de la base fait que certaines requêtes peuvent êtres très longues. Il y aura donc des précautions à prendre comme nous le verrons dans la prochaine partie. Les tables originales ont été légèrement modifiées pour faciliter la compréhension. Pour information, l'importation sur les ordinateurs modestes du lycée a pris une dizaine d'heures.

Voici le schéma relationnel de la base de données :

Schéma relationnel de la base de donnée de l'IMDb

Avertissement !

N'importe quelle requête innocente peut prendre plusieurs secondes ou minutes à être générée et encore plus pour être affichée (s'il y a des millions de lignes). Par prudence, il vous faudra ajouter systématiquement la clause LIMIT 100 (ou une valeur inférieure si besoin) à la fin de vos requêtes. Comme vous l'avez compris, cela limite l'affichage à 100 lignes. Cela évitera d'avoir des millions de lignes à défiler en plusieurs minutes devant vos yeux.

Vous ne ferez que des SELECT pour ne pas modifier la base de données. En cas de suppression de lignes ou de tables, il ne sera pas possible de recréer la base en un temps raisonnable.

Connexion à MySQL

Dans un terminal, saisissez la comande suivante :

mysql

Vous devez alors avoir l'invite de commande mysql comme ceci :

mysql>

Découverte et nouvelles instructions

Une fois dans MySQL, placez-vous dans la base « imdb » :

use imdb;

La table principale qui contient entre autre le nom des œuvres, leur date de sortie et leur durrée est work_basics. Nous allons nous intéresser à cette table pour commencer. Le mot « work » est utilisé pour désigner une œuvre (film, série, épisode…). On utilisera le champ originalTitle pour avoir le titre d'une œuvre.

Pour jauger cette base de données nous allons avoir besoin de nouvelles instructions SQL. La première est count(*) qui permet d'avoir le nombre de lignes d'une requête sans que toutes les lignes s'affichent.

1) Exécutez la requête suivante pour connaitre le nombre d'œuvres dans la base :

SELECT count(*) FROM work_basics;
(1 point)

8314694

2) Avec la commande DESCRIBE donnez les champs composant de la table work_basics.

(1 point)
+----------------+-----------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                                            | Null | Key | Default | Extra |
+----------------+-----------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+
| id_work        | int                                                                                                             | NO   | PRI | NULL    |       |
| worktype       | enum('tvEpisode','tvMiniSeries','short','tvMovie','tvSeries','tvShort','video','videoGame','movie','tvSpecial') | YES  | MUL | NULL    |       |
| primaryTitle   | varchar(1000)                                                                                                   | YES  |     | NULL    |       |
| originalTitle  | varchar(1000)                                                                                                   | YES  | MUL | NULL    |       |
| isAdult        | tinyint                                                                                                         | YES  |     | NULL    |       |
| startYear      | smallint                                                                                                        | YES  | MUL | NULL    |       |
| endYear        | smallint                                                                                                        | YES  |     | NULL    |       |
| runtimeminutes | smallint                                                                                                        | YES  | MUL | NULL    |       |
| genre          | varchar(50)                                                                                                     | YES  |     | NULL    |       |
+----------------+-----------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+

Les champs sont : id_work, worktype, primaryTitle, originalTitle, isAdult, startYear, endYear, runtimeminutes et genre.

3) D'après son domaine, expliquez ce qu'est le champ worktype.

(1 point)

Ce champ donne le type de l'œuvrve : série, film, épisode, jeux-vidéo…

Nous utiliserons également la clause LIMIT déjà évoquée plus haut pour limiter le nombre de lignes à afficher.

4) Donnez une requête affichant le nom et la durée des 10 films (movie) les plus longs. En déduire la durée en heures du plus long film.

(2 points)
SELECT originalTitle, runtimeminutes
FROM work_basics
WHERE worktype LIKE 'movie'
ORDER BY runtimeminutes DESC
LIMIT 10;

La durée du plus long film est 477 heures.

5) Donnez une requête affichant le nom et l'année de « sortie » (startYear) des 10 premiers films (movie) de l'histoire. Donner le nom et la date du premier film de l'histoire. (aide : pour éviter d'avoir des champs nuls on peut utiliser la condition IS NOT NULL)

(2 points)
SELECT originalTitle, startYear
FROM work_basics
WHERE worktype LIKE 'movie' AND startYear IS NOT NULL
ORDER BY startYear
LIMIT 10;

Le film est « Birmingham » sorti en 1896.

6) Donnez une requête affichant le nombre de films pour adultes dans la base et donnez ce nombre.

(2 points)
SELECT count(*) FROM work_basics WHERE worktype LIKE 'movie' AND isAdult;

Il y en a 9063.

Le champ genre contient la liste des genres attribués à l'œuvre.

7) Donnez une requête affichant le nombre de comédies (comedy) et donnez ce nombre.

(2 points)
SELECT count(*) FROM work_basics WHERE genre LIKE '%comedy%';

Il y en a 1676266 (585124 sans les '%').

Requêtes avancées

La table work_ratings regroupe les notes attribuées à chaque œuvre par les utilisateur de l'Imdb.

8) Donnez les champs de cette table.

(1 point)

id_work, averageRating et numVotes.

9) Sur quel champ allez-vous faire une jointure pour faire le lien avec la table work_basics?

(1 point)

id_work.

10) Donnez une requête renvoyant les 10 films (movie) avec le plus de votes. Quel est le film avec le plus de votes ?

(2 points)
SELECT originalTitle, startYear, numVotes
FROM work_basics JOIN work_ratings USING (id_work)
WHERE worktype LIKE 'movie'
ORDER BY numVotes DESC
LIMIT 10;

The Shawshank Redemption.

11) Donnez une requête renvoyant les 100 films (movie) avec la meilleure note. On ne gardera que les films avec plus de 100000 votes. Quel est le film avec le mieux noté ?

(2 points)
SELECT originalTitle, startYear, averageRating
FROM work_basics
JOIN work_ratings USING (id_work)
WHERE numVotes > 50000 AND worktype LIKE 'movie'
ORDER BY averageRating DESC
LIMIT 100;

The Shawshank Redemption.

12) Donnez une requête renvoyant les 100 œuvres avec la meilleure note. On ne gardera que les œuvres avec plus de 100000 votes. Quel est le type de l'œuvre la mieux notée ?

(2 points)
SELECT originalTitle, startYear, worktype, averageRating
FROM work_basics JOIN work_ratings USING (id_work)
WHERE numVotes > 50000
ORDER BY averageRating DESC
LIMIT 100;

Ozymandias. C'est un épisode de série.

La table work_episode permet de faire le lien entre un épisode et la série dont il est issu.

Il est possible d'utiliser des alias pour les tables lorsqu'elles apparaissent dans plusieurs jointures :

SELECT a.champA, b.champA FROM table1 a JOIN table2 … JOIN table1 b …;

Ici a et b sont des alias.

Sacahant tout cela, répondez à la question très difficile suivante :

13) Donnez une requête renvoyant les 100 épisodes de série avec la meilleure note. On ne gardera que les épisodes avec plus de 10000 votes. Quelle est la seule série avec un épisode ayant une note de 10 ?

(3 points bonus)
SELECT b.originalTitle, b.startYear, b.worktype, averageRating, p.originalTitle
FROM work_basics b
JOIN work_ratings USING (id_work)
JOIN work_episode e USING(id_work)
JOIN work_basics p on e.id_work_parent = p.id_work
WHERE numVotes > 10000 AND b.worktype = 'tvEpisode'
ORDER BY averageRating DESC
LIMIT 100;

Breaking Bad.

La table work_director fait le lien entre les œuvres et les réalisateurs de la table name_basics

14) Donner une requête permettant de lister tous les films de Christopher Nolan par ordre chronologique avec leur année de sortie.

(2 points)
SELECT startYear, originalTitle
FROM work_basics
JOIN work_director USING(id_work)
JOIN name_basics USING (id_person)
WHERE primaryName LIKE 'Christopher Nolan' AND worktype LIKE 'movie'
ORDER BY startYear
LIMIT 100;
+-----------+-----------------------+
| startYear | originalTitle         |
+-----------+-----------------------+
|      1998 | Following             |
|      2000 | Memento               |
|      2002 | Insomnia              |
|      2005 | Batman Begins         |
|      2006 | The Prestige          |
|      2008 | The Dark Knight       |
|      2010 | Inception             |
|      2012 | The Dark Knight Rises |
|      2014 | Interstellar          |
|      2017 | Dunkirk               |
|      2020 | Tenet                 |
+-----------+-----------------------+

15) Inventez une requête pour impressionner le prof.