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

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

select 
        q2.id_shelter,
        q2.name_shelter,
        q2.email_shelter,
        q2.telephone_shelter,
        org.name_organisation as name_of_organisation,
        q2.average_verified_adopters
from
        (
                select 
                        shelter.id_shelter,
                        shelter.name_shelter,
                        shelter.email_shelter,
                        shelter.telephone_shelter,
                        avg(adopters_verified) as average_verified_adopters
                from 
                        shelter
                left join
                        (
                                select
                                        employee.id_user as employee_id,
                                        employee.id_shelter as shelter_id,
                                        count(distinct adopter.id_user) as adopters_verified
                                from 
                                        employee
                                join adopter
                                        on adopter.verified_by_employee = employee.id_user
                                group by 
                                        employee.id_user
                        ) as q1
                        on q1.shelter_id = shelter.id_shelter 
                group by 
                        shelter.id_shelter
        ) as q2              
left join shelter as sh
        on sh.id_shelter = q2.id_shelter
left join organisation as org
        on org.id_organisation = sh.id_organisation
order by 
        q2.average_verified_adopters desc

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

select 
        q2.id_adoption as id,
        q2.start_date,
        q2.end_date_foster,
        q2.status,
        user_table.name_user as adopter_name,
        user_table.email_user as adopter_email,
        user_table.telephone_user as adopter_phone,
        q2.number_of_pets,
        q2.number_of_species,
        q2.total_therapies,
        q2.total_interventions
from 
        (
                select 
                        adoption.id_adoption,
                        adoption.start_date,
                        adoption.end_date_foster,
                        case 
                        when adoption.approved = false then 'under review'
                        when adoption.approved = true then 'approved'
                        end 
                        as status,
                    count(distinct q1.id_pet) as number_of_pets,
                    count(distinct q1.species) as number_of_species,
                    sum(q1.therapies) as total_therapies,
                    sum(q1.interventions) as total_interventions
                from
                        (
                                select 
                                        pet.id_pet,
                                        pet.name_pet,
                                        pet.id_adoption,
                                        pet.species,
                                    count(distinct pnt.id_therapy) as therapies,
                                    count(distinct description) as interventions
                                from 
                                        pet
                                left join
                                        pet_needs_therapy pnt
                                        on pnt.id_pet = pet.id_pet 
                                left join 
                                        pet_needs_intervention_in_vet_clinic pni
                                        on pni.id_pet = pet.id_pet 
                                group by 
                                        pet.id_pet
                        ) as q1
                right join 
                        adoption
                        on q1.id_adoption = adoption.id_adoption 
                group by 
                        adoption.id_adoption
        ) as q2
left join       
        adoption
        on adoption.id_adoption = q2.id_adoption
left join 
        adopter 
        on adopter.id_user = adoption.id_adopter 
left join 
        user_table
        on user_table.id_user = adopter.id_user 

Приказ на секоја храна која ја јадат миленици со ниска физичка активност кои примаат терапии. За секоја од нив да се прикаже и вкупниот број такви миленици.

select 
        food.name_food as food_name,
        sum(q2.has_therapies) as total_pets
from 
        (
        select 
        q1.pet_id,
        q1.has_therapies
        from 
                (
                        select
                                pet.id_pet as pet_id,
                                count(therapy.id_therapy) as has_therapies
                        from 
                                pet
                        join
                                pet_needs_therapy pnt 
                                on pnt.id_pet = pet.id_pet 
                        join
                                therapy
                                on therapy.id_therapy = pnt.id_therapy 
                        group by 
                                pet.id_pet
                ) as q1
                join personal_profile  
                        on q1.pet_id = personal_profile.id_pet
                where 
                        personal_profile.physical_activity = 0
        ) as q2
join pet_preferably_eats_food ppef 
        on ppef.id_pet = q2.pet_id
join food
        on food.id_food = ppef.id_food
group by 
        food.id_food 
order by 
        total_pets

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

select 
        q3.id_user,
        user_table.name_user,
        user_table.email_user,
        user_table.telephone_user,
        q3.pets_from_surendee,
        q3.pets_from_shelter
