CS50 Pset 7 13.sql, I can’t solve it, nested sqlite3 database

DataBase movies.db

tables

directors (movie_id, person_id)

movies (id, title, year)

people (id, name, birth)

ratings (movie_id, rating, votes)

stars (movie_id, person_id)

you can download the database.

Question

I’m new in programming, so I decided, to begin with, CS50 Harvard course here is the problem and the test solution:

In 13.sql, write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred. Your query should output a table with a single column for the name of each person. There may be multiple people named Kevin Bacon in the database. Be sure to only select the Kevin Bacon born in 1958. Kevin Bacon himself should not be included in the resulting list.

solution hint

Executing 13.sql results in a table with 1 column and 176 rows.

executing code in PowerShell or bash cat 13.sql | sqlite3 movies.db

My Code:

SELECT COUNT(name)
FROM people 
JOIN stars ON stars.person_id = people.id 
JOIN movies ON movies.id = stars.movie_id 
WHERE people.id IN(
SELECT stars.person_id 
FROM stars 
GROUP BY stars.person_id 
HAVING name = "Kevin Bacon");

problem:

when I execute code it stop working, I can’t choose stars with Kevin

Answer

Get values in top-bottom hierarchy but make sure that you’re searching for it in the correct domain. GET the name which is the first thing needed but search for it in the correct domain which is dependent on person_id, in turn to movie_id. Finally to invoke the condition, we’ve to recall people.id as the condition is dependent on data in the people table. It’s essential to do the needed JOINs at each step.

To remove Kevin Bacon from results, you can use the EXCEPT keyword and specifically choose him in the other query.

SELECT name FROM people WHERE people.id
IN
( SELECT person_id FROM stars JOIN movies ON movies.id = stars.movie_id 
WHERE movie_id IN
( SELECT movie_id FROM movies JOIN stars ON stars.movie_id = movies.id JOIN people ON 
 people.id = stars.person_id WHERE people.id IN (
SELECT id FROM people WHERE people.name = "Kevin Bacon" AND people.birth = 1958 )))
EXCEPT
SELECT name FROM people WHERE people.name = "Kevin Bacon" AND people.birth = 1958