sql pivo

Pivo (id_pivo, znacka, stupen)
Pobockapivovaru (id_pobocka, jmeno_piv, mesto, adresa)
Odbira (id_pobocka, id_pivo, hospoda, cena)
Vari (id_pobocka, id_pivo)

-- Pro kazdou hospodu vypiste nazev nejlevnejsiho piva a jeho cenu
SELECT hospoda, znacka, min(cena) from odbira o, pivo p where o.id_pivo = p.id_pivo group by hospoda

-- Pro kazdou hospodu vypiste prumernou cenu piva
select hospoda, avg(cena) from odbira group by hospoda

-- Vypiste nazev pobocky od ktere je nejvice odebirano
select id_pobocka, count(*) as cou from odbira group by id_pobocka order by cou desc limit 1

-- Vypiste hospody, ktere odebiraji vice nez 4 piva
select hospoda from odbira group by hospoda having count(*) > 4

-- Pro kazdou pobocku pivovaru vypiste pocet piv ktere vari
select id_pobocka, count(*) from vari group by id_pobocka

-- Vypiste piva ktera nikdo nevari
select distinct id_pivo from pivo where id_pivo not in (select distinct id_pivo from vari)

-- Pro kazde pivo, vypiste jeho prumernou cenu
select id_pivo, avg(cena) from odbira group by id_pivo

-- Vypište rozdíl mezi nejdražším a nejlenějším prodávaným pivem
select (drahy.x-levny.x) from (select cena x from odbira order by cena limit 1) as levny, (select cena x from odbira order by cena desc limit 1) as drahy

-- Prumerna cena piva v hospode, ktera ma v nazvu 'p'
select avg(cena), hospoda from odbira where hospoda like '%p%'