[Solved] SQL Assignment2-the sakila database

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

14. Show all the countries that have a city named “London”
15. For each store, show the city where it is located and the name of the store manager
16. List all the customers in alphabetical order of last name, and the total payments for each customer
17. Show the movie that was rented the most times
18. Show all the movies that are out for rental (at a customer’s house)

Updating the database

19. 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
20. Write a query that updates the rental price of the new row to 7.50
21. Write a query that updates the return date when the movie is returned to the store