EXERCICE 3 :
1) Les films réalisés par Steven Spielberg.
SELECT title
FROM movie
WHERE director = 'Steven Spielberg';
2) Trouver toutes les années, dans l"ordre croissant, qui ont un film qui a reçu
une note de 4 ou 5.
SELECT distinct year
FROM movie M, rating R
WHERE [Link] = [Link]
AND stars = 4 OR stars = 5
ORDER BY year ASC;
3) Trouver le nom des personnes qui ont noté le film 'Gone with the Wind'
SELECT distinct name
FROM reviewer W, movie M, rating R
WHERE [Link] = [Link]
AND [Link] = [Link]
AND title = 'Gone with the Wind';
4) Pour chaque évaluation où l"examinateur est identique au réalisateur du film
(meme nom), retourner le nom de l"examinateur, le titre du film, et le nombre
d"étoiles.
SELECT name, title, stars
FROM movie M, reviewer W, rating R
WHERE [Link] = [Link]
AND [Link] = [Link]
AND name = director;
5) Retourner l"intégralité des évaluations , en remplaçant rID evec les noms des
examinateurs et mID par les titres de films. Trier le resultat, d"abord par le nom
de relecteur, puis par le titre de film, et enfin par le nombre d"étoiles.
SELECT *
FROM rating
6) Retourner les titres des films non encore examinés par 'Chris Jackson'.
SELECT distinct title
FROM movie M, reviewer W, Rating R
WHERE [Link] = [Link]
AND [Link] = [Link]
AND name <> 'Chris Jackson';
7) Pour tous les cas où la meme personne note deux fois le meme film et donne une
note plus élevée la seconde fois, retourner le nom de l"examinateur et le titre du
film.
SELECT name, title
FROM reviewer W, movie M, Rating R1, Rating R2
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link] AND [Link] = [Link]
AND [Link] > [Link]
AND [Link] > [Link];
8) Retourner les paires d"évaluateurs qui ont noté le meme film, retourner le nom
de ces deux examinateurs en éliminant les duplications. ((a,b) et (b,a)
représentent la meme chose)
SELECT distinct [Link], [Link]
FROM reviewer W1, reviewer W2, Rating R1, Rating R2
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] < [Link];
EXERCICE 4 :
1) Trouver les titres des films qui n"ont pas reçu d"évaluations.
SELECT title
FROM movie
WHERE mid NOT IN (SELECT mid
FROM rating);
2) Retourner le nom de l"examinateur, le titre du film, et le nombre d"étoiles pour
tous les films qui ont actuellement la plus mauvaise note dans la base.
SELECT name, title, stars
FROM reviewer W, movie M, Rating R
WHERE [Link] = [Link]
AND [Link] = [Link]
AND stars = (SELECT MIN(stars)
FROM rating);
3) Pour chaque film, trouver la meilleure note reçue. Retourner le titre de film et
le nombre d"étoiles. Trier par rapport au titre de film (ordre alphabétique).
SELECT title, MAX(stars)
FROM movie M, rating R
WHERE [Link] = [Link]
GROUP BY [Link], title
ORDER BY title ASC;
EXERCICE 5:
1) Lister les titres des films et leur note moyenne.
SELECT title, AVG(stars)
FROM movie M, rating R
WHERE [Link] = [Link]
GROUP BY [Link], title;
2) Trouver le nom de tous les examinateurs qui ont fait au moins 3 évaluations.
SELECT name
FROM reviewer W, Rating R
WHERE [Link] = [Link]
GROUP BY [Link], name
HAVING count(stars) >= 3;
3) Trouver le(s) film(s) ayant la meilleure moyenne de note. Retourner le titre de
film, et la note moyenne.
SELECT title, AVG(stars)
FROM movie M, rating R
WHERE [Link] = [Link]
GROUP BY [Link], title
HAVING AVG(stars) >= all ( SELECT MAX(moyenne)
FROM ( SELECT AVG(stars) AS moyenne
FROM rating
GROUP BY mid) AS moy);
4) Idem pour la plus mauvaise note.
SELECT title, AVG(stars)
FROM movie M, rating R
WHERE [Link] = [Link]
GROUP BY [Link], title
HAVING AVG(stars) <= all ( SELECT MIN(moyenne)
FROM ( SELECT AVG(stars) AS moyenne
FROM rating
GROUP BY mid) AS moy);
5) Trouver la différence entre la moyenne des notes des films réalisés avant 1980
et ceux réalisés après.
SELECT AVG(moyenne_avant) - AVG(moyenne_apres) AS difference
FROM (SELECT AVG(stars) AS moyenne_avant
FROM rating R, movie M
WHERE [Link] = [Link]
AND year < 1980) AS moy_avant,
(SELECT AVG(stars) AS moyenne_apres
FROM rating R, movie M
WHERE [Link] = [Link]
AND year > 1980) AS moy_apres;
6) Pour chaque film, retourner le titre et la différence entre la meilleure et la
plus mauvaise note pour un film donné. Trier par rapport à cette amplitude puis en
fonction du titre.
SELECT title, MAX(stars) - MIN(stars) AS difference
FROM movie M, rating R
WHERE [Link] = [Link]
GROUP BY [Link], title
ORDER BY difference ASC, title ASC;
EXERCICE 6 :
1) Trouver les noms des examinateurs qui n"ont pas daté leurs évaluations.
SELECT name
FROM reviewer W, rating R
WHERE [Link] = [Link]
AND ratingdate IS NULL;
2) Pour chaque réalisateurs, retourner leur nom, titre film(s) dirigés et ayant
reçu la meilleure note de leur carrière. Retourner également cette note. Ignorer
les films dont le réalisateur n"est pas spécifié.
SELECT director, title, MAX(stars)
FROM movie M, rating R
WHERE [Link] = [Link]
AND director IS NOT NULL
GROUP BY director, title;
EXERCICE 7:
1) Ajouter un examinateur Roger Ebert dans la base de données avec un rID égal à
209.
INSERT INTO reviewer VALUES (209, 'Roger Ebert');
2) Pour vérifier l"insertion précédente, écrire une requete pour retourner le
nombre d"évaluateur.
SELECT COUNT(rid) AS nbre_evaluateur
FROM reviewer;
3) Inserer des évaluations à 5 étoiles faites par Roger Ebert pour tous les les
films de la base.
Laisser la date à NULL.
INSERT INTO Rating values (209, 101, 5, NULL);
INSERT INTO Rating values (209, 102, 5, NULL);
INSERT INTO Rating values (209, 103, 5, NULL);
INSERT INTO Rating values (209, 104, 5, NULL);
INSERT INTO Rating values (209, 105, 5, NULL);
INSERT INTO Rating values (209, 106, 5, NULL);
INSERT INTO Rating values (209, 107, 5, NULL);
INSERT INTO Rating values (209, 108, 5, NULL);
4) Pour vérifier l"insertion précédente, retourner le nom des examinateurs qui ont
noté tous les films.
SELECT name
FROM reviewer W1
WHERE NOT EXISTS (SELECT mid
FROM rating R1
WHERE NOT EXISTS (SELECT mid
FROM rating R2
WHERE [Link] = [Link]
AND [Link] = [Link]));
5) Pour tous les films qui ont une note moyenne supérieure ou égale à 4, ajouter 25
ans à la date de réalisation (mettre à jour les tuples, ne pas en créer).
UPDATE movie M
SET year = year + 25
WHERE 4 < (SELECT AVG(stars)
FROM rating R
WHERE [Link] = [Link]
GROUP BY [Link]);
6) Pour vérifier la modification précédente, retourner le nombre de films réalisés
avant 1990.
SELECT count(mid) AS nbre_films
FROM movie
WHERE year < 1990;
7) Supprimer tous les films de la base à l"exception des films réalisés entre 2000
et 2010.
DELETE FROM movie
WHERE year < 2000 OR year > 2010;
8) Maintenant, beaucoup d"évaluations référent à des films qui ne sont plus dans la
table Movie. Supprimer toutes les évaluations dont le film correspondant n"apparait
plus dans la table Movie.
DELETE FROM rating R
WHERE [Link] NOT IN (SELECT [Link]
FROM movie M);
9) Maintenant, des évaluateurs n"ont plus aucune évaluations. Supprimer les
examinateurs qui n"ont pas d"évaluation dans la table Rating.
DELETE FROM reviewer
WHERE rid NOT IN (SELECT rid
FROM rating);
EXERCICE 8 :
Supprimer les tables créées dans l"exercice 1. Modifier ensuite les déclarations de
création de tables afin de prendre en compte les contraintes suivantes
DROP TABLE IF EXISTS movie;
DROP TABLE IF EXISTS reviewer;
DROP TABLE IF EXISTS rating;
1) Ajouter les contraintes de clés (PRIMARY KEY, UNIQUE) suivantes dans la
déclarations des tables.
create table Movie(mID integer PRIMARY KEY, title varchar(50), year integer,
director varchar(30), PRIMARY KEY(title, year));
create table Reviewer(rID integer PRIMARY KEY, name varchar(30));
create table Rating(rID integer, mID integer, stars integer, ratingDate date,
PRIMARY KEY(rID, mID, ratingDate);
2) Exprimer les contraintes des questions précédentes dans le formalisme des
dépendances fonctionnelles.
3) Ajouter les contraintes de valeurs non-nulles (NOT NULL) suivantes dans la
déclaration des tables.
- [Link] doit etre non nul.
- [Link] doit etre non nul.
