Exercices sur les bases de données

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_ralisateur ! On appellera naissance le champ de l'année de naissance).

id_realisateur : int
nom : varchar(50)
prenom : varchar(50)
naissance : int

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 ?

Une clé étrangère id_realisateur.

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 ue 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.