Jorge Castro DAPT NOV2021
In this lab, you will be using the Sakila database of movie rentals.
- Instructions:
- 1. List number of films per
category. - 2. Display the first and last names, as well as the address, of each staff member.
- 3. Display the total amount rung up by each staff member in August of 2005.
- 4. List each film and the number of actors who are listed for that film.
- 5. Using the tables payment and customer and the JOIN command
- 1. List number of films per
Answer:
SELECT
c.`name` AS film_category,
count(fc.film_id) AS number_of_films
FROM
category c
INNER JOIN film_category AS fc ON c.category_id = fc.category_id
GROUP BY
1
ORDER BY
2 DESC;
Answer:
SELECT
concat((last_name), ', ', (first_name)) AS staff_member_name,
address
FROM
staff s
INNER JOIN address a ON s.address_id = a.address_id
ORDER BY
1;
Answer:
SELECT
concat((last_name), ', ', (first_name)) AS staff_member_name,
sum(amount) AS total_sales_august_2005
FROM
staff s
INNER JOIN payment p ON s.staff_id = p.staff_id
WHERE
monthname(payment_date) = 'August'
AND year(payment_date) = 2005
GROUP BY
1;
Answer:
SELECT
f.title,
count(fa.actor_id) AS number_of_actors
FROM
film f
INNER JOIN film_actor fa ON f.film_id = fa.film_id
GROUP BY
f.film_id
ORDER BY
2 DESC
LIMIT
10;
-
list the total paid by each customer.
-
List the customers alphabetically by last name.
Answer:
SELECT
concat((last_name), ', ', (first_name)) AS customer_name,
sum(amount) AS amount_total_paid
FROM
customer c
INNER JOIN payment p ON c.customer_id = p.customer_id
GROUP BY
p.customer_id
ORDER BY
1
LIMIT
10;