Напредни извештаи од базата (SQL и складирани процедури)

• Ресторан со најмногу нарачки во последните 3 месеци (приказ на ресторани со сите нарачки за последните 3 месеци, сортирани?)

  SELECT q1.restoran_ime as restoran, q1.naracki as broj_na_naracki 
  FROM(
  SELECT r.restoran_ime, COUNT(n.naracka_id) as naracki
  FROM restoran as r
  JOIN meni as m on r.restoran_id=m.restoran_id 
  JOIN obrok as o on m.meni_id=o.meni_id 
  JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
  JOIN naracka as n on sso.naracka_id=n.naracka_id
  where n.naracana_na between (now()-interval '3 months') and now()
  GROUP BY r.restoran_ime) q1
  WHERE q1.naracki=(SELECT MAX(naracki)FROM (
  SELECT r.restoran_ime, COUNT (n.naracka_id) as naracki
  FROM restoran as r
  JOIN meni as m on r.restoran_id=m.restoran_id 
  JOIN obrok as o on m.meni_id=o.meni_id 
  JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
  JOIN naracka as n on sso.naracka_id=n.naracka_id
  where n.naracana_na between (now()-interval '3 months') and now()
  GROUP BY r.restoran_ime) q2
  )
  

• Ресторан со најголем профит (исто како горе)

  select rr.restoran_ime as restoran, q1.cena_vkupna as profit from restoran rr join(select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna 
  from restoran r
  join meni as m on r.restoran_id=m.restoran_id 
  join obrok as o on m.meni_id=o.meni_id 
  join se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
  join naracka as n on sso.naracka_id = n.naracka_id
  group by r.restoran_id)q1
  on q1.restoran_id=rr.restoran_id
  where cena_vkupna=(select max(cena_vkupna) from(
  select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna 
  from restoran r
  join meni as m on r.restoran_id=m.restoran_id 
  join obrok as o on m.meni_id=o.meni_id 
  join se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
  join naracka as n on sso.naracka_id = n.naracka_id
  group by r.restoran_id
  )q2)
  

• Најнарачан производ по ресторан сортиран

  select q1.restoran_ime as restoran, MAX(q1.obrok_ime) as obrok, MAX(q1.br_obroci) as broj_naracki FROM(
  SELECT r.restoran_ime, o.obrok_ime,  SUM(sso.kolicina) as br_obroci
  FROM restoran as r
  JOIN meni as m on r.restoran_id=m.restoran_id 
  JOIN obrok as o on m.meni_id=o.meni_id 
  JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
  JOIN naracka as n on sso.naracka_id=n.naracka_id
  GROUP BY r.restoran_ime, o.obrok_ime)q1
  group by restoran
  order by broj_naracki desc
  

• Кој корисник колку има платено во секој ресторан подредено

  select k2.user_ime as ime, k2.user_prezime as prezime, r2.restoran_ime as restoran, q1.plateno as plateno from( 
  select ku.user_id, r.restoran_id, sum(sso.cena * sso.kolicina) as plateno
  from korisnik k
  join kupuvac as ku on k.user_id =ku.user_id 
  join naracka as n on ku.user_id =n.user_id_kupuvac 
  join se_sostoi_od as sso on n.naracka_id =sso.naracka_id 
  join obrok as o on sso.obrok_id =o.obrok_id 
  join meni as m on o.meni_id=m.meni_id 
  join restoran as r on m.restoran_id =r.restoran_id 
  group by  ku.user_id ,r.restoran_id)q1 join korisnik k2 on q1.user_id=k2.user_id  join restoran r2 on q1.restoran_id=r2.restoran_id
  order by plateno desc