[Solved] SQL Assignment2-the sakila database

$25

File Name: SQL_Assignment2-the_sakila_database.zip
File Size: 329.7 KB

SKU: [Solved] SQL Assignment2-the sakila database Category: Tag:
5/5 - (1 vote)

For this assignment, you will use the sakila database. If you installed MySQL on your computer, sakila is included. If you are using vlab, there are installation instructions in a separate file.

The sakila database includes information about a video rental store in the days before live streaming.

The figure shows the tables and relations. On the left side you can see tables of data about the movies and actors, and on the right there are tables about employees, customers and rentals.

Write SQL queries to do the following:

Queries about movies and actors

  1. Show all the actresses with first name Alice
  2. Show the different last names of all the actors
  3. List the different first names and a count of how many actors have this name
  4. Show all the movies that are in Italian
  5. Show all the movies in the category Comendy
  6. Show the names of all the actors in the movie Citizen Shrek
  7. List the names of all the movies and the number of actors in each one
  8. Show the name of the actor who appeared in the most movies
  9. Show the number of actors that did not appear in any of the movies
  10. List the actors that did not appear in any movie
  11. Show the average running time of all the movies
  12. Show the average running time of each category of movies
  13. Show the categories that have more than 100 movies

Queries about stores and rentals

  1. Show all the countries that have a city named London
  2. For each store, show the city where it is located and the name of the store manager
  3. List all the customers in alphabetical order of last name, and the total payments for each customer
  4. Show the movie that was rented the most times
  5. Show all the movies that are out for rental (at a customers house)

Updating the database

  1. Write a query that adds a new row to the rental table when customer 222 rents movie 4444 from employee 1. For rental time, use NOW(). Rental_id is automatically incremented
  2. Write a query that updates the rental price of the new row to 7.50
  3. Write a query that updates the return date when the movie is returned to the store
Shopping Cart

No products in the cart.

No products in the cart.

[Solved] SQL Assignment2-the sakila database
$25