TP SQL Allociné
sous MySQL.
Réponse AJAX

Accès à la base de données des films

La base de données films est accessible sur le phpmyadmin du lycée (utilisateur sio).

Présentation des tables

Cette base a été scrappée depuis le site d'Allociné en 2020. Elle contient plus de 130 000 films, depuis la création du cinéma jusqu'à aujourd'hui. Elle est structurée selon le modèle logique suivant :
(nb : pour les genres, n'utilisez plus le champ film_genre, mais les tables genre et film_genre)

Ecrire les requêtes SQL qui donnent les résultats demandés

Si vous le souhaitez, à chaque SELECT, vous pouvez rajouter la colonne suivante, qui affiche l'URL vers la fiche du film sur Allociné :

concat('https://www.allocine.fr/film/fichefilm_gen_cfilm=', film_id, '.html') as url

Lorsque les résultats s'affichent, vous devrez alors cliquer sur + Options -> Textes complets pour voir l'URL complète.

Cours SELECT 1 : Where, renommage de colonnes, tris, jointures, expressions, Like, Distinct

Les films produits en 2021, triés par ordre alphabétiques (film_anneeproduction, film_titre).

Votre réponse n°1

Les 100 meilleurs films de tous les temps (film_titre, film_anneeproduction, film_etoilesspectateurs) : films triés par critique spectateurs. Attention au tri, n'affichez pas les plus mauvais ! Utilisez LIMIT 100.

Votre réponse n°2

Affichez les prochaines sorties au cinéma film_titre, film_datesortie - (basez-vous sur le champs film_datesortie et la fonction CURDATE(), faites un tri sur la date de sortie).

Votre réponse n°3

Films produits en 2021 qui sont des films d' 'epouvante-horreur' (film_titre, film_datesortie, triés par date de sortie) - (utilisez les tables film, film_genre, genre).

Votre réponse n°4

Films produits en 2021 qui ne sont pas des films d' 'epouvante-horreur' (vous aurez besoin d'un NOT IN car un film d'horreur peut être également d'un autre genre, auquel cas il ne faut pas l'afficher).

Votre réponse n°5

Quel sont les acteurs qui jouent dans District 9 (filmart_type="acteur") (problème : la requête ne renvoie aucune valeur car la base est incomplète) ?

Votre réponse n°6

Quel est le réalisateur du film Taxi produit en 1998 (filmart_type="realisateur") ?

Votre réponse n°7

Cours SELECT 2 : Gestion des dates

(Pas de date dans cette base de données. La date de sortie du film est au format texte, donc difficilement exploitable)
Nd2020 : maintenant la date de sortie est au format date, ainsi que la date de mise à jour de la fiche.

Cours SELECT 3 : IN, NOT IN

Afficher les films non américains (le film n'est pas associé à la nationalité : nat_pays="americain").

Votre réponse n°8

Afficher les films de Luc Besson dans lesquels Jean Réno ne joue pas (la requête doit renvoyer 'Le cinquième élément', 'Les Minimoys', etc. Elle ne doit pas afficher 'Le grand bleu', 'Léon'. Parce que la base est incomplète, elle va afficher par erreur : 'Nikita' et 'Subway' dans lesquels Jean Réno jouequand même, mais pas en acteur principal).

Votre réponse n°9

Afficher les acteurs qui ont joué dans des films français mais pas dans des films américains.

Votre réponse n°10

Cours SELECT 4 : fonctions de regroupement, Group By, Having

Combien y a-t-il de films au total dans la base ?

Votre réponse n°11

Combien y a-t-il de réalisateurs au total dans la base ? (filmart_type = 'realisateur')

Votre réponse n°12

Nombre de films réalisés par Steven Spielberg ?

Votre réponse n°13

Nombre de films dont le genre contient Science fiction ? (film_genre contient 'science fiction')

Votre réponse n°14

Année de réalisation et titre du tout premier film réalisé par Peter Jackson ?

Votre réponse n°15

Comptez le nombre de films par nationalité, trié par nationalité.

Votre réponse n°16

Pour chaque année, triée par année : nombre de films produits cette année-là (film_anneeproduction). Triez par années de production les plus récentes.

Votre réponse n°17

Comptez le nombre de films par acteur (tri : affichez en premier les acteurs qui en ont tourné le plus, n'affichez pas les acteurs qui n'ont tourné qu'un seul film).

Votre réponse n°18

Comptez le nombre de films par réalisateur (tri : affichez en premier les réalisateurs qui en ont réalisé le plus, n'affichez pas les réalisateurs qui ont réalisé qu'un ou deux films).

Votre réponse n°19

Cours SELECT 5 : auto-jointures (ou jointures réflexives)

Les requêtes avec jointures réflexives sont les requêtes qui utilisent plusieurs fois la même table, ce qui impose de renommer les tables. Par exemple : qui follow l'utilisateur 'S-Pas' ?

SELECT follower.*
FROM user as follower, follow, user as followed
where follower.id = follow.id_follower
and follow.id_followed = followed.id

Ou, si vous comprenez mieux en français :

SELECT user_qui_suit.*
FROM user as user_qui_suit, suivre, user as user_qui_est_suivi
where user_qui_suit.id = suivre.id_user_qui_suit
and suivre.id_user_qui_est_suivi = user_qui_est_suivi.id

Afficher les artistes qui jouent à la fois dans des films français et dans des films américains.

Votre réponse n°20

Afficher les films du même réalisateur que le film Avatar.

Votre réponse n°21

Affichez les acteurs ayant joué dans des films de Steven Spielberg, affichez également le film correspondant et l'année de production.

Votre réponse n°22

Regroupements de regroupements, Vues

Rappel sur... les vues

Quel est le réalisateur qui a réalisé le plus de films ?

Votre réponse n°23

Parmi les réalisateurs qui ont tourné 4 films ou plus, quel est celui qui a la meilleure critique spectateur sur l'ensemble de ses films ?
(Attention pour ceux qui ont déjà répondu à cette question : je l'ai complètement reformulée)

Votre réponse n°24