From 6ca531116f36a8cbb27485130703e61e179a4cab Mon Sep 17 00:00:00 2001
From: William Carroll <wpcarro@gmail.com>
Date: Sun, 26 Jul 2020 12:52:42 +0100
Subject: Create a scratch buffer for common queries

Defining some commonly used criteria for selecting movies as SQL queries.
---
 todo-lists/imdb/scratch.sql | 50 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 50 insertions(+)
 create mode 100644 todo-lists/imdb/scratch.sql

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;
-- 
cgit 1.4.1