CREATE TABLE Director ( dirID INT NOT NULL, dirName VARCHAR(255) NOT NULL, CONSTRAINT pk_Director PRIMARY KEY (dirID), CONSTRAINT ck_Director UNIQUE (dirName) ); CREATE TABLE Movie ( movielD INT, dirlD INT, movieTitle VARCHAR(255) NOT NULL, movieGenre VARCHAR(255) NOT NULL, movieLength INT NOT NULL, CONSTRAINT pk_movie PRIMARY KEY (movieID), CONSTRAINT fk_movie_dir FOREIGN KEY (dirID) REFERENCES Director (dirlD) ); INSERT INTO Director (dirID, dirName) VALUES (1, 'Hitchcock'); INSERT INTO Director (dirID, dirName) VALUES (2, 'Kubrick'); INSERT INTO Director (dirlD, dirName) VALUES (3, 'Scorsese'); INSERT INTO Director (dirID, dirName) VALUES (4, 'Kurosawa'); INSERT INTO Director (dirID, dirName) VALUES (5, 'Wilder'); INSERT INTO Director (dirID, dirName) VALUES (6, 'Speilberg'); INSERT INTO Director (dirID, dirName) VALUES (7, 'Welles'); INSERT INTO Movie VALUES (1, 1, 'Psycho', 'Suspense', 108); INSERT INTO Movie VALUES (2, 1, 'The Birds', 'Suspense', 120); INSERT INTO Movie VALUES (3, 3, 'Raging Bull', 'Drama',129); INSERT INTO Movie VALUES (4, 3, 'Mean Streets', 'Drama', 107); INSERT INTO Movie VALUES (5, 3, 'Goodfellas', 'Drama', 146 ); INSERT INTO Movie VALUES (6,4, 'Seven Samurai', 'Samurai', 207); INSERT INTO Movie VALUES (7, 4, 'Throne of Blood', 'Horror', 109); INSERT INTO Movie VALUES (8, 5, 'Sunset Boulevard', 'Film Noir', 110); INSERT INTO Movie VALUES (9, 5, 'Some Like It Hot', 'Comedy', 132); INSERT INTO Movie VALUES (10, 6, 'Schindler'"s List', 'Drama', 195); INSERT INTO Movie VALUES (11, 6, 'Jaws', 'Horror', 124); INSERT INTO Movie VALUES (12, 2, 'Dr Strangelove', 'Drama', 93); INSERT INTO Movie VALUES (13, 2, 'Clockwork Orange', 'Drama', 136); INSERT INTO Movie VALUES (14, 2, 'Full Metal Jacket', 'Drama', 116); INSERT INTO Movie VALUES (15, 7, 'Citizen Kane', 'Film Noir', 119); SELECT * FROM Director; DIRID DIRNAME 1| Hitcheock 2 | Kubrick 4 | Kurosawa 3 | Scorsese Question 1) Use aggregate functions and GROUP BY to do the following: a) Find a list of genres and the average length of a Movie in each genre [output] MOVIEGENRE AVERAGE_GENRE_LENGTH Comedy 132.0000 Drama | 131.7143 Film Noir | 114.5000 Horror | 116.5000 Samurai | 207.0000 Suspense | 114.0000 b) Find a list of directors, the number of Movies by that director, and the average length for their Movies [output] DIRID TOTAL_MOVIES AVG_LENGTH 1 | 2 | 114.0000 2 | 3 | 115.0000 33127.3333 42158.0000 5 | 2 | 121.0000 6 | 2 | 159.5000 71119.0000 c) Find a list of directors, the number of Movies by that director, and the average length for their Movies, but only including directors that have more than 1 Movie \begin{tabular}{l} [output] \\ DIRID TOTAL_MOVIES AVG_LENGTH \\ \hline 12114.0000 \\ 23115.0000 \\ 33127.3333 \\ 42158.0000 \\ 52121.0000 \\ 62159.5000 \end{tabular}