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

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

SELECT 
    s.student_id,
    u.user_name || ' ' || u.user_surname AS full_name,
    COUNT(up.experiment_id) AS total_experiments
FROM Student s
JOIN "User" u ON s.student_id = u.user_id
JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
JOIN Experiment e ON up.experiment_id = e.experiment_id
WHERE 
    s.teacher_id = :teacher_id  -- Замени со ID на професорот
GROUP BY s.student_id, full_name
ORDER BY total_experiments DESC;

Извештај за лабораториските инструменти и колку пати биле искористени

SELECT 
    le.equipment_name,
    COUNT(ele.experiment_id) AS usage_count
FROM 
    ExperimentLabEquipment ele
JOIN 
    LabEquipment le ON ele.equipment_id = le.equipment_id
GROUP BY 
    le.equipment_name
ORDER BY 
    usage_count DESC;

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

SELECT 
    s.student_id,
    u.user_name || ' ' || u.user_surname AS full_name,
    e.experiment_id,
    e.result,
    up.participation_timestamp AS participation_time
FROM Student s
JOIN "User" u ON s.student_id = u.user_id
JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
JOIN Experiment e ON up.experiment_id = e.experiment_id
ORDER BY u.user_name, up.participation_timestamp DESC;

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

SELECT 
    s.student_id,
    u.user_name || ' ' || u.user_surname AS full_name,
    e.experiment_id,
    e.result
FROM Student s
JOIN "User" u ON s.student_id = u.user_id
JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
JOIN Experiment e ON up.experiment_id = e.experiment_id
ORDER BY u.user_name;

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

SELECT 
    AVG(instrument_count) AS average_lab_equipment_per_experiment
FROM (
    SELECT 
        e.experiment_id, 
        COUNT(ele.equipment_id) AS instrument_count
    FROM Experiment e
    LEFT JOIN ExperimentLabEquipment ele ON e.experiment_id = ele.experiment_id
    GROUP BY e.experiment_id
) subquery;

Извештај за најчесто користени хемиски елементи во екпериментите

SELECT 
    el.element_name,
    COUNT(r.reaction_id) AS total_uses
FROM Elements el
JOIN Reaction r ON el.element_id = r.element1_id OR el.element_id = r.element2_id
GROUP BY el.element_name
ORDER BY total_uses DESC;

Извештај за најчесто реализирани експерименти

SELECT 
    e.experiment_id,
    e.result,
    COUNT(up.user_id) AS student_participation
FROM Experiment e
JOIN UserParticipatesInExperiment up ON e.experiment_id = up.experiment_id
GROUP BY e.experiment_id, e.result
ORDER BY student_participation DESC;

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

SELECT 
    s.student_id,
    u.user_name || ' ' || u.user_surname AS full_name
FROM Student s
JOIN "User" u ON s.student_id = u.user_id
LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
WHERE s.teacher_id = :teacher_id  -- Замени со ID на професорот
AND up.user_id IS NULL;

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

SELECT 
    s.student_id,
    u.user_name || ' ' || u.user_surname AS full_name,
    COUNT(up.experiment_id) AS total_experiments
FROM Student s
JOIN "User" u ON s.student_id = u.user_id
LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
GROUP BY s.student_id, full_name
HAVING COUNT(up.experiment_id) < 3 
ORDER BY total_experiments ASC;

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

SELECT 
    t.teacher_id,
    u.user_name || ' ' || u.user_surname AS full_name,
    COUNT(DISTINCT s.student_id) AS total_students,
    COUNT(up.experiment_id) AS total_experiments,
    ROUND(
        COUNT(up.experiment_id) * 1.0 / NULLIF(COUNT(DISTINCT s.student_id), 0), 2
    ) AS avg_experiments_per_student
FROM Teacher t
JOIN "User" u ON t.teacher_id = u.user_id
LEFT JOIN Student s ON t.teacher_id = s.teacher_id
LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id
GROUP BY t.teacher_id, full_name
ORDER BY avg_experiments_per_student DESC;

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

SELECT 
    s.student_id,
    u.user_name || ' ' || u.user_surname AS full_name,
    COUNT(DISTINCT ue.element_id) AS total_elements_viewed,
    COUNT(DISTINCT ul.equipment_id) AS total_lab_equipment_viewed
FROM Student s
JOIN "User" u ON s.student_id = u.user_id
LEFT JOIN UserViewsElement ue ON s.student_id = ue.user_id
LEFT JOIN UserViewsLabEquipment ul ON s.student_id = ul.user_id
GROUP BY s.student_id, full_name
ORDER BY total_elements_viewed DESC, total_lab_equipment_viewed DESC;