View FILM_LIST

This view contains a formatted view of the FILM table, with a comma-separated list of actors for each film. The view incorporates data from the FILM, CATEGORY, FILM_CATEGORY, ACTOR, and FILM_ACTOR tables.

Query

SQL

SELECT 
  film.film_id AS FID,
  film.title AS title,
  film.description AS description,
  category.name AS category,
  film.rental_rate AS price,
  film.length AS length,
  film.rating AS rating,
  LIST(actor.first_name||' '||actor.last_name, ', ') AS actors
FROM 
  film 
LEFT JOIN 
  film_category ON film_category.film_id = film.film_id
LEFT JOIN 
  category ON category.category_id = film_category.category_id 
LEFT JOIN 
  film_actor ON film.film_id = film_actor.film_id 
LEFT JOIN 
  actor ON film_actor.actor_id = actor.actor_id
GROUP BY 
  film.film_id, 
  category.name, 
  film.title,
  film.description, 
  film.rental_rate, 
  film.length, 
  film.rating

Columns

This view contains 8 columns.

FID: INTEGER
TITLE: VARCHAR(255)
DESCRIPTION: BLOB subtype text
CATEGORY: VARCHAR(25)
PRICE: DECIMAL
LENGTH: SMALLINT
RATING: VARCHAR(5)
ACTORS: BLOB subtype text