Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
Le langage SQL – TD N 1
Partie 1
Schéma de la base de données
Movie ( mID, title, year, director )
Reviewer ( rID, name )
Rating ( rID, mID, stars, ratingDate )
Exercice 1
Voir le fichier « schema.sql ».
Exercice 2
1. Trouver les titres des films réalisés par « James Cameron ».
select title
from Movie
where director = ' James Cameron';
2. Trouver les années possédant un film qui a reçu un classement de 4 ou 5 étoiles, en les triant par ordre
décroissant.
select distinct year
from Movie, Rating
where Movie.mID = Rating.mID and (stars = 4 or stars = 5)
order by year desc;
Sans doublons :
select year
from Movie
where mID in (
select mID from Rating where stars = 4 or stars = 5)
order by year desc;
3. Trouver les titres des films qui n’ont pas reçu un classement
select title
from Movie
where mID not in (Select mID From Rating);
4. Quelques examinateurs n’ont pas fournit une date avec leurs classement. Trouver les noms de tous les
examinateurs qui n’ont pas fournit une date avec leurs classement.
select name
from Reviewer, Rating
where Reviewer.rID = Rating.rID and ratingDate is null;
1
Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
Another solution (sans doublons) :
select *
from Reviewer
where rID in (select rID from Rating where ratingDate is null);
5. Ecrire une requête qui retourne les données classées dans un format plus lisible : nom de l’examinateur,
titre du film, étoiles et la date de classement. Trier les données, premièrement par nom de l’examinateur,
ensuite par titre du film et finalement par le nombre des étoiles reçu.
select name, title, stars, ratingDate
from (Reviewer natural join Rating) natural join Movie
order by name, title, stars;
6. Pour les cas où le même examinateur a classé deux fois le même film en donnant un classement
supérieur dans la deuxième fois, trouver le nom de l’examinateur et le nom du film.
select name, title
from Movie M, Reviewer R, Rating Ra
where M.mID = Ra.mID and R.rID = Ra.rID and R.rID in (
select rID from Rating where Ra.mID = mID and stars < Ra.stars and ratingDate < Ra.ratingDate);
7. Pour chaque film qui possède au moins un classement, trouver le nombre maximal des étoiles reçu par
ce film. Afficher le titre du film et le nombre des étoiles. Trier les résultats par titre des films.
select title, max(stars)
from Movie, Rating
where Movie.mID = Rating.mID
group by Movie.mID
order by title;
8. Pour chaque film, afficher le titre et la ‘propagation du classement’, c’est-à-dire, la différence entre le
classement maximal et minimal donné pour ce film. Trier les résultats par ordre décroissant selon la
‘propagation du classement’.
select title, (max(stars) - min(stars)) as spr
from Movie, Rating
where Movie.mID = Rating.mID
group by Rating.mID
order by spr desc;
Exercice 3
Question 1
Oooops : probleme de doublons avec la solution suivante :
select name
from Reviewer R, Movie M, Rating Ra
where R.rID = Ra.rID and M.mID = Ra.mID and title = 'Gone with the Wind';
Sans doublons
2
Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
select name
from Reviewer
where rID in (select rID from Rating, Movie
where Movie.mID = Rating.mID and title = 'Gone with the Wind');
Question 2
select name
from Movie, Rating, Reviewer
where Movie.mID = Rating.mID and Rating.rID = Reviewer.rID and director = name;
Question 3
select name as myname from Reviewer where director is not null
union
select title as myname from Movie where title is not null
order by myname;
Question 4
select title
from Movie
where mID not in (select mID from Rating R, Reviewer Re
where R.rID = Re.rID and name = 'Chris Jackson');
Question 5
select distinct R1.name, R2.name
from Reviewer R1, Reviewer R2, Rating Ra1, Rating Ra2
where R1.rID = Ra1.rID and R2.rID = Ra2.rID and
Ra1.mID = Ra2.mID and R1.name < R2.name
order by R1.name, R2.name;
Question 6
select name, title, stars
from Movie M, Rating R, Reviewer Re
where M.mID = R.mID and Re.rID = R.rID and stars = (
select min(stars)
from Rating);
Question 7
select title, avg(stars) as av
from Movie M, Rating R
where M.mID = R.mID
group by R.mID
order by av, title;
Question 8
select name
from Reviewer R, Rating Ra
3
Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
where R.rID = Ra.rID
group by Ra.rID
having count(*) >= 3;
Autre solution (challenge) without using count or having
select distinct name
from Reviewer R, Rating R1
where R1.rID = R.rID and 3 <= (
select count(*) from Rating R2 where R1.rID = R2.rID);
Question 9
select title, director
from Movie M1
where 1 < (
select count(*) from Movie M2 where M1.director = M2.director)
order by director, title;
Question 10
select director, title, max(stars)
from Movie M, Rating R
where M.mID = R.mID and director is not null
group by director;
Exercice 4
Question 1
Ajouter à la base de données l’examinateur ‘Roger Ebert’ avec un rID de 209.
insert into Reviewer values (209, 'Roger Ebert');
Question 2
Insérer dans la base de données un classement de 5-étoiles donné par James Cameron pour tous les films.
Laissez le champ ‘date’ NULL.
insert into Rating
select (select rID from Reviewer where name = 'James Cameron') as James, mID, 5, null
from Movie;
Question 3
Pour tous les films possédant une moyenne de classement de 4-étoiles ou plus, ajouter 25 à l’année de
production (mettre à jour les tuples).
update Movie set year = year + 25
where mID in (
select mID
from Rating
group by mID
having avg(stars) >= 4);
4
Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
Question 4
Supprimer tous les classement des films dont l’année de production est avant 1970 ou après 2000, et le
classement est moins de 4 étoiles.
delete from Rating
where stars < 4 and mID in (
select mID from Movie where year < 1970 or year > 2000);
Partie 2
Student (ID, name, mark)
Friend (ID1, ID2)
Likes (ID1, ID2)
Exercice 5
1. Trouver les noms des étudiants qui sont des amis avec ‘Gabriel’.
select S1.name
from Student S1, Student S2, Friend F1
where S1.ID = F1.ID2 and S2.ID = F1.ID1 and S2.name = 'Gabriel';
Autre méthode :
select name
from Student S1
where S1.ID in (
select ID2 from Student S, Friend F
where S.ID = F.ID1 and S1.ID = F.ID2 and S.name = 'Gabriel');
2. Pour chaque étudiant qui aime un autre dont le dernier possède une note inferieur de 2 ou plus, retourne
le nom et la note de cet étudiant, ainsi que le nom et la note de l’étudiant qui aime.
select S1.name, S1.mark, S2.name, S2.mark
from Student S1, Likes L1, Student S2
where S1.ID = L1.ID1 and L1.ID2 = S2.ID and (S1.mark >= S2.mark+2);
5
Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
3. Pour chaque pair des étudiants dont l’un aime l’autre, afficher le nom et la note des deux étudiants.
Afficher chaque pair une seule fois en ordre alphabétique.
select H1.name, H1.mark, H2.name, H2.mark
from Likes L1, Likes L2, Student S1, Student S2
where S1.ID = L1.ID1 and S2.ID = L2.ID1 and L1.ID1 = L2.ID2 and L1.ID2 = L2.ID1
and H1.name < H2.name;
Second solution
select S1.name, S1.mark, S2.name, S2.mark
from Student S1, Likes L1, Student S2
where S1.ID = L1.ID1 and L1.ID2 = S2.ID and S1.name < S2.name and ID2 in (
select ID1 from Likes where ID2 = L1.ID1)
order by S1.name;
4. Trouver tous les étudiants qui n’apparaissent pas dans la table ‘Likes’ et retourne leurs noms et leurs
notes. Trier les résultats par note, ensuite par nom.
select name, mark
from Student
where ID not in (select ID1 from Likes) and ID not in (select ID2 from Likes)
order by mark, name;
5. Pour chaque situation dont un étudiant A aime un étudiant B, mais on ne possède aucune information
sur les étudiants aimés par l’étudiant B (c’est-à-dire, B n’apparait pas comme ID1 dans la table ‘Likes’),
retourne les nomes et les notes de A et B.
select S1.name, S1.mark, S2.name, S2.mark
from Student S1, Student S2, Likes L
where S1.ID = L.ID1 and S2.ID = L.ID2 and ID2 not in (select ID1 from Likes)
order by S1.name;
6. Pour chaque étudiant A qui aime un étudiant B dont les deux ne sont pas des amis, trouver s’ils
possèdent un ami C en commun. Pour ce trios, afficher le nom et la note de A, B et C.
select S1.name, S1.mark, S2.name, S2.mark, S3.name, S3.mark
6
Université Ibn Zohr 2019/2020
Ecole Supérieure de Technologie – Agadir
from Student S1, Student S2, Student S3, Likes L, Friend F1, Friend F2
where S1.ID = L.ID1 and S2.ID = L.ID2 and S1.ID not in (
select ID1 from Friend where ID2 = S2.ID)
and S3.ID = F1.ID1 and S3.ID = F2.ID1 and F1.ID2 = S1.ID and F2.ID2 = S2.ID ;
7. Trouver la différence entre le nombre des étudiants à l’école et le nombre de diffèrent noms.
select (count(ID) - count(distinct name)) as difference
from Student;
8. Trouver le nom et la note de tous les étudiants qui sont aimés par plus d’un autre étudiant.
select name, mark
from Student S, Likes L
where S.ID = L.ID2
group by ID2
having count(*) > 1;
