the first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director." width="" height="" /> 6. From the following table, write a SQL query to find the directors who have directed films in a variety of genres. Group the result set on director first name, last name and generic title. Sort the result-set in ascending order by director first name and last name. Return director first name, last name and number of genres movies.
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 | JacksonSample 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
gen_id | gen_title --------+---------------------- 1001 | Action 1002 | Adventure 1003 | Animation 1004 | Biography 1005 | Comedy 1006 | Crime 1007 | Drama 1008 | Horror 1009 | Music 1010 | Mystery 1011 | Romance 1012 | Thriller 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 918 | 1007 921 | 1007 902 | 1008 923 | 1009 907 | 1010 927 | 1010 901 | 1010 914 | 1011 906 | 1012 904 | 1013
Sample Solution:
-- Selecting director first names, last names, genre titles, and the count of genre titles -- Using the 'director' table SELECT dir_fname, dir_lname, gen_title, COUNT(gen_title) FROM director -- Joining with 'movie_direction' using NATURAL JOIN to link directors with movies NATURAL JOIN movie_direction -- Further joining with 'movie_genres' using NATURAL JOIN to get additional movie genres information NATURAL JOIN movie_genres -- Joining with 'genres' using NATURAL JOIN to retrieve genre titles NATURAL JOIN genres -- Grouping the result by director names and genre titles GROUP BY dir_fname, dir_lname, gen_title -- Ordering the result by director first names and last names ORDER BY dir_fname, dir_lname; dir_fname | dir_lname | gen_title | count ----------------------+----------------------+----------------------+------- Alfred | Hitchcock | Mystery | 1 Bryan | Singer | Crime | 1 Danny | Boyle | Drama | 2 David | Lean | Adventure | 1 Frank | Darabont | Crime | 1 Hayao | Miyazaki | Animation | 1 Jack | Clayton | Horror | 1 James | Cameron | Action | 1 John | Boorman | Adventure | 1 Kevin | Spacey | Music | 1 Michael | Cimino | War | 1 Ridley | Scott | Thriller | 1 Sam | Mendes | Romance | 1 Stanley | Kubrick | Mystery | 1 Woody | Allen | Comedy | 1 (15 rows)
Code Explanation:
The said query in SQL that joins the tables director, movie_direction, movie_genres, and genres, and retrieves the first name and last name of the directors, the genre titles, and the count of movies directed by each director in each genre. The query groups the results by director first name, last name, and genre title, and sorts the results by director first name and last name.
The NATURAL JOIN keyword, which joins tables based on columns with the same name in each table. This joins the director table with the movie_direction table to get the movies directed by each director. Then joins the resulting table with the movie_genres table to get the genre for each movie and then it join the resulting table with the genres table to get the genre title for each genre ID.
The results then grouped by the director first name, last name, and genre title and then it sort the results by director first name and last name.
Alternative Solutions:
Using INNER JOIN and GROUP BY:
SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year SELECT d.dir_fname, d.dir_lname, g.gen_title, COUNT(g.gen_title) FROM director d JOIN movie_direction md ON d.dir_id = md.dir_id JOIN movie_genres mg ON md.mov_id = mg.mov_id JOIN genres g ON mg.gen_id = g.gen_id GROUP BY d.dir_fname, d.dir_lname, g.gen_title ORDER BY d.dir_fname, d.dir_lname; This query uses INNER JOINs to combine the director, movie_direction, movie_genres, and genres tables based on their respective IDs. It then applies a GROUP BY clause to group the results by director names (dir_fname, dir_lname) and genre titles (gen_title), along with a COUNT to get the count of each genre. The results are ordered by director names.
Using WHERE Clause with Table Aliases and GROUP BY:
SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year SELECT d.dir_fname, d.dir_lname, g.gen_title, COUNT(g.gen_title) FROM director d, movie_direction md, movie_genres mg, genres g WHERE d.dir_id = md.dir_id AND md.mov_id = mg.mov_id AND mg.gen_id = g.gen_id GROUP BY d.dir_fname, d.dir_lname, g.gen_title ORDER BY d.dir_fname, d.dir_lname; This query uses the older comma-separated syntax for joining tables and specifies the join conditions in the WHERE clause. It then applies a GROUP BY clause to group the results by director names and genre titles, along with a COUNT to get the count of each genre. The results are ordered by director names.
Relational Algebra Expression:
the first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director." width="" height="" />
Relational Algebra Tree:
the first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director." width="" height="" />
Movie database model" width="" height="" />
Duration:
the first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director - Duration" width="" height="" />
Rows:
the first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director - Rows" width="" height="" />
Cost:
the first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director - Cost" width="" height="" />
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.
Follow us on Facebook and Twitter for latest update.
