6. Оператор SELECT. Частина 2

Наявність визначника NULL у рядках таблиці перевіряється за допомогою такої конструкції:

ПРИКЛАД 6.17

Виберіть коди й назви книжок, які не мають УДК (табл. 6.15).

Таблиця 6.15

6.14.1. Розрахунки підсумкових значень в операторі SELECT

Розрахунок підсумкових значень в операторі SELECT здійснюється за допомогою агрегатних або статистичних функцій: COUNT(), SUM(), AVG(), МАХ(), МIN().

Функція COUNT(<вираз>) підраховує число входжень значень виразу в усі записи результуючого набору даних. Вона працює з данними будь-якого типу: числові, символьні або типу «дата/час».

ПРИКЛАД 6.18

Визначить, вартість скількох книжок перевищує 25 грн. (табл. 6.16).

Таблиця 6.16

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

ПРИКЛАД 6.19

Визначіть, скільки різних бібліотекарів видавали книги (табл. 6.17).

Таблиця 6.17

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

Функція SUM(<вираз>) розраховує суму всіх значень стовпця або виразу. При цьому стовпець має складатися з числових даних (містити цілі числа, числа із плаваючою комою або грошові величини). Результат, що повертається цієї функцією, має той же тип даних, що й стовпець або вираз, однак точність (розрядність) результату може бути вищою. Наприклад, якщо застосувати функцію SUM() до стовпця, що містить 16-розрядні цілі числа, то вона може повернути 32-розрядне ціле число.

ПРИКЛАД 6.20

Визначіть загальну вартість книжок бібліотечного фонду (табл. 6.18).

Таблиця 6.18

Функції MIN(<вираз>), MAX(<вираз>) і AVG(<вираз>) дозволяють розрахувати відповідно найменше, найбільше й середнє значення в стовпці або виразі. Функції MIN(), MAX() працюють із числовими, рядковими й даними типу «дата/час». AVG() працює тільки з числовими даними. Результат, що повертається цими функціями, має такий самий тип даних, як і вираз.

ПРИКЛАД 6.21

Визначіть мінімальну, максимальну й середню вартість книжок (табл. 6.19).

Таблиця 6.19

6.14.2. Угруповання

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

GROUP BY стовпець[, стовпець ...]

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

ПРИКЛАД 6.22

Визначіть кількість книжок, що зберігаються в різних бібліотечних фондах, а також їх сумарну вартість (табл. 6.20).

Нема сенсу включати імена стовпців BookCode і Cost у список фрази GROUP BY, оскільки у списку пропозиції SELECT вони використовуються тільки в узагальнюючих функціях. Однак, стовпець FundCode у списку пропозиції SELECT не зв'язаний із жодною узагальнюючою функцією, а тому обов'язково має бути зазначений у фразі GROUP BY.

Таблиця 6.20

При обробці цього запиту логіка виконання операцій така.

Таблиця 6.21

Рядки таблиці BookInventoryNumbers поділяються на групи згідно зі значеннями коду фонду (FundCode). У нашому прикладі буде створено дві групи, за кількістю унікальних значень у стовпці FundCode. Для кожної із груп розраховується загальне число рядків, яке дорівнює кількості кодів книжок, а також сума їх вартості (стовбець Cost), що нас цікавить. Потім генерується єдиний зведений рядок для всієї групи вихідних рядків. Нарешті, отримані рядки результуючої таблиці перегруповуються у порядку зростання номера коду фонду, який зазначений у стовпці FundCode.

6.14.3. Умова відбору згрупованих записів

Умова відбору згрупованих записів наводиться в пропозиції

HAVING <умови пошуку>

Тут умови пошуку задають згідно з тими самими правилам, що й у пропозиції WHERE. HAVING та WHERE можна сумісно використовувати у запиті. Спочатку пропозиція WHERE виключає з розрахунку рядки, які не відповідають її умовам пошуку. Далі працюють умови пошуку пропозиції HAVING. Вони відфільтровують з результуючого набору даних групи рядків, де підсумкові значення не відповідають їм.

Стандарт ISO вимагає, щоб імена стовпців, які використовуються у пропозиції HAVING, обов'язково були в списку GROUP BY або оброблялися агрегатними функціями. На практиці умови пошуку у пропозиції HAVING завжди включають, щонайменше, одну агрегатну функцію. Умови пошуку, що не містять агрегатних функцій, мають входити до пропозиції WHERE і застосовуватися для відбору окремих рядків. В умовах пошуку пропозиції WHERE застосовувати агрегатні функції заборонено.

