· 9 years ago · Dec 07, 2016, 11:50 AM
1DROP TABLE IF EXISTS movies;
2DROP TABLE IF EXISTS boxoffice;
3DROP TABLE IF EXISTS Language;
4DROP TABLE IF EXISTS directors;
5
6CREATE TABLE movies
7(
8 Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
9 Title VARCHAR(255),
10 Director_obsolete VARCHAR(255),
11 Year INT,
12 Length_min INT
13);
14
15CREATE TABLE boxoffice
16(
17 Rating DOUBLE,
18 Domestic_sales BIGINT,
19 International_sales BIGINT,
20 Aspect_ratio DOUBLE,
21 Movie_id INT NOT NULL,
22 Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
23);
24
25CREATE TABLE language
26(
27 Movie_id INT NOT NULL,
28 Lang_name VARCHAR(50),
29 IsSubtitles BOOL
30);
31
32CREATE TABLE directors
33(
34 Movie_id_obsolete INT,
35 Director_name VARCHAR(255),
36 Born_date DATE,
37 Died_date DATE,
38 Born_place VARCHAR(1024),
39 Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
40);
41
42CREATE TABLE MoviesDirectedBy
43(
44 Movie_id INT NOT NULL,
45 Directors_id INT NOT NULL
46);
47
48
49INSERT INTO movies
50 (Id, Title, Director_obsolete, Year, Length_min)
51VALUES
52 (3, 'Toy Story 2', 'John Lasseter', 1999, 93),
53 (6, 'The Incredibles', 'Brad Bird', 2004, 116),
54 (11,'Toy Story 3', 'Lee Unkrich', 2010, 103),
55 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),
56 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),
57 (7, 'Cars', 'John Lasseter', 2006, 117),
58 (8, 'Ratatouille', 'Brad Bird', 2007, 115),
59 (9, 'WALL-E', 'Andrew Stanton', 2008, 104),
60 (10,'Up', 'Pete Docter', 2009, 101),
61 (12,'Cars 2', 'John Lasseter', 2011, 120),
62 (13,'Brave', 'Brenda Chapman', 2012, 102),
63 (14,'Monsters University', 'Dan Scanlon', 2013, 110);
64
65INSERT INTO movies
66 (Title, Director_obsolete, Year, Length_min)
67VALUES
68 ('The Incredibles 2', 'Brad Bird', 2018, NULL),
69 ('Toy Story 4', NULL, 2019, NULL),
70 ('Cars 3', 'Brian Fee', 2017, 91),
71 ('Coco', 'Lee Unkrich', 2017, 90),
72 ('Finding Dory', 'Andrew Stanton', 2016, 97),
73 ('Inside Out', 'Pete Docter', 2015, 94),
74 ('The Good Dinosaur', 'Peter Sohn', 2015, 93);
75
76
77INSERT INTO boxoffice
78 (Rating, Domestic_sales, International_sales, Aspect_ratio, Movie_id)
79VALUES
80 (8.2, 380843261, 555900000, 1.85, 5),
81 (7.4, 268492764, 475066843, 1.85, 14),
82 (8, 206445654, 417277164, 1.85, 8),
83 (6.4, 191452396, 368400000, 1.7778, 12),
84 (7.9, 245852179, 239163000, 1.85, 3),
85 (8, 261441092, 370001000, 2.35, 6),
86 (8.5, 223808164, 297503696, 1.7778, 9),
87 (8.4, 415004880, 648167031, 1.85, 11),
88 (7.2, 244082982, 217900167, 1.7778, 7),
89 (8.3, 293004164, 438338580, 1.85, 10),
90 (8.1, 289916256, 272900000, 1.85, 4),
91 (7.2, 237283207, 301700000, 1.85, 13),
92 (8.2, 356454367, 857611174, 1.85, 20),
93 (6.8, 123087120, 208839027, 2.35, 21);
94
95INSERT INTO language
96 (Lang_name, movie_id, IsSubtitles)
97VALUES
98 ('English', 5, false),
99 ('English', 14, false),
100 ('English', 8, false),
101 ('English', 12, false),
102 ('English', 3, false),
103 ('French', 6, true),
104 ('English', 9, false),
105 ('English', 11, false),
106 ('English', 7, false),
107 ('English', 10, false),
108 ('English', 4, false),
109 ('English', 13, false),
110 ('Spanish', 3, true),
111 ('Arabic', 3, true),
112 ('English', 6, false),
113 ('Spanish', 11, true),
114 ('French', 8, false),
115 ('Italian', 7, true),
116 ('Japanese', 7, true),
117 ('Yiddish', 7, true),
118 ('English', 20, FALSE),
119 ('English', 21, false);
120
121INSERT INTO directors
122 (Director_name, Movie_id_obsolete)
123VALUES
124 ('John Lasseter', 3),
125 ('Pete Docter', 4),
126 ('Andrew Stanton', 5),
127 ('Brad Bird', 6),
128 ('John Lasseter', 7),
129 ('Brad Bird', 8),
130 ('Andrew Stanton', 9),
131 ('Pete Docter', 10),
132 ('Lee Unkrich', 11),
133 ('John Lasseter', 12),
134 ('Brenda Chapman', 13),
135 ('Dan Scanlon', 14),
136 ('Brad Bird', 15),
137 ('Brian Fee', 17),
138 ('Lee Unkrich', 18),
139 ('Andrew Stanton', 19),
140 ('Pete Docter', 20),
141 ('Peter Sohn', 21),
142 ('Mark Andrews', NULL),
143 ('Steve Purcell', NULL);
144
145INSERT INTO moviesdirectedby
146 (moviesdirectedby.Movie_id, moviesdirectedby.Directors_id)
147VALUES
148 (3, 1),
149 (4, 2),
150 (5, 3),
151 (6, 4),
152 (7, 5),
153 (8, 6),
154 (9, 7),
155 (10, 8),
156 (11, 9),
157 (12, 10),
158 (13, 11),
159 (14, 12),
160 (15, 13),
161 (17, 14),
162 (18, 15),
163 (19, 16),
164 (20, 17),
165 (21, 18),
166 (3, 9),
167 (4, 9),
168 (5, 9),
169 (13,19),
170 (13, 20);