SQL exercises on movie Database: Find the director's first and last name together with the title of the movie(s) they directed and received the rating

SQL movie Database: Join Exercise-15 with Solution

15. From the following tables, write a SQL query to find those movies, which have received ratings. Return movie title, director first name, director last name and review stars.

Sample table: movie

Sample table: rating

Sample table: director

Sample Solution:

SELECT mov_title, dir_fname,dir_lname, rev_stars
FROM Movie
JOIN movie_direction USING(mov_id)
join director using (dir_id)
left join rating using(mov_id)
where rev_stars is not null;

Sample Output:

                     mov_title                      |      dir_fname       |      dir_lname       | rev_stars
----------------------------------------------------+----------------------+----------------------+-----------
 Vertigo                                            | Alfred               | Hitchcock            |      8.40
 The Innocents                                      | Jack                 | Clayton              |      7.90
 Lawrence of Arabia                                 | David                | Lean                 |      8.30
 Blade Runner                                       | Ridley               | Scott                |      8.20
 The Usual Suspects                                 | Bryan                | Singer               |      8.60
 Boogie Nights                                      | Paul                 | Thomas Anderson      |      3.00
 Annie Hall                                         | Woody                | Allen                |      8.10
 Princess Mononoke                                  | Hayao                | Miyazaki             |      8.40
 American Beauty                                    | Sam                  | Mendes               |      7.00
 Titanic                                            | James                | Cameron              |      7.70
 Good Will Hunting                                  | Gus                  | Van Sant             |      4.00
 Donnie Darko                                       | Richard              | Kelly                |      8.10
 Slumdog Millionaire                                | Danny                | Boyle                |      8.00
 Aliens                                             | James                | Cameron              |      8.40
 Beyond the Sea                                     | Kevin                | Spacey               |      6.70
(15 rows)

Practice Online

Query Visualization:

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 those movies that have at least one rating and received highest number of stars. Sort the result-set on movie title. Return movie title and maximum review stars.
Next: Write a query in SQL to find the movie title, actor first and last name, and the role for those movies where one or more actors acted in two or more movies.