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

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

select  q4.id_person1 as id_person, q4.PERSON 
     q4.id_member1 as id_member , q4.MEMBER_LIBRARY as namee, sum(q4.owes) as owes from (
        
        
        
        
        select q3.id_person1, q3.person1 || ' ' || q3.person_ime1 as PERSON ,
                         q3.id_member1, q3.namee_member1 as MEMBER_LIBRARY , (q3.price - q3.PRICELIST) as owes 
                         from
                         (
                                 select * from
                         
                                 (
                                 select p.id_person  as id_person1, p.namee  as person1, p.surname  person_surname1,
                                                                         ml.id_member  as id_member1, ml.namee  || ' ' || ml.surname  namee_member1,
                                 sum(suma.iznos) as price  from project.member_library ml
                                 join project.reservation_place rp  on rp.id_person = p.id_person 
                                 join project.reservation_book rb  on rb.id_price  = rp.id_price 
                                 join project.book b on b.id_member = ml.id_member 
                                 join project.person p on p.id_person = b.id_person 
                                 group by 1,2,3,4
                                 ) q1
                         
                              join
                               (
                              select p.id_person as id_person2, p.namee as person2, p.surname  person_surname2,
                                                                    ml2.id_member  as id_member2, ml2.namee  || ' ' || mb2.surname  as  namee_person2,
                                 (rb.book_price * rp.duration) price from project.member_library ml2 
                                 join project.reservation_place rp on rp.id_member = ml2.id_member 
                                 join project.person p on p.id_person = rp.id_person 
                                 ) q2
                                 on q1.namee_member1 = q2.namee_member2
                         ) q3
                )q4
                group by 1,2,3,4
                order by id_person 

Број на книги во секоја библиотека

select ld.names as library_db , count(b.id_book) number_of_books
    from library_db ld
    join book b on b.id_library = ld.id_library  
    group by ld.id_library 
    order by ld.names 

Библиотеки со најголем број на членови на годишно ниво

select q4.yearss as years, ld.names as library_db , q4.number_member as number_member 
    from(
              
              select * from
              (
              select q1.years  as yearss, max(q1.number_member) as maximum from 
              (
              select extract (year from rp.dates) as years,  ld.id_library as library_db, count(ml.id_member) number_member 
              from library_db ld 
              join member_library ml on ml.id_library = ld.id_library 
              group by yearss, library_db 
              )q1
              group by yearss              
              
          ) q2
          join 
          (
          select extract (year from rp.dates) as years , ld.id_library as library_db , count(ml.id_member) number_member 
          from library_db ld 
          join member_library  ml on ml.id_library = ld.id_library 
          group by years, library_db 
         ) q3
         
         on q2.yearss=q3.years  and q2.maximum = q3.number_member 
    )q4
    join library_db ld on ld.id_library = q4.library_db 
    order by years desc;

Колку пати една книга во одредена библиотека е симната онлајн

select b.namee as book, ld.names as library_db, count(od.id_onlinedown) as br_verzii
   from project.book b 
   join project.online_download od on b.id_book = od.id_book  
   join project.library_db ld on b.id_library  = ld.id_library 
   join project.reservation_book rb on b.id_book = rb.id_book 
  
  where b.id_book = 1
  group by b.namee , ld.names 
  order by br_verzii  desc; 

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

select ld.id_library, 
       case when number_of_reservation.kvartal = 1 then number_of_reservation.number_reservation else 0 end as Kvartal1_number_reservation,
       case when number_of_reservation.kvartal = 2 then number_of_reservation.number_reservation else 0 end as Kvartal2_number_reservation,
       case when number_of_reservation.kvartal = 3 then number_of_reservation.number_reservation else 0 end as Kvartal3_number_reservation,
       case when number_of_reservation.kvartal = 4 then number_of_reservation.number_reservation else 0 end as Kvartal4_number_reservation
       
       from project.library_db as ld 
       left join  project.member_library as ml on ld.id_library = ml.id_library 
       left join project.employee as e on ml.id_person  = e.id_person 
       left join project.reservation_place as rp on e.id_person  = rp.id_member 
       
       
       left join(
       select ld.id_library, extract (quarter from rp.duration) as kvartal, count (rp.id_resRoom ) as number_reservation 
       from project.library_db ld
       left join  project.member_library as ml on ld.id_library = ml.id_library 
       left join project.employee as e on ml.id_person  = e.id_person 
       left join project.reservation_place as rp on e.id_person = rp.id_member 
       where extract (year from rp.dates ) = extract (year from current_date)
       group by 1,2
       )as number_of_reservation  on ld.id_library = number_of_reservation.id_library