A basic study was conducted of the Sakila Database. All queries were performed in Sakila_Study.sql
and are summarize below.
A complete Enhanced entity–relationship (EER) model
of the Sakila Database is included
-
a. Display the first and last names of all actors from the table
actor
.
b. Display the first & last name of each actor using all upper case in a single column titledActor Name
.
-
a. Given an actor's 1st name ('Joe'), How would you query their ID number, last name, (and first name)?
b. Find all actors whose last name contains the lettersGEN
.
c. Find all actors whose last name contains lettersLI
, & order the rows by last name then first name.
d. UsingIN
, displaycountry_id
&country
of the following: Afghanistan, Bangladesh, & China.
-
a. For a place to store actor descriptions, create
actor
.description
and use the data typeBLOB
.
b. Delete thedescription
column.
-
a. List the last names of actors, as well as the number of records sharing that last name.
b. List last names of actors, & the number of records sharing that last name, only for shared names.
c. Write a Corrective Query to Change actorGROUCHO WILLIAMS
toHARPO WILLIAMS
.
d. Change it back: If the first name of the actor is currentlyHARPO
, change it toGROUCHO
.
-
a. You cannot locate the schema of the
address
table. Which query would you use to re-create it?
-
a. Use
JOIN
to display the first and last names, as well as the address, of each staff member.
b. UseJOIN
to display total staff member sales in August 2005. Use tablesstaff
&payment
.
c. List each film & number of actors listed for it. Use INNER JOIN on tablesfilm_actor
&film
.
d. How many copies of the filmHunchback Impossible
exist in the inventory system?
e. JOIN tablespayment
&customer
& list total paid per customer, sorting last name alphabetically.
-
a. Use subqueries to display titles of movies starting w/ letters
K
&Q
whose language is English.
b. Use subqueries to display all actors who appear in the filmAlone Trip
.
c. Retrieve names & email addresses of all Canadian customers using JOINs.
d. Query all films in the category 'Family'.
e. Display the most frequently rented movies in descending order.
f. Write a query to display how much business, in dollars, each store brought in.
g. Write a query to display for each store its store ID, city, and country.
h. What are the top 5 genres in gross revenue (descending order).
-
a. Create view for top 5 genres in gross revenue (solution of 7h).
b. How would you display the view that you created in 8a?
c. You find that you no longer need the viewtop_five_genres
. Write a query to delete it.