Напредни извештаи од базата (SQL)

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

SELECT 
    COUNT(DISTINCT str.student_id) AS br_studenti_zemale_soba_prv_den_koja_pobarale,
    (SELECT 
         str.block_id 
     FROM StudentTookRoom str
     GROUP BY str.block_id
     ORDER BY COUNT(*) DESC 
     LIMIT 1
     ) AS blok_so_najvekje_zafateni_sobi,
     (
     SELECT COUNT(*) 
     FROM room r 
     WHERE r.is_available = FALSE
       AND r.block_id = (SELECT str.block_id 
                          FROM StudentTookRoom str
                          JOIN room r ON r.room_number = str.room_num AND r.block_id = str.block_id
                          WHERE r.is_available = FALSE
                          GROUP BY str.block_id
                          ORDER BY COUNT(*) DESC 
                          LIMIT 1)
     ) AS br_zafateni_sobi
FROM student s
JOIN roomrequest rr 
    ON s.u_id = rr.student_id
JOIN studenttookroom str 
    ON str.student_id = s.u_id
JOIN room r 
    ON r.room_number = str.room_num
JOIN block b 
    ON b.block_id = r.block_id
WHERE rr.status = 'Approved' 
  AND str.start_date = (SELECT MIN(start_date) FROM StudentTookRoom)
  AND rr.room_number = str.room_num
  group by b.num_available_rooms
  

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

SELECT 
    du.u_id AS student_id,
    CONCAT(du.first_name, ' ', du.last_name) AS student_name,
    rr.block_id AS requested_block,
    rr.room_number AS requested_room,
    str.block_id AS assigned_block,
    str.room_num AS assigned_room,
    COUNT(*) OVER (PARTITION BY rr.block_id, rr.room_number) AS mismatches_per_requested_room
FROM dorm_user du 
JOIN roomrequest rr 
    ON du.u_id = rr.student_id
JOIN studenttookroom str 
    ON str.student_id = du.u_id
WHERE rr.block_id != str.block_id OR rr.room_number != str.room_num

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

SELECT 
    b.block_id AS Block, 
     (
        SELECT COUNT(*) 
        FROM Room r
        WHERE r.block_id = b.block_id 
        AND r.is_available = TRUE 
        AND r.capacity > 0
    ) AS brojka_na_slobodni_sobi_posle_prviot_den,
    (
        SELECT COUNT(*) 
        FROM RoomRequest rr 
        WHERE rr.status = 'Pending' 
        AND rr.block_id = b.block_id
    ) AS br_studenti_koi_pobarale_soba_i_cekaat_odgovor
FROM 
    Block b
ORDER BY 
    b.block_id