Think Summer: Project 2 — 2023

Submission

Students need to submit the following file by 10:00PM EST through Gradescope inside Brightspace.

  1. A Jupyter notebook (a .ipynb file).

We’ve provided you with a template notebook for you to use. Please carefully read this section to get started.

When you are finished with the project, please make sure to run every cell in the notebook prior to submitting. To do this click Run  Run All Cells. Next, to export your notebook (your .ipynb file), click on File  Download, and download your .ipynb file.

Questions

Question 1

How many crews include George Lucas in the role of director?

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 2

How many shows have more than 10000 episodes? Hint: Use the episodes table, and GROUP BY the show_title_id and use the condition HAVING COUNT(*) > 10000 at the end of the query.

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 3

What are the 5 most popular episodes of Friends? Please include the title of each episode. Please verify your answer by double-checking with IMDB.

Hint: Friends has show_title_id = tt0108778.

Another hint: When you join the episodes table and the ratings table, you might want to add the condition e.episode_title_id = r.title_id

Another hint: You might want to have ORDER BY r.rating DESC LIMIT 3 at the end of your query, so that you are ordering the results by the ratings, and putting them in descending order (with the biggest at the top).

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 4

Identify the 6 movies that have rating 9 or higher and have 50000 or more votes.

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 5

For how many movies has Sean Connery been on the crew?

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 6

Revisiting the question about George Lucas: What are the titles of the movies in which George Lucas had the role of director?

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 7

Revisiting the question about the shows that have more than 10000 episodes, please find the primary_title of these shows.

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 8

Revisiting the question about how many movies has Sean Connery been on the crew: Which movie is his most popular movie?

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 9

Again revisiting the previous question about the popularity of movies for which Sean Connery has been on the crew: Which movie is his most popular movie, if we limit our query to results with 1000 or more votes? What is the title of the most popular result?

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)

Question 10

Create your own interesting question about movie(s) or TV show(s) or actor(s)/actress(es) and try to solve this question. What insights can you find?

Items to submit

  • SQL queries used to solve this problem. (2 pts)

  • Output from running the SQL queries. (1 pt)