Repository navigation
Expand file tree
/
Copy pathSQL_Queries_2.sql
More file actions
281 lines (281 loc) · 9.68 KB
/
Copy pathSQL_Queries_2.sql
File metadata and controls
281 lines (281 loc) · 9.68 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
--
-- Query 1
-- How many different title_types are there? How many of each?
SELECT T.title_type, COUNT(*)
FROM Titles AS T
GROUP BY T.title_type
ORDER BY T.title_type ASC;
--
-- Query 2
-- How many different professions are there? What are they?
SELECT P.job_category, COUNT(*)
FROM Principals AS P
GROUP BY P.job_category
ORDER BY P.job_category ASC;
--
-- Query 3
-- What genres are there? How many movies are there in each genre?
SELECT G.genre, COUNT(G.genre) AS Count
FROM Title_genres AS G, Titles AS T
WHERE T.title_id = G.title_id
AND T.title_type = 'movie'
GROUP BY genre
ORDER BY Count DESC;
--
-- Query 4
-- List movies (runtime_minutes, title_type, primary_title) which are
-- longer than 10 hours. Place them in descending ordering of runtime_minutes.
SELECT runtime_minutes, title_type, primary_title
FROM Titles WHERE runtime_minutes > (10*60)
ORDER BY runtime_minutes DESC, title_type ASC
LIMIT 10;
--
--
-- Query 6
-- How many movies are there in the database?
-- SELECT COUNT(DISTINCT T.title_id) AS Number_of_movies
-- FROM Titles AS T
-- WHERE T.title_type IN ('movie','video');
--
-- Query 7
-- What time period does the database cover?
SELECT LEAST(MIN(T.start_year), MIN(T.end_year)) AS Earliest,
GREATEST(MAX(T.start_year), MAX(T.end_year)) AS Latest
FROM Titles AS T;
--
-- Query 8
-- How many movies where made each year over the past 30 years? (Up to and
-- including 2019)
SELECT T.start_year, COUNT(*) AS Number_of_movies
FROM Titles AS T
WHERE T.title_type IN ('movie','video')
GROUP BY T.start_year
HAVING T.start_year BETWEEN 1990 AND 2019
ORDER BY T.start_year ASC;
--
-- Query 9
-- Who are the actors who played James Bond in a movie? How many times did they
-- play the role of James Bond?
SELECT N.name_id, N.name_, COUNT(*) AS number_of_films
FROM Names_ AS N, Had_role AS H, Titles AS T
WHERE H.role_ LIKE 'James Bond'
AND T.title_type LIKE 'movie'
AND T.title_id = H.title_id
AND N.name_id = H.name_id
GROUP BY N.name_id;
--
-- Query 10
-- How many actors played James Bond?
SELECT COUNT(DISTINCT Q9.name_id) AS number_of_JB_actors
FROM
( SELECT N.name_id, N.name_, COUNT(*) AS number_of_films
FROM Names_ AS N, Had_role AS H, Titles AS T
WHERE H.role_ LIKE 'James Bond'
AND T.title_type LIKE 'movie'
AND T.title_id = H.title_id
AND N.name_id = H.name_id
GROUP BY N.name_id) as Q9;
--
-- Query 11
-- I don't recognise some of these names lets look at them more closely
SELECT Q9.name_, T.title_id, T.primary_title, T.start_year
FROM ( SELECT N.name_id, N.name_, COUNT(*) AS number_of_films
FROM Names_ AS N, Had_role AS H, Titles AS T
WHERE H.role_ LIKE 'James Bond'
AND T.title_type LIKE 'movie'
AND T.title_id = H.title_id
AND N.name_id = H.name_id
GROUP BY N.name_id) as Q9,
Titles AS T, Had_role AS H
WHERE Q9.name_id = H.name_id
AND H.role_ LIKE 'James Bond'
AND T.title_id = H.title_id
AND T.title_type LIKE 'movie'
ORDER BY T.start_year DESC;
--
-- Query 12
-- Find all the movies made by Don "The Dragon" Wilson, the former light heavy
-- weight kickboxing champion. He was born in 1954 and is famous for the
-- Bloodfist series. Omit entries where he appears as himself. Output the start
-- year, the title type, title and the role he played. Order these by year.
SELECT DISTINCT T.start_year, T.title_type, T.primary_title, H.role_
FROM Titles AS T, Had_role AS H
WHERE T.title_id = H.title_id
AND H.role_ <> 'Himself'
AND T.title_type IN ('movie','video')
AND H.name_id = (
SELECT N.name_id
FROM Names_ AS N
WHERE N.name_ LIKE 'Don Wilson' AND N.birth_year = 1954)
ORDER BY T.start_year ASC;
--
-- Query 13
-- Did he ever play a role multiple times ?
SELECT Q12.role_, COUNT(*) AS Count
FROM (SELECT DISTINCT T.start_year, T.title_type, T.primary_title, H.role_
FROM Titles AS T, Had_role AS H
WHERE T.title_id = H.title_id
AND H.role_ <> 'Himself'
AND T.title_type IN ('movie','video')
AND H.name_id = (
SELECT N.name_id
FROM Names_ AS N
WHERE N.name_ LIKE 'Don Wilson' AND N.birth_year = 1954)
ORDER BY T.start_year ASC) as Q12
GROUP BY Q12.role_
HAVING Count >=2;
--
-- Query 14
-- What movies were these ?
SELECT Q12.primary_title, Q12.role_
FROM (SELECT DISTINCT T.start_year, T.title_type, T.primary_title, H.role_
FROM Titles AS T, Had_role AS H
WHERE T.title_id = H.title_id
AND H.role_ <> 'Himself'
AND T.title_type IN ('movie','video')
AND H.name_id = (
SELECT N.name_id
FROM Names_ AS N
WHERE N.name_ LIKE 'Don Wilson' AND N.birth_year = 1954)
ORDER BY T.start_year ASC) as Q12,
(SELECT Q12.role_, COUNT(*) AS Count
FROM (SELECT DISTINCT T.start_year, T.title_type, T.primary_title, H.role_
FROM Titles AS T, Had_role AS H
WHERE T.title_id = H.title_id
AND H.role_ <> 'Himself'
AND T.title_type IN ('movie','video')
AND H.name_id = (
SELECT N.name_id
FROM Names_ AS N
WHERE N.name_ LIKE 'Don Wilson' AND N.birth_year = 1954)
ORDER BY T.start_year ASC) as Q12
GROUP BY Q12.role_
HAVING Count >=2) as Q13
WHERE Q12.role_ = Q13.role_;
--
-- Query 15
-- What are the top 250 movies as determined by the average rating with the over
-- 100,000 votes?
SELECT T.title_id, T.primary_title, R.average_rating
FROM Titles AS T, Title_ratings AS R
WHERE T.title_id = R.title_id
AND T.title_type = 'movie'
AND R.num_votes > 100000
ORDER BY R.average_rating DESC
LIMIT 250;
--
-- Query 16
-- Who are the top 10 actors who have made the most movies listed in the top
-- 250 movies (determined as in Q15) and how many?
SELECT H.name_id, N.name_, COUNT(*) AS Count
FROM (SELECT T.title_id, T.primary_title, R.average_rating
FROM Titles AS T, Title_ratings AS R
WHERE T.title_id = R.title_id
AND T.title_type = 'movie'
AND R.num_votes > 100000
ORDER BY R.average_rating DESC
LIMIT 250)as Q15,
Titles AS T,
Names_ AS N, Had_role AS H
WHERE Q15.title_id = T.title_id
AND T.title_id = H.title_id
AND N.name_id = H.name_id
GROUP BY H.name_id
ORDER BY Count DESC
LIMIT 10;
-- Query 17
-- List all actor names and their roles who starred in the movie Back to the
-- future
SELECT N.name_, H.role_
FROM Titles AS T, Had_role AS H, Names_ AS N
WHERE T.primary_title LIKE 'Back to the Future'
AND T.title_type LIKE 'movie'
AND T.title_id = H.title_id
AND H.name_id = N.name_id;
--
-- Query 18
-- What are the average ratings of the entire back to the future series?
SELECT T.primary_title, R.average_rating
FROM Titles AS T, Title_ratings AS R
WHERE T.primary_title REGEXP '^Back to the Future.*'
AND T.title_id = R.title_id
AND T.title_type = 'movie';
--
-- Query 19
-- What are the average ratings of the entire Trancers series?
SELECT T.primary_title, R.average_rating
FROM Titles AS T, Title_ratings AS R
WHERE T.primary_title REGEXP '^Trancers.*'
AND T.title_id = R.title_id
AND T.title_type IN ('movie','video');
--
-- Query 20
-- How many horror movies are made in leap years>? (~start_year divisible by 4)
SELECT T.start_year, COUNT(DISTINCT T.title_id) AS Number_of_horror_movies
FROM Titles AS T, Title_genres AS G
WHERE T.title_id = G.title_id
AND G.genre = 'Horror'
AND T.title_type IN ('movie','video')
AND (T.start_year % 4) = 0
GROUP BY T.start_year
ORDER BY T.start_year DESC;
--
-- Query 21
-- What were the episodes of Fawlty Towers?
SELECT E.season_number, E.episode_number, T2.primary_title
FROM Titles AS T1, Titles AS T2, Episode_belongs_to AS E
WHERE T1.primary_title = 'Fawlty Towers'
AND T1.title_type = 'tvSeries'
AND T1.title_id = E.parent_tv_show_title_id
AND T2.title_type = 'tvEpisode'
AND T2.title_id = E.episode_title_id
ORDER BY E.season_number, E.episode_number;
--
-- Query 22
-- What were the names and average ratings of each episode of The X-Files?
SELECT E.season_number, E.episode_number, T2.primary_title, R.average_rating
FROM Titles AS T1, Titles AS T2, Episode_belongs_to AS E, Title_ratings AS R
WHERE T1.primary_title = 'The X-Files'
AND T1.title_type = 'tvSeries'
AND T1.title_id = E.parent_tv_show_title_id
AND T2.title_type = 'tvEpisode'
AND T2.title_id = E.episode_title_id
AND T2.title_id = R.title_id
ORDER BY E.season_number, E.episode_number;
--
-- Query 23
-- What was the most popular episode of The X-Files?
SELECT Q22.season_number, Q22.episode_number, Q22.primary_title, Q22.average_rating
FROM (SELECT E.season_number, E.episode_number, T2.primary_title, R.average_rating
FROM Titles AS T1, Titles AS T2, Episode_belongs_to AS E, Title_ratings AS R
WHERE T1.primary_title = 'The X-Files'
AND T1.title_type = 'tvSeries'
AND T1.title_id = E.parent_tv_show_title_id
AND T2.title_type = 'tvEpisode'
AND T2.title_id = E.episode_title_id
AND T2.title_id = R.title_id
ORDER BY E.season_number, E.episode_number) as Q22
WHERE Q22.average_rating = (SELECT MAX(Q22.average_rating) FROM (SELECT E.season_number, E.episode_number, T2.primary_title, R.average_rating
FROM Titles AS T1, Titles AS T2, Episode_belongs_to AS E, Title_ratings AS R
WHERE T1.primary_title = 'The X-Files'
AND T1.title_type = 'tvSeries'
AND T1.title_id = E.parent_tv_show_title_id
AND T2.title_type = 'tvEpisode'
AND T2.title_id = E.episode_title_id
AND T2.title_id = R.title_id
ORDER BY E.season_number, E.episode_number) as Q22);
--
-- Query Q24
-- How many episodes were there in The X-Files per season? And what was the average of the average episode ratings ?
SELECT Q22.season_number, COUNT(*) AS Number_of_episodes, AVG(Q22.average_rating) AS Average_of_ep_average_ratings
FROM (SELECT E.season_number, E.episode_number, T2.primary_title, R.average_rating
FROM Titles AS T1, Titles AS T2, Episode_belongs_to AS E, Title_ratings AS R
WHERE T1.primary_title = 'The X-Files'
AND T1.title_type = 'tvSeries'
AND T1.title_id = E.parent_tv_show_title_id
AND T2.title_type = 'tvEpisode'
AND T2.title_id = E.episode_title_id
AND T2.title_id = R.title_id
ORDER BY E.season_number, E.episode_number) as Q22
GROUP BY Q22.season_number
ORDER BY Q22.season_number;