about summary refs log tree commit diff
path: root/todo-lists
diff options
context:
space:
mode:
authorWilliam Carroll <wpcarro@gmail.com>2020-07-26T11·52+0100
committerWilliam Carroll <wpcarro@gmail.com>2020-07-26T14·52+0100
commit6ca531116f36a8cbb27485130703e61e179a4cab (patch)
treeb7fe82104241309833f5a2de2f54a081a831af02 /todo-lists
parentc36ae072d277b7f8e54a58c3c1d4c5ef31b31dc4 (diff)
Create a scratch buffer for common queries
Defining some commonly used criteria for selecting movies as SQL queries.
Diffstat (limited to 'todo-lists')
-rw-r--r--todo-lists/imdb/scratch.sql50
1 files changed, 50 insertions, 0 deletions
diff --git a/todo-lists/imdb/scratch.sql b/todo-lists/imdb/scratch.sql
new file mode 100644
index 000000000000..9ddba8e9106e
--- /dev/null
+++ b/todo-lists/imdb/scratch.sql
@@ -0,0 +1,50 @@
+-- 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;