TDM 20100: Project 10 — Fall 2026

Introduction to Databases (Part 2)

Project Objectives

Motivation: We will continue to learn about databases, which allow you to write queries about data.

Context: We will focus on SQLite. Once you understand how to make queries in SQLite, you will have a strong foundation that can be used to learn other database resources, such as MySQL.

Scope: SQLite queries that use JOIN

Learning Objectives

  • We will learn how to make SQLite queries on multiple tables at the same time (using one or more `JOIN`s)

Dataset

  • /anvil/projects/tdm/data/movies_and_tv/imdbSept2026.db

Use 4 cores for this project!

First read our continued notes about SQL (part 2) here:

the-examples-book.com/projects/fall2026/20100/sqlexamples2

Question 1

Which of George Clooney’s movies are the most popular? (By "popularity", you can choose to either analyze the ratings or the number of votes; either way is OK with us! Just be sure to explain what you did in your solution.)

Question 2

Identify the six most popular episodes of the show Grey’s Anatomy (where "popular" denotes a high rating).

Question 3

  1. Use LIKE to analyze the primaryTitle of all IMDB titles: First determine how many titles have Batman anywhere in the title, and then determine how many titles have Superman anywhere in the title? Which one occurs more often?

  2. How many titles have Adventure as one of the genres? In which year did the most titles appear, with Adventure as one of the genres?

Question 4

  1. Who was the Director of the movie Say Anything…​? Hint: Start by finding the movie title Say Anything…​ in the basics table. Then join the basics table to the crew table, to find out which person was the director of Say Anything…​. (P.S. This is Dr Ward’s all-time favorite movie.)

  2. Starting with only the nconst of this Director, use (only one) SQL query to find their name and also the titles of all of the movies that they have directed in their career.

Question 5

  1. Join the basics table and the ratings table, to see how many rows have tvEpisode as the titleType and have more than 200000 votes.

  2. How many episodes of The West Wing had rating 9 or higher?

Submitting your Work

Please make sure that you added comments for each question, which explain your thinking about your method of solving each question. Please also make sure that your work is your own work, and that any outside sources (people, internet pages, generative AI, etc.) are cited properly in the project template.

Prior to submitting your work, you need to put your work into the project template, and re-run all of the code in Jupyter Lab and make sure that the results of running that code is visible in your template. Please check the detailed instructions on how to ensure that your submission is formatted correctly. To download your completed project, you can right-click on the file in the file explorer and click 'download'.

Items to submit

  • firstname_lastname_project10.ipynb

It is necessary to document your work, with comments about each solution. All of your work needs to be your own work, with citations to any source that you used. Please make sure that your work is your own work, and that any outside sources (people, internet pages, generative AI, etc.) are cited properly in the project template.

You must double check your .ipynb after submitting it in gradescope. A very common mistake is to assume that your .ipynb file has been rendered properly and contains your code, markdown, and code output even though it may not.

Please take the time to double check your work. See here for instructions on how to double check this.

You will not receive full credit if your .ipynb file does not contain all of the information you expect it to, or if it does not render properly in Gradescope. Please ask a TA if you need help with this.