Question

1. Write the sql statement that returns a list of store's cities and countries. Where are they located (city and country)? Show your results.
2. Write the sql statement that returns the total revenue for those stores. Show your results including store number, city and country.
3. Write the sql statement that returns the total revenue per year for those stores. Show your results including store number, city and country.
4. Write the sql statement that returns the average revenue per month regardless of store.Show your results by month number.
5. Write the sql statement that returns the revenue for the day of the week for those stores, regardless of year or month. Show your results including store number, city and country. Start the list on Sunday.

https://dev.mysql.com/doc/sakila/en/sakila-installation.html

DO NUMBER 3 -- all of the questions are required to know to do this in full context

Answer 1

Please find the SQL query below.

Q) Write the sql statement that returns the total revenue per year for those stores. Show your results including store number, city and country.

Query:

SELECT s.store_id, sum(p.payment), c.city, co.country from country co, city c, address a, store s, inventory i, rental r, payment p where s.store_id = i.store_id and i.inventory_id = r.inventory_id and r.rental_i = p.rental_id and s.address_id = a.address_id and a.city_id = c.city_id and c.country_id = co.country_id group by year(p.payment_date), s.store_id;