Question
Use the sakila database for this lab Download Sakila here: https://dev.mysql.com/doc/index-other.html Task 1: Write a query that would produce a list of films from inventory
Use the sakila database for this lab
Download Sakila here: https://dev.mysql.com/doc/index-other.html
Task 1:
- Write a query that would produce a list of films from inventory (you will need to join inventory and film tables).
- Use table alias in your query
- Use a subquery to show only those films that were rented in May of 2005.
- Your subquery will select a list of inventory_id from rental table.
- To select only the items that were rented in May (month #5), you will use the MySQL MONTH() function. Ex. MONTH(return_date) = 5.
- To select only the items that were rented in 2005, you will use the MySQL YEAR() function. Ex. YEAR(return_date) = 2005.
The report should show the following fields:
- Movie title
- Rental rate
- Replacement cost
Task 2: Write a query that will show a report containing the following columns:
- Movie Title
- Rental Rate
- Maximum profit (maximum amount) earned from rentals.
Your report must show all records, not just the ones that exist in the inventory.
Use a JOIN not a subquery
You might have some films that havent been entered into the inventory. Hint: Do NOT USE AN INNER JOIN
Use table alias in your query
Use a quadruple join join tables inventory, film, rental and payment.
Since you will be selecting a MAX(amount), dont forget to write a GROUP BY clause!
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started