3

I am currently working of CS50 PSET7 (https://cs50.harvard.edu/x/2020/psets/7/movies/) and I CAN NOT figure out how to do 12.sql and 13.sql (explained in link). Can someone PLEASE help me?

Kalyn Muhlenberg
  • 31
  • 1
  • 3
  • I know this is an educational exercise, but please provide more detail on the table structure for each question. The community is trying to help, but you have to at least give us some information other than a link to your homework/class. – Ray K May 06 '20 at 01:54

3 Answers3

2

For 12.sql: Find movie titles where 'id' in "id's of Johnny Depp movies" and 'id' in "id's of Helena Bonham Carter movies", such as:

SELECT "title" FROM "movies" 
WHERE "id" IN (-- code to select movie id's in which "Johnny Depp" starred)
AND "id" IN (-- code to select movie id's in which "Helena Bonham Carter" starred);

For 13.sql: Find names of people where "person_id's" in "stars" correspond to the "movie_id" in which "Kevin Bacon (born: 1958)" starred, and names != "Kevin Bacon", such as: 

SELECT "name" FROM "people"
WHERE "id" IN 
(-- select "person id's" from "stars" where "movie id" in
(-- select "movie id's" in which "Kevin Bacon (born: 1958)" starred))
AND "name" != "Kevin Bacon";

Inside the second brackets of 13.sql, to query "Kevin Bacon born in 1958", you can write some code like this:

... WHERE "people"."name" = "Kevin Bacon" AND "people"."birth" = 1958))...

Think simple, no need to do anything fancy.

Guest Star
  • 21
  • 3
  • Looks good - especially for what the asker gave us. Only addition to 13 is a where statement to include the need for the birthday check "There may be multiple people named Kevin Bacon / Assure you use the Kevin Bacon born in 1958" – Ray K May 06 '20 at 01:56
  • 1
    I didn't mention it because that query should be in the second brackets. But good point, people may miss that. So I'm editing the answer. Thx. – Guest Star May 06 '20 at 12:51
1

12.sql

Consider using HAVING COUNT()

https://www.w3resource.com/sql/aggregate-functions/count-having.php


13.sql

As I've also answered in another thread, I found these steps helpful:

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

For both of these Psets you need to use nested SELECT statements e.g.:

SELECT table.column FROM table WHERE table.column IN (SELECT table.column2 FROM table WHERE ...)

Based on my experience for 12 you will need to use 2 separate nested queries (each of which should have multiple values) and then use an AND operator to find movies that appear in both of these.

For 13 I found using several nested queries helped, starting with finding the id for Kevin Bacon and working up to selecting people. name values from a query that contained multiple possible people.id values.

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Batfish
  • 1