TP

Objectif du TP

travailler sur la manipulation des données à l'aide d'un langage de requêtes AVEC 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" :

REALISATEUR (idrealisateur, nom, prenom, date_naissance, #nation)
FILM (idFilm, titre, annee, genre, sortie, recettes, idrealisateur, "idnation")
NATION (idnation, )

Modèle Physique des données

Q1

EnoncéCorrection

afficher le titre des films réalisés par George Lucas.

🐬 SQL

SELECT Film.titre, Realisateur.nom
FROM Film INNER JOIN Realisateur
ON Film.idrealisateur = Realisateur.idrealisateur
WHERE Realisateur.nom = 'Lucas';

Q2

EnoncéCorrection

afficher le titre et le nom des réalisateurs des films « Harry Potter »

🐬 SQL

SELECT Film.titre, Realisateur.nom
FROM Film INNER JOIN Realisateur
ON Film.idrealisateur = Realisateur.idrealisateur
WHERE Film.titre LIKE 'Harry%';

Q3

EnoncéCorrection

afficher le nombre de film par nation

🐬 SQL

SELECT COUNT(*) as nombre, nation.nation
FROM Film INNER JOIN Realisateur
ON Film.idrealisateur = Realisateur.idrealisateur
INNER JOIN nation
ON realisateur.nation = nation.idnation
GROUP BY nation.idnation

Q4

EnoncéCorrection

afficher le titre et le nom du réalisateur du film réalisé par le réalisateur mexicain

🐬 SQL

SELECT Film.titre, Realisateur.nom
FROM Film INNER JOIN Realisateur
    ON Film.idrealisateur = Realisateur.idrealisateur
    INNER JOIN nation
    ON realisateur.nation = nation.idnation
HERE nation.nation = 'Mexique'

Q5

EnoncéCorrection

afficher les différentes nationalité des réalisateurs

🐬 SQL

SELECT DISTINCT nation.nation
FROM nation INNER JOIN realisateur
ON nation.idnation = realisateur.nation ;

note : la jointure est ici obligatoire. On ne doit afficher que les nations ayant des réalisateurs. Or dans la base, le canada n'a pas de réalisateur associé, il ne doit donc pas apparaître.

Q6

EnoncéCorrection

Qui est le réalisateur du film « 2012 » ?

🐬 SQL

SELECT Film.titre, Realisateur.nom
FROM Film INNER JOIN Realisateur
ON Film.idrealisateur = Realisateur.idrealisateur
WHERE Film.titre = '2012';

Q7

EnoncéCorrection

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

🐬 SQL

SELECT Realisateur.nom
FROM Realisateur
WHERE ( YEAR(NOW()) - YEAR(Realisateur.date_naissance) ) <= 55;

Q8

EnoncéCorrection

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 SUM(Film.recettes) as recettes, Realisateur.nom
FROM Film INNER JOIN Realisateur
ON Film.idrealisateur = Realisateur.idrealisateur
GROUP BY Realisateur.nom
ORDER BY recettes DESC