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

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

select q4.id_nastavnik1 as id_nastavnik,q4.nastavnik, 
        q4.id_ucenik1 as id_ucenik, q4.ucenik as ime_ucenik, sum(q4.dolzi) as dolzi from (
        
        
        
        
        select q3.id_nastavnik1, q3.nastavnik1 || ' ' || q3.nastavnik_prezime1 as nastavnik,
                         q3.id_ucenik1,q3.ime_ucenik1 as ucenik, (q3.vkupno_za_plakjanje-q3.plateno) as dolzi
                         from
                         (
                                 select * from
                         
                                 (
                                 select n.id_nastavnik as id_nastavnik1,n.ime as nastavnik1, n.prezime nastavnik_prezime1,
                                                                         u.id_ucenik as id_ucenik1,u.ime || ' ' || u.prezime ime_ucenik1,
                                 sum(pl.iznos) as plateno  from project.ucenici u
                                 join project.slusanje s on s.id_ucenik = u.id_ucenik
                                 join project.plakjanja pl on pl.id_plakjanja = s.id_plakjanja
                                 join project.predava_na pn on pn.id_ucenik = u.id_ucenik
                                 join project.nastavnici n on n.id_nastavnik =pn.id_nastavnik
                                 group by 1,2,3,4
                                 ) q1
                         
                                 join
                         
                                 (
                                 select n.id_nastavnik as id_nastavnik2,n.ime as nastavnik2, n.prezime nastavnik_prezime2,
                                                                         u2.id_ucenik as id_ucenik2, u2.ime || ' ' || u2.prezime as ime_ucenik2,
                                 (pn.cena_po_cas * pn.broj_casovi_po_dogovor) vkupno_za_plakjanje from project.ucenici u2
                                 join project.predava_na pn on pn.id_ucenik = u2.id_ucenik
                                 join project.nastavnici n on n.id_nastavnik =pn.id_nastavnik
                                 ) q2
                                 on q1.ime_ucenik1 = q2.ime_ucenik2
                         ) q3
                )q4
                group by 1,2,3,4
                order by id_nastavnik 

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

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