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