from 
        (
                select 
                        adopter.id_user,
                        sum(q1.surendee_count) as pets_from_surendee,
                        sUm(q2.shelter_count) as pets_from_shelter
                from 
                        adopter
                left join
                        (
                                select
                                        adoption.id_adoption,
                                        adoption.id_adopter,
                                        count(surendee.id_user) as surendee_count
                                from    
                                        adoption
                                left join
                                        pet 
                                        on pet.id_adoption = adoption.id_adoption 
                                left join
                                        post
                                        on post.id_pet = pet.id_pet 
                                left join 
                                        surendee
                                        on surendee.id_user = post.id_surendee 
                                group by 
                                        adoption.id_adoption
                        ) as q1
                        on q1.id_adopter = adopter.id_user 
                left join 
                        (
                                select
                                        adoption.id_adoption,
                                        adoption.id_adopter,
                                        count(employee.id_user) as shelter_count
                                from    
                                        adoption
                                left join
                                        pet 
                                        on pet.id_adoption = adoption.id_adoption 
                                left join
                                        post
                                        on post.id_pet = pet.id_pet 
                                left join 
                                        employee
                                        on employee.id_user = post.id_employee
                                group by 
                                        adoption.id_adoption
                        ) as q2
                        on q2.id_adopter = adopter.id_user
                group by 
                        adopter.id_user 
                order by 
                        adopter.id_user 
        ) as q3
left join 
        user_table
        on user_table.id_user = q3.id_user

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

select q2.god as godina, q2.maximum as max, 
        case 
        when q3.species = 0 then 'cat'
        when q3.species = 1 then 'dog'
        when q3.species = 2 then 'bird'
        end as species
from
        (       select q1.godina as god, max(broj_posvojuvanja) as maximum from
                (
                select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja
                from adoption a 
                join pet p2 on a.id_adoption = p2.id_adoption 
                where (end_date_foster is null) and (a.approved is true)
                group by godina, p2.species
                order by godina
                ) q1
                group by godina
        )q2
        join
        (
        select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja
                from adoption a
                join pet p2 on a.id_adoption = p2.id_adoption 
                where (end_date_foster is null) and (a.approved is true)
                group by godina, p2.species
                order by godina
        ) q3
on q2.god = q3.godina and q2.maximum = q3.broj_posvojuvanja

Извештаи за организации - број на вработени, број на верифицирани посвојувачи од страна на вработени во таа организација и вкупен број на посвојувања направени од посвојувачи кои биле верифицирани од страна на вработени во таа организација

select 
        o.name_organisation,
        sum(q3.number_of_employees) as number_of_employees,
        sum(q2.verified_adopters) as verified_adopters,
        sum (q2.number_of_adoptions_under_employee) as number_of_adoptions_under_employee
from shelter s 
join
        (
        select 
                e.id_user,
                e.id_shelter,
                count(distinct q1.id_user) as verified_adopters,
                sum(q1.number_of_adoptions) as number_of_adoptions_under_employee
        from
                (
                select 
                                a.id_user,
                                a.verified_by_employee,
                                count(distinct a2.id_adoption) as number_of_adoptions
                from adopter a 
                join adoption a2 on a.id_user = a2.id_adopter 
                group by a.id_user
                ) q1
        join employee e on e.id_user = q1.verified_by_employee
        group by e.id_user
        order by e.id_user asc
        ) q2 on s.id_shelter = q2.id_shelter
join (
                select
                        s.id_shelter,
                        count(distinct e2.id_user) as number_of_employees
                from shelter s 
                join employee e2 on e2.id_shelter = s.id_shelter 
                group by s.id_shelter 
        ) q3 on q3.id_shelter = s.id_shelter 
        join organisation o on s.id_organisation = o.id_organisation 
group by o.id_organisation 
order by o.name_organisation

За секој посвојувач, број на посвоени миленичиња од секој вид (мачки, кучиња, птици) и вкупен број посвоени миленици. Табелата е подредена во опаѓачки редослед според бројот на посвојувања т.ш. посвојувачот со највеќе посвојувања е во првиот ред.

select 
        ut.name_user as posvojuvac, 
        count(distinct pm.id_pet) as broj_posvoeni_macki,
        count(distinct pd.id_pet) as broj_posvoeni_kucinja,
        count(distinct pp.id_pet) as broj_posvoeni_ptici,
        count(distinct p.id_pet) as vkupno_posvoeni_milenici
from
        adopter a
join 
        user_table ut on a.id_user = ut.id_user
left join 
        adoption ad on ad.id_adopter = a.id_user
left join 
        pet pm on pm.id_adoption = ad.id_adoption
        and
        pm.species = 0
left join 
        pet pd on pd.id_adoption = ad.id_adoption
        and
        pd.species = 1
left join 
        pet pp on pp.id_adoption = ad.id_adoption
        and
        pp.species = 2
left join
        pet p on p.id_adoption = ad.id_adoption 
group by 
        a.id_user, ut.name_user
order by 
        vkupno_posvoeni_milenici desc

Направени донации во секоја организација

select o.name_organisation, 
        count (d.id_user) as broj_donacii
from organisation o 
left join donor_donates_to_organisation ddto on o.id_organisation = ddto.id_organisation
left join donor d on ddto.id_user = d.id_user
group by o.id_organisation
order by o.id_organisation 

---

​Назад кон почетна