статус, більший статусу другого постачальника:
SELECT
P1.PNAME AS PNAME1
P1.PSTATUS AS PSTATUS1
P2.PNAME AS PNAME2
P2.PSTATUS AS PSTATUS2
FROM
P P1, P P2
WHERE P1.PSTATUS1 > P2.PSTATUS2;
У результаті одержимо наступну таблицю:
PNAME1 | PSTATUS1 | PNAME2 | PSTATUS2
Іванов | 4 | Петров | 1
Іванов | 4 | Сидоров | 2
Сидоров | 2 | Петров | 1
Приклад 20. Розглянемо ситуацію, коли деякі постачальники (назвемо їх контрагенти) можуть виступати як як постачальники деталей, так і як одержувачі. Таблиці, що бережуть дані можуть мати наступний вигляд:
Номер контрагента
NUM | Найменування контрагента
NAME
1 | Іванов
2 | Петров
3 | Сидоров
Таблиця 3 Відношення CONTRAGENTS
Номер деталі
DNUM | Найменування деталі
DNAME
1 | Болт
2 | Гайка
3 | Гвинт
Таблиця 4 Відношення DETAILS (Деталі)
Номер постачальника
PNUM | Номер отримувача
CNUM | Номер деталі
DNUM | Поставляемое количество
VOLUME
1 | 2 | 1 | 100
1 | 3 | 2 | 200
1 | 3 | 3 | 300
2 | 3 | 1 | 150
2 | 3 | 2 | 250
3 | 1 | 1 | 1000
Таблиця 5 Відношення CD (Поставки)
У таблиці CD (поставки) поля PNUM і CNUM є зовнішніми ключами, що посилаються на потенційний ключ NUM в таблиці CONTRAGENTS.
Відповідь на питання "хто кому що в якій кількості поставляє" дається наступним запитом:
SELECT
P.NAME AS PNAME
C.NAME AS CNAME
DETAILS.DNAME
CD.VOLUME
FROM
CONTRAGENTS P
CONTRAGENTS З
DETAILS
CD
WHERE
P.NUM = CD.PNUM AND
C.NUM = CD.CNUM AND
D.DNUM = CD.DNUM;
У результаті одержимо наступну таблицю:
Найменування постачальника
PNAME | Найменування одержувача
CNAME | Найменування деталі
DNAME | Кількість, що поставляється
VOLUME
Іванов | Петров | Болт | 100
Іванов | Сидоров | Гайка | 200
Іванов | Сидоров | Гвинт | 300
Петров | Сидоров | Болт | 150
Петров | Сидоров | Гайка | 250
Сидоров | Іванов | Болт | 1000
Зауваження. Цей же запит може бути виражений дуже великою кількістю способів, наприклад, так:
SELECT
P.NAME AS PNAME
C.NAME AS CNAME
DETAILS.DNAME
CD.VOLUME
FROM
CONTRAGENTS P
CONTRAGENTS З
DETAILS NATURAL JOIN CD
WHERE
P.NUM = CD.PNUM AND
C.NUM = CD.CNUM;
Використовування агрегатних функцій в запитах
Приклад 21. Одержати загальну кількість постачальників (ключове слово COUNT):
SELECT COUNT(*) AS N
FROM P;
У результаті одержимо таблицю з одним стовпцем і одним рядком, що містить кількість рядків з таблиці P:
N
3
Приклад 22. Одержати загальне, максимальне, мінімальне і середнє кількості деталей, що поставляються (ключові слова SUM, MAX, MIN, AVG):
SELECT
SUM(PD.VOLUME) AS SM
MAX(PD.VOLUME) AS MX
MIN(PD.VOLUME) AS MN
AVG(PD.VOLUME) AS AV
FROM PD;
У результаті одержимо наступну таблицю з одним рядком:
SM | MX | MN | AV
2000 | 1000 | 100 | 333.33333333
Використовування агрегатних функцій з групуваннями
Приклад 23. Для кожної деталі одержати сумарну кількість, що поставляється (ключове слово GROUP.):
SELECT
PD.DNUM
SUM(PD.VOLUME) AS SM
GROUP PD.DNUM;
Цей запит виконуватиметься таким чином. Спочатку рядки початкової таблиці будуть згруповані так, щоб в кожну групу потрапили рядки з однаковими значеннями DNUM. Потім усередині кожної групи буде підсумовано поле VOLUME. Від кожної групи в результуючу таблицю буде включений один рядок:
DNUM | SM
1 | 1250
2 | 450
3 | 300
Зауваження. В списку відбираних полів оператора SELECT, що містить розділ GROUP можна включати тільки агрегатні функції і поля, які входять в умову угрупування. Наступний запит видасть синтаксичну помилку:
SELECT
PD.PNUM
PD.DNUM
SUM(PD.VOLUME) AS SM
GROUP PD.DNUM;
Причина помилки в тому, що в список відбираних полів включено поле PNUM, яке не входить в розділ GROUP. І дійсно, в кожну одержану групу рядків може входити декілька рядків з різними значеннями поля PNUM. З кожної групи рядків буде сформовано по одному підсумковому рядку. При цьому немає однозначної відповіді на питання, яке значення вибрати для поля PNUM в підсумковому рядку.
Зауваження. Деякі діалекти SQL не рахують це за помилку. Запит буде виконаний, але передбачити, які значення будуть внесені в полі PNUM в результуючій таблиці, неможливо.
Приклад 24. Одержати номери деталей, сумарна кількість яких, що поставляється, перевершує 400 (ключове слово HAVING.):
Зауваження. Умова, що сумарна кількість, що поставляється, повинна бути більше 400 не може бути сформульовано в розділі WHERE, оскільки в цьому розділі не можна використовувати агрегатні функції. Умови, що використовують агрегатні функції повинні бути розміщені в спеціальному розділі HAVING:
SELECT
PD.DNUM
SUM(PD.VOLUME) AS SM
GROUP PD.DNUM
HAVING SUM(PD.VOLUME)> 400;
У результаті одержимо наступну таблицю:
DNUM | SM
1 | 1250
2 | 450
Зауваження. В одному запиті можуть зустрітися як умови відбору рядків в розділі WHERE, так і умови відбору груп в розділі HAVING. Умови відбору груп не можна перенести з розділу HAVING в розділ WHERE. Аналогічно і умови відбору рядків не можна перенести з розділу WHERE в розділ HAVING, за винятком умов, що включають поля із списку угрупування GROUP.
Використовування підзапитів
Дуже зручним засобом, що дозволяє формулювати запити більш зрозумілим чином, є можливість використовування підзапитів, вкладених в основний запит.
Приклад 25. Одержати список постачальників, статус яких менше максимального статусу в таблиці постачальників (порівняння з підзапитом):
SELECT *
FROM P
WHERE P.STATYS <
(SELECT MAX(P.STATUS)
FROM P);
Зауваження. Оскільки поле P.STATUS порівнюється з результатом підзапиту, то підзапит повинен бути сформульований так, щоб повертати таблицю, що складається рівно з одного рядка і однієї колонки.
Зауваження. Результат виконання запиту буде еквівалентний результату наступної послідовності дій:
Виконати один раз вкладений підзапит і набути максимальне значення статусу.
Просканувати таблицю постачальників P, кожного разу порівнюючи значення статусу постачальника з результатом підзапиту, і відібрати тільки ті рядки, в яких статус менше максимального.
Приклад 26. Використання предиката IN. Одержати список постачальників, що поставляють деталь номер 2:
SELECT *
FROM P
WHERE P.PNUM IN
(SELECT DISTINCT PD.PNUM
FROM PD
WHERE PD.DNUM = 2);
Зауваження. В даному випадку вкладений підзапит може повертати таблицю, що містить декілька рядків.
Зауваження. Результат виконання запиту буде еквівалентний результату наступної послідовності дій:
Виконати