Yamenko_Modul_3_2.sql

--=============== ÌÎÄÓËÜ 3. ÎÑÍÎÂÛ SQL =======================================

--ÇÀÄÀÍÈÅ ¹1
--Âûâåäèòå äëÿ êàæäîãî ïîêóïàòåëÿ åãî àäðåñ ïðîæèâàíèÿ, 
--ãîðîä è ñòðàíó ïðîæèâàíèÿ.
select 
	customer_id, first_name, last_name, address, city, country
from
	customer
left join (
				select address_id, address, city, country 
				from address
				left join (
							select country, city, city_id
							from city
							left join country on city.country_id = country.country_id) as CT 
							on CT.city_id = address.city_id) as addr 
				on addr.address_id=customer.address_id;

--ÇÀÄÀÍÈÅ ¹2
--Ñ ïîìîùüþ SQL-çàïðîñà ïîñ÷èòàéòå äëÿ êàæäîãî ìàãàçèíà êîëè÷åñòâî åãî ïîêóïàòåëåé.

select store_id, count(store_id) 
from customer group by store_id;



--Äîðàáîòàéòå çàïðîñ è âûâåäèòå òîëüêî òå ìàãàçèíû, 
--ó êîòîðûõ êîëè÷åñòâî ïîêóïàòåëåé áîëüøå 300-îò.
--Äëÿ ðåøåíèÿ èñïîëüçóéòå ôèëüòðàöèþ ïî ñãðóïïèðîâàííûì ñòðîêàì 
--ñ èñïîëüçîâàíèåì ôóíêöèè àãðåãàöèè.
select store_id, count(store_id)
from customer group by store_id having count(store_id) > 300;




-- Äîðàáîòàéòå çàïðîñ, äîáàâèâ â íåãî èíôîðìàöèþ î ãîðîäå ìàãàçèíà, 
-- à òàêæå ôàìèëèþ è èìÿ ïðîäàâöà, êîòîðûé ðàáîòàåò â ýòîì ìàãàçèíå.

select store_id, city, staff_first_name, staff_last_name, count(store_id)
	from customer 
	left join 
		(select store_id, city, first_name staff_first_name, last_name staff_last_name
		from staff			
		right join 
					(select city, manager_staff_id 
					from store
					left join (
							select address_id, city
							from address 
							left join city 
							using (city_id)) as address_city 
					using(address_id)) as address_store 
		on address_store.manager_staff_id = staff.staff_id) as address_store_manager
	using(store_id)
GROUP by store_id, city, staff_first_name, staff_last_name having count(store_id) > 300;

--ÇÀÄÀÍÈÅ ¹3
--Âûâåäèòå ÒÎÏ-5 ïîêóïàòåëåé, 
--êîòîðûå âçÿëè â àðåíäó çà âñ¸ âðåìÿ íàèáîëüøåå êîëè÷åñòâî ôèëüìîâ
select customer_id, count(customer_id), first_name, last_name 
from rental
left join customer using(customer_id)
group by customer_id, first_name, last_name order by count(customer_id) desc limit 5;


--ÇÀÄÀÍÈÅ ¹4
--Ïîñ÷èòàéòå äëÿ êàæäîãî ïîêóïàòåëÿ 4 àíàëèòè÷åñêèõ ïîêàçàòåëÿ:
--  1. êîëè÷åñòâî ôèëüìîâ, êîòîðûå îí âçÿë â àðåíäó
--  2. îáùóþ ñòîèìîñòü ïëàòåæåé çà àðåíäó âñåõ ôèëüìîâ (çíà÷åíèå îêðóãëèòå äî öåëîãî ÷èñëà)
--  3. ìèíèìàëüíîå çíà÷åíèå ïëàòåæà çà àðåíäó ôèëüìà
--  4. ìàêñèìàëüíîå çíà÷åíèå ïëàòåæà çà àðåíäó ôèëüìà
--äîáàâèì ñòîèìîñòü ôèëüìîâ ê òàáëèöå äëÿ ðàñ÷åòîâ è âûâåäåì îêîí÷àòåëüíóþ òàáëèöó

select p.customer_id, count(p.customer_id), round(sum(p.amount)), min(p.amount), max (p.amount)
from payment p
left join rental r using(rental_id) 
left join inventory i using (inventory_id)
left join film f using (film_id)
group by p.customer_id
order by round desc;

--ÇÀÄÀÍÈÅ ¹5
--Èñïîëüçóÿ äàííûå èç òàáëèöû ãîðîäîâ ñîñòàâüòå îäíèì çàïðîñîì âñåâîçìîæíûå ïàðû ãîðîäîâ òàêèì îáðàçîì,
--÷òîáû â ðåçóëüòàòå íå áûëî ïàð ñ îäèíàêîâûìè íàçâàíèÿìè ãîðîäîâ. 
--Äëÿ ðåøåíèÿ íåîáõîäèìî èñïîëüçîâàòü äåêàðòîâî ïðîèçâåäåíèå.
 
select a.city, b.city
from city a
cross join city b
where a.city <> b.city;

--ÇÀÄÀÍÈÅ ¹6
--Èñïîëüçóÿ äàííûå èç òàáëèöû rental î äàòå âûäà÷è ôèëüìà â àðåíäó (ïîëå rental_date)
--è äàòå âîçâðàòà ôèëüìà (ïîëå return_date), 
--âû÷èñëèòå äëÿ êàæäîãî ïîêóïàòåëÿ ñðåäíåå êîëè÷åñòâî äíåé, çà êîòîðûå ïîêóïàòåëü âîçâðàùàåò ôèëüìû.
 
select customer_id, avg(return_date - rental_date) as using_date
from rental
group by customer_id
order by using_date desc;



--======== ÄÎÏÎËÍÈÒÅËÜÍÀß ×ÀÑÒÜ ==============

--ÇÀÄÀÍÈÅ ¹1
--Ïîñ÷èòàéòå äëÿ êàæäîãî ôèëüìà ñêîëüêî ðàç åãî áðàëè â àðåíäó è çíà÷åíèå îáùåé ñòîèìîñòè àðåíäû ôèëüìà çà âñ¸ âðåìÿ.

select film_id, count(film_id), sum(p.amount) 
from film f
left join inventory i using (film_id)
left join rental r using (inventory_id)
left join payment p using (rental_id)
where payment_id is not null
group by film_id 
order by sum desc
;


--ÇÀÄÀÍÈÅ ¹2
--Äîðàáîòàéòå çàïðîñ èç ïðåäûäóùåãî çàäàíèÿ è âûâåäèòå ñ ïîìîùüþ çàïðîñà ôèëüìû, êîòîðûå íè ðàçó íå áðàëè â àðåíäó.

select * 
from film f
left join inventory i using (film_id)
left join rental r using (inventory_id)
left join payment p using (rental_id)
where payment_id is null
;

--ÇÀÄÀÍÈÅ ¹3
--Ïîñ÷èòàéòå êîëè÷åñòâî ïðîäàæ, âûïîëíåííûõ êàæäûì ïðîäàâöîì. Äîáàâüòå âû÷èñëÿåìóþ êîëîíêó "Ïðåìèÿ".
--Åñëè êîëè÷åñòâî ïðîäàæ ïðåâûøàåò 7300, òî çíà÷åíèå â êîëîíêå áóäåò "Äà", èíà÷å äîëæíî áûòü çíà÷åíèå "Íåò".
select staff_id, count(staff_id),
case 
	when count(staff_id) > 7300 then 'äà'
	else 'íåò'
end as Ïðåìèÿ
from payment
group by staff_id;