SQL exercises on movie Database: Generate a report which contain the columns movie title, name of the female actor, year of the movie, role, movie genres, the director, date of release, and rating of that movie
24. From the following tables, write a query in SQL to generate a report, which contain the fields movie title, name of the female actor, year of the movie, role, movie genres, the director, date of release, and rating of that movie.
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
......
925 | Braveheart | 1995 | 178 | English | 1995-09-08 | UK
Sample table: genres
gen_id | gen_title --------+---------------------- 1001 | Action 1002 | Adventure 1003 | Animation 1004 | Biography 1005 | Comedy 1006 | Crime 1007 | Drama 1008 | Horror 1009 | Music ..... 1013 | War
Sample table: movie_genres
mov_id | gen_id
--------+--------
922 | 1001
917 | 1002
903 | 1002
912 | 1003
911 | 1005
908 | 1006
913 | 1006
926 | 1007
928 | 1007
.....
904 | 1013
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
.....
923 | 9020 | 6.70 | 13091
Sample table: actor
act_id | act_fname | act_lname | act_gender
--------+----------------------+----------------------+------------
101 | James | Stewart | M
102 | Deborah | Kerr | F
103 | Peter | OToole | M
104 | Robert | De Niro | M
105 | F. Murray | Abraham | M
106 | Harrison | Ford | M
107 | Nicole | Kidman | F
108 | Stephen | Baldwin | M
109 | Jack | Nicholson | M
110 | Mark | Wahlberg | M
.....
124 | Ali | Astin | F
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
.....
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
....
221 | 923
Sample table: movie_cast
act_id | mov_id | role
--------+--------+--------------------------------
101 | 901 | John Scottie Ferguson
102 | 902 | Miss Giddens
103 | 903 | T.E. Lawrence
104 | 904 | Michael
105 | 905 | Antonio Salieri
106 | 906 | Rick Deckard
107 | 907 | Alice Harford
108 | 908 | McManus
110 | 910 | Eddie Adams
.....
119 | 919 | Alfred Borden
Sample Solution:
-- Selecting various columns from multiple tables: movie, movie_cast, actor, movie_genres, genres, movie_direction, director, and rating
SELECT mov_title, act_fname, act_lname,
mov_year, role, gen_title, dir_fname, dir_lname,
mov_dt_rel, rev_stars
-- Performing natural joins between the movie, movie_cast, actor, movie_genres, genres, movie_direction, director, and rating tables
FROM movie
NATURAL JOIN movie_cast
NATURAL JOIN actor
NATURAL JOIN movie_genres
NATURAL JOIN genres
NATURAL JOIN movie_direction
NATURAL JOIN director
NATURAL JOIN rating
-- Filtering the result to include only records where act_gender is 'F'
WHERE act_gender='F';
Sample Output:
mov_title | act_fname | act_lname | mov_year | role | gen_title | dir_fname | dir_lname | mov_dt_rel | rev_stars
----------------------------------------------------+----------------------+----------------------+----------+--------------------------------+----------------------+----------------------+----------------------+------------+-----------
The Innocents | Deborah | Kerr | 1961 | Miss Giddens | Horror | Jack | Clayton | 1962-02-19 | 7.90
Princess Mononoke | Claire | Danes | 1997 | San | Animation | Hayao | Miyazaki | 2001-10-19 | 8.40
Aliens | Sigourney | Weaver | 1986 | Ripley | Action | James | Cameron | 1986-08-29 | 8.40
(3 rows)
Code Explanation :
The said query in SQL that retrieves the title, year, release date, and rating of movies, as well as the first and last name of the actresses who played a role in the movie, the genres of the movie, and the first and last name of the director of the movie.
1. The NATURAL JOIN clause joins the movie_cast table to the movie table based on matching column names in the two tables.
2. The actor table joined to the result of the previous join step 1. 3. The movie_genres table joined to the result of the previous join step 2.
4. The genres table joined to the result of the previous join step 3.
5. The movie_direction table joined to the result of the previous join step 4.
6. The director table joined to the result of the previous join step 5.
7. The rating table joined to the result of the previous join step 6.
The WHERE clause filter the result, where only actors with a gender of 'F' will be included in the result set.
Alternative Solutions:
Using Subquery with EXISTS:
SELECT mov_title, act_fname, act_lname,
mov_year, role, gen_title, dir_fname, dir_lname,
mov_dt_rel, rev_stars
FROM movie
JOIN movie_cast ON movie.mov_id = movie_cast.mov_id
JOIN actor ON movie_cast.act_id = actor.act_id
JOIN movie_genres ON movie.mov_id = movie_genres.mov_id
JOIN genres ON movie_genres.gen_id = genres.gen_id
JOIN movie_direction ON movie.mov_id = movie_direction.mov_id
JOIN director ON movie_direction.dir_id = director.dir_id
JOIN rating ON movie.mov_id = rating.mov_id
WHERE EXISTS (
SELECT 1
FROM actor
WHERE actor.act_id = movie_cast.act_id
AND actor.act_gender = 'F'
);
Explanation:
This SQL query uses a subquery with EXISTS to check if there exists an actor with gender 'F' who is part of the cast. If such an actor exists, the main query includes the corresponding movie details.
Using INNER JOINs with Subquery:
SELECT mov_title, act_fname, act_lname,
mov_year, role, gen_title, dir_fname, dir_lname,
mov_dt_rel, rev_stars
FROM movie
JOIN movie_cast ON movie.mov_id = movie_cast.mov_id
JOIN actor ON movie_cast.act_id = actor.act_id
JOIN movie_genres ON movie.mov_id = movie_genres.mov_id
JOIN genres ON movie_genres.gen_id = genres.gen_id
JOIN movie_direction ON movie.mov_id = movie_direction.mov_id
JOIN director ON movie_direction.dir_id = director.dir_id
JOIN rating ON movie.mov_id = rating.mov_id
WHERE actor.act_gender = 'F';
Explanation:
This SQL query combines INNER JOINs with a WHERE clause to directly filter for actors with gender 'F'.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : From the following tables, write a SQL query to find the years when most of the ‘Mystery Movies’ produced. Count the number of generic title and compute their average rating. Group the result set on movie release year, generic title. Return movie year, generic title, number of generic title and average rating.
NEXT : SQL Basic Exercises on Soccer Database
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.