SQL Problems (a) List the names of all directors for whom no movies have been en
ID: 3674729 • Letter: S
Question
SQL Problems
(a) List the names of all directors for whom no movies have been entered into the Movies table.
(b) For each director, list the number of movies by that director and the latest movie release year.
(c) For directors with at least 3 movies, compute the average US box office amount. Return director name, country name, and average box office amount. Sort results in increasing order of average box office amount.
(d)List the names of all movies by a director who directed the earliest (least recent) movie in the Movies table.
Directors DirectorName Country USA Canada USA Spain Lynch Almodovar Kusturica Tarkovsk Russia Movies Movie Name Underground Time of the Gypsies Black cat, white cat Crash Fl Spider Bad education Matador All about my mother Talk to her AndreiRublev Stalker US box office Director_name Kusturica Kusturica Kusturi Cronenber Cronenbe Cronenber Almodovar Almodovar Year 1995 1988 1998 1996 1986 2002 2004 1986 1999 171,082 351,447 2,038,450 60,629,159 5,808,94'1 40,266,982 Almodovar 67,989,091 Almodovar Tarkovsk Tarkovsk 1966 1979Explanation / Answer
a) select director_name from directors where director_name not in (select distinct director_name from movies);
b)select director_name,count(*),max(year) from movies group by director_name;
c)select d.director_name,d.country,avg(m.us_box_office) from directors d,movies m where d.director_name=m.director_name group by m.director_name order by 3 desc;
d)select director_name ,min(year) from movies group by director_name;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.