blob: 6835c73bd87c9775440609ca0363f86a1676c032 (
plain) (
blame)
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
|
-- which directors appear most often
SELECT director, COUNT(*)
FROM Movies
GROUP BY director
ORDER BY COUNT(*) DESC
LIMIT 10;
-- top-rated, most recent movies
SELECT *
FROM (
SELECT *
FROM Movies
ORDER BY rating DESC
LIMIT 20
)
ORDER BY YEAR DESC;
-- top-rated, most recent movies (ignore foreign)
SELECT *
FROM (
SELECT *
FROM Movies
WHERE requiresSubtitles = 0
ORDER BY rating DESC
LIMIT 20
)
ORDER BY YEAR DESC;
-- most recent movies
SELECT *
FROM Movies
ORDER BY YEAR DESC
LIMIT 15;
-- most recent movies (ignore foreign)
SELECT *
FROM Movies
WHERE requiresSubtitles = 0
ORDER BY YEAR DESC
LIMIT 10;
-- only cartoons
SELECT *
FROM Movies
WHERE isCartoon = true;
-- only cartoons (ignore foreign)
SELECT *
FROM Movies
WHERE isCartoon = true AND requiresSubtitles = false;
-- show the movies from the directors that show up on the list more than once.
SELECT *
FROM Movies
WHERE director in (
SELECT director
FROM (
SELECT director, COUNT(*) as num
FROM Movies
GROUP BY director
HAVING num > 1
ORDER BY num DESC
)
)
ORDER BY director, rating DESC, year DESC;
|
