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

1. Припустимо, що стипендія всім студентам збільшена на 20%. Напишіть запит до таблиці STUDENT, що виконує вивід номера студента, прізвище студента й величину збільшеної стипендії. Вихідні дані впорядкувати: а) за значенням останнього стовпця (величині стипендії); б) за абеткою прізвищ студентів.

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

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

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

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

3.7. Лабораторна робота №7

Тема: Вкладені підзапити

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL дозволяє використовувати одні запити усередині інших запитів, тобто вкладати запити друг у друга.

Припустимо, відоме прізвище студента («Петренко»), але невідоме значення поля STUDENT_ID для нього. Щоб витягти дані про всі оцінки цього студента, можна записати наступний запит:

SELECT *

FROM EXAM_MARKS

WHERE STUDENT_ID = (SELECT STUDENT_ID

FROM STUDENT SURNAME = 'Петренко') ;

Як працює запит SQL зі зв'язаним підзапитом?

• Вибирається рядок з таблиці, ім'я якої зазначене в зовнішньому запиті.

• Виконується підзапит і отримане значення застосовується для аналізу цього рядка в умові конструкції WHERE зовнішнього запиту.

• По результатах оцінки цієї умови приймається рішення про включення або не включення рядка до складу вихідних даних.

• Процедура повторюється для наступного рядка таблиці зовнішнього запиту.

Варто звернути увагу, що наведений вище запит коректний тільки в тому випадку, якщо в результаті виконання зазначеного в дужках підзапиту вертається єдине значення. Якщо в результаті виконання підзапиту буде повернуто кілька значень, то цей підзапит буде помилковим. У даному прикладі це відбудеться, якщо в таблиці STUDENT буде кілька записів зі значеннями поля

SURNAME = 'Петренко'.

У деяких випадках для гарантії одержання єдиного значення в результаті виконання підзапиту використовується DISTINCT. Одним з видів функцій, які автоматично завжди видають у результаті єдине значення для будь-якої кількості рядків, є агрегуючі функції.

Оператор IN також широко застосовується в підзапитах. Він задає список значень, з якими порівняються інші значення для визначення істинності предиката, що задається цим оператором.

Приклад 1. Вибрати дані про всі оцінки (таблиця EXAM_MARKS) студентів з Вінниці за допомогою наступного запиту:

SELECT *

FROM EXAM_MARKS

WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM STUDENT

WHERE CITY = 'Вінниця') ;

Підзапити можна застосовувати усередині пропозиції HAVING.

Приклад 2. Потрібно визначити кількість предметів навчання з оцінкою, що перевищує середнє значення оцінки студента з ідентифікатором 301:

SELECT COONT (DISTINCT SUBJ_ID) , MARK

FROM EXAM_MARKS

GROUP BY MARK

HAVING MARK >

(SELECT AVG(MARK)

FROM EXAM_MARKS

WHERE STUDENT_ID = 301) ;

Формування зв'язаних підзапитів

При використанні підзапитів у внутрішньому запиті можна посилатися на таблицю, ім'я якої зазначено в конструкції FROM зовнішнього запиту. У цьому випадку такий зв'язаний підзапит виконується по одному разу для кожного рядка таблиці основного запиту.

Приклад 3: Вибрати відомості про всі предмети навчання, по яких проводився іспит 20 січня 2008 р.

SELECT *

FROM SUBJECT SU

WHERE '20/01/2008' IN

(SELECT EXAM_DATE

FROM EXAM_MARKS EX

WHERE SU.SUBJ_ID = EX.SUBJ_ID);

У деяких СУБД для виконання цього запиту може знадобитися перетворення значення дати в символьний тип. У наведеному запиті SU і ЕХ є псевдонімами (аліасами), тобто іменами, що вводяться спеціально, які можуть бути використані в даному запиті замість дійсних імен. У наведеному прикладі вони використаються замість імен таблиць SUBJECT і EXAM_MARKS.

Це ж завдання можна вирішити за допомогою операції з'єднання таблиць.

Приклад 4. Використання операції з'єднання таблиць:

SELECT DISTINCT SU.SUBJ_ID, SUBJ_NAME, HOUR, SEMESTER

FROM SUBJECT FIRST, EXAM_MARKS SECOND

WHERE FIRST.SUBJ_ID = SECOND.SUBJ_ID

AND SECOND.EXAM_DATE = '20/01/2008';

У цьому виразі аліасами таблиць є імена FIRST і SECOND.

Можна використати підзапити, що зв'язують таблицю зі своєю власною копією.

Приклад 5. Знайти ідентифікатори, прізвища й стипендії студентів, що одержують стипендію вище середньої на курсі, на якому вони вчаться.

SELECT DISTINCT STUDENT_ID , SURNAME , STIPEND

FROM STUDENT El

WHERE STIPEND >

(SELECT AVG( STIPEND)

FROM STUDENT E2

WHERE El.KURS = E2.KURS);

Той же результат можна одержати за допомогою наступного запиту:

SELECT DISTINCT STUDENT_ID, SURNAME, STIPEND

FROM STUDENT El,

(SELECT KURS, AVG (STIPEND) AS AVG_STIPEND

FROM STUDENT E2

GROUP BY E2. KURS) E3

WHERE El. STIPEND > AVG_STIPEND AND El.KURS=E3.KURS;

Зверніть увагу — другий запит буде виконаний набагато швидше. Справа в тому, що в першому варіанті запиту агрегуюча функція AVG виконується над таблицею, зазначеною в підзапиті, для кожного рядка зовнішнього запиту. В іншому варіанті друга таблиця (аліас Е2) обробляється агрегуючою функцією один раз, у результаті чого формується допоміжна таблиця (у запиті вона має аліас ЕЗ), з рядками якої потім з'єднуються рядки першої таблиці (аліас Е1). Варто мати на увазі, що реальний час виконання запиту у великому ступені залежить від оптимізатора запитів конкретної СУБД.

Зв'язані підзапити в HAVING

Раніше вказувалося, що конструкція GROUP BY дозволяє групувати виведені SELECT-запитом записи за значенням деякого поля. Використання конструкції HAVING дозволяє при виводі здійснювати фільтрацію таких груп. Предикат конструкції HAVING оцінюється не для кожного рядка результату, а для кожної групи вихідних записів, сформованої конструкцією GROUP BY зовнішнього запиту.

Приклад 6. Необхідно за даними з таблиці EXAM_MARKS визначити суму отриманих студентами оцінок (значень поля MARK), згрупувавши значення оцінок по датах іспитів і виключивши ті дні, коли число студентів, що здавали протягом дня іспити, було менше 10.

SELECT EXAM_DATE, SUM (MARK)

FROM EXAM_MARKS A

GROUP BY EXAM_DATE

HAVING 10 <

(SELECT COUNT (MARK)

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = В.EXAM_DATE) ;

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