Homework SourseSQL Queries in MySQL Multiple Tables This is using the Sakil
SQL Queries in MySQL: Multiple Tables
This is using the Sakila Sample database which can download here:
https://dev.mysql.com/doc/sakila/en
**Note that you will be performing a query on multiple tables. See below the instructions for table names.**
Instructions:
Write a query that produces the last name and first name and film title of all actors who are in Family, Foreign, or Horror films ordered by last name. Next, write a query that produces the last name, first name, film title and film category of every actor who is in either both a Family movie OR a Horror movie ordered by last name. Rename the category from “name” to “category” in your result table. (hint: “AS” keyword)
Schema Name: sakila
LIST OF TABLES
Table Name: film
Column Names: film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features
Table Name: actor
Column Names: actor_id, first_name, last_name, last_update
Table Name: film_actor
Column Names: actor_id, film_id, last_update
Table Name: film_category
Column Names: film_id, category_id, last_update
Table Name: category
Column Names: category_id, name, last_update
SQL Queries in MySQL: Multiple Tables
This is using the Sakila Sample database which can download here:
https://dev.mysql.com/doc/sakila/en
**Note that you will be performing a query on multiple tables. See below the instructions for table names.**
Instructions:
Write a query that produces the last name and first name and film title of all actors who are in Family, Foreign, or Horror films ordered by last name. Next, write a query that produces the last name, first name, film title and film category of every actor who is in either both a Family movie OR a Horror movie ordered by last name. Rename the category from “name” to “category” in your result table. (hint: “AS” keyword)
Schema Name: sakila
LIST OF TABLES
Table Name: film
Column Names: film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features
Table Name: actor
Column Names: actor_id, first_name, last_name, last_update
Table Name: film_actor
Column Names: actor_id, film_id, last_update
Table Name: film_category
Column Names: film_id, category_id, last_update
Table Name: category
Column Names: category_id, name, last_update
SQL Queries in MySQL: Multiple Tables
This is using the Sakila Sample database which can download here:
https://dev.mysql.com/doc/sakila/en
**Note that you will be performing a query on multiple tables. See below the instructions for table names.**
Instructions:
Write a query that produces the last name and first name and film title of all actors who are in Family, Foreign, or Horror films ordered by last name. Next, write a query that produces the last name, first name, film title and film category of every actor who is in either both a Family movie OR a Horror movie ordered by last name. Rename the category from “name” to “category” in your result table. (hint: “AS” keyword)
Schema Name: sakila
LIST OF TABLES
Table Name: film
Column Names: film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features
Table Name: actor
Column Names: actor_id, first_name, last_name, last_update
Table Name: film_actor
Column Names: actor_id, film_id, last_update
Table Name: film_category
Column Names: film_id, category_id, last_update
Table Name: category
Column Names: category_id, name, last_update
Solution
Q1)
select a.last_name,a.first_name,f.title from film f,actor a,film_actor fa,film_category fc,category c where a.actor_id=fa.actor_id AND fa.film_id=fc.film_id AND fc.category_id=c.category_id AND c.name in(\'Family\',\'Foreign\',\'Horror\') AND fa.film_id=f.film_id ORDER BY a.last_name ASC
Q2)
select a.last_name,a.first_name,f.title,c.name AS category from film f,actor a,film_actor fa,film_category fc,category c where a.actor_id=fa.actor_id AND fa.film_id=fc.film_id AND fc.category_id=c.category_id AND c.name in(\'Family\',\'Horror\') AND fa.film_id=f.film_id ORDER BY a.last_name ASC
