7.7 LAB - Multiple joins with aggregate (Sakila)

Refer to the film, actor, and film_actor tables of the Sakila database. The tables in this lab have the same columns and data types but fewer rows. The tables are initialized with LOAD rather than INSERT statements. The LOAD statements read data from .csv files. In these files, \N represents NULL.

Write a statement that:

• Computes the average length of all films that each actor appears in.
• Rounds average length to the nearest minute and renames the result column average.
• Displays last name, first name, and average, in that order, for each actor.
• Sorts the result in descending order by average, then ascending order by last name.

The statement should exclude films with no actors and actors that do not appear in films.

Hint: Use the ROUND() and AVG() functions.

- Initialize.sql(read only)

-- Drop all existing tables
DROP TABLE IF EXISTS actor, film, film_actor;

-- Create actor, film, and film_actor tables
CREATE TABLE actor (
   actor_id SMALLINT UNSIGNED NOT NULL,
   first_name VARCHAR(45) NOT NULL,
   last_name VARCHAR(45) NOT NULL,
   last_update TIMESTAMP NOT NULL,
   PRIMARY KEY  (actor_id)
);

CREATE TABLE film (
   film_id SMALLINT UNSIGNED NOT NULL,
   title VARCHAR(128) NOT NULL,
   description TEXT DEFAULT NULL,
   release_year YEAR DEFAULT NULL,
   language_id TINYINT UNSIGNED NOT NULL,
   original_language_id TINYINT UNSIGNED DEFAULT NULL,
   rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
   rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
   length SMALLINT UNSIGNED DEFAULT NULL,
   replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
   rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
   special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
   last_update TIMESTAMP NOT NULL,
   PRIMARY KEY  (film_id)
);
  
CREATE TABLE film_actor (
   actor_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
   film_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
   last_update TIMESTAMP NOT NULL,
   PRIMARY KEY  (actor_id,film_id),
   CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,
   CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE 
);

-- Load data to tables
LOAD DATA INFILE '/usercode/actor.csv' INTO TABLE actor
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/usercode/film.csv' INTO TABLE film
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/usercode/film_actor.csv' INTO TABLE film_actor
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

- Main.sql

-- Initialize database
source Initialize.sql

-- Your SELECT statment goes here

▼View answer

SELECT actor.last_name, actor.first_name, ROUND(AVG(film.length)) AS average
FROM actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
JOIN film ON film_actor.film_id = film.film_id
GROUP BY actor.last_name, actor.first_name
ORDER BY average DESC, actor.last_name;