The following questions will help you to practice writing SQL statements. The qu
ID: 3917376 • Letter: T
Question
The following questions will help you to practice writing SQL statements. The questions are based on the "sakila" database schema on MySQL Workbench.
1. Write the sql statement that will provide you a list of store's cities and countries. Where are they located (city and country)? Show your results.
2. Write the sql statement that will provide you the total revenue for those stores. Show your results including store number, city and country.
3. Write the sql statement that will provide you the total revenue per year for those stores. Show your results including store number, city and country.
4. Write the sql statement that will provide you the average revenue per month for those stores. Show your results including store number, city and country.
5. Write the sql statement that will provide you the total revenue per month per year for those stores. Show your results including store number, city and country.
The EERD is below.
MySQL Inventory country id SMALLENT ??country VARCHAR(50) ? last-update TIMESTAMP tfilm_id SMALLINT catepory_d TINYINT ? 1stupdate TIMESTAMP category id TINYINT name YARCHAR(25) last-update TIMESTAMP - city id SMALLINT ?city VARCHAR(50) ? country-d SMALLINT lst update TIMESTAMP film_d SMALL INT title VARCHAR(255) descrption TET redease year YEAR anguage_jd TINYINT ? name CHAR(20) actor id SMALLINT ? first-name VARCHAR(45) last name VARCHAR(45) ? Est-update TIMESTAMP store-id TINYINT ? first nam e VARCHAR(45 ? language_id TINYINT ? orig naU ano age-d TINYINT address -4 >lastupdate TIMESTAMP last name VARCrAR(45) - address id SMALL INT -e emal VARCHAR(50) rental-duraton TINYINT rental rate DECIMAL(4,2) lengh SMIALLINT ? repacement_cost DECIMAL (5,20 ratng ENLM.) Ospecial features SET address VARCHAR( 50) ? address2 VARCHAR(50) ? district VAR? AR(20) address_d SMALLINT ? active BOOLEAN ?create-date DATETIME last update TIMESTAMP ty id SMALLINT ? postal-code VAR?AR(10) ? phone VARCHAR (20) lst update TIMESTAMP actor id SMALLINT t fim_jd SMALLINT last-update TIMESTAMP last-update TIMESTAMP Customer related data inventory_id MEDIU MINT filmd SMALLINT - store dTININT flm id SMALLINT ?title VARCHAR(255) ? description TEXT H t ?1st-update TIMESTAMP Movie database store id TINYINT staff id TINYINT maneger stff id TENYINT addressjd SMALLINT lastupdate TIMESTAMP ? first-name VAROAR(45)?? last name VARCHAR(45) ? address id SMALLINT ?emal VARCHAR(50) nicer but slower film list ?store id TINYINT active BOOLEAN actor_info film_not _n_stodk imventory_hald by_au. username VARCHARC16) ? password VARCHAR(40) ? last-update TIMESTAMP renta d INT ? rentadate DATETIME ? inventory-id MEDIUM NT -- - paym ent_id SMA. LINT ?customer_id SMALLINT ? staff_d TINYINT | rewards report ? reurn_dar DATETINE -renta_id INT sales by_film_category ? staff id TDWINT amount DECIMAL( 5,2) ? payment-date DATETIME ? 1st-pdate TIMESTAMP last-update TIMESTAMP staff_list customer_list Data required to run the business Special view on catain cata used for appraisasExplanation / Answer
-- 1
Select st.store_id, ct.city, cn.country
From sakila.store st inner join sakila.address ad
on st.address_id = ad.address_id
inner join sakila.city ct
on ad.city_id = ct.city_id
inner join sakila.country cn
on ct.country_id = cn.country_id;
-- 2
Select st.store_id, ct.city, cn.country, SUM(py.amount) "Amount"
From sakila.store st inner join sakila.address ad
on st.address_id = ad.address_id
inner join sakila.city ct
on ad.city_id = ct.city_id
inner join sakila.country cn
on ct.country_id = cn.country_id
inner join sakila.inventory inv
on st.store_id = inv.store_id
inner join sakila.rental rn
on inv.inventory_id = rn.inventory_id
inner join sakila.payment py
on rn.rental_id = py.rental_id
group by st.store_id, ct.city, cn.country;
-- 3
Select st.store_id, ct.city, cn.country, YEAR(py.payment_date) "Year", SUM(py.amount) "Amount"
From sakila.store st inner join sakila.address ad
on st.address_id = ad.address_id
inner join sakila.city ct
on ad.city_id = ct.city_id
inner join sakila.country cn
on ct.country_id = cn.country_id
inner join sakila.inventory inv
on st.store_id = inv.store_id
inner join sakila.rental rn
on inv.inventory_id = rn.inventory_id
inner join sakila.payment py
on rn.rental_id = py.rental_id
group by st.store_id, ct.city, cn.country, YEAR(py.payment_date);
-- 4
Select tbl.store_id, tbl.city, tbl.country, tbl.Month, AVG(tbl.Amount)
From
(Select st.store_id, ct.city, cn.country, YEAR(py.payment_date) "Year", MONTHNAME(py.payment_date) "Month", SUM(py.amount) "Amount"
From sakila.store st inner join sakila.address ad
on st.address_id = ad.address_id
inner join sakila.city ct
on ad.city_id = ct.city_id
inner join sakila.country cn
on ct.country_id = cn.country_id
inner join sakila.inventory inv
on st.store_id = inv.store_id
inner join sakila.rental rn
on inv.inventory_id = rn.inventory_id
inner join sakila.payment py
on rn.rental_id = py.rental_id
group by st.store_id, ct.city, cn.country, YEAR(py.payment_date), MONTHNAME(py.payment_date)) as tbl
Group by tbl.store_id, tbl.city, tbl.country, tbl.Month;
-- 5
Select st.store_id, ct.city, cn.country, YEAR(py.payment_date) "Year", MONTHNAME(py.payment_date) "Month", SUM(py.amount) "Amount"
From sakila.store st inner join sakila.address ad
on st.address_id = ad.address_id
inner join sakila.city ct
on ad.city_id = ct.city_id
inner join sakila.country cn
on ct.country_id = cn.country_id
inner join sakila.inventory inv
on st.store_id = inv.store_id
inner join sakila.rental rn
on inv.inventory_id = rn.inventory_id
inner join sakila.payment py
on rn.rental_id = py.rental_id
group by st.store_id, ct.city, cn.country, YEAR(py.payment_date), MONTHNAME(py.payment_date);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.