Exercices sur les bases de données

Suite du cours (citations de films)

On se propose d'ajouter une table realisateurs à notre base de données. Cette table contiendra les noms, prénoms et date de naissance des réalisateurs des films déja présents dans la base.

1) Proposez une structure pour la table realisateurs (n'oubliez pas la clé primaire qu'on pourra appeler id_realisateur ! On appellera naissance le champ de l'année de naissance).

2) Sachant que la commande pour créer la table films était la suivante, proposez une commande pour créer la table realisateurs. Demandez au professeur avant de la tester.

CREATE TABLE films
(
    id_film INT PRIMARY KEY NOT NULL,
    titre VARCHAR(100),
    annee INT
);

CREATE TABLE realisateurs
(
	id_realisateur INT PRIMARY KEY NOT NULL,
	nom VARCHAR(50),
	prenom VARCHAR(50),
	naissance INT
);

3) Ajoutez les réalisateurs avec leurs informations dans la table realisateurs.

INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (1, 'Lasseter', 'John', 1957);
INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (2, 'Russo', 'Joe', 1971);
INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (3, 'Weir', 'Peter', 1944);
INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (4, 'Fincher', 'David', 1962);
INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (5, 'Jeunet', 'Jean-Pierre', 1953);
INSERT INTO realisateurs (id_realisateur, nom, prenom) VALUES (6, 'Van Hoofstadt', 'Olivier');
INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (7, 'Tarentino', 'Quentin', 1963);
INSERT INTO realisateurs (id_realisateur, nom, prenom, naissance) VALUES (8, 'Hazanavicius', 'Michel', 1967);

4) Proposez et testez une requête permettant d'afficher les noms, prénoms et date de naissance des réalisateur par ordre d'année de naissance.

SELECT nom, prenom, naissance FROM realisateurs ORDER BY naissance;

5) Que faut-il ajouter à la table films pour la lier avec la table réalisateur ?

6) La commande pour ajouter une colonne est donnée ci-dessous. Proposez une commande pour ajouter le champ nécessaire à la table films.

ALTER TABLE table
ADD champ type;

Le type peut être INT, VARCHAR ou TEXT

ALTER TABLE films ADD id_realisateur INT;

UPDATE films SET id_realisateur = 1 WHERE id_film = 1;
UPDATE films SET id_realisateur = 2 WHERE id_film = 2;
UPDATE films SET id_realisateur = 3 WHERE id_film = 3;
UPDATE films SET id_realisateur = 4 WHERE id_film = 4;
UPDATE films SET id_realisateur = 5 WHERE id_film = 5;
UPDATE films SET id_realisateur = 6 WHERE id_film = 6;
UPDATE films SET id_realisateur = 7 WHERE id_film = 7;
UPDATE films SET id_realisateur = 8 WHERE id_film = 8;

7) Proposez une requête permettant d'afficher les champs suivants : texte, titre, annee, nom et prenom par ordre chronologique de sortie des films.

SELECT texte, titre, annee, nom, prenom
FROM citations
JOIN films USING (id_film)
JOIN realisateurs USING (id_realisateur)
ORDER BY annee;

8) Dessinez le schéma relationnel de la base de données avec les trois tables.

Rolling Stone Magazine

Le Magazine Rolling Stone publie périodiquement un classement des 500 meilleures chansons de tous les temps.

Voici un fichier sql avec le classement de 2024. Placez-vous dans la base de donnée travail et importez la table songs avec la commande :

SOURCE rollingstone.sql;

9) Quelle est la structure de la table songs ? Donner les attributs et leur domaine ainsi que la clé primaire.

10) Proposer une requête affichant le classement dans l'ordre croissant (on affichera toutes les informations)

SELECT * FROM songs ORDER BY rank;

11) Proposer une requête affichant l'artiste et le titre de la 100^e chanson.

SELECT artist, title FROM songs WHERE rank = 100;

12) Proposer une requête affichant toutes les chansons d'Aretha Franklin dans le classement.

SELECT title FROM songs WHERE artist = 'Aretha Franklin';

SELECT title FROM songs WHERE artist LIKE 'Aretha Franklin';

13) Proposer une requête affichant le classement (dans l'ordre croissant) des chansons du XXI^e siècle. (on affichera toutes les informations)

SELECT * FROM songs WHERE year > 2000 ORDER BY rank ASC;

14) Proposer une requête affichant uniquement le nombre de chansons du XX^e siècle (2000 compris).

SELECT count(*) FROM songs WHERE year <= 2000;

15) Proposer une requête affichant les chansons dont le titre contient le mot « rock ».

SELECT * FROM songs WHERE title LIKE '%rock%';

16) Proposer une requête affichant toutes les chansons de l'année 1971

SELECT * FROM songs WHERE year = 1971;

17) [Hors programme : GROUP BY] On souhaite connaitre les meilleurs années pour la musique. Proposez une requête permettant d'afficher les années avec leur nombre de chansons par ordre décroissant (du nombre de chansons). Le résultat doit ressembler à cela :

+------+----+
| year | nb |
+------+----+
| 1971 | 21 |
| 1972 | 20 |
| 1965 | 20 |
| 1970 | 19 |
| 1967 | 18 |
…

SELECT year, count(*) as nb FROM songs GROUP BY year ORDER BY nb DESC;

18) [Hors programme : GROUP BY] De la même façon, proposez une requête pour classer les artistes par nombre de chansons décroissant.

SELECT artist, count(*) as nb FROM songs GROUP BY artist ORDER BY nb DESC;