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

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.