ПРИКЛАД 6.23

Для кожного бібліотечного фонду, де зберігається більше шести книжок, визначіть їх кількість та сумарну вартість (табл. 6.22).

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

Таблиця 6.22

6.15.1. Підзапит

Підзапит – це інструмент створення тимчасової таблиці, вміст якої обробляється зовнішнім оператором. Внутрішні запити можуть бути використані для обчислення значень після пропозиції SELECT та в умовах пошуку після операторів порівняння (=, <,>, >=, <=, <<) або IN у пропозиціях WHERE і HAVING зовнішнього оператора SELECT. Крім того, підзапити можуть застосовуватися в операторах INSERT, UPDATE і DELETE. Текст підзапита береться у дужки.

Існує три види підзапитів:

1. скалярний – повертає таблицю, що складається з одного стовпця й одного рядка, тобто одного значення; він може використовуватися усюди, де потрібно навести одне значення;
2. рядковий – повертає значення декількох стовпців таблиці, але у вигляді єдиного рядка; він може використовуватися всюди, де застосовується конструктор рядкових значень;
3. табличний – повертає значення одного або більше стовпців таблиці, розміщених в декількох рядках; він може використовуватися всюди, де допускається вказувати множини значень, наприклад, з оператором IN.

До підзапитів установлюються такі правила й обмеження:

1. У підзапитах не повинна використовуватися фраза ORDER BY, хоча вона може бути у зовнішньому запиті.
2. Список у пропозиції SELECT має складатися з імен окремих стовпців або складених з них виразів, за винятком випадку, коли підзапит є операндом оператора EXISTS.
3. За замовчуванням імена стовпців у підзапиті відносяться до таблиці, ім'я якої є в пропозиції FROM. Однак допускається посилатися й на стовпці таблиці, зазначеної у FROM зовнішнього запиту, для чого використовуються кваліфіковані імена стовпців. Такі підзапити називаються співвіднесеними.
4. Якщо підзапит є одним із двох операндів, що брали участь в операції порівняння, то він має наводитися в правій частині цієї операції.

Коли неможливо обійтися одним підзапитом, тоді в ньому використовують вкладений підзапит і т.д.

6.15.2. Скалярні підзапити

Скалярні підзапити повертають таблицю, що складається з одного стовпця й одного рядка. Таку таблицю можна одержати, якщо після пропозиції SELECT вказати один стовпець, а в пропозиції WHERE умову пошуку створити за допомогою потенційного ключа.

ПРИКЛАД 6.24

Виведіть на екран усі дати повернення книжок читачем з номером читацького квитка 28 (табл. 6.23).

Внутрішній оператор

призначений для визначення коду читача з номером квитка 28. Після цього працює зовнішній запит. Інакше кажучи, внутрішній оператор SELECT повертає таблицю, що має єдине значення – Code = 2. У результаті зовнішній оператор SELECT набуває такого вигляду:

Таблиця 6.23

Таблицю з одним стовпцем і одним рядком можна одержати, якщо після SELECT вказати одну агрегатну функцію без використання угруповання записів у пропозиції GROUP BY. У цьому разі умови відбору які наведені в WHERE і HAVING, не відіграють неякої ролі.

ПРИКЛАД 6.25

Складіть список інвентарних номерів книжок, вартість яких вище від середньої. Вкажіть, наскільки їхня вартість вище від середньої вартості всіх книжок бібліотеки (табл. 6.24).

Таблиця 6.24

Слід зазначити, що не можна вживати «WHERE Cost > AVG(Cost)», оскільки використовувати агрегатні функції в пропозиції WHERE заборонено. Для досягнення бажаного результату слід створити підзапит, в якому розраховують середню вартість книжок, а потім застосувати його у зовнішньому операторі SELECT, який призначений для вибірки відомостей про ті книжки, вартість яких перевищує середню. Інакше кажучи, підзапит повертає середню вартість книжок бібліотеки: 38,27 грн. Результат виконання цього скалярного підзапиту використовується в зовнішньому операторі SELECT як для розрахунку відхилення вартості від середньої, так і для відбору відомостей про книжки. Тому зовнішній оператор SELECT має такий вигляд:

6.15.3. Підзапити, що повертають множину значень

