Завдання для самостійного виконання

1. Напишіть запит з підзапитом для одержання даних про всі оцінки студента із прізвищем «Іваненко». Припустимо, що його персональний номер невідомий. Чи завжди такий запит буде коректним?

2. Напишіть запит, що вибирає дані про імена всіх студентів, що мають по предмету з ідентифікатором 101 бал вище загального середнього бала.

3. Напишіть запит, що виконує вибірку імен всіх студентів, що мають по предмету з ідентифікатором 102 бал нижче загального середнього бала.

4. Напишіть запит, що виконує вивід кількості предметів, по яких екзаменувався кожний студент, що здавав більше 20 предметів.

5. Напишіть команду SELECT, що використовує зв'язані підзапити й виконує вивід імен і ідентифікаторів студентів, у яких стипендія збігається з максимальним значенням стипендії для міста, у якому живе студент.

6. Напишіть запит, що дозволяє вивести імена й ідентифікатори всіх студентів, для яких точно відомо, що вони проживають у місті, де немає жодного університету.

7. Напишіть два запити, які дозволяють вивести імена й ідентифікатори всіх студентів, для яких точно відомо, що вони проживають не в тому місті, де розташований їхній університет. Один запит з використанням з'єднання, а інший - з використанням зв'язаного підзапиту.

3.8. Лабораторна робота №8

Тема:. Використання оператора EXISTS

Мета роботи: набути практичні навички використання оператора EXISTS при роботі з реляційною базою даних.

Порядок виконання роботи

1. Ознайомитися з теоретичними положеннями до лабораторної роботи.

2. Виконати всі наведені приклади в середовище СУБД ACCESS.Для цього необхідно:

• завантажити СУБД ACCESS;

• виконати режим відкриття бази даних «ВНЗ SQL»;

• у вікні БД «ВНЗ SQL» вибрати вкладку «Запити»;

• в діалоговому вікні вказати режим «Додати таблицю» і вибрати необхідні дані;

• на панелі інструментів вибрати пункт меню «Вид» і вказати режим роботи SQL;

• в поточному діалоговому вікні ввести текст запиту в форматі SQL;

• виконати введений запит, для чого натиснути кнопку «Запуск» (!) на панелі інструментів;

• отриманий результат виконання запиту занести в звіт та перевірити на відповідність умовам прикладу;

• зберегти створений запит, надавши йому власне ім’я.

3. Виконати індивідуальне завдання (дивись завдання для самостійної роботи). Номери індивідуальних завдань видаються викладачем.

4. Результати виконання індивідуальних завдань занести у звіт.

5. Захистити звіт.

Теоретичні відомості

Використовуваний в SQL оператор EXISTS (існує) генерує значення істина або хибність, подібно булевому виразу Використовуючи підзапити як аргумент, цей оператор оцінює результат виконання підзапиту, як істинний, якщо цей підзапит генерує вихідні дані, тобто у випадку існування (повернення) хоча б одного знайденого значення. У противному випадку результат підзапиту помилковий. Оператор EXISTS не може приймати значення UNKNOWN (не відомо).

Приклад 1. Витягти з таблиці EXAM_MARK дані про студентів, що одержали хоча б одну незадовільну оцінку.

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS A

WHERE EXISTS

(SELECT *

FROM EXAM_MARKS В

WHERE MARK < 3

AND B.STUDENT_ID = A. STUDENT_ID) ;

При використанні зв'язаних підзапитів оператор EXISTS аналізує кожний рядок таблиці, на яку є посилання в зовнішньому запиті. Головний запит одержує рядки-кандидати на перевірку умови. Для кожного рядка-кандидата виконується підзапит. Як тільки підзапит знаходить рядок, де в стовпці MARK значення задовольняє умові, він припиняє виконання й повертає значення істина зовнішньому запиту, що потім аналізує свій рядок-кандидат.

Приклад 2. Потрібно одержати ідентифікатори предметів навчання, екзамени з яких здавалися не одним, а декількома студентами:

SELECT DISTINCT SUBJ_ID

FROM EXAM_MARKS A

WHERE EXISTS

(SELECT *

FROM EXAM_MARKS В

WHERE A.SUBJ_ID = B. SUBJ_ID

AND A.STUDENT_ID < > В.STUDENT_ID);

Часто EXISTS застосовується з оператором NOT (NOT EXISTS інтерпретується, як «не існує»). Якщо попередній запит сформулювати в такий спосіб - знайти ідентифікатори предметів навчання, які здавалися одним, і тільки одним студентом (інакше кажучи, для яких не існує іншого студента, що здавав іспит), то досить просто поставити NOT перед EXISTS.

Варто мати на увазі, що в підзапиті, що вказується в операторі EXISTS, не можна використати агрегуючі функції.

Можливості застосування вкладених запитів досить різноманітні.

Приклад 3. З таблиці STUDENT потрібно витягти рядки для кожного студента, що здав більше одного I предмета.

SELECT *

FROM STUDENT FIRST

WHERE EXISTS

(SELECT SUBJ_ID

FROM EXAM_MARKS SECOND

GROUP BY SUBJ_ID

HAVING COUNT (SUBJ_ID) > 1

WHERE FIRST. STUDENT_ID = SECOND. STUDENT_ID);