Homework #1 - SQL

Overview

The first homework is to construct a set of SQL queries for analysing a dataset that will be provided to you. For this, you will look into IMDB data. This homework is an opportunity to: (1) learn basic and certain advanced SQL features, and (2) get familiar with using a full-featured DBMS, SQLite, that can be useful for you in the future.

This is a single-person project that will be completed individually (i.e., no groups).

  • Release Date: Sep 01, 2022
  • Due Date: Sep 11, 2022 @ 11:59pm

Specification

The homework contains 10 questions in total and is graded out of 100 points. For each question, you will need to construct a SQL query that fetches the desired data from the SQLite DBMS. It will likely take you approximately 6-8 hours to complete the questions.

Placeholder Folder

Create the placeholder submission folder with the empty SQL files that you will use for each question:

$ mkdir placeholder
$ cd placeholder
$ touch \
  q1_sample.sql \
  q2_sci_fi.sql \
  q3_oldest_people.sql \
  q4_crew_appears_most.sql \
  q5_decade_ratings.sql \
  q6_cruiseing_altitude.sql \
  q7_year_of_thieves.sql \
  q8_kidman_colleagues.sql \
  q9_9th_decile_ratings.sql \
  q10_house_of_the_dragon.sql
$ cd ..

After filling in the queries, you can compress the folder by running the following command:

