C6.3 : Requêtes SQL d'interrogation d'une base de données

La base de données de travail

Dans toute cette partie, on travaillera avec la base de données suivante sur des films.

• - télécharger la base : films.db ;
• - ouvrir la base dans DB Browser.

Questions

a) Combien de tables cette base de données contient-elle ?

b) Donner la structure de cette base de données.

c) De quelle façon cette base de données pourrait-elle être optimisée ?

Afficher la correction

a) La base de donnée contient 6 tables.

b)

c) Cette base de donnée pourrait être optimisée au niveau des tables Noation et Internaute avec un id numérique pour minimiser la place occupée en mémoire et une clé étrangère.

Interrogation d'une table

Prendre connaissance

Dans le menu : Mémos SQL, partie "Requêtes d'interrogation".

Requêtes SQL sur la table 'Film'

Rédiger les requêtes pour répondre aux demandes suivantes (on rédigera d'abord les requêtes sur le papier avant de les tester) :

a) Retourner tous les titres de films.

b) Retourner le nombre de films.

c) Retourner le nombre de films français.

d) Retourner les titres des films sortis en 2000.

e) Retourner les titres des films sortis avant 1950.

f) Retourner les titres des films sortis entre 1980 et 1990.

g) Retourner les titres et les années de sortie des films sortis avant 1950.

h) Retourner les titres et les années de sortie des films sortis entre 1950 et 1960 par année croissante.

i) Retourner les titres des films de science-fiction

j) Retourner le nombre de films de science-fiction

k) Retourner les titres des films de science-fiction sortis avant 1980

l) Retourner les années lors desquelles des films sont sortis (chaque année ne doit apparaitre qu'une seule fois)

SELECT titre
FROM Film;

SELECT COUNT(*)
FROM Film;

SELECT COUNT(*)
FROM Film
WHERE codePays='FR';

SELECT titre
FROM Film
WHERE année = 2000;

SELECT titre
FROM Film
WHERE année < 1950;

SELECT titre
FROM Film
WHERE année >= 1980 AND année < 1990;

SELECT titre, année 
FROM Film
WHERE année < 1950;

SELECT titre, année 
FROM Film
WHERE année >= 1950 AND année < 1960
ORDER BY année;

SELECT titre 
FROM Film
WHERE genre = 'Science-Fiction';

SELECT COUNT(titre) 
FROM Film
WHERE genre = 'Science-Fiction';

SELECT titre 
FROM Film
WHERE genre = 'Science-Fiction' AND année < 1980;

SELECT DISTINCT année 
FROM Film;

Pour aller plus loin : l'opérateur de comparaison LIKE

Usage de LIKE et de % ou _ dans les chaines de caractères

L'opérateur LIKE utilisé dans la clause WHERE permet de comparer un attribut à un modèle de chaine.

Dans le modèle de chaine, le caractère % remplace n'importe quelle suite de caractère et le caractère _ remplace n'importe quel caractère.

Exemple WHERE nom_attribut LIKE 'm%' permet de recherche les lignes dont l'attribut nom-attribut commence par m.

Travail à faire

Rédiger les requêtes pour répondre aux demandes suivantes (on rédigera d'abord les requêtes sur le papier avant de les tester) :

a) Retourner tous les titres de films qui commencent par la lettre p.

b) Retourner tous les titres de films de comédie qui commencent par la lettre p.

c) Retourner tous les titres de films qui commencent par une voyelle.

d) Retourner tous les titres de films qui commencent par une consonne.

e) Retourner tous les titres de films qui parlent de l'hiver (c'est à dire les films dont le résumé contient le mot hiver).

Interrogation avec jointure de plusieurs tables

Prendre connaissance

Travail à faire (jointure simple)

Rédiger les requêtes pour répondre aux demandes suivantes (on rédigera d'abord les requêtes sur le papier avant de les tester) :

a) Retourner la liste des films (titre et nom du réalisateur) classés par ordre alphabétique pour les titres.

SELECT Film.titre, Artiste.nom
FROM Film
JOIN Artiste ON Film.idRéalisateur = Artiste.idArtiste
ORDER BY Film.titre

b) Retourner la liste des films (titre et nom du pays en toutes lettres) sortis en 2000.

SELECT Film.titre, Pays.nom
FROM Film
JOIN Pays ON Film.codePays = Pays.code
WHERE année = 2000

c) La liste des acteurs (prénom, nom et rôle) du film "Il faut sauver le soldat Ryan" dont l'idFilm est 857.

SELECT Artiste.prénom, Artiste.nom, Rôle.nomRôle
FROM Rôle
JOIN Artiste ON Rôle.idActeur = Artiste.idArtiste
WHERE Rôle.idFilm = 857

d) La liste des films (titre et année) dans lesquels l'acteur Tom Cruise a tournée sachant que l'idArtiste de Tom Cruise est 500.

SELECT Film.titre, Film.année
FROM Rôle
JOIN Film ON Rôle.idFilm = Film.idFilm
WHERE Rôle.idActeur = 500

Travail à faire (jointure double)

Rédiger les requêtes pour répondre aux demandes suivantes (on rédigera d'abord les requêtes sur le papier avant de les tester) :

a) Retourner la liste des films (titre, nom du réalisateur et pays).

SELECT Film.titre, Artiste.nom, Pays.nom
FROM Film
JOIN Artiste ON Film.idRéalisateur = Artiste.idArtiste
JOIN Pays ON Film.codePays = Pays.code

b) Retourner la liste des acteurs (prénom, nom et rôle) du film "Les Quatre Cents Coups" (sans chercher à l'avance l'id du film).

SELECT Artiste.prénom, Artiste.nom, Rôle.nomRôle
FROM Rôle
JOIN Film ON Rôle.idFilm = Film.idFilm
JOIN Artiste ON Rôle.idActeur = Artiste.idArtiste
WHERE Film.titre = "Les Quatre Cents Coups"

c) Retourner la liste des films avec les noms des acteurs dans lesquels l'un des rôles est/contient Indiana Jones.

SELECT Film.titre, Artiste.nom, Rôle.nomRôle
FROM Rôle
JOIN Film ON Rôle.idFilm = Film.idFilm
JOIN Artiste ON Rôle.idActeur = Artiste.idArtiste
WHERE Rôle.nomRôle LIKE '%Indiana Jones%'

Requêtes imbriquées

Expliquer, en détaillant chaque clause, la requête suivante :

SELECT DISTINCT prénom, nom
FROM Artiste JOIN Rôle ON Artiste.idArtiste = Rôle.idActeur
WHERE idFilm IN (SELECT idFilm FROM Film WHERE codePays= 'FR')
ORDER BY nom