CS50 Solution pset7 movies

1.sql

SELECT title FROM movies
WHERE year == 2008

10.sql

SELECT name FROM people
JOIN directors ON people.id = directors.person_id
JOIN ratings ON directors.movie_id = ratings.movie_id
WHERE ratings.rating >= 9.0

11.sql

SELECT movies.title FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
JOIN ratings ON movies.id = ratings.movie_id
WHERE name = "Chadwick Boseman"
ORDER BY rating DESC
LIMIT 5

12.sql

SELECT movies.title FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE people.name = "Johnny Depp" AND movies.title IN(
SELECT movies.title FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE people.name = "Helena Bonham Carter")

13.sql

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

2.sql

SELECT birth FROM people
WHERE name == "Emma Stone"

3.sql

SELECT title FROM movies
WHERE year >= 2018
ORDER BY title

4.sql

SELECT COUNT(title) FROM movies
JOIN ratings ON movies.id = ratings.movie_id
WHERE rating == 10

5.sql

SELECT title, year FROM movies
WHERE title LIKE "Harry Potter%"
ORDER BY year

6.sql

SELECT AVG(rating) FROM ratings
JOIN movies ON ratings.movie_id = movies.id 
WHERE year = 2012;

7.sql

SELECT movies.title, ratings.rating FROM movies
JOIN ratings ON movies.id = ratings.movie_id
WHERE year = 2010
ORDER BY rating DESC, title

8.sql

SELECT name FROM people
JOIN stars ON people.id = person_id
JOIN movies ON movie_id = movies.id
WHERE movies.title = "Toy Story"

9.sql

SELECT name FROM people
JOIN stars ON stars.person_id = people.id
JOIN movies ON stars.movie_id = movies.id
WHERE movies.year = 2004
ORDER BY people.birth

IT helps me a lot

For the q12, using INTERSECT is also a good option.

it helped me a lot
note: you can get as twice as fast result in 12.sql with this subtle change:

SELECT title FROM movies WHERE movies.id IN             -- This approach (matching by movies.id instead of movies.title) is more efficient
(SELECT movies.id FROM movies 
JOIN stars ON stars.movie_id = movies.id 
JOIN people ON people.id = stars.person_id 
WHERE people.name = 'Helena Bonham Carter' AND movies.id IN
(SELECT movies.id FROM movies 
JOIN stars ON stars.movie_id = movies.id 
JOIN people ON people.id = stars.person_id 
WHERE people.name = 'Johnny Depp'));

10.sql is wrong, you are getting every names (not even distincts) but we want every distinct directors (based on ID not name as there may be namesakes). Here is my corrected version:

SELECT name FROM people 
WHERE people.id IN 
(
    SELECT DISTINCT(people.id) FROM people
    JOIN directors ON directors.person_id = people.id
    JOIN movies ON movies.id = directors.movie_id
    JOIN ratings ON ratings.movie_id = movies.id
    WHERE rating >= 9.0
);

13.sql is also wrong, it is more subtle, but you are again getting actors who act in films namesakes of films starring Kevin Bacon. I also had that mistake and realized it when I saw Udo Lindenberg (German singer) in my list. He acted in a movie called Super in 1984 which is clearly not the same as Super of 2010 starring Kevin Bacon. Here is how to fix this, replace the filtering on the film name by a filtering on the film ID.

SELECT DISTINCT name FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON movies.id = stars.movie_id
WHERE movies.id IN
(
    SELECT movies.id FROM movies
    JOIN stars ON movies.id = stars.movie_id
    JOIN people ON stars.person_id = people.id
    WHERE name = 'Kevin Bacon' AND birth = 1958
)
AND name != 'Kevin Bacon';

9.sql is wrong as there are duplicates

9.sql is wrong as there are duplicates

Yes, instead it should be

SELECT DISTINCT name FROM people
JOIN stars ON stars.person_id = people.id
JOIN movies ON movies.id = stars.movie_id
WHERE year = 2004
ORDER BY birth

9.sql is wrong as there are duplicates

Yes, instead it should be

SELECT DISTINCT name FROM people
JOIN stars ON stars.person_id = people.id
JOIN movies ON movies.id = stars.movie_id
WHERE year = 2004
ORDER BY birth

This doesn't work. Using COUNT(DISTINCT name), you see that the returned value is actually 19261, 64 names short of the 19325 that CS50 says are in the database. I had actually tried the above method before arriving here, so if you know something I don't I'd appreciate you educating me.

why is my 9.sql wrong please help me

SELECT DISTINCT name FROM people WHERE id IN
(SELECT person_id FROM stars WHERE movie_id IN
(SELECT id FROM movies WHERE year = 2004))
ORDER BY birth ASC;

it prints 19261 values ​​instead of 19325 values

i believe that check50 is wrong because it is counting duplicates

but if I remove "DISTINCT" it prints out exactly 19325 values ​​as CS50 requires, I can't understand =))

So are we expected to submit a wrong SQL query, based on the task, because check50 / submit50 are not excluding duplicates?

9.sql is wrong as there are duplicates

Yes, instead it should be

SELECT DISTINCT name FROM people
JOIN stars ON stars.person_id = people.id
JOIN movies ON movies.id = stars.movie_id
WHERE year = 2004
ORDER BY birth

This doesn't work. Using COUNT(DISTINCT name), you see that the returned value is actually 19261, 64 names short of the 19325 that CS50 says are in the database. I had actually tried the above method before arriving here, so if you know something I don't I'd appreciate you educating me.

So weird thing is, this query DOES return a positive result in check50. I submitted it and got full marks. If someone brave would like to contact the course makers and inform them, feel free. I don't actually know how you'd go about that though.