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