Підзапити, що повертають множину значень, використовуються як операнд предиката IN. Наприклад, табличний підзапит можна використати замість внутрішнього з'єднання таблиць. Для ілюстрації цього повернемося до ПРИКЛАДУ 6.7: «Створіть список усіх читачів, які коли-небудь брали книжки в бібліотеці». З табличним підзапитом й псевдонімами таблиць оператор SELECT буде мати такий вигляд:

Усі читачі, які коли-небудь брали книжки в бібліотеці, зареєстровані в таблиці BookGiveOutRecord. Отже, нам необхідно вивести з таблиці Readers прізвища тільки тих читачів, коди яких містяться в множині значень, що повертаються підзапитом.

6.16.1. Оператор EXISTS

Оператор EXISTS застосовується тоді, коли в умовах пошуку необхідно вказати, що з таблиці потрібно вибрати тільки ті записи, для яких підзапит повертає одне або більше значень:

[NOT] EXISTS(<підзапит>)

Отже, оператор EXISTS застосовується тільки з підзапитами. Він повертає TRUE, якщо в результуючому наборі даних підзапита є один або більше записів. В іншому разі він повертає FALSE. Для NOT EXISTS правила зворотні.

ПРИКЛАД 6.26

Виберіть коди і назви тих книжок студентського фонду бібліотеки, які коли-небудь видавалися читачам (табл. 6.25).

Таблиця 6.25

6.16.2. Оператор SINGULAR

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

[NOT] SINGULAR(<підзапит>)

ПРИКЛАД 6.27

Виберіть коди і назви тих книжок, які видавалися читачам тільки один раз (табл. 6.26).

Таблиця 6.26

6.16.3. Оператори ALL, SOME, ANY

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

Тут підзапит може повертати більше одного значення. Оператор виконує операцію порівняння (>, >=, < і т.д.). Відношення між порівнюваним значенням та значеннями, що повертаються підзапитом, визначаються за допомогою операторів ALL та SOME (ANY). Отже,

поверне TRUE, якщо поточне значення атрибута «STOLBEZ» буде більше всіх значень в атрибуті «POLE» відношення «TABLIZA»;

поверне TRUE, якщо поточне значення атрибута «STOLBEZ» буде більше хоча б одного значення в атрибуті «POLE» відношення «TABLIZA».

ПРИКЛАД 6.28

Знайдіть інвентарні номери всіх книжок, вартість яких вище принаймні від вартості однієї книжки фонду під номером «1» (табл. 6.27).

У цьому випадку внутрішній підзапит поверне такі значення: {15.56, 22.33, 34.01, 12.99, 36.05, 74.20, 36.69, 48.13, 27.99}, а зовнішній – вибере відомості про ті книжки, вартість яких перевищує принаймні одне зі значень у цьому списку (фактично, більше мінімального значення – 12.99).

Таблиця 6.27

ПРИКЛАД 6.29

Знайдіть інвентарні номери книжок, вартість яких вище від вартості будь-якої книжки книжкового фонду під номером «1» (табл. 6.28).

Тут зовнішній запит вибирає відомості про книжки, вартість яких більша за максимальне значення множини {15.56, 22.33, 34.01, 12.99, 36.05, 74.20, 36.69, 48.13, 27.99}.

Таблиця 6.28

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

ПРИКЛАД 6.30

До бібліотеки примірники той самої книжки можуть надходити у різний час і мати різну вартість. Визначіть код та середню вартість примірників книжок, у яких мають середня вартість одного примірника вища від середньої вартості примірників інших книжок (табл. 6.29).

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

Таблиця 6.29

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

Зовнішнє з'єднання визначається в пропозиції FROM оператора SELECT. Воно записується в такому форматі:

У результуючий набір даних включаються усі записи базової таблиці. Яка з таблиць буде провідною, визначає вид з'єднання:

