Show titles, genre, director last name, rating for all movies with rating > average critic rating...

 

Transcribed Image Text:

Show titles, genre, director last name, rating for all movies with rating > average critic rating of all movies. Use a subquery in the Where clause to find the average rating within the subquery. Order by title. (SCREENSHOT) Aliens Aliens Aliens Aliens Aliens Aliens Amadeus Amadeus Amadeus Amadeus Amadeus American Beauty American Beauty American Beauty Rom... Mendes American Rasiu Rnm Monda Geme L Action Cameron Action Cameron Action Cameron Action Cameron Action Cameron Action Cameron Drama Forman Aliens Aliens Aliens Aliens Aliens Aliens Drama Forman Drama Forman Drama Forman Drama Forman Rom... Mendes Rom... Mendes Show titles, genre, director last name and rating for all movies where: (1) rating is> average rating of all movies and (2) do not display movies from director Forman or romance movies Use a subquery in the Where clause. Order by title. (SCREENSHOT) MY 8.7 8.6 9.0 8.1 7.9 8.0 7.4 7.0 8.1 9.4 9.6 9.0 8.2 9.0 05 La Cameron Action Action Cameron Action Cameron Action Cameron Action Cameron Action Mary 8.7 8.6 9.0 8.1 7.9 8.0 7.8 9.5 Cameron American Splendor Comedy Springer Berman American Splendor Comedy Springer Berman American Splendor Comedy Springer Berman 8.0 American Splendor Comedy Springer Berman 9.6 American Splendor Comedy Springer Berman 8.3. American Splendor Comedy Springer Berman 9.1 American Splendor Comedy Springer Berman 7.3 American Splendor Comedy Springer Berman 9.5 Annie Hall Annie Hall Comedy Allen Comedy Allen Annie Hall Annie Hall Avatar Comedy Allen Comedy Allen Adven... Cameron 86575 9.8 8.6 8.5 7.7 N 7.5 Name director genre movie rating reviewer Infor Tables Engine InnoDB InnoDB InnoDB InnoDB InnoDB Version 10 10 10 10 10 Columns Indexes Triggers Row Format Dynamic Dynamic Dynamic Dynamic Dynamic Rows 32 13 35 366 29 Views Stored Procedures Functions Grants Events Avg Row Length 512 1260 468 44 564 Data Length 16.0 KiB 16.0 KIB 16.0 KiB 16.0 KiB 16.0 KIB Max Data Length 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes Index Length 0.0 bytes 0.0 bytes 32.0 KIB 16.0 KIB 0.0 bytes Data Free 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes Auto Incre 1 9 Table director director director genre genre movie movie movie movie movie movie movie movie movie moviesview moviesview moviesview moviesview moviesview moviesview moviesview moviesview rating rating rating reviewer reviewer Column dir_firstName > dir_lastName O O O O id gen_title id dir_id gen_id Info Tables id mov_dt_rel mov_lang mov_rel_country mov_time mov_title mov_year Director First Name O O Director Last Name Genre ID Rating Reviewer >Title Year mov_id rev_id rev_rating id rev_name Columns Type varchar(30) varchar(30) int varchar(10) int int int int date varchar(15) varchar(5) int varchar(35) int varchar(30) varchar(30) varchar(10) int decimal(6,1) varchar(45) varchar(35) int int int decimal(6,1) int varchar(45) Indexes Triggers Views Stored Procedures Functions Grants Events Default Value 0 Nullable NO NO NO NO NO NO NO NO YES NO NO NO NO NO NO NO NO NO NO YES NO NO NO NO NO NO YES Character Set utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 Privileges select, insert, update, references select,insert,update, references select, insert,update, references select, insert, update, references select, insert, update, references select, insert, update, references select, insert, update, references select, insert, update, references select, insert, update, references utf8mb4_090... select, insert, update, references utf8mb4_090... select, insert, update, references select, insert, update, references select, insert,update, references select, insert, update, references select, insert, update, references select, insert,update, references select, insert, update, references select,insert, update, references select, insert, update, references select, insert,update,references select, insert, update, references select, insert,update, references select, insert, update, references select, insert,update,references select, insert, update, references select, insert, update, references select, insert, update, references Collation. utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... Extra auto_increi auto_increi auto_increi auto_increi Show titles, genre, director last name, rating for all movies with rating > average critic rating of all movies. Use a subquery in the Where clause to find the average rating within the subquery. Order by title. (SCREENSHOT) Aliens Aliens Aliens Aliens Aliens Aliens Amadeus Amadeus Amadeus Amadeus Amadeus American Beauty American Beauty American Beauty Rom... Mendes American Rasiu Rnm Monda Geme L Action Cameron Action Cameron Action Cameron Action Cameron Action Cameron Action Cameron Drama Forman Aliens Aliens Aliens Aliens Aliens Aliens Drama Forman Drama Forman Drama Forman Drama Forman Rom... Mendes Rom... Mendes Show titles, genre, director last name and rating for all movies where: (1) rating is> average rating of all movies and (2) do not display movies from director Forman or romance movies Use a subquery in the Where clause. Order by title. (SCREENSHOT) MY 8.7 8.6 9.0 8.1 7.9 8.0 7.4 7.0 8.1 9.4 9.6 9.0 8.2 9.0 05 La Cameron Action Action Cameron Action Cameron Action Cameron Action Cameron Action Mary 8.7 8.6 9.0 8.1 7.9 8.0 7.8 9.5 Cameron American Splendor Comedy Springer Berman American Splendor Comedy Springer Berman American Splendor Comedy Springer Berman 8.0 American Splendor Comedy Springer Berman 9.6 American Splendor Comedy Springer Berman 8.3. American Splendor Comedy Springer Berman 9.1 American Splendor Comedy Springer Berman 7.3 American Splendor Comedy Springer Berman 9.5 Annie Hall Annie Hall Comedy Allen Comedy Allen Annie Hall Annie Hall Avatar Comedy Allen Comedy Allen Adven... Cameron 86575 9.8 8.6 8.5 7.7 N 7.5 Name director genre movie rating reviewer Infor Tables Engine InnoDB InnoDB InnoDB InnoDB InnoDB Version 10 10 10 10 10 Columns Indexes Triggers Row Format Dynamic Dynamic Dynamic Dynamic Dynamic Rows 32 13 35 366 29 Views Stored Procedures Functions Grants Events Avg Row Length 512 1260 468 44 564 Data Length 16.0 KiB 16.0 KIB 16.0 KiB 16.0 KiB 16.0 KIB Max Data Length 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes Index Length 0.0 bytes 0.0 bytes 32.0 KIB 16.0 KIB 0.0 bytes Data Free 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes 0.0 bytes Auto Incre 1 9 Table director director director genre genre movie movie movie movie movie movie movie movie movie moviesview moviesview moviesview moviesview moviesview moviesview moviesview moviesview rating rating rating reviewer reviewer Column dir_firstName > dir_lastName O O O O id gen_title id dir_id gen_id Info Tables id mov_dt_rel mov_lang mov_rel_country mov_time mov_title mov_year Director First Name O O Director Last Name Genre ID Rating Reviewer >Title Year mov_id rev_id rev_rating id rev_name Columns Type varchar(30) varchar(30) int varchar(10) int int int int date varchar(15) varchar(5) int varchar(35) int varchar(30) varchar(30) varchar(10) int decimal(6,1) varchar(45) varchar(35) int int int decimal(6,1) int varchar(45) Indexes Triggers Views Stored Procedures Functions Grants Events Default Value 0 Nullable NO NO NO NO NO NO NO NO YES NO NO NO NO NO NO NO NO NO NO YES NO NO NO NO NO NO YES Character Set utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4 Privileges select, insert, update, references select,insert,update, references select, insert,update, references select, insert, update, references select, insert, update, references select, insert, update, references select, insert, update, references select, insert, update, references select, insert, update, references utf8mb4_090... select, insert, update, references utf8mb4_090... select, insert, update, references select, insert, update, references select, insert,update, references select, insert, update, references select, insert, update, references select, insert,update, references select, insert, update, references select,insert, update, references select, insert, update, references select, insert,update,references select, insert, update, references select, insert,update, references select, insert, update, references select, insert,update,references select, insert, update, references select, insert, update, references select, insert, update, references Collation. utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... utf8mb4_090... Extra auto_increi auto_increi auto_increi auto_increi

Answer rating: 100% (QA)

Solution Solution You can use a subquery in the WHERE clause to find the average critic rating of all movies and then filter for movies with a rating greater than that average Heres the SQL query to a... View the full answer