Question

What is the SQL query to select the name of movies directed by Steven Spielberg?

1) SELECT M.name FROM Movie M JOIN Director D ON M.id = D.movie_id JOIN Person P ON D.director_id = P.id WHERE P.name LIKE 'Steven Spielberg';
2) SELECT M.name FROM Movie M JOIN Director D ON M.id = D.movie_id JOIN Person P ON D.director_id = P.id WHERE P.name = 'Steven Spielberg';
3) SELECT M.name FROM Movie M JOIN Director D ON M.id = D.movie_id JOIN Person P ON D.director_id = P.id WHERE P.name = 'Steven Spielberg' AND M.director_id = D.id;
4) SELECT M.name FROM Movie M JOIN Director D ON M.id = D.movie_id JOIN Person P ON D.director_id = P.id WHERE P.name LIKE '%Steven Spielberg%';

Answer 1

The correct SQL query to select movies directed by Steven Spielberg is: SELECT M.name FROM Movie M JOIN Director D ON M.id = D.movie_id JOIN Person P ON D.director_id = P.id WHERE P.name = 'Steven Spielberg'; This query joins the Movie, Director, and Person tables, filtering the results based on the director's exact name.

Step-by-step explanation:

To select the name of movies directed by Steven Spielberg using SQL, you should look for a query that correctly joins the relevant tables and applies the appropriate filter to match the director's name exactly. Considering the provided choices, the correct SQL query would be:

SELECT M.name FROM Movie M JOIN Director D ON M.id = D.movie_id JOIN Person P ON D.director_id = P.id WHERE P.name = 'Steven Spielberg';

This query joins the Movie, Director, and Person tables based on the associated ID columns and filters the result set to include only those entries where the Person's name matches 'Steven Spielberg' exactly. Option 2 is correct because it provides an exact match without using the LIKE operator, which is typically used for pattern matching, not exact comparisons. Option 1 and 4 include the LIKE operator which is unnecessary in this case. Option 3 has a redundant AND condition that is not needed since the join conditions have already been specified.