$ zip -j submission.zip placeholder/*.sql

The -j flag lets you compress all the SQL queries in the zip file without path information. The grading scripts will not work correctly unless you do this.

Instructions

Setting Up SQLite

You will first need to install SQLite on your development machine.

Make sure that you are using at least SQLite version 3.25! Older releases (prior to 2019) will not support the SQL features that you need to complete this assignment.

Install SQLite3 on Ubuntu Linux

Please follow the instructions.

Install SQLite3 on Mac OS X

On Mac OS Leopard or later, you don't have to! It comes pre-installed. You can upgrade it, if you absolutely need to, with Homebrew.

Load the Database Dump

  1. Check if sqlite3 is properly working by following this tutorial.

  2. Download the database dump file:

    $ wget https://15445.courses.cs.cmu.edu/fall2022/files/imdb-cmudb2022.db.gz

    Check its MD5 checksum to ensure that you have correctly downloaded the file:

    $ md5sum imdb-cmudb2022.db.gz
    d7cdf34f4ba029597c3774fc96bc3519  imdb-cmudb2022.db.gz

  3. Unzip the database from the provided database dump by running the following commands on your shell. Note that the database file be 836MB after you decompress it.

    $ gunzip imdb-cmudb2022.db.gz
    $ sqlite3 imdb-cmudb2022.db

  4. We have prepared a random sample of the original dataset for this assignment. Although this is not required to complete the assignment, the complete dataset is available by following the steps here.

  5. Check the contents of the database by running the .tables command on the sqlite3 terminal. You should see 6 tables, and the output should look like this:

    $ sqlite3 imdb-cmudb2022.db
    SQLite version 3.31.1
    Enter ".help" for usage hints.
    sqlite> .tables
    akas      crew      episodes  people    ratings   titles

  6. Create indices using the following commands in SQLite:

    CREATE INDEX ix_people_name ON people (name);
    CREATE INDEX ix_titles_type ON titles (type);
    CREATE INDEX ix_titles_primary_title ON titles (primary_title);
    CREATE INDEX ix_titles_original_title ON titles (original_title);
    CREATE INDEX ix_akas_title_id ON akas (title_id);
    CREATE INDEX ix_akas_title ON akas (title);
    CREATE INDEX ix_crew_title_id ON crew (title_id);
    CREATE INDEX ix_crew_person_id ON crew (person_id);

Check the schema

Get familiar with the schema (structure) of the tables (what attributes do they contain, what are the primary and foreign keys). Run the .schema $TABLE_NAME command on the sqlite3 terminal for each table. The output should look like the example below for each table.

PEOPLE

sqlite> .schema people
CREATE TABLE people (
  person_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  born INTEGER,
  died INTEGER
);
CREATE INDEX ix_people_name ON people (name);

Contains details for a person. For example, this is a row from the table:

nm0000006|Ingrid Bergman|1915|1982

To breakdown the row, the field person_id corresponds to "nm0000006", name corresponds to "Ingrid Bergman", born corresponds to "1981", and died corresponds to "1982".

TITLES

sqlite> .schema titles
CREATE TABLE titles (
  title_id VARCHAR PRIMARY KEY,
  type VARCHAR,
  primary_title VARCHAR,
  original_title VARCHAR,
  is_adult INTEGER,
  premiered INTEGER,
  ended INTEGER,
  runtime_minutes INTEGER,
  genres VARCHAR
);
CREATE INDEX ix_titles_type ON titles (type);
CREATE INDEX ix_titles_primary_title ON titles (primary_title);
CREATE INDEX ix_titles_original_title ON titles (original_title);

Contains details of a title. For example, this is a row from the table:

tt0088763|movie|Back to the Future|Back to the Future|0|1985||116|Adventure,Comedy,Sci-Fi

For this assignment, we will focus on the fields title_id (e.g. "tt0088763"), type (e.g. "movie"), primary_title (e.g. "Back to the Future"), premiered (e.g. "1985"), and genres (e.g. "Adventure,Comedy,Sci-Fi").

Titles may also be referred to as "works" in the assignment specification.

AKAS

sqlite> .schema akas
CREATE TABLE akas (
  title_id VARCHAR, -- REFERENCES titles (title_id),
  title VARCHAR,
  region VARCHAR,
  language VARCHAR,
  types VARCHAR,
  attributes VARCHAR,
  is_original_title INTEGER
);
CREATE INDEX ix_akas_title_id ON akas (title_id);
CREATE INDEX ix_akas_title ON akas (title);

This table contains the alternate titles for the dubbed movies. Note that title_id in this table corresponds to title_id in titles. For example, this is a row in the table:

tt0015648|El acorazado Potemkin|XSA|es|imdbDisplay||0

For this assignment, we will not use the fields region, types, attributes or is_original_title.

Note that title_id in this table corresponds to title_id in titles.

CREW

sqlite> .schema crew
CREATE TABLE crew (
  title_id VARCHAR, -- REFERENCES titles (title_id),
  person_id VARCHAR, -- REFERENCES people (person_id),
  category VARCHAR,
  job VARCHAR,
  characters VARCHAR
);
CREATE INDEX ix_crew_title_id ON crew (title_id);
CREATE INDEX ix_crew_person_id ON crew (person_id);

Contains the details of the cast of the title. For example, this is a row from the table:

tt0000886|nm0609814|actor||["Hamlet"]

For this assignment, we will not use the fields job or characters. When considering the role of an individual on the crew, refer to the field category.

Note that title_id corresponds to title_id in titles and person_id corresponds to person_id in people.

RATINGS

sqlite> .schema ratings
CREATE TABLE ratings (
  title_id VARCHAR PRIMARY KEY, -- REFERENCES titles (title_id),
  rating FLOAT,
  votes INTEGER
);

Contains the ratings for each title. For example, this is a row from the table:

tt0000803|6.0|8

Note that title_id in this table corresponds to title_id in titles.

EPISODES

While the table episodes is included in our sample dataset, you should not need to reference this table.

Sanity Check

Count the number of rows in the titles table

sqlite> select count(*) from titles;
1375462

schema

Construct the SQL Queries

Now, it's time to start constructing the SQL queries and put them into the placeholder files.

Q1 [0 points] (q1_sample):

The purpose of this query is to make sure that the formatting of your output matches exactly the formatting of our auto-grading script.

Details: List all Category Names ordered alphabetically.

Answer: Here's the correct SQL query and expected output:

sqlite> SELECT DISTINCT(language)
   ...> FROM akas
   ...> ORDER BY language
   ...> LIMIT 10;

af
ar
az
be
bg
bn
bs
ca
cmn

You should put this SQL query into the appropriate file (q1_sample.sql) in the submission directory (placeholder). The empty line in the result is there on purpose! A NULL value shows up as a blank line and is part of the intended output

Q2 [5 points] (q2_sci_fi):

Find the 10 `Sci-Fi` works with the longest runtimes.

Details: Print the title of the work, the premiere date, and the runtime. The column listing the runtime should be suffixed with the string " (mins)", for example, if the runtime_mins value is `12`, you should output 12 (mins). Note a work is Sci-Fi even if it is categorized in multiple genres, as long as Sci-Fi is one of the genres.
Your first row should look like this: Cicak-Man 2: Planet Hitam|2008|999 (mins)

Q3 [5 points] (q3_oldest_people):

Determine the oldest people in the dataset who were born in or after 1900. You should assume that a person without a known death year is still alive.

Details: Print the name and age of each person. People should be ordered by a compound value of their age and secondly their name in alphabetical order. Return the first 20 results.
Your output should have the format: NAME|AGE

Q4 [10 points] (q4_crew_appears_most):

Find the people who appear most frequently as crew members.

Details: Print the names and number of appearances of the 20 people with the most crew appearances ordered by their number of appearances in a descending fashion.
Your output should look like this: NAME|NUM_APPEARANCES

Q5 [10 points] (q5_decade_ratings):

Compute intersting statistics on the ratings of content on a per-decade basis.

Details: Get the average rating (rounded to two decimal places), top rating, min rating, and the number of releases in each decade. Exclude titles which have not been premiered (i.e. where premiered is NULL). Print the relevant decade in a fancier format by constructing a string that looks like this: 1990s. Order the decades first by their average rating in a descending fashion and secondly by the decade, ascending, to break ties.
Your output should have the format: DECADE|AVG_RATING|TOP_RATING|MIN_RATING|NUM_RELEASES

Q6 [10 points] (q6_cruiseing_altitude):

Determine the most popular works with a person who has "Cruise" in their name and is born in 1962.

Details: Get the works with the most votes that have a person in the crew with "Cruise" in their name who was born in 1962. Return both the name of the work and the number of votes and only list the top 10 results in order from most to least votes. Make sure your output is formatted as follows: Top Gun|408389

Q7 [15 points] (q7_year_of_thieves):

List the number of works that premiered in the same year that "Army of Thieves" premiered.

Details: Print only the total number of works. The answer should include "Army of Thieves" itself. For this question, determine distinct works by their title_id, not their names.

Q8 [15 points] (q8_kidman_colleagues):

List the all the different actors and actresses who have starred in a work with Nicole Kidman (born in 1967).

Details: Print only the names of the actors and actresses in alphabetical order. The answer should include Nicole Kidman herself. Each name should only appear once in the output.
Note: As mentioned in the schema, when considering the role of an individual on the crew, refer to the field category. The roles "actor" and "actress" are different and should be accounted for as such.

Q9 [15 points] (q9_9th_decile_ratings):

For all people born in 1955, get their name and average rating on all movies they have been part of through their careers. Output the 9th decile of individuals as measured by their average career movie rating.

Details: Calculate average ratings for each individual born in 1955 across only the movies they have been part of. Compute the quantiles for each individual's average rating using NTILE(10).
Make sure your output is formatted as follows (round average rating to the nearest hundredth, results should be ordered by a compound value of their ratings descending and secondly their name in alphabetical order): Stanley Nelson|7.13
Note: You should take quantiles after processing the average career movie rating of individuals. In other words, find the individuals who have an average career movie rating in the 9th decile of all individuals.

Q10 [15 points] (q10_house_of_the_dragon):

Concatenate all the unique titles for the TV Series "House of the Dragon" as a string of comma-separated values in alphabetical order of the titles.

Details: Find all the unique dubbed titles for the new TV show "House of the Dragon" and order them alphabetically. Print a single string containing all these titles separated by commas.
Hint: You might find Recursive CTEs useful.
Note: Two titles are different even if they differ only in capitalization. Elements in the comma-separated value result should be separated with both a comma and a space, e.g. "foo, bar".

Grading Rubric

Each submission will be graded based on whether the SQL queries fetch the expected sets of tuples from the database. Note that your SQL queries will be auto-graded by comparing their outputs (i.e. tuple sets) to the correct outputs. For your queries, the order of the output columns is important; their names are not.

Late Policy

See the late policy in the syllabus.

Submission

We use the Autograder from Gradescope for grading in order to provide you with immediate feedback. After completing the homework, you can submit your compressed folder submission.zip (only one file) to Gradescope:

Important: Use the Gradescope course code announced on Piazza.

We will be comparing the output files using a function similar to diff. You can submit your answers as many times as you like.

Collaboration Policy

  • Every student has to work individually on this assignment.
  • Students are allowed to discuss high-level details about the project with others.
  • Students are not allowed to copy the contents of a white-board after a group meeting with other students.
  • Students are not allowed to copy the solutions from another colleague.

WARNING: All of the code for this project must be your own. You may not copy source code from other students or other sources that you find on the web. Plagiarism will not be tolerated. See CMU's Policy on Academic Integrity for additional information.