w3resource
SQL exercises

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. Write a query in SQL to find the director's first and last name together with the title of the movie(s) they directed and received the rating.

Sample table: movie


Sample table: rating


p>Sample table: movie_direction


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


Movie database model

Query Visualization:

Duration:

Query visualization of Find the director's first and last name together with the title of the movie(s) they directed and received the rating - Duration

Rows:

Query visualization of Find the director's first and last name together with the title of the movie(s) they directed and received the rating - Rows

Cost:

Query visualization of Find the director's first and last name together with the title of the movie(s) they directed and received the rating - Cost

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

Previous: Write a query in SQL to find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received and sort the result by movie title.
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.

s

What is the difficulty level of this exercise?



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