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

Извештај за сите решени случаеви од минатата година кој што ги водат полициските станици во секорот за внатрешни работи во Скопје. Дополнително за секој решен случај да се пресмета бројот на дадени изјави

select cc.c_id as c_id,cc.c_name as c_name,
cc.opening_date as opening_date,sc.date_of_closing as date_of_closing,
sc.final_document as final_document,
(select count(*) from statements  where c_id=cc.c_id) as broj_na_izjavi
from
sector_of_interal_affairs sia
join police_station ps on sia.s_id=ps.s_id
join crime_case cc on ps.p_id=cc.p_id
join solved_case sc on cc.c_id=sc.c_id
where
sia.city ~ '(Skopje|Скопје)'

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

select ps.p_id, ps.p_address,
        count(po.pe_id) as vkupno_policajci,
        (select count(*)
                        from people p
                        join policeman pol on p.pe_id=pol.pe_id
                        where
                        p.nationality='Македонец' and pol.p_id=ps.p_id) as Makedonci_nachalnici_i_policajci,
        (select count(*) from policeman pol
        where p_date_of_employment between now()-interval '2 years' and now()
        and pol.p_id=ps.p_id ) as Vraboteni_vo_interval_2_god
from police_station ps
join policeman po on po.p_id = ps.p_id
group by ps.p_id;

Извештај за секоја сектор за внатрешни работи, колку вработени има во сите полициски станици секторот кои што имаат ранг поголем од 2 и се помлади од 30 години

select sia.s_id as sector_id, sia.city as sector_city,
ps.p_id as police_station_id, ps.p_address as police_station_address,
(select count(*) from 
 people pe 
 join policeman pl on pe.pe_id=pl.pe_id
 where rank>2 and pl.p_id=ps.p_id) as broj_na_vraboteni_so_rang_pogolem_od_2,
 (select count(*) from 
    crime_case cc
    join solved_case  sc on cc.c_id=sc.c_id
    where
    cc.p_id=ps.p_id
 ) as broj_na_resheni_slucai
from sector_of_interal_affairs sia
join police_station ps on sia.s_id=ps.s_id
join policeman p on ps.p_id=p.p_id

Извештај за секоја полициска станица, колку случаи биле отворени со тип на криминал ‘Семејно насилство’ и колку жртви биле жени од ромско потекло

select ps.p_id, ps.p_address, 
        (
        select count(cs.c_id) 
        from police_station ps
        join crime_case cs on cs.p_id = ps.p_id
        join statements st on st.c_id = cs.c_id
        join victim vic on vic.pe_id = st.victim_pe_id
        join people pe on pe.pe_id = vic.pe_id
        join case_belongs_to_type_of_crime cbtt on cbtt.c_id = cs.c_id
        join type_of_crime toc on toc.t_id = cbtt.t_id
        where pe.gender = 'Ж' and toc.t_name = 'Семејно насилство' 
        and pe.nationality = 'Ромка'
        ) as vk_sluchaevi_zheni_zhrtvi
from police_station ps
group by ps.p_id;

Извештај за секоја полициска станица кој полицаец има земено најмногу изјави во последната година

with stanici_policajci_izjavi
         as (select ps.p_id, ps.p_address, pm.pe_id, pm.badge_no, count(s.c_id) br_izjavi
             from police_station ps
                      left join policeman pm on ps.p_id = pm.p_id
                      left join statements s on pm.pe_id = s.pe_id
             group by ps.p_id, ps.p_address, pm.pe_id, pm.badge_no),
     stanici_max_izjavi
         as (select psi.p_id, psi.p_address, max(psi.br_izjavi) max_izjavi
             from stanici_policajci_izjavi psi
             group by psi.p_id, psi.p_address)

select psi.p_id, psi.p_address, psi.pe_id, psi.badge_no, psi.br_izjavi
from stanici_policajci_izjavi psi
         join stanici_max_izjavi smi
              on (psi.p_id, psi.p_address, psi.br_izjavi) = (smi.p_id, smi.p_address, smi.max_izjavi)

Извештај за секој тип на криминал која полициска станица има отворено најмногу случаеви со тој тип на криминал

SELECT 
    toc.t_id AS type_id,
    toc.t_name AS type_name,
    ps.p_id AS station_id,
    ps.p_address ,
    COUNT(cs.c_id) AS case_count
FROM 
    type_of_crime toc
JOIN case_belongs_to_type_of_crime cbtt ON toc.t_id = cbtt.t_id
JOIN crime_case cs ON cbtt.c_id = cs.c_id
JOIN police_station ps ON cs.p_id = ps.p_id
GROUP BY 
    toc.t_id, toc.t_name, ps.p_id, ps.p_address 
HAVING 
    COUNT(cs.c_id) = (
        SELECT 
            MAX(case_count)
        FROM (
            SELECT 
                toc_sub.t_id AS type_id,
                COUNT(cs_sub.c_id) AS case_count
            FROM 
                type_of_crime toc_sub
            JOIN 
                case_belongs_to_type_of_crime cbtt_sub ON toc_sub.t_id = cbtt_sub.t_id
            JOIN 
                crime_case cs_sub ON cbtt_sub.c_id = cs_sub.c_id
            GROUP BY 
                toc_sub.t_id
        ) max_cases
        WHERE max_cases.type_id = toc.t_id
    )
ORDER BY 
    toc.t_id, case_count DESC;