about summary refs log tree commit diff
path: root/todo-lists/imdb/scratch.sql
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;