--1
select u.login,isnull(SUM(k.cena),0) as celkovaUtrata from uzivatel u left join koupil k on u.login =k.login left join  vyrobek v on k.vID = v.vid
group by u.login

--2
select v.vID,v.jmeno,isnull(SUM(k.cena),0) as celkem from vyrobek v left join koupil k on k.vID=v.vID
group by v.vID,v.jmeno
order by SUM(k.cena) desc

--3
select v.vID,v.jmeno,isnull(count(*),0) as celkem 
from vyrobek v left join koupil k on k.vID=v.vID
left join uzivatel u on u.login=k.login
where (u.rok_narozeni between 1980 and 1995) and k.rok=2009
group by v.vID,v.jmeno
order by count(*) desc


--4
select u1.login, sum(case when k1.cena is null then 0 else 1 end) as KolikratNejlevneji 
from uzivatel u1 left join koupil k1 on u1.login=k1.login
where k1.cena < all
(
	select cena  
	from uzivatel u2 left join koupil k2 on u2.login = k2.login 
	where k1.login != k2.login and k1.vID = k2.vID
)
group by u1.login

--5
--nejprodávanější výrobek
select k1.vID,v1.jmeno from vyrobek v1, koupil k1, uzivatel u1
where v1.vID =k1.vID and k1.login = u1.login
group by k1.vID,v1.jmeno having COUNT(*) >= all
(
	select COUNT(*) from vyrobek v2, koupil k2, uzivatel u2
	where v2.vID =k2.vID and k2.login = u2.login
	group by k2.vID
)

--6
select v1.vid, max(isnull(k1.cena,0)) - min(isnull(k1.cena,0)) as rozptyl from vyrobek v1 
left join koupil k1 on v1.vid = k1.vid 
left join uzivatel u1 on u1.login = k1.login
group by v1.vid having max(isnull(k1.cena,0)) - min(isnull(k1.cena,0)) >= all
(
	select max(isnull(k2.cena,0)) - min(isnull(k2.cena,0)) as rozptyl from vyrobek v2 
	left join koupil k2 on v2.vid = k2.vid 
	left join uzivatel u2 on u2.login = k2.login
	group by v2.vid 
)