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

Question

I am currently trying to find the names of all people who have directed a movie that received a rating of at least 9.0 the scheme for these tables is

CREATE TABLE movies (

                    id INTEGER,
                    title TEXT NOT NULL,
                    year NUMERIC,
                    PRIMARY KEY(id)
                );
CREATE TABLE stars (

                movie_id INTEGER NOT NULL,
                person_id INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id),
                FOREIGN KEY(person_id) REFERENCES people(id)
            );
CREATE TABLE directors (

                movie_id INTEGER NOT NULL,
                person_id INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id),
                FOREIGN KEY(person_id) REFERENCES people(id)
            );
CREATE TABLE ratings (

                movie_id INTEGER NOT NULL,
                rating REAL NOT NULL,
                votes INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id)
            );
CREATE TABLE people (

                id INTEGER,
                name TEXT NOT NULL,
                birth NUMERIC,
                PRIMARY KEY(id)
            );

MY SQL QUERY IS :

SELECT DISTINCT name FROM people 
WHERE id IN ( SELECT person_id FROM directors WHERE movie_id IN ( 
    SELECT id FROM movies WHERE id IN (
        SELECT movie_id FROM ratings WHERE votes >= 9.0)));

This however fails the check50 test, and gives incorrect output. Can anyone help me with where I'm going wrong?

question from:https://stackoverflow.com/questions/65881284/where-am-i-going-wrong-in-my-cs50-pset-7-10-sql-nested-query

Answer 1

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.