1 of 18

CS50 for MBAs

SQL

2 of 18

Learning Objectives

Learn how to answer questions via SQL.

Learn how to SELECT data from a relational database.
Learn how to JOIN tables on keys.
Learn how to nest queries.

3 of 18

DB Browser for SQLite

https://sqlitebrowser.org/dl/

4 of 18

movies.db

https://cdn.cs50.net/hbs/2020/spring/labs/4/movies.db

5 of 18

Tables

movies
people
stars
directors
ratings

6 of 18

What are the titles of all of the movies in which Kevin Bacon (b. 1958) starred?

Select Kevin Bacon's ID.
Select the IDs of all movies associated with that ID.
Select the titles of all movies associated with those IDs.

7 of 18

What are the titles of all of the movies in which Kevin Bacon (b. 1958) starred?

Select Kevin Bacon's ID.
Select the IDs of all movies associated with that ID.
Select the titles of all movies associated with those IDs.

8 of 18

What are the titles of all of the movies in which Kevin Bacon (b. 1958) starred?

Select Kevin Bacon's ID.
Select the IDs of all movies associated with that ID.
Select the titles of all movies associated with those IDs.

9 of 18

What are the titles of all of the movies in which Kevin Bacon (b. 1958) starred?

Select Kevin Bacon's ID.
Select the IDs of all movies associated with that ID.
Select the titles of all movies associated with those IDs.

10 of 18

Nested SELECTs

SELECT title FROM movies WHERE id IN�(SELECT movie_id FROM stars WHERE person_id =�(SELECT id FROM people WHERE name = "Kevin Bacon" and birth = 1958));

11 of 18

Implicit JOINs

SELECT title FROM movies, stars, people WHERE�people.name = "Kevin Bacon" and people.birth = 1958 AND�stars.person_id = people.id AND�stars.movie_id = movies.id;

12 of 18

Explicit JOINs

SELECT title FROM movies �JOIN stars ON movies.id = stars.movie_id�JOIN people on stars.person_id = people.id WHERE�people.name = "Kevin Bacon" and people.birth = 1958 AND�stars.person_id = people.id AND�stars.movie_id = movies.id;