Cs50 Pset 7 13.Sql, I Can't Solve It, Nested SQLite3 Database

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

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

cs50 pset7 13.sql - remove kevin from results

Try

SELECT name 
FROM   people 
WHERE  id IN (SELECT DISTINCT person_id 
              FROM   stars 
              WHERE  movie_id IN (SELECT movie_id 
                                  FROM   stars 
                                         INNER JOIN people 
                                                 ON stars.person_id = people.id 
                                  WHERE  people.name = "kevin bacon" 
                                         AND people.birth = 1958)) 
       AND NOT ( name = "kevin bacon" 
                 AND birth = 1958 ); 

Explanation-

• First SELECT all movie_ids where "kevin bacon" (the one who was born in 1958) has starred
• Now SELECT all person_ids who've starred in the aforementioned movies
• Now SELECT the names of all these people
• Finally, exclude only "kevin bacon" (the one who was born in 1958) from this result

CS50 Pset7 Movies - 13.sql

This SELECT movie_id --Get all movie ids essentially returns a list of the movies in which KB starred. Here WHERE stars.movie_id = ( it needs to select all movie_id that are IN that "list".

Ditto here WHERE id = ( it needs to select all the id that are IN the person_id "list".

Where am I going wrong in my cs50 pset 7 10.sql nested query?

The spec says (emphasis added)

In 10.sql, write a SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.

The query does not filter on the ratings.rating column.

What is wrong with this SQL statement for cs50 problem set 7?

This statement:

select id from people where name="Johnny Depp" 
intersect 
select id from people where name="Helena Bonham Carter"

returns the common results of the 2 queries, but since id is the unique primary key of the table people there is no case that it will return anything.

Simpler: if the 1st query returns 10 and the 2nd returns 20 the intersection of 10 and 20 does not exist.

You can get the result that you want with joins:

select distinct m.title
from movies m
inner join stars s1 on s1.movie_id = m.id 
inner join stars s2 on s2.movie_id = m.id 
inner join people p1 on p1.id = s1.person_id and p1.name = 'Johnny Depp'
inner join people p2 on p2.id = s2.person_id and p2.name = 'Helena Bonham Carter'

Why is this SQL query not working? CS50 Pset7 - movies

If you count(distinct person_id), then you will get 18013. It's reasonable that name is not unique. What is unreasonable is the directive in the exam saying you should list only name.

One way to correctly distinct the names is to execute this:

SELECT p.name
from people p
where p.id in (
select distinct s.person_id
from stars s join movies m on s.movie_id = m.id
WHERE m.year = 2004)

And if you do it that way, then you don't even need the distinct due to the definition of the in operator. But you probably will get the same execution plan regardless.

It's ok, in my opinion, to list a p.name more than once if it belongs to another person. The query you wrote would have been ok if the rule started with these words:

If a person's name ...

instead of these words:

If a person ...

Which reminds me of something C. J. Date did in class one day. He put a foil on his projector that projected an image of a smoking pipe on the wall. He then asked: what is this?

• A pipe, one guy said (probably me).
• An image of a pipe another guy said.
• Finally, someone said an image of a pipe projected on a wall.

Since it was a database class and not a physics class, nobody dared to be a smart-a**.

Why does my SQL script select too many outputs (CS50 pset7 sql.13)?

Something like this would work in postgres. May need to adapt to your database.

    select name
    from (
        with kb_movies as
            (select distinct movies.id as kb_movie_id
            from movies
            join stars 
                on stars.movie_id = movies.id
            join people 
                on people.id = stars.people_id
            where people.name = 'Kevin Bacon'
            and people.birth = '1958' --or 1958
            )
            select distinct people.name
            from people
            join stars 
                on stars.people_id = people.id
            join movies 
                on movies.id = stars.movie_id
            join kb_movies 
                on kb_movie_id = movies.id
        )z
    where name <> 'Kevin Bacon'

---

Related Topics