TP Cinéma⚓︎
Objectif du TP
travailler sur la manipulation des données à l'aide d'un langage de requêtes SANS Jointure.
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⚓︎
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⚓︎
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 dateMONTH(attribut): la partie mois de la dateDAY(attribut): la parte jour de la date
etc.....
Combien de film ont été réalisés en 2005 ?
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 ;