Практична робота №17. Складні багатотабличні запити (JOIN + підзапити)

Мета

Закріпити навички побудови складних багатотабличних запитів, що поєднують з'єднання, підзапити, агрегацію та операції над множинами.

Підготовка

Перед виконанням опрацюйте пов'язані лекції:

• JOIN і групування даних
• Складні SQL-запити

Завдання

Виконайте наскрізний набір запитів для схеми college із повним набором таблиць (departments, courses, students, enrollments, teachers).

1. Звіт «Підрозділ → кількість курсів → кількість реєстрацій» з двома JOIN та агрегацією:

SELECT d.name AS department,
       COUNT(DISTINCT c.course_id) AS courses,
       COUNT(e.student_id) AS enrollments
FROM college.departments AS d
LEFT JOIN college.courses AS c USING (department_id)
LEFT JOIN college.enrollments AS e USING (course_id)
GROUP BY d.name
ORDER BY enrollments DESC;

2. «Середній бал студента» з JOIN, агрегацією та фільтрацією HAVING (лише студенти з щонайменше однією оцінкою).
3. «Курси, які жодного разу не обрали» — через LEFT JOIN і перевірку NULL; потім той самий результат через NOT EXISTS.
4. «Викладачі, які ведуть найбільше курсів» — з підзапитом у FROM або HAVING.
5. Комбінований звіт: студенти, що мають оцінку вищу за середню по курсу, — з корельованим підзапитом.
6. Операція над множинами: перелічіть коди курсів, які є і в першого, і в другого підрозділу, через INTERSECT.

Вимоги до результату

• SQL-скрипт advanced.sql із запитами в порядку виконання.
• Для кожного запиту: мета, SQL, результат, пояснення.
• Порівняння LEFT JOIN + NULL і NOT EXISTS для одного завдання.
• Підсумковий висновок про типові підходи до складних звітів.

Питання для захисту

• Як вирішити, що використати: JOIN чи підзапит?
• Чому COUNT(DISTINCT ...) потрібен після множення рядків через JOIN?
• Як перевірити коректність складного запиту?
• Що змінить LEFT JOIN на INNER JOIN у першому запиті?