w3resource
SQL exercises

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

SQL movie Database: Subquery Exercise-15 with Solution

15. Write a query in SQL to find the titles of all movies directed by James Cameron.

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)

Practice Online


Movie database model

Query Visualization for Sample Solution:

Duration:

Query visualization of Find the titles of all movies directed by James Cameron - Duration

Rows:

Query visualization of Find the titles of all movies directed by James Cameron - Rows

Cost:

Query visualization of Find the titles of all movies directed by James Cameron - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Find the titles of all movies directed by James Cameron - Duration

Rows:

Query visualization of Find the titles of all movies directed by James Cameron - Rows

Cost:

Query visualization of Find the titles of all movies directed by James Cameron - Cost

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

Previous: Write a query in SQL to return the reviewer name, movie title, and number of stars for those movies which rating is the lowest one.
Next: Write a query in SQL to find the name of those movies where one or more actors acted in two or more movies.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming