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

1. Листање(приказ) на денешни events, count на денешни часови, испити и tasks:

WITH EventResult AS (
    SELECT 
        NULL::INTEGER AS task_id,
        ce.calendar_event_id,
        ce.event_name,
        ce.event_type,
        ce.event_start_time,
        ce.event_end_time,
        nre.event_date
    FROM 
        calendar_event AS ce
    JOIN 
        non_repeating_event AS nre ON ce.calendar_event_id = nre.calendar_event_id
    LEFT JOIN 
        student_event AS se ON se.calendar_event_id = ce.calendar_event_id
    LEFT JOIN 
        subject_event AS sje ON sje.calendar_event_id = ce.calendar_event_id
    WHERE 
        nre.event_date = CURRENT_DATE 
        AND (se.student_id = 91324 OR sje.calendar_event_id = 91324)
        
    UNION

    SELECT 
        NULL::INTEGER AS task_id,
        ce.calendar_event_id,
        ce.event_name,
        ce.event_type,
        ce.event_start_time,
        ce.event_end_time, 
        CASE
            WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
            WHEN re.event_week_day = 'TUESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '1 day')::DATE
            WHEN re.event_week_day = 'WEDNESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '2 days')::DATE
            WHEN re.event_week_day = 'THURSDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '3 days')::DATE
            WHEN re.event_week_day = 'FRIDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '4 days')::DATE
            WHEN re.event_week_day = 'SATURDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '5 days')::DATE
            WHEN re.event_week_day = 'SUNDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '6 days')::DATE
        END AS event_date
    FROM 
        calendar_event AS ce
    JOIN 
        repeating_event AS re ON ce.calendar_event_id = re.calendar_event_id
    LEFT JOIN 
        student_event AS se ON se.calendar_event_id = ce.calendar_event_id
    LEFT JOIN 
        subject_event AS sje ON sje.calendar_event_id = ce.calendar_event_id
    WHERE 
        re.starting_date <= (
            CASE
                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
                WHEN re.event_week_day = 'TUESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '1 day')::DATE
                WHEN re.event_week_day = 'WEDNESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '2 days')::DATE
                WHEN re.event_week_day = 'THURSDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '3 days')::DATE
                WHEN re.event_week_day = 'FRIDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '4 days')::DATE
                WHEN re.event_week_day = 'SATURDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '5 days')::DATE
                WHEN re.event_week_day = 'SUNDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '6 days')::DATE
            END
        )
        AND re.ending_date >= (
            CASE
                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
                WHEN re.event_week_day = 'TUESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '1 day')::DATE
                WHEN re.event_week_day = 'WEDNESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '2 days')::DATE
                WHEN re.event_week_day = 'THURSDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '3 days')::DATE
                WHEN re.event_week_day = 'FRIDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '4 days')::DATE
                WHEN re.event_week_day = 'SATURDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '5 days')::DATE
                WHEN re.event_week_day = 'SUNDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '6 days')::DATE
            END
        )
        AND (EXTRACT(day FROM date_trunc('week', CURRENT_DATE) - date_trunc('week', (
            CASE
                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
                WHEN re.event_week_day = 'TUESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '1 day')::DATE
                WHEN re.event_week_day = 'WEDNESDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '2 days')::DATE
                WHEN re.event_week_day = 'THURSDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '3 days')::DATE
                WHEN re.event_week_day = 'FRIDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '4 days')::DATE
                WHEN re.event_week_day = 'SATURDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '5 days')::DATE
                WHEN re.event_week_day = 'SUNDAY' THEN (date_trunc('week', CURRENT_DATE) + INTERVAL '6 days')::DATE
            END
        ))) % (7 * re.repeats_every_n_weeks)) = 0
        AND (se.student_id = 91324 OR sje.student_id = 91324)
                
    UNION
      
    SELECT 
        t.task_id,
        NULL AS calendar_event_id,
        NULL AS event_name,
        NULL AS event_type,
        NULL AS event_start_time,
        NULL AS event_end_time,
        NULL AS event_date
    FROM task t
    JOIN student_task st ON t.task_id = st.task_id
    LEFT JOIN creates_task ct ON t.task_id = ct.task_id
    WHERE (st.student_id = 91324 OR ct.task_id = 91324) AND task_due_date = CURRENT_DATE
)

SELECT 
    COUNT(CASE WHEN event_type = 'Exam' THEN 1 END) AS exam_count,
    COUNT(CASE WHEN event_type <> 'Exam' THEN 1 END) AS non_exam_count,
    COUNT(DISTINCT task_id) AS distinct_task_count
FROM EventResult;

2. Листање(приказ) на таскови за наредните три дена:

SELECT 
    t.task_name,
    t.task_description,
    t.task_due_date,
    t.task_priority
FROM 
    task AS t
JOIN 
    student_task AS st ON t.task_id = st.task_id
LEFT JOIN 
    creates_task AS ct ON t.task_id = ct.task_id
LEFT JOIN 
    subject_at_term AS sat ON ct.term_subject_id = sat.term_subject_id
        AND ct.term_type = sat.term_type 
        AND ct.starting_year = sat.starting_year
        AND ct.finishing_year = sat.finishing_year
        AND ct.student_id = sat.student_id
WHERE 
    (st.student_id = 91324 OR ct.student_id = 91324)
    AND t.task_due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '3 days'
ORDER BY 
    t.task_priority;

3. Листање(приказ) на испити за наредните седум дена:

SELECT 
    ce.event_name, 
    sat.subject_name,
    ce.event_type,
    ce.event_start_time,
    ce.event_end_time,
    nre.event_date 
FROM 
    subject_event AS se
JOIN 
    calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
JOIN 
    subject_at_term AS sat ON se.term_subject_id = sat.term_subject_id
        AND se.term_type = sat.term_type 
        AND se.starting_year = sat.starting_year
        AND se.finishing_year = sat.finishing_year
        AND se.student_id = sat.student_id
JOIN 
    non_repeating_event AS nre ON ce.calendar_event_id = nre.calendar_event_id
WHERE 
    ce.event_type = 'Exam'
    AND se.student_id = 91324
    AND nre.event_date >= CURRENT_DATE
    AND nre.event_date <= CURRENT_DATE + INTERVAL '7 days'
ORDER BY
    nre.event_date,
    ce.event_start_time;

4. Листање(приказ) на сите евенти за одредена недела:

WITH Result AS (
  SELECT 
    ce.calendar_event_id,
    ce.event_name,
    ce.event_type,
    ce.event_start_time,
    ce.event_end_time,
    nre.event_date
  FROM 
    calendar_event AS ce
  JOIN 
    non_repeating_event AS nre ON ce.calendar_event_id = nre.calendar_event_id
  LEFT JOIN 
    student_event AS se ON se.calendar_event_id = ce.calendar_event_id
  LEFT JOIN 
    subject_event AS sje ON sje.calendar_event_id = ce.calendar_event_id
  WHERE 
    nre.event_date >= date_trunc('week', CURRENT_DATE) 
    AND nre.event_date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'
    AND (se.student_id = 91324 OR sje.student_id = 91324)

  UNION

  SELECT 
    ce.calendar_event_id,
    ce.event_name,
    ce.event_type,
    ce.event_start_time,
    ce.event_end_time, 
    CASE
      WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
      WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '1 day'
      WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '2 days'
      WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '3 days'
      WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '4 days'
      WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '5 days'
      WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '6 days'
    END AS event_date
  FROM 
    calendar_event AS ce
  JOIN 
    repeating_event AS re ON ce.calendar_event_id = re.calendar_event_id
  LEFT JOIN 
    student_event AS se ON se.calendar_event_id = ce.calendar_event_id
  LEFT JOIN 
    subject_event AS sje ON sje.calendar_event_id = ce.calendar_event_id
  WHERE 
    re.starting_date <= (
      CASE
        WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
        WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '1 day'
        WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '2 days'
        WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '3 days'
        WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '4 days'
        WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '5 days'
        WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '6 days'
      END
    )
    AND re.ending_date >= (
      CASE
        WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
        WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '1 day'
        WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '2 days'
        WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '3 days'
        WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '4 days'
        WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '5 days'
        WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '6 days'
      END
    )
    AND (EXTRACT(day FROM date_trunc('week', CURRENT_DATE) - date_trunc('week', (
      CASE
        WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
        WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '1 day'
        WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '2 days'
        WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '3 days'
        WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '4 days'
        WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '5 days'
        WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE)::DATE + INTERVAL '6 days'
      END
    ))) % (7 * re.repeats_every_n_weeks)) = 0
    AND (se.student_id = 91324 OR sje.student_id = 91324)
)
SELECT 
  calendar_event_id,
  event_name,
  event_type,
  event_start_time,
  event_end_time,
  event_date::DATE
FROM Result
ORDER BY 
  event_date, -- Order by day of the week
  event_start_time; -- Order by time

5. Листање(приказ) на сите евенти за одреден семестар:

SELECT 
    ce.event_name,
    sat.subject_name,
    ce.event_type,
    ce.event_description
FROM
    subject_at_term AS sat
JOIN
    subject_event AS se
    ON sat.student_id = se.student_id
    AND sat.term_subject_id = se.term_subject_id
    AND sat.term_type = se.term_type
    AND sat.starting_year = se.starting_year
    AND sat.finishing_year = se.finishing_year
JOIN
    calendar_event AS ce
    ON se.calendar_event_id = ce.calendar_event_id
WHERE 
    se.student_id = 91324
    AND se.starting_year = '2023'
    AND se.finishing_year = '2024'
    AND se.term_type = 'Zimski';

6. Листање(приказ) на сите евенти и task-ови за зададен предмет:

SELECT
    ce.calendar_event_id,
    ce.event_name,
    ce.event_type,
    ce.event_description,
    ce.event_start_time,
    ce.event_end_time,
    NULL AS task_id,
    NULL AS task_name,
    NULL AS task_description,
    NULL AS task_priority,
    NULL AS is_done
FROM
    subject_event se
JOIN
    calendar_event ce ON se.calendar_event_id = ce.calendar_event_id
WHERE
    se.term_subject_id = 56
    AND se.term_type = 'Zimski'
    AND se.starting_year = 2023
    AND se.finishing_year = 2024
    AND se.student_id = 91324

UNION

SELECT
    NULL AS calendar_event_id,
    NULL AS event_name,
    NULL AS event_type,
    NULL AS event_description,
    NULL AS event_start_time,
    NULL AS event_end_time,
    t.task_id,
    t.task_name,
    t.task_description,
    t.task_priority,
    t.is_done
FROM
    creates_task ct
JOIN 
    task AS t ON ct.task_id = t.task_id
WHERE
    ct.term_subject_id = 56
    AND ct.term_type = 'Zimski'
    AND ct.starting_year = 2023
    AND ct.finishing_year = 2024
    AND ct.student_id = 91324;

7. Листање(приказ) на сите на сите книги и автори по одреден предмет (пример:Бази на податоци):

WITH SubjectBooks AS (
    SELECT 
        rb.book_id,
        sbb.book_title,
        sbb.book_edition,
        ba.author_id,
        a.author_name
    FROM 
        recommended_book AS rb
    JOIN 
        subject_at_term AS sat ON 
            rb.term_subject_id = sat.term_subject_id
            AND rb.term_type = sat.term_type
            AND rb.starting_year = sat.starting_year
            AND rb.finishing_year = sat.finishing_year
            AND rb.student_id = sat.student_id
    JOIN
        subject_book AS sbb ON rb.book_id = sbb.book_id
    JOIN 
        book_author AS ba ON rb.book_id = ba.book_id
    JOIN 
        author AS a ON ba.author_id = a.author_id
    WHERE 
        sat.subject_name = 'Bazi na podatoci'
        AND rb.student_id = 91324
)

SELECT 
    sb.book_id,
    sb.book_title,
    sb.book_edition,
    sb.author_id,
    sb.author_name
FROM 
    SubjectBooks AS sb;

8. Приказна на сите feedbacks (описи и наслови на feedback) со оценка помала од 3:

SELECT 
    fb.fb_id,
    fb.fb_date,
    fb.fb_title,
    fb.fb_description,
    fb.fb_rating
FROM 
    feedback AS fb
WHERE 
    fb.fb_rating < 3
    AND EXISTS (
        SELECT 1
        FROM 
            gives_feedback AS gf
        WHERE 
            gf.fb_id = fb.fb_id
            AND gf.student_id = 91324
    );

9:Просечен број на креирани настани во тековната година по студент

WITH counted_events AS (
  select 
    s.student_id,
    COUNT(distinct(se.calendar_event_id)) AS student_events,
    COUNT(distinct(sje.calendar_event_id)) as subject_events
  FROM
    student s
  LEFT join
    student_event se ON s.student_id = se.student_id
  left join 
  	subject_at_term sat on sat.student_id = s.student_id 
  LEFT JOIN
    subject_event sje ON sat.student_id = sje.student_id
  LEFT JOIN
    calendar_event ce ON se.calendar_event_id = ce.calendar_event_id or sje.calendar_event_id = ce.calendar_event_id 
  LEFT JOIN
    repeating_event re ON ce.calendar_event_id = re.calendar_event_id
      AND EXTRACT(YEAR FROM CURRENT_DATE) >= EXTRACT(YEAR FROM re.starting_date)
      AND EXTRACT(YEAR FROM CURRENT_DATE) <= EXTRACT(YEAR FROM re.ending_date)
  LEFT JOIN
    non_repeating_event nre ON ce.calendar_event_id = nre.calendar_event_id
      AND EXTRACT(YEAR FROM nre.event_date) = EXTRACT(YEAR FROM CURRENT_DATE)
  GROUP BY
    s.student_id
)

SELECT 
  AVG(student_events) AS average_student_events, 
  AVG(subject_events) AS average_subject_events
	FROM 
  	counted_events

10: Просечен рејтинг во тековната година по студент

WITH student_ratings AS (
  SELECT 
    s.student_id,
    AVG(fb.fb_rating) AS student_rating
  FROM 
    student s 
  JOIN 
    gives_feedback gf ON gf.student_id = s.student_id 
  JOIN 
    feedback fb ON gf.fb_id = fb.fb_id 
      AND EXTRACT(YEAR FROM fb.fb_date) = EXTRACT(YEAR FROM CURRENT_DATE)
  GROUP BY 
    s.student_id
)

SELECT 
  AVG(student_rating) AS average_student_rating
FROM 
  student_ratings;