SQL Queries in MySQL: Multiple Tables This is using the Sakila Sample database w

Question

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

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.