film.sql

-- 19. List the 10 best directors (use the average score of their films to determine who is best) in descending 
-- order along with the number of films they've made and the average score for their films. Only consider 
-- directors who have made at least five films (4 marks)
SELECT * FROM (
	SELECT * FROM (
		SELECT name, COUNT(*) AS "NUM_FILMS", AVG(score) as "AVERAGE" FROM shared.MOVIE, shared.ACTOR
		WHERE shared.actor.id = director
		GROUP BY name)
	WHERE NUM_FILMS > 4
	ORDER BY AVERAGE DESC)
WHERE rownum < 11;

-- 20. List the decades from the 30s (1930-39) to the 90s (1990-99) and for each of those decades show the 
-- average film score, the best film and the actor who starred in most films (5 marks)
SELECT avgscore.DECADE, AVERAGE, BEST_FILM, name as "ACTOR" FROM 
	(SELECT DECADE, AVG(score) as "AVERAGE" FROM (SELECT SUBSTR(TO_CHAR(yr), 1, 3) || '0' AS DECADE, title, score, id FROM shared.MOVIE WHERE yr > 1929 AND yr < 2000) GROUP BY DECADE) avgscore,	
	(SELECT M.DECADE, MOV.title AS "BEST_FILM" FROM 
		(SELECT DECADE, MAX(score) as "SCORE" FROM (SELECT SUBSTR(TO_CHAR(yr), 1, 3) || '0' AS DECADE, title, score, id FROM shared.MOVIE WHERE yr > 1929 AND yr < 2000) GROUP BY DECADE) M,
		shared.MOVIE "MOV"
	WHERE M.score = MOV.score and m.decade = SUBSTR(TO_CHAR(MOV.yr), 1, 3) || '0') B,
	(SELECT A.decade, name FROM 
		(SELECT decade, MAX(C) as "CNT" FROM 
			(SELECT decade, actorid, count(*) as "C" FROM (
				SELECT SUBSTR(TO_CHAR(yr), 1, 3) || '0' AS DECADE, actorid 
				FROM shared.MOVIE, shared.CASTING 
				WHERE shared.MOVIE.id = movieid 
					AND yr > 1929
					AND yr < 2000
					AND ord = 1)
			GROUP BY decade, actorid )
		GROUP BY decade) cnt,
		(SELECT decade, actorid, count(*) as "C" FROM (
			SELECT SUBSTR(TO_CHAR(yr), 1, 3) || '0' AS DECADE, actorid 
			FROM shared.MOVIE, shared.CASTING 
			WHERE shared.MOVIE.id = movieid 
				AND yr > 1929
				AND yr < 2000
				AND ord = 1)
		GROUP BY decade, actorid) A,
		shared.ACTOR actr
	WHERE A.actorid = actr.id
		AND A.decade = cnt.decade
		AND cnt.cnt = A.c) C
WHERE avgscore.decade = B.decade
	AND B.decade = C.decade
ORDER BY DECADE DESC;