Оператори all, any, some

Квантора існування (NOT) EXISTS та оператора (NOT) IN достатньо для створення будь яких складних запитів. Стандарт SQL-89 обмежується тільки цими можливостями. Однак, як відомо, немає межі досконалості. Комерційні реалізації мови прагнули зробити SQL більш схожим на англійську і, тим самим доступним для неспеціалістів. Так з’явились оператори результуючі такі складні конструкції людської мови як всі (ALL), будь-який (ANY), деякий (SOME). Пізніше вони були закріплені стандартом. Треба відмітити, що їх використання тільки заважає спеціалістам, так як заплутує спеціалістів, бо включає в себе багато нюансів використання, а велика різниця між логікою та розмовною мовою може взагалі призвести до неправильно поставленого запиту.

На відміну від квантора існування (NOT) EXISTS синтаксис операторів ALL, ANY, SOME передбачає наявність одного з операторів порівняння (=, !=, >, >= ,<, <=). Оператори ANY та SOME взаємозамістні, та вживання якогось з них лише справа смаку програміста.

За допомогою ALL (SubQuery), предикат є вірним, якщо кожне значення, вибране підзапитом, задовольняє умову в предикаті зовнішнього запиту. Оператор ALL використовується а основному з нерівностями, так як „=ALL (SubQuery)” має зміст тільки якщо результатом запиту є одне значення або стовпець однакових значень.

Запит:Визначити ПІБ студентів, які здали іспити по найбільшій кількості предметів.

Рішення:

SELECT ID_Stud, СПрізв FROM СТУДЕНТ AS СТ WHERE ID_Stud IN (SELECT У1. ID_Stud, У1.ID_Subj, COUNT(*)

FROM УСПІШНІСТЬ AS У1

WHERE CT. ID_Stud = У1.ID_Stud

GROUP BY У1.ID_Stud, У1.ID_Subj

HAVING COUNT(*) > ALL (SELECT COUNT(*)

FROM УСПІШНІСТЬAS У2

WHERE CT.ID_ ID_Stud != У2. ID_Stud GROUP BY У2.ID_Stud, У2. ID_Subj))

Даний запит можна записати більш оптимально за допомогою функції MAX. Тат він приведений тільки для ілюстрації використання оператора ALL.

При використанні операторів ALL , ANY, SOME необхідно брати до уваги наступні правила:

• Будь який запит, який може бути сформульований з ALL , ANY, SOME може бути сформульований за допомогою квантора загальності EXIST. Навпаки – не завжди.

• “<ANY (SubQuery)” – значення яке менше ніж найбільше з вибраних в SubQuery, а “>ANY (SubQuery)” – значення, більше ніж найменше з вибраних.

• „!=ALL (SubQuery)” – требі розуміти як не рівне будь-якому результату підзапита.

• Якщо під запит повертає порожню множину строк, то вираз „ALL (SubQuery)” є істинним, а „ANY (SubQuery)” – хибним.

Оператори модицікації даних

Вставка Даних

Загальний синтаксис оператора вставки наступний.

Синтаксис:

INSERT INTO <ім’я_таблиці | ім’я _представлення> [(<ім’я _поля> {, < ім’я_поля2> }...)] {VALUES <список_значень> | <підзапит>}

В своєму простішому виді оператор INSERT дозволяє вставляти в таблицю нові рядки шляхом безпосереднього завдання значень кожного поля. Якщо при цьому заповняються всі поля таблиці то імена можна не вказувати, а порядок записуваних значень має відповідати порядку полів, використаному при описі структури таблиці. Якщо якесь значення не треба вводити, на його місце треба поставити NULL. Щоб присвоїти значення не всім полям рядка або змінити порядок введення значень, необхідно явно задати список полів після вказування назви таблиці. Значення в VALUES не можуть бути обчислюваними виразами.

Запит: Додати в таблицю УСПІШНІСТЬ результати екзамена по фізиці (ID_Subj = ‘К3’) для студентів з ID_Stud ‘003’ та ‘002’.

Рішення:

INSERT INTO УСПЕВАЕМОСТЬ

VALUES ('003', 'K3', 3, 4, '002', 'K3', 3, 4)

Інша можливість оператора INSERT – це вставка даних на основі запиту. Для цього замість VALUES необхідно вказати запит, який сформулює записувані рядки. Однак FROM цього запиту не має включати в себе посилання на таблицю в яку вставляються рядки, тобто не можна використовувати корельовані підзапити.

Запит:Перенести всі дані про відмінників в окрему таблицю ВІДММІННИК.

