Version 1 (modified by 11 months ago) ( diff ) | ,
---|
Напредни извештаи од базата (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;
Note:
See TracWiki
for help on using the wiki.