Aller au contenu

TP Cinéma⚓︎

Objectif du TP

travailler sur la manipulation des données à l'aide d'un langage de requêtes SANS Jointure.

gif

Télécharger fichier Création de la base ⬇️

Crédit

Adaptation d'un TP SIO de Stéphane Guyon

Schéma conceptuel "en intention" :⚓︎

ARTISTE (idActeur, nom, prenom, anneeNaiss)
FILM (^^idFilm, titre, annee, prenom_realisateur, nom_realisateur, #genre, sortie, recettes)
GENRE (id, nomGenre)
ROLE (#idFilm, #idActeur, nomRole)

Modèle Physique des données⚓︎

MPD

Q1

Sélectionner tous les films dont les recettes sont supérieures à 1000

🐬 SQL
SELECT titre,recettes
FROM Film
WHERE recettes > 1000 ;

Q2

Sélectionner tous les films de George Lucas

🐬 SQL
SELECT titre
FROM Film
WHERE nom_realisateur = 'Lucas' AND prenom_realisateur='Georges';

Q3

Sélectionner tous les films dont les recettes sont supérieures à 1000 et inférieures ou égale à 1500. (Ecrire la requête de deux manières différentes)

🐬 SQL
SELECT titre,recettes
FROM Film
WHERE recettes BETWEEN 1000 AND 1500;
/*ou*/
SELECT titre,recettes
FROM Film
WHERE recettes > 1000 AND recettes <= 1500;

Q4

Afficher les films entre 2000 et 2005

Est-ce que les bornes du BETWEEN sont inclusives ou exclusives ?

🐬 SQL
SELECT titre,annee
FROM Film
WHERE annee BETWEEN 2000 AND 2005;

Q5

Sélectionner tous les films de Harry Potter

🐬 SQL
SELECT titre
FROM Film
WHERE titre LIKE 'Harry Potter%';

Q6

Afficher le titre des films par ordre décroissant des recettes

🐬 SQL
SELECT titre,recettes
FROM Film
ORDER BY recettes DESC;

Q7

Quels sont les films fantastiques réalisés par Steven Spielberg ?

🐬 SQL
SELECT titre
FROM Film 
WHERE nom_realisateur = "Spielberg";

Q8

Afficher le titre des films d'animation par ordre décroissant des recettes.

🐬 SQL
SELECT titre, recettes
FROM Film 
WHERE genre = 3
ORDER BY recettes DESC

Point de cours sur les dates⚓︎

date

Les dates sont un type complexe en SQL. et même si ils ressemblent à une chaîne de caractère, on ne peut pas les manipuler en tant que telle. Les SGBD (documentation MySQL) mettent à notre disposition des fonctions de manipulations dédiés.
exemple :

  • la date du jour : CURRENT_DATE
  • l'heure : CURRENT_TIME
  • YEAR(nom_attribut): la partie année de la date
  • MONTH(attribut) : la partie mois de la date
  • DAY(attribut): la parte jour de la date
    etc.....

Combien de film ont été réalisés en 2005 ?

🐬 SQL
SELECT titre
FROM Film
WHERE YEAR(sortie)='2005';

Q9

Afficher tous les films fantastiques réalisés entre 2000 et 2010 .

🐬 SQL
SELECT * 
FROM Film 
WHERE genre = 4
AND YEAR( sortie ) BETWEEN 2000 AND 2010

Q10

Afficher tous les films dont le titre commence par « T ».

🐬 SQL
SELECT * 
FROM Film 
WHERE titre LIKE "T%"

Q11

Afficher tous les films dont le titre comporte le mot « Monde ».

🐬 SQL
SELECT * 
FROM Film 
WHERE titre LIKE "%Monde%"

Q12

Affichez pour chaque acteur le nombre de ses films présents dans la base

🐬 SQL
SELECT COUNT(*), idacteur
FROM role 
GROUP BY idActeur;

Q13

Affichez le nombre de film pour chaque catégorie

🐬 SQL
SELECT COUNT(*), genre
FROM film 
GROUP BY genre;

Q14

Affichez pour chaque année le film qui a réalisé le plus de recettes

🐬 SQL
SELECT MAX(recettes),titre 
FROM film
GROUP BY annee ;

Q15

afficher le nom et la date de naissance des acteurs qui ont moins de 45 ans. (essayer dans un premier temps de trouver comment afficher l'année en cours)

🐬 SQL
SELECT nom, anneeNaiss
FROM artiste
WHere (YEAR(CURRENT_DATE)- anneeNaiss ) <= 45

Q16

Combien de films sont sortie au mois de septembre ?

🐬 SQL
SELECT * 
FROM FILM
WHERE MONTH(sortie) = 9;

Q17

afficher les réalisateurs et la somme des recettes générées par leurs films classés par ordre décroissant des recettes ?

🐬 SQL
SELECT nom_realisateur, SUM(recettes)
FROM FILM
GROUP BY nom_realisateur
ORDER BY SUM(recettes) DESC ;