LEFT – (ліве зовнішнє з'єднання), коли базовою є таблиця, яка розташована ліворуч від назви типу з'єднання;

RIGHT – (праве зовнішнє з'єднання), коли базовою є таблиця, яка розташована праворуч від назви типу з'єднання;

FULL – у результуючий набір даних входять усі рядки таблиці, яка розташована ліворуч від типу з'єднання, та таблиці, що розташована праворуч від назви типу з'єднання, навіть якщо умови пошуку для них не виконуються.

ПРИКЛАД 6.31

Виконайте зовнішнє ліве й праве з'єднання таблиць 6.30 і 6.31 за атрибутами Р2 і Р1 відповідно.

Таблиця 6.30

Таблиця 6.31

Запишемо оператор SELECT для лівого з'єднання, позначивши таблиці 6.30 і 6.31 як А и В відповідно.

Сірим кольором зображені стовпці базової таблиці А. Як бачимо, для рядка таблиці А, де стовпець А.Р1 має значення «D», немає парних записів у таблиці В, які задовольняли би умову пошуку А.Р2 = В.Р1. Тому даний запис таблиці А з'єднаний з порожнім записом (табл. 6.32).

Таблиця 6.32

Запишемо оператор SELECT для правого з'єднання:

Сірим кольором зображений стовпець базової таблиці В. Як бачимо, для рядка таблиці В, де стовпець В.Р1 має значення «z», а стовпець В.Р2 має значення «2», немає парних записів у таблиці А, для яких виконувалась би умова пошуку А.Р2 = В.Р1. Тому даний рядок таблиці В з'єднаний з порожнім записом (табл. 6.33).

Таблиця 6.33

Іноді виникає необхідність в об'єднанні двох або більше результуючих наборів даних, які повертають окремі оператори SELECT. Для цього результуючі набори даних повинні мати однакову структуру, тобто однакову кількість і однаковий тип стовпців, що повертаються. Такі набори даних називають сумісними за об'єднанням. Записи, що дублюються, з об'єднаного набору даних виключаються.

ПРИКЛАД 6.32

Об'єднати результати виконання трьох запитів:

1. Паспорти із серією «АА» (табл. 6.34).

Таблиця 6.34

2. Паспорти, які видані у 2000 році (табл. 6.35).

Таблиця 6.35

3. Паспорти, номери яких починаються на 4 (табл. 6.36).

Таблиця 6.36

Об'єднаємо три набори даних (табл. 6.37).

Таблиця 6.37

Операція || з'єднує два рядкових значення, які можуть бути представлені виразами: <рядковий вираз1> || <рядковий вираз2>.

Операцію || можна використовувати як після пропозиції SELECT для вказівки значень, що повертаються, так і в пропозиції WHERE.

ПРИКЛАД 6.33

Помістить прізвище, ім'я, по батькові та місце роботи читачів в одну колонку, причому місце роботи взяти в круглі дужки (табл. 6.38).

Таблиця 6.38

В одному запиті можна використовувати таблиці з різних БД. У цьому випадку в СКБД Interbase ім'я таблиці подається у форматі

:Псевдонім БД :Ім’я таблиці

Під псевдонімом БД розуміють псевдонім, такий який визначений в утиліті BDE Administrator. Нижче наведений приклад звернення в одному запиті до таблиць БД InterВase (псевдонім «MONITOR») і Oracle (псевдонім «DWH»).

SELECT U.*

Формат оператора SELECT необхідно уточнювати у відповідній документації, яка надається до вибраної Вами СКБД.

1. Яка конструкція в операторі SELECT відповідає за перевірку наявності визначника NULL?
2. Що є спільного та відмінного між відомими Вам функціями, які розраховують підсумкові значення в операторі SELECT?
3. Для чого використовується пропозиція GROUP BY в операторі SELECT?
4. Яка існує різниця між умовами пошуку пропозицій HAVING та WHERE?
5. З якою метою і де застосовують підзапити?
6. Чим відрізняються результуючі набори даних, що повертаються підзапитами відомих Вам типів?
7. Які правила та обмеження формування підзапитів Вам відомі?
8. Яким чином в умовах пошуку пропозиції WHERE підзапит дозволяє використовувати агрегатні функції?
9. Які значення та за яких умов повертають оператори EXISTS та SUNGULAR?
10. Як в умовах пошуку можна порівняти скалярне значення з усіма значеннями множини, що повертається підзапитом?
11. Яка існує різниця між відомими Вам типами зовнішніх з'єднань відношень?
12. Яка існує різниця між зовнішніми та внутрішніми з'єднаннями відношень?
13. Яким умовам мають відповідати результуючі набори даних операторів SELECT, що об’єднуються за допомогою оператора UNION?
14. Як в результуючому наборі даних оператору SELECT об'єднати в одному стовпці дані, що розташовані в різних стовпцях вихідних таблиць?
15. Чи можливо в межах одного оператора SELECT працювати з таблицями, що розташовані у різних БД?