- Make a list of actor first and last names and the films they appear in. Sort by actor
last name.
SELECT `actor`.`last_name`, `actor`.`first_name`, `film`.`title`
FROM `film_actor`, `actor`, `film`
WHERE ((`film_actor`.`actor_id` =actor.actor_id) AND (`film_actor`.`film_id` =film.film_id))
ORDER BY `actor`.`last_name` ASC
- Make a list of actor first and last names who appear in the movie ANGELS LIFE.
SELECT `actor`.`last_name`, `actor`.`first_name`
FROM `film_actor`, `actor`, `film`
WHERE ((`film_actor`.`actor_id` =actor.actor_id) AND (`film_actor`.`film_id` =film.film_id))
AND film.title= 'ANGELS LIFE'
ORDER BY `actor`.`last_name` ASC
- Make a list of unique films available at the store with store_id of 1.
SELECT DISTINCT `film`.`title`
FROM `inventory`, `film`
WHERE (`inventory`.`store_id` =1)
ORDER BY `film`.`title` ASC
- Make a list of films in the Horror category.
SELECT `film`.`title`
FROM `category`,`film_category`, `film`
WHERE category.category_id=`film_category`.`category_id`
AND `film_category`.`film_id` =film.film_id
AND `category`.`name` ='Horror'
SELECT `film`.`title`
FROM `category` INNER JOIN `film_category`
ON category.category_id=`film_category`.`category_id`
INNER JOIN `film`
ON `film_category`.`film_id` =film.film_id
WHERE `category`.`name` ='Horror'
- Make a list that contains and names in the database: customers, staff and actors.
SELECT `actor`.`last_name`, `actor`.`first_name`
FROM `actor`
UNION
SELECT `customer`.`last_name`, `customer`.`first_name`
FROM `customer`
UNION
SELECT `staff`.`last_name`, `staff`.`first_name`
FROM `staff`