w3resource

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 table, write a SQL query to find out which movies have received ratings. Return movie title, director first name, director last name and review stars.

Sample table: movie
 mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
    914 | American Beauty                                    |     1999 |      122 | English         |            | UK
    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
Sample table: rating
 mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
    901 |   9001 |      8.40 |        263575
    902 |   9002 |      7.90 |         20207
    903 |   9003 |      8.30 |        202778
    906 |   9005 |      8.20 |        484746
    924 |   9006 |      7.30 |
    908 |   9007 |      8.60 |        779489
    909 |   9008 |           |        227235
    910 |   9009 |      3.00 |        195961
    911 |   9010 |      8.10 |        203875
    912 |   9011 |      8.40 |
    914 |   9013 |      7.00 |        862618
    915 |   9001 |      7.70 |        830095
    916 |   9014 |      4.00 |        642132
    925 |   9015 |      7.70 |         81328
    918 |   9016 |           |        580301
    920 |   9017 |      8.10 |        609451
    921 |   9018 |      8.00 |        667758
    922 |   9019 |      8.40 |        511613
    923 |   9020 |      6.70 |         13091
Sample table: movie_direction
 dir_id | mov_id
--------+--------
    201 |    901
    202 |    902
    203 |    903
    204 |    904
    205 |    905
    206 |    906
    207 |    907
    208 |    908
    209 |    909
    210 |    910
    211 |    911
    212 |    912
    213 |    913
    214 |    914
    215 |    915
    216 |    916
    217 |    917
    218 |    918
    219 |    919
    220 |    920
    218 |    921
    215 |    922
    221 |    923
Sample table: director
 dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
    201 | Alfred               | Hitchcock
    202 | Jack                 | Clayton
    203 | David                | Lean
    204 | Michael              | Cimino
    205 | Milos                | Forman
    206 | Ridley               | Scott
    207 | Stanley              | Kubrick
    208 | Bryan                | Singer
    209 | Roman                | Polanski
    210 | Paul                 | Thomas Anderson
    211 | Woody                | Allen
    212 | Hayao                | Miyazaki
    213 | Frank                | Darabont
    214 | Sam                  | Mendes
    215 | James                | Cameron
    216 | Gus                  | Van Sant
    217 | John                 | Boorman
    218 | Danny                | Boyle
    219 | Christopher          | Nolan
    220 | Richard              | Kelly
    221 | Kevin                | Spacey
    222 | Andrei               | Tarkovsky
    223 | Peter                | Jackson

Sample Solution:

-- Selecting specific columns from the Movie table, movie_direction table, director table, and rating table
SELECT mov_title, dir_fname, dir_lname, rev_stars
-- Joining the Movie and movie_direction tables using the mov_id column
FROM Movie
JOIN movie_direction USING(mov_id)
-- Joining the result with the director table using the dir_id column
JOIN director USING (dir_id)
-- Performing a left join with the rating table using the mov_id column
LEFT JOIN rating USING(mov_id)
-- Filtering the result to include only records where rev_stars is not null
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)

Code Explanation :

The said query in SQL that retrieves data from the tables Movie, movie_direction, and director, and a left join to rating table to retrieve movies that have received ratings.
The FROM clause specifies the main table Movie and two other tables movie_direction and director that are joined using the JOIN keyword with mov_id as the common column.
The left join keyword is used to retrieve movies that have received ratings from the rating table.
The where clause is used to filter the result set to only include movies that have received ratings.

Alternative Solutions:

Using EXISTS Clause:


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 EXISTS (SELECT 1 FROM rating WHERE rating.mov_id = Movie.mov_id AND rev_stars IS NOT NULL);

Explanation:

This SQL query uses the EXISTS clause with a subquery to check if there exists a rating for a particular movie. If a rating exists, the row is included in the result set.

Using a Subquery with IN Clause:


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 mov_id IN (SELECT mov_id FROM rating WHERE rev_stars IS NOT NULL);

Explanation:

This SQL query uses a subquery in the WHERE clause with an IN clause to filter out movies without ratings. It selects mov_id values from the rating table where rev_stars is not null, and then includes only those movies in the result set.

Using a Subquery with JOIN:


SELECT mov_title, dir_fname, dir_lname, rev_stars
FROM Movie
JOIN movie_direction USING(mov_id)
JOIN director USING(dir_id)
LEFT JOIN (
    SELECT mov_id, rev_stars
    FROM rating
    WHERE rev_stars IS NOT NULL
) AS rated_movies USING(mov_id)
WHERE rev_stars IS NOT NULL;

Explanation:

This SQL query uses a subquery that selects mov_id and rev_stars from the rating table where rev_stars is not null. This subquery is then joined with the main query using a LEFT JOIN, allowing for movies without ratings to still appear in the result set.

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: 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-40.php