forked from lloydtabb/imdb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
harvard-cs050.malloynb
158 lines (158 loc) · 8.88 KB
/
harvard-cs050.malloynb
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
[
{
"kind": 1,
"language": "markdown",
"value": "# Harvard CS 050 SQL Problems\nHarvard's beginnning computer science class teaches a section on SQL. The class uses the IMDB as a data set for the class. Here are the questions and answers in Malloy\n\nBelow is the semantic data model used to answer all the questions",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "source: people is table('duckdb:data/names.parquet')\nsource: principals is table('duckdb:data/principals.parquet') {\n join_one: people is people on nconst = people.nconst\n}\nsource: movies is table('duckdb:data/titles.parquet') + {\n join_many: principals on tconst = principals.tconst\n measure:\n title_count is count() \n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 1: Movie Titles after 2008\nList the titles of all movies released in 2008. Your query should output a table with a single column for the title of each movie.",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: startYear = 2008\n project: primaryTitle\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 2: When was Emma Stone Born?\nWrite a query to determine the birth year of Emma Stone. Your query should output a table with a single column and a single row (not including the header) containing Emma Stone’s birth year.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: people -> {\n where: primaryName = 'Emma Stone'\n project: birthYear\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 3: Emma Stone Titles\nYou may assume that there is only one person in the database with the name Emma Stone. Write a query to list the titles of all movies with a release date on or after 2018, in alphabetical order. Your query should output a table with a single column for the title of each movie.",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: principals.people.primaryName = 'Emma Stone' \n and startYear >= 2018\n project: primaryTitle\n order_by: 1\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 4: Movies with 10 rating\nMovies released in 2018 should be included, as should movies with release dates in the future. Write a query to determine the number of movies with an IMDb rating of 10.0. Your query should output a table with a single column and a single row (not including the header) containing the number of movies with a 10.0 rating.",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: ratings.averageRating = 10\n aggregate: title_count\n nest: by_title is {\n group_by: primaryTitle\n }\n}\n",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 5: Harry Potter Movies\n Write a Query to list the titles and release years of \n all Harry Potter movies, in chronological order.\n Your query should output a table with two columns, one \n for the title of each movie and one for the release year\n of each movie.\n You may assume that the title of all Harry Potter \n movies will begin with the words “Harry Potter”, \n and that if a movie title begins with the words \n “Harry Potter”, it is a Harry Potter movie.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: primaryTitle ~ 'Harry Potter%'\n project: primaryTitle, startYear\n order_by: 2\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "Question 6: Average Rating in 2012\n Write a query to determine the average rating of all movies \n released in 2012.\n Your query should output a table with a single column and \n a single row (not including the header) containing the\n average rating.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: startYear = 2012\n aggregate: average_rating is ratings.averageRating.avg()\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 7: Toy Story Who?\n Write a query to list the names of all people \n who starred in Toy Story.\n Your query should output a table with a single column for \n the name of each person. You may assume that there is only \n one movie in the database with the title Toy Story.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: primaryTitle = 'Toy Story'\n project: principals.people.primaryName\n order_by: 1\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 8: Who's Who 2004\n Write a query to list the names of all people who starred \n in a movie released in 2004, ordered by birth year.\n Your query should output a table with a single column \n for the name of each person.\n People with the same birth year may be listed in any order.\n No need to worry about people who have no birth year listed,\n so long as those who do have a birth year are listed \n in order. If a person appeared in more than one movie in 2004, \n they should only appear in your results once.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: startYear = 2004\n group_by: \n principals.people.primaryName \n principals.people.birthYear\n order_by: 1\n} ",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 9: High Directors\n Write SQL query to list the names of all people who have \n directed a movie that received a rating of at least 9.0.\n Your query should output a table with a single column for\n the name of each person. If a person directed more than \n one movie that received a rating of at least 9.0, they \n should only appear in your results once.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: ratings.averageRating >= 9\n and principals.category = 'director'\n group_by: \n principals.people.primaryName \n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 10: Chadwick Boseman?\n Write a Query to list the titles of the five highest \n rated movies (in order) that Chadwick Boseman starred \n in, starting with the highest rated.\n Your query should output a table with a single column \n for the title of each movie.\n You may assume that there is only one person in the \n database with the name Chadwick Boseman.",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: movies -> {\n where: principals.people.primaryName = 'Chadwick Boseman' \n project: \n primaryTitle\n ratings.averageRating\n order_by: 2 desc\n limit: 5\n}",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 11: Johnny and Helena?\n Write a SQL query to list the titles of all\n movies in which both Johnny Depp and Helena Bonham \n Carter starred.\n Your query should output a table with a single column \n for the title of each movie.\n You may assume that there is only one person in the \n database with the name Johnny Depp.\n You may assume that there is only one person in \n the database with the name Helena Bonham Carter.\n",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: johnnys_movies is movies ->{\n where: principals.people.primaryName = 'Johnny Depp'\n project: tconst\n}\n\nquery: movies -> {\n join_one: x is from(->johnnys_movies) with tconst\n where: x.tconst != null\n and principals.people.primaryName = 'Helena Bonham Carter'\n project: primaryTitle\n}\n",
"metadata": {}
},
{
"kind": 1,
"language": "markdown",
"value": "## Question 12: One Degree of Kevin Bacon\n Write a SQL query to list the names of all people who \n starred in a movie in which Kevin Bacon also starred.\n Your query should output a table with a single column \n for the name of each person.\n There may be multiple people named Kevin Bacon in \n the database. Be sure to only select the Kevin Bacon \n born in 1958.\n Kevin Bacon himself should not be included in \n the resulting list.",
"metadata": {}
},
{
"kind": 2,
"language": "malloy",
"value": "query: kevins_movies is movies ->{\n where: principals.people.primaryName = 'Kevin Bacon'\n and principals.people.birthYear = 1958\n project: tconst\n}\n\nquery: movies -> {\n join_one: x is from(->kevins_movies) with tconst\n where: x.tconst != null\n and principals.people.primaryName != 'Kevin Bacon'\n group_by: principals.people.primaryName\n aggregate: title_count\n}\n",
"metadata": {}
}
]