SQL exercises on movie Database: Find the movie title, year, date of release, director and actor for those movies which reviewer is unknown

SQL movie Database: Subquery Exercise-4 with Solution

4. From the following tables, write a SQL query to find those movies where reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name.

Sample table: movie

Sample table: actor

Sample table: director

Sample table: movie_direction

Sample table: movie_cast

Sample table: reviewer

Sample table: rating

Sample Solution:

SELECT mov_title, mov_year, mov_dt_rel, dir_fname, dir_lname, 
       act_fname, act_lname
	   FROM movie a, movie_direction b, director c, 
                rating d, reviewer e, actor f, movie_cast g
	   WHERE a.mov_id=b.mov_id
AND  b.dir_id=c.dir_id 
 AND a.mov_id=d.mov_id 
  AND  d.rev_id=e.rev_id 
   AND  a.mov_id=g.mov_id 
    AND g.act_id=f.act_id 
	 AND e.rev_name IS NULL;

Sample Output:

                     mov_title                      | mov_year | mov_dt_rel |      dir_fname       |      dir_lname       |      act_fname       |      act_lname
----------------------------------------------------+----------+------------+----------------------+----------------------+----------------------+----------------------
 Blade Runner                                       |     1982 | 1982-09-09 | Ridley               | Scott                | Harrison             | Ford
 Princess Mononoke                                  |     1997 | 2001-10-19 | Hayao                | Miyazaki             | Claire              | Danes
(2 rows)

Relational Algebra Expression:

Relational Algebra Tree:

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 table, write a SQL query to find those movies, which released in the country besides UK. Return movie title, movie year, movie time, date of release, releasing country.
Next: From the following tables, write a SQL query to find those movies directed by the director whose first name is ‘Woddy’ and last name is ‘Allen’. Return movie title.