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

Извештај за секој ученик, кај кој наставник колку изнесува сумата што ја должи/што треба да ја доплати до крајот на часовите по договор

select q3.nastavnik1 || ' ' || q3.nastavnik_prezime1 as nastavnik,
q3.ime_ucenik1 as ucenik, (q3.vkupno_za_plakjanje-q3.plateno) as dolzi
from
(
        select * from

        (
        select n.ime as nastavnik1, n.prezime nastavnik_prezime1, u.ime || ' ' || u.prezime ime_ucenik1, 
        sum(p.iznos) plateno from ucenici u
        join slusanje s on s.id_ucenik = u.id_ucenik 
        join plakjanja p on p.id_plakjanja = s.id_plakjanja 
        join predava_na pn on pn.id_ucenik = u.id_ucenik 
        join nastavnici n on n.id_nastavnik =pn.id_nastavnik
        group by 1,2,3
        ) q1

        join

        (
        select n.ime as nastavnik2, n.prezime nastavnik_prezime2, u2.ime || ' ' || u2.prezime as ime_ucenik2, 
        (pn.cena_po_cas * pn.broj_casovi_po_dogovor) vkupno_za_plakjanje from ucenici u2
        join predava_na pn on pn.id_ucenik = u2.id_ucenik 
        join nastavnici n on n.id_nastavnik =pn.id_nastavnik
        ) q2
        on q1.ime_ucenik1 = q2.ime_ucenik2
) q3

Листа на предмети каде за секој предмет е прикажан најпопуларниот наставник (наставник со најголем број на ученици на кои им предава)

select p.ime as predmet, n.ime as ime_nastavnik, n.prezime as prezime_nastavnik, q3.ucenici as broj_ucenici
from 
(               
 select * from 
 (

                select broj_ucenici.predmet as predmet, max(broj_ucenici.ucenici) as ucenici 
                from 
                        (
                                select p.id_predmet as predmet, n.id_nastavnik, count(u.id_ucenik) as ucenici
                                from predmeti p 
                                join predava_predmet pp on p.id_predmet = pp.id_predmet 
                                join nastavnici n on pp.id_nastavnik = n.id_nastavnik 
                                join predava_na pn on n.id_nastavnik = pn.id_nastavnik 
                                join ucenici u on u.id_ucenik =pn.id_ucenik 
                                group by p.id_predmet, n.id_nastavnik 
                        ) 
                        as broj_ucenici 
                        group by broj_ucenici.predmet
 ) q1
        
                join 
                        (
                                select p.id_predmet as predmet2, n.id_nastavnik, count(u.id_ucenik) as ucenici2
                                from predmeti p 
                                join predava_predmet pp on p.id_predmet = pp.id_predmet 
                                join nastavnici n on pp.id_nastavnik = n.id_nastavnik 
                                join predava_na pn on n.id_nastavnik = pn.id_nastavnik 
                                join ucenici u on u.id_ucenik =pn.id_ucenik 
                                group by p.id_predmet, n.id_nastavnik 
                        ) q2 
                        on q2.predmet2 = q1.predmet and q2.ucenici2 = q1.ucenici

) q3
join nastavnici n on n.id_nastavnik = q3.id_nastavnik
join predmeti p on p.id_predmet = q3.predmet

За секоја година, предмети со најголем број одржани часови

select q4.god as godina, p.ime as predmet, q4.broj_casovi as broj_casovi
from
(

        select * from 
        (
                select q1.godina as god, max(q1.broj_casovi) as maksimum from
                (
                select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
                from predmeti p
                join casovi c on c.id_predmet = p.id_predmet 
                group by godina,predmet
                ) q1
                group by godina
        ) q2
        join 
        (
        select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
                from predmeti p
                join casovi c on c.id_predmet = p.id_predmet 
                group by godina,predmet
        ) q3
        on q2.god=q3.godina and q2.maksimum=q3.broj_casovi

) q4
join predmeti p on p.id_predmet = q4.predmet
order by godina desc;

Приказ на предмети, за секој предмет број на наставници кои го предаваат предметот и просечна заработувачка за секој предмет, врз основа на заработувачката на наставниците по предметот

select p.ime predmet, coalesce(q4.broj_nastavnici,0) broj_nastavnici, coalesce(q4.prosecna_zarabotuvacka,0) prosecna_zarabotuvacka
from
(
        select
        q3.id_predmet,
        count(q3.id_nastavnik) broj_nastavnici,
        (
                select avg(pl.iznos) from nastavnici n 
                left join plakjanja pl on pl.id_nastavnik = n.id_nastavnik 
                left join predava_predmet pp on pp.id_nastavnik = n.id_nastavnik 
                left join predmeti pr on pr.id_predmet = pp.id_predmet 
                group by pp.id_predmet 
                having pp.id_predmet=q3.id_predmet
        ) prosecna_zarabotuvacka
        from
        (
                select * from
                        (
                                select pred.id_predmet, pred.ime, n1.id_nastavnik 
                                from predmeti pred
                                left join predava_predmet pp1 on pp1.id_predmet = pred.id_predmet 
                                left join nastavnici n1 on n1.id_nastavnik = pp1.id_nastavnik ) q1
                join 
                        (
                                select n.id_nastavnik nastavnik, avg(pl.iznos) prosecna_zarabotuvacka
                                from nastavnici n 
                                left join plakjanja pl on pl.id_nastavnik = n.id_nastavnik 
                                left join predava_predmet pp on pp.id_nastavnik = n.id_nastavnik 
                                left join predmeti pr on pr.id_predmet = pp.id_predmet 
                                group by n.id_nastavnik 
                        ) q2 
                on q2.nastavnik=q1.id_nastavnik
        ) q3
        group by id_predmet
) q4
full outer join predmeti p on p.id_predmet = q4.id_predmet
order by prosecna_zarabotuvacka desc;

Предмет со најмногу заинтересирани ученици во последните 100 дена

select p.ime as predmet, count(zz.id_ucenik) as broj_na_zainteresirani_ucenici
from predmeti p 
join zainteresiran_za zz 
on zz.id_predmet = p.id_predmet 
join ucenici u 
on u.id_ucenik = zz.id_ucenik 
where zz.datum between current_date - interval '100 days' and current_date
group by p.ime
order by count(zz.id_ucenik) desc 
limit 1;

Број на наставници за секој предмет

select p.ime as predmet, count(pp.id_nastavnik) broj_na_nastavnici
from predmeti p
join predava_predmet pp on pp.id_predmet = p.id_predmet 
group by p.id_predmet 
order by p.ime;

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

select p.ime as predmet, n.ime as nastavnik, count(u.id_ucenik) as br_ucenici
from predmeti p 
join predava_predmet pp on p.id_predmet = pp.id_predmet 
join nastavnici n on pp.id_nastavnik = n.id_nastavnik 
join predava_na pn on n.id_nastavnik = pn.id_nastavnik 
join ucenici u on u.id_ucenik =pn.id_ucenik 
where p.id_predmet = 1
group by p.ime, n.ime
order by br_ucenici desc

Листа на наставници со број на одржани часови

select n.id_nastavnik, n.ime, n.prezime, count(c.id_cas) as br_casovi
from nastavnici n 
left join casovi c on c.id_nastavnik = n.id_nastavnik 
group by n.id_nastavnik, n.ime
order by br_casovi desc