Рішення:

INSERT INTO ВІДМІННИК

(S_Num, ПІБ, Дисципліна, Num_Sem, Бал)

SELECT ID_Stud, СПрізв, Найменування, Семестр, Оцінка

FROM СТУДЕНТ, УСПІШНІСТЬ, КУРС

WHERE СТУДЕНТ.ID_Stud = УСПІШНІСТЬ.ID_Stud AND KУРС.ID_Subj = УСПІШНІСТЬ.ID_Subj AND NOT EXISTS

(SELECT * FROM УСПІШНІСТЬ AS У1

WHERE СТУДЕНТ. ID_Stud =У1. ID_Stud AND Оцінка<>5)

Зауваження: Таблиця ВІДМІННИК має існувати в базі даних. Типи полів S_Num, ПІБ, Дисципліна, Num_Sem, Бал мають співпадати з типами відповідних полів з таблиць СТУДЕНТ, УСПІШНІСТЬ, КУРС.

Зауваження: Таблиці ВІДМІННИК та СТУДЕНТ, УСПІШНІСТЬ, КУРС ніяк не пов’язані між собою, якщо в подальшому відмінник отримує двійку, або змінить прізвище, то зміни, внесені в останні три таблиці не вплинуть на таблицю ВІДМІННИК. Якщо ви хочете, щоб в таблиці ВІДМІННИК підтримувався список відмінників, треба використовувати можливості представлення.

Зауваження: При слідуванні умовам першого зауваження допускається використання групувань в запитах оператора INSERT.

Видалення рядків з таблиці

Загальний синтаксис оператора видалення рядків наступний.

Синтаксис:

DELETE FROM <ім’я_таблиці> [WHERE clause]

Якщо умова не вказана, то за таблиці видаляються всі строки, але сама таблиця залишається. В умові відбору стандартом SQL-92 допускається вставка підзапитів.

Запит: Видалити з таблиці СТУДЕНТ інформацію про студентів, які отримали більше двох двійок.

Рішення:

DELETE FROM СТУДЕНТ

WHERE 2 < (SELECT Count(*) FROM УСПІШНІСТЬ WHERE СТУДЕНТ.ID_Stud = УСПІШНІСТЬ.ID_Stud AND Оцінка=2))

Зауваження: В операторі DELETE на відмін від оператора INSERT підзапит може бути корельованим з таблицею, вказаною в FROM, тобто можна посилатися на поле поточного запису таблиці з якої видаляють. Однак не можна посилатися на саму таблицю.

Заборона на використання списку таблиць, для яких ставиться умова відбору, для складних умов обов’язково призводить до багатьох вкладених підзапитів. Деякі платформи допускають указування такого списку перед WHERE clause.

Cинтаксис:

DELETE FROM <ім'я_таблиці>

FROM <спісок_таблиць>

WHERE clause

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

Запит: Видалити з таблиці успішності відомості про Іваненкоа І. І.

Рішення:

DELETE FROM УСПІШНІСТЬ FROM СТУДЕНТ

WHERE СТУДЕНТ.ID_Stud = УСПІШНІСТЬ. ID_Stud

AND СПрізв= 'Іваненко І.І.'

Зміни даних

Загальний синтаксис оператора модифікації вже існуючих рядків наступний.

Синтаксис:

UPDATE <ім'я_таблиці>

SET <ім'я_поля> = <значення | вираз>

[{, <Ім'я_поля> = <значення | вираз>} ...]

[WHERE clause]

Зауваження: Якщо пропозиція WHERE відсутня, то зміни вносяться у всі рядки таблиці.

Зауваження: На відміну від оператора INSERT при модифікації даних можна використовувати обчислювані вирази.

Зауваження: Оператор UPDATE використовує підзапити так само, як і оператор DELETE.

Запит: Студенти, які проживали за адресами 'A2' і 'A3', змінили їх на адресу 'A10'. Внести відповідні зміни в БД.

Рішення:

UPDATE СТУДЕНТ

SET САдреса = 'A10'

WHERE САдрес а= 'A2'OR САдреса =' A3 '

Запит: Змінити значення поля Консультант на NULL, якщо середній бал студента більше або дорівнює 4.

Рішення:

UPDATE СТУДЕНТ

SET Консультант = NULL

WHERE 4 <= (SELECT AVG (Оцінка) FROM УСПІШНІСТЬ WHERE СТУДЕНТ.ID_Stud = УСПІШНІСТЬ. ID_Stud)

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