 |
Теоретичні відомості
|
|
|
|
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);
Підзапит обчислює кількість рядків з однієї й тією же датою, що збігається з датою, для якої сформована чергова група основного запиту.
Завдання для самостійного виконання
1. Напишіть запит з підзапитом для одержання даних про всі оцінки студента із прізвищем «Іваненко». Припустимо, що його персональний номер невідомий. Чи завжди такий запит буде коректним?
2. Напишіть запит, що вибирає дані про імена всіх студентів, що мають по предмету з ідентифікатором 101 бал вище загального середнього бала.
3. Напишіть запит, що виконує вибірку імен всіх студентів, що мають по предмету з ідентифікатором 102 бал нижче загального середнього бала.
4. Напишіть запит, що виконує вивід кількості предметів, по якихекзаменувався кожний студент, що здавав більше 20 предметів.
|
|
|
5. Напишіть команду SELECT, що використовує зв'язані підзапити й виконує вивід імен і ідентифікаторів студентів, у яких стипендія збігається з максимальним значенням стипендії для міста, у якому живе студент.
6. Напишіть запит, що дозволяє вивести імена й ідентифікатори всіх студентів, для яких точно відомо, що вони проживають у місті, де немає жодного університету.
7. Напишіть два запити, які дозволяють вивести імена й ідентифікатори всіх студентів, для яких точно відомо, що вони проживають не в тому місті, де розташований їхній університет. Один запит з використанням з'єднання, а інший - з використанням зв'язаного підзапиту.
3.8. Лабораторна робота №8
|
|
|
Cтислі теоретичні відомості
Cтислі теоретичні відомості
Вихідні теоретичні положення
Відомості про втому матеріалів
Властивості наукової свідомості людини та систематизація науки
ВСТУП. КОРОТКІ ТЕОРЕТИЧНІ ВІДОМОСТІ.
Деякі теоретичні відомості
Додаткові теоретичні відомості
Додаткові теоретичні відомості
Додаткові теоретичні відомості
