In this lab, you will be using the Sakila database of movie rentals. You have been using this database for a couple labs already, but if you need to get the data again, refer to the official installation link.
The database is structured as follows:
Create a .sql
file where you'll gather your responses. Either copy them as you test them on workbench or code them and File > Save script.
-
How many distinct (different) actors' last names are there?
-
In how many different languages where the films originally produced? (Use the column
language_id
from thefilm
table) -
How many movies were released with
"PG-13"
rating? -
Get 10 the longest movies from 2006.
-
How many days has been the company operating (check
DATEDIFF()
function)? -
Show rental info with additional columns month and weekday. Get 20.
-
Add an additional column
day_type
with values 'weekend' and 'workday' depending on the rental day of the week. -
How many rentals were in the last month of activity?
-
Get film ratings.
-
Get release years.
-
Get all films with ARMAGEDDON in the title.
-
Get all films with APOLLO in the title
-
Get all films which title ends with APOLLO.
-
Get all films with word DATE in the title.
-
Get 10 films with the longest title.
-
Get 10 the longest films.
-
How many films include Behind the Scenes content?
-
List films ordered by release year and title in alphabetical order.
-
Drop column
picture
fromstaff
. -
A new person is hired to help Jon. Her name is TAMMY SANDERS, and she is a customer. Update the database accordingly.
-
Add rental for movie "Academy Dinosaur" by Charlotte Hunter from Mike Hillyer at Store 1. You can use current date for the
rental_date
column in therental
table. Hint: Check the columns in the table rental and see what information you would need to add there. You can query those pieces of information. For eg., you would notice that you needcustomer_id
information as well. To get that you can use the following query:select customer_id from sakila.customer where first_name = 'CHARLOTTE' and last_name = 'HUNTER';
Use similar method to get
inventory_id
,film_id
, andstaff_id
. -
Delete non-active users, but first, create a backup table
deleted_users
to storecustomer_id
,email
, and thedate
for the users that would be deleted. Follow these steps:- Check if there are any non-active users
- Create a table backup table as suggested
- Insert the non active users in the table backup table
- Delete the non active users from the table customer