Overview

Today, we’ll learn basic/advanced SQL by poking at some IMDb data. We’ll see how many exercises we can get through in class. The rest are left to the reader as good practice for a future exam. No grading here, please work with those around you.

Specification

Examing the following IMDb schema, then try to complete the exercises using sqlite3. To work on this, please use the devcontainer from Bootcamp 1. You will look in ./sql to get started.

We’ll use a random sample of the full IMDb. If you are curious, the full dataset is here.

Getting Started

From the ./sql directory, setup the database, then 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 < setup.sql
$ sqlite3 imdb-cmudb2022.db
SQLite version 3.31.1
Enter ".help" for usage hints.
sqlite> .tables
akas      crew      episodes  people    ratings   titles

Our setup script creates the following indexes for you:

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);

Without these, your queries would run super slow. Why? Keep coming to class.

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 “1915”, 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

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 this 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

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"]

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

Construct the SQL Queries

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

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

af
ar
az
be
bg
bn
bs
ca
cmn

Ok your turn…