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

Question

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

I think the question is asking you to get a list of names that are people who were in movies with Kevin Bacon, not a count of them. I also think you need two references to the people table, one to get Kevin Bacon's list of movies and the other to get the people who were stars in them. — David Parvin, Mar 17 '20 at 23:36

score 1 · Answer 1 · answered Mar 22 '20 at 19:43

1

I found these steps helpful:

Get the ID of Kevin Bacon, with the criteria that it's the Kevin Bacon who was born in 1958
Get the movie IDs of Kevin Bacon using his ID (hint: linking his ID in table1 with table2)
Get other stars' IDs with the same movie IDs
Get the name of these stars, and exclude Kevin Bacon (because the spec says he shouldn't be included in the resulting list)

Note: In the first line of your code, instead of COUNT(name), you can use SELECT name to get the people's names

answered Mar 22 '20 at 19:43

Zara

21
1

First of all, Thank you very much, and in step 3 I can't Get other stars' IDs with the same movie Is, that's the problem I tried subquery but unfortunately FAILED. I add a count to know the count of stars "it's in solution hint above 176 rows" – MrAhmedElsayed Mar 24 '20 at 15:48
I think it's because you're mixing person.id and people.id. It would be something like `SELECT movie_id FROM stars WHERE person_id = (your next condition)` (I don't have enough reputation to add comment in your other thread so I'm adding it here) – Zara Mar 26 '20 at 18:16

score 1 · Accepted Answer · answered Apr 21 '20 at 11:41

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

kj9849 · Answer 3 · 2020-06-04T03:04:52.633

I solved this problem by using DISTINCT and NOT EQUAL operator in SQL.

Here are the steps I took:

SELECT DISTINCT(name)
FROM people
JOIN stars ON stars.person_id = people.id
JOIN movies ON movies.id = stars.movie_id
WHERE movies.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.name = "Kevin Bacon"
       AND people.birth = 1958)
  AND people.name != "Kevin Bacon";

Result is 176 distinct rows of names.

score 1 · Answer 4 · answered Dec 05 '22 at 21:10

I also solved this problem without using JOIN and merely using nested queries:

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

I have tried to avoid using nested queries but really I cannot get into the right path without using nested queries.

My code goes like this:

Get the id of Kevin Bacon who was born in 1958. (returns the id Kevin Bacon)
Get all the movies that Kevin Bacon starred. (returns a list of movies starred by Kevin Bacon)
Get all the person_ids related to each of the movies (returns a list of person_ids for each movie including Kevin Bacon's id)
Except to exclude Kevin Bacon himself.

score 0 · Answer 5 · answered Mar 25 '20 at 16:00

I try the steps in Zara answer:

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

Result:

I must have done the wrong steps to get this result

Expected 176 rows but got one row "Steve Guttenberg"

score 0 · Answer 6 · answered Jun 02 '20 at 16:43

I did it without using any Join, just nesting the conditions.

SELECT people.name FROM people
WHERE people.id IN 
(
    SELECT stars.person_id FROM stars
    WHERE stars.movie_id IN 
    (
        SELECT stars.movie_id FROM stars
        WHERE stars.person_id IN
        (
            SELECT people.id FROM people
            WHERE people.name = "Kevin Bacon" AND 
            people.birth = 1958
        )
    )
)
AND people.name != "Kevin Bacon" 
GROUP BY people.name

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

6 Answers6

Linked