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?
Best Answer
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.
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:
- 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)
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.