SQL exercises on movie Database: Find the titles of all movies directed by James Cameron

SQL movie Database: Subquery Exercise-15 with Solution

15. From the following tables, write a SQL query to find the movies directed by 'James Cameron'. Return movie title.

Sample table: director

Sample table: movie_direction

Sample table: movie

Sample Solution:

SELECT mov_title
FROM movie
WHERE mov_id IN (
SELECT mov_id 
FROM movie_direction 
WHERE dir_id IN (
SELECT dir_id 
FROM director 
WHERE dir_fname = 'James' AND dir_lname='Cameron'
));

OR

SELECT mov_title FROM movie 
JOIN  movie_direction 
 ON movie.mov_id=movie_direction.mov_id
JOIN  director 
 ON movie_direction.dir_id=director.dir_id
WHERE dir_fname = 'James' AND dir_lname='Cameron';

Sample Output:

                     mov_title
----------------------------------------------------
 Titanic
 Aliens
(2 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization for Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for alternate Sample Solution:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find the lowest rated movies. Return reviewer name, movie title, and number of stars for those movies.
Next: Write a query in SQL to find the name of those movies where one or more actors acted in two or more movies.