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

Ресторан со најмногу нарачки во последните 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 r.restoran_ime,
coalesce((select count( distinct o.obrok_id)
                from obrok o
                left join meni m on o.meni_id=m.meni_id
                left join restoran r1 on m.restoran_id=r1.restoran_id
                where r.restoran_id=r1.restoran_id) ,0) as broj_obroci,
coalesce((select SUM( sso.kolicina)
                from se_sostoi_od sso 
                left join obrok o on sso.obrok_id=o.obrok_id 
                left join meni m on o.meni_id=m.meni_id
                left join restoran r1 on m.restoran_id=r1.restoran_id
                where r.restoran_id=r1.restoran_id) ,0) as broj_prodadeni_obroci,
coalesce((select COUNT(  distinct k.user_id )
                from kupuvac k
                left join naracka n on k.user_id=n.user_id_kupuvac 
                left join se_sostoi_od sso on n.naracka_id=sso.naracka_id 
                left join obrok o on sso.obrok_id=o.obrok_id 
                left join meni m on o.meni_id=m.meni_id
                left join restoran r1 on m.restoran_id=r1.restoran_id
                where r.restoran_id=r1.restoran_id) ,0) as broj_kupuvaci,
coalesce((select SUM( sso.cena * sso.kolicina)
                from se_sostoi_od sso 
                left join obrok o on sso.obrok_id=o.obrok_id 
                left join meni m on o.meni_id=m.meni_id
                left join restoran r1 on m.restoran_id=r1.restoran_id
                where r.restoran_id=r1.restoran_id) ,0) as zarabotka,
r.restoran_rejting as rejting
from restoran r

За секој купувач, за секој ресторан колку оброци има нарачано од секој ресторан, колку пари има потрошено во секој ресторан и колку пати има направено резервација во секој ресторан.

select k.user_ime as ime, r.restoran_ime as restoran,q1.broj_obroci,q1.plateno,q1.broj_rezervacii from korisnik k , restoran r,(
                select k.user_id , r.restoran_id,
                coalesce((select sum( sso.kolicina)
                                from kupuvac as ku 
                                left join naracka as n on ku.user_id =n.user_id_kupuvac 
                                left join se_sostoi_od as sso on n.naracka_id =sso.naracka_id 
                                left join obrok as o on sso.obrok_id =o.obrok_id 
                                left join meni as m on o.meni_id=m.meni_id 
                                left join restoran as r1 on m.restoran_id =r1.restoran_id 
                                where r.restoran_id=r1.restoran_id and k.user_id =ku.user_id  ) ,0) as broj_obroci,
                coalesce((select SUM( sso.cena * sso.kolicina)
                                from kupuvac as ku 
                                left join naracka as n on ku.user_id =n.user_id_kupuvac 
                                left join se_sostoi_od as sso on n.naracka_id =sso.naracka_id 
                                left join obrok as o on sso.obrok_id =o.obrok_id 
                                left join meni as m on o.meni_id=m.meni_id 
                                left join restoran as r1 on m.restoran_id =r1.restoran_id 
                                where r.restoran_id=r1.restoran_id and k.user_id =ku.user_id  ) ,0) as plateno,
                coalesce((select COUNT( re.rezervacija_id)
                                from kupuvac as ku 
                                left join rezervacija re on ku.user_id=re.user_id 
                                left join restoran as r1 on re.restoran_id =r1.restoran_id
                                where r.restoran_id=r1.restoran_id and k.user_id =ku.user_id  ) ,0) as broj_rezervacii
                from korisnik k
                left join kupuvac ku on k.user_id=ku.user_id 
                left join rezervacija re ON k.user_id=re.user_id
                left join naracka n on k.user_id=n.user_id_kupuvac 
                left join se_sostoi_od sso on n.naracka_id=sso.naracka_id 
                left join obrok o on sso.obrok_id=o.obrok_id 
                left join meni m on o.meni_id=m.meni_id
                left join restoran r on re.restoran_id=r.restoran_id or m.restoran_id=r.restoran_id 
                group by k.user_id , r.restoran_id)q1 
where q1.user_id=k.user_id and q1.restoran_id=r.restoran_id 

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

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 ime