· 8 years ago · Apr 24, 2018, 01:48 PM
1-- TP5
2
3-- Q0a. [A corriger au tableau] Ecrivez le Modèle Conceptuel de Données correspondant à ce MLD.
4-- Au tableau
5
6-- Q0b. [Résolue] Créer une base movies, et utiliser cette base
7CREATE DATABASE IF NOT EXISTS `movies` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
8USE `movies`;
9
10-- Q1 Ã Q7
11CREATE TABLE films (
12 id MEDIUMINT (6) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
13 titre VARCHAR (254),
14 date_sortie DATE,
15 duree TIMESTAMP,
16 note_presse DECIMAL(1,1),
17 note_public DECIMAL(1,1),
18 affiche VARCHAR (254),
19 PRIMARY KEY (id)
20);
21
22CREATE TABLE personnes (
23 id MEDIUMINT (6) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
24 nom VARCHAR (254),
25 prenom VARCHAR (254),
26 date_naissance DATE,
27 date_deces DATE,
28 lieu_naissance VARCHAR (254),
29 pays_naissance VARCHAR (254),
30 PRIMARY KEY (id)
31);
32
33CREATE TABLE acteurs (
34 `id_film` MEDIUMINT (6),
35 `id_personne` MEDIUMINT (6),
36 role VARCHAR (254)
37);
38
39CREATE TABLE fonctions (
40 id SMALLINT (6) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
41 nom VARCHAR (254),
42 PRIMARY KEY (id)
43);
44
45CREATE TABLE staff (
46 `id_film` MEDIUMINT (6),
47 `id_personne` MEDIUMINT (6),
48 `id_fonction` SMALLINT (6)
49);
50
51CREATE TABLE genres (
52 id SMALLINT (6) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
53 nom VARCHAR (254),
54 PRIMARY KEY (id)
55);
56
57CREATE TABLE genres_films (
58 `id_film` MEDIUMINT (6),
59 `id_genre` SMALLINT (6)
60);
61
62-- Q8. Tom Hanks joue Forrest Gump dans le film éponyme. Ecrivez la requête qui ajoute cette information.
63INSERT INTO movies.`acteurs` (id_film, id_personne, role) SELECT films.id, personnes.id, 'Forrest Gump' from films, personnes where films.titre = "Forrest Gump" and personnes.nom ='Hanks' and personnes.prenom = "Tom";
64
65-- Q9. Michael Duncan, qui jouait le géant injustement condamné à mort dans La Ligne verte, est décédé le 3 septembre 2012. Ecrivez la requête qui met à jour la base de données.
66UPDATE movies.`personnes` SET date_deces = '2012-03-09' WHERE nom='Duncan';
67
68-- Q10. The Dark Knight, Le Chevalier Noir est non seulement un film d'Action, mais c'est aussi un Drame. Ajoutez cette dernière information.
69INSERT INTO movies.`genres_films` (id_film, id_genre) SELECT films.id, genres.id from films, genres where films.titre = "The Dark Knight, Le Chevalier Noir " and genres.nom ='Drame';
70
71-- Q11. Affichez les prénom, noms et lieu de naissance de toutes les personnes nées aux Etats-Unis, triés par ordre alphabétique des noms.
72SELECT prenom, nom, lieu_naissance, pays_naissance
73FROM personnes
74WHERE pays_naissance = 'Etats-Unis'
75ORDER BY nom;
76
77-- Q12. Affichez toutes les personnes, par date de naissance (pour l'affichage dans une seule colonne, utilisez CONCAT)
78SELECT CONCAT(prenom, ' ', nom) AS Personnes, date_naissance
79FROM personnes
80ORDER BY date_naissance;
81
82-- Q13. Classez les personnes vivantes selon leur âge actuel
83SELECT CONCAT(prenom, ' ', nom) AS Personnes, date_naissance, DATE(NOW()) AS "Aujourd'hui", ROUND(DATEDIFF(DATE(NOW()), date_naissance)/365) AS "Age"
84FROM personnes
85ORDER BY Age;
86
87
88
89
90-- Bases de données - TP4 - Christian FRIEDRICH - MMI 1 / TP 1.1
91
92
93
94
95
96
97
98USE `movies`;
99
100INSERT INTO `movies`.`films` (`id`, `titre`, `date_sortie`, `duree`, `note_presse`, `note_public`, `affiche`) VALUES
101('190918', 'Django Unchained', '2013-01-16', '164', '4.5', '4.5', 'http://fr.web.img4.acsta.net/cx_120_160/medias/nmedia/18/90/08/59/20366454.jpg'),
102('10568', 'Forrest Gump', '1994-10-05', '140', '2.6', '4.5', 'http://fr.web.img3.acsta.net/cx_120_160/medias/nmedia/18/63/30/77/18686566.jpg'),
103('22779', 'La Ligne verte', '2000-03-01', '189', '2.8', '4.5', 'http://fr.web.img2.acsta.net/cx_120_160/medias/nmedia/18/66/15/78/19254683.jpg'),
104('135063', 'Gran Torino', '2009-02-25', '111', '4.7', '4.5', 'http://fr.web.img4.acsta.net/cx_120_160/medias/nmedia/18/67/90/93/19057560.jpg'),
105('115362', 'The Dark Knight, Le Chevalier Noir ', '2008-08-13', '147', '4.0', '4.5', 'http://fr.web.img6.acsta.net/cx_120_160/medias/nmedia/18/63/97/89/18949761.jpg');
106
107INSERT INTO `movies`.`personnes` (`id`, `nom`, `prenom`, `date_naissance`, `lieu_naissance`, `pays_naissance`) VALUES
108('32404', 'Foxx', 'Jamie', '1967-12-13', 'Terrell, Texas', 'Etats-Unis'),
109('15570', 'Tarantino', 'Quentin', '1963-03-27', 'Knoxville, Tennessee', 'Etats-Unis'),
110('155364', 'Waltz', 'Christoph', '1956-10-04', 'Vienne', 'Autriche'),
111('14100', 'Hanks', 'Tom', '1956-07-09', 'Oakland, Californie', 'Etats-Unis'),
112('14624', 'Sinise', 'Gary', '1955-03-17', 'Blue Island, Illinois', 'Etats-Unis'),
113('1457', 'Zemeckis', 'Robert', '1951-05-14', 'Chicago, Illinois', 'Etats-Unis'),
114('31406', 'Darabont', 'Frank', '1959-01-28', 'Montbéliard', 'France'),
115('31950', 'Duncan', 'Michael Clarke', '1957-12-10', 'Chicago, Illinois', 'Etats-Unis'),
116('1146', 'Eastwood', 'Clint', '1930-05-31', 'San-Francisco, Californie', 'Etats-Unis'),
117('240551', 'Vang', 'Bee', '1991-11-04', 'Fresno, Californie', 'Etats-Unis'),
118('30367', 'Nolan', 'Christopher', '1970-07-30', 'Londres', 'Angleterre'),
119('8471', 'Bale', 'Christian', '1974-01-30', 'Pembrokshire, Pays de Galles', 'Royaume-Uni'),
120('33198', 'Ledger', 'Heath', '1979-04-04', 'Perth', 'Australie');
121
122DELETE FROM acteurs;
123INSERT INTO `movies`.`acteurs`
124(`id_film`, `id_personne`, `role`) VALUES
125('190918', '32404', 'Django'),
126('190918', '155364', 'Dr. King Schultz'),
127('10568','14624','Le lieutenant Dan Taylor '),
128('22779', '14100', 'Paul Edgecomb'),
129('22779', '31950', 'John Coffey '),
130('135063', '1146', 'Walt Kowalski'),
131('135063', '240551', 'Tao'),
132('115362', '8471', 'Batman / Bruce Wayne'),
133('115362', '33198', 'Le Joker');
134
135INSERT INTO `movies`.`genres` (`id`, `nom`) VALUES
136('2', 'Comédie dramatique'),
137('8', 'Drame'),
138('12', 'Fantastique'),
139('18', 'Policier'),
140('19', 'Western'),
141('23', 'Thriller'),
142('24', 'Romance'),
143('25', 'Action');
144
145INSERT INTO `movies`.`genres_films`
146(`id_film`, `id_genre`) VALUES
147('190918', '19'),
148('10568', '2'),
149('10568', '24'),
150('22779', '12'),
151('22779', '18'),
152('135063', '8'),
153('135063', '23'),
154('115362', '25');
155
156INSERT INTO `fonctions`
157(`id`, `nom`) VALUES
158(1, 'Réalisateur'),
159(2, 'Producteur');
160
161INSERT INTO `movies`.`staff`
162(`id_film`, `id_personne`, `id_fonction`) VALUES
163('190918', '15570', '1'),
164('10568', '1457', '1'),
165('22779', '31406', '1'),
166('135063', '1146', '1'),
167('115362', '30367', '1');