Використання функції приведення типу (CAST)

Приклад. Знайти покупця, який робив закупівлі чи то на 209, чи то на 309 одиниць товару. На скільки саме, ніхто не пам'ятає; пригадали лише, що останніми розрядами в кількості товару, що відпускається, були цифри '09'. Тоді приводимо значення поля KILK до типу CHARACTER і до результату застосовуємо LIKE.

SELECT *

FROM PRODAZH

WHERE CAST(KILK AS CHAR(4)) LIKE “%09%”

Приклад. Видати всі покупки товару за 20 число (припустимо, кожного місяця). У InterBase немає вбудованих функцій для розділення дати на число, місяць і рік. Варіанти рішення:

• у додатку, розробленому на Delphi, аналізувати дати за допомогою процедури DecodeDate;

• написати UDF (User Defined Function, визначену користувачем функцію), яка реалізує виділення номера дня з дати, і використовувати ім'я цієї функції в операторові SELECT;

• привести значення дати до типу CHAR (або, що краще, трактувати значення дати як строкове значення) і застосувати до отриманого значення LIKE, CONTAINING або STARTING WITH, залежно від потреби.

Скористаємося останнім способом.

SELECT DAT_PROD, TOVAR, POKUP, KILK

FROM PRODAZH

WHERE CAST(DAT_PROD AS CHAR(6)) STARTING WITH "20"

або, усунувши непотрібне, в даному випадку, приведення типів

SELECT DAT_PROD, TOVAR, POKUP, KILK FROM PRODAZH

WHERE DAT_PROD STARTING WITH "20"

Використання підзапитів

Внутрішній підзапит є також оператором SELECT і кодування його пропозицій підкоряється тим же правилам, що і для основного оператора SELECT.

У загальному випадку оператор SELECT з підзапитом має вигляд

SELECT ...

FROM ...

WHERE <порівнюване значення>  <оператор>  (SELECT ...)

Приклад. Видати всі дати, на які припадає максимальний продаж товарів

SELECT KILK, DAT_PROD

FROM PRODAZH

WHERE KILK = (SELECT MAX(KILK) FROM PRODAZH)

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

SELECT PR.KILK, PR.DAT_PROD, PO.POKUP, PO.MISTO, PO.ADRESA

FROM PRODAZH PR, POKUPCI PO

WHERE (PR.POKUP = PO.POKUP) AND

KILK =(SELECT MAX(KILK)

FROM PRODAZH)

В порівнянні з попереднім прикладом в запит включено внутрішнє з'єднання таблиць PRODAZH і POKUPCI.

Приклад. Знайти в таблиці POKUPCI покупця, у якого поле MISTO містить "Луцьк" і видати всі здійснені ним покупки товарів, з таблиці PRODAZH. Може бути написаний наступний потенційно помилковий запит.

SELECT PR.DAT_PROD, PR.TOVAR, PR.KILK, PR.POKUP

FROM PRODAZH PR

WHERE PR.POKUP = (SELECT POK.POKUP

FROM POKUPCI POK

WHERE UPPER(POK.MISTO)= 'ЛУЦЬК')

Хоча для значення "Луцьк" може бути виданий коректний результат, такий запит потенційно помилковий, оскільки здатний повертати декілька значень (покупців з одного міста).

Вихід: замінити знак рівності (=) на IN.

Вкладення підзапитів

Приклад. Скласти список відвантаження товарів покупцеві, який свого часу купив максимальну партію якого-небудь товару.

SELECT RRR.* FROM PRODAZH RRR

WHERE RRR.POKUP IN

(SELECT PR.POKUP FROM PRODAZH PR

WHERE KILK = (SELECT MAX(RSH.KILK) FROM PRODAZH RSH)

)

або, наприклад

SELECT RRR.* FROM PRODAZH RRR

WHERE RRR.POKUP IN

(SELECT PR.POKUP FROM PRODAZH PR GROUP BY PR.POKUP

HAVING MAX(PR.KILK)= (SELECT MAX(RSH.KILK) FROM PRODAZH RSH)

)

Додаткові можливості використання підзапитів, що повертають одиничне значення

Використання відбору “хоча б одне значення” (EXISTS)

Приклад. Видати список всіх покупців, які отримували товар з складу.

SELECT PO.POKUP FROM POKUPCI PO

WHERE EXISTS (SELECT PR.POKUP FROM PRODAZH PR WHERE PR.POKUP = PO.POKUP)

Використання відбору “лише одне значення” (SINGULAR)

Приклад. Видати список всіх покупців, що купили тільки один товар.

SELECT PO.* FROM POKUPCI PO

WHERE SINGULAR (SELECT * FROM PRODAZH PR WHERE PR.POKUP = PO.POKUP)

Використання підзапитів, що повертають множинузначень

Використання “відношення з усіма” (ALL), “відношення з деякими” (SOME, ANY)

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

SELECT * FROM PRODAZH P1

WHERE P1.KILK > ALL (SELECT AVG(P2.KILK) FROM PRODAZH P2 GROUP BY POKUP)

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

SELECT * FROM PRODAZH P1

WHERE Pl.KILK > SOME (SELECT AVG(P2.KILK) FROM PRODAZH P2 GROUP BY POKUP )

Використання HAVING і агрегатних функцій для вкладених підзапитів

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

SELECT P1.POKUP, AVG(Pl.KILK) FROM PRODAZH P1

GROUP BY P1.POKUP HAVING AVG(P1.KILK) >= ALL

(SELECT AVG(P2.KILK) FROM PRODAZH P2 GROUP BY P2.POKUP )

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

SELECT PO.* FROM POKUPCI PO

WHERE PO.POKUP =

(SELECT RR.POKUP FROM PRODAZH RR

GROUP BY RR.POKUP HAVING SUM(RR.KILK) >= ALL

(SELECT SUM(RRR.KILK) FROM PRODAZH RRR GROUP BY RRR.POKUP)

)

Приклад. Перерахувати всі товари, які придбав покупець, що купив найбільшу кількість товарів.

SELECT DISTINCT PR.TOVAR FROM PRODAZH PR

WHERE PR.POKUP =

(SELECT RR.POKUP FROM PRODAZH RR

GROUP BY RR.POKUP HAVING SUM(RR.KILK) >= ALL

(SELECT SUM(RRR.KILK) FROM PRODAZH RRR GROUP BY RRR.POKUP)

)

Приклад. Перерахувати вартість одиниць товарів, які придбав покупець, що купив найбільшу кількість товарів

SELECT DISTINCT PR.TOVAR, T.OD_VYM, T.CINA FROM PRODAZH PR, TOVARY T

WHERE T.TOVAR = PR.TOVAR AND PR.POKUP = (SELECT RR.POKUP FROM PRODAZH RR

GROUP BY RR.POKUP HAVING SUM(RR.KILK) >= ALL

(SELECT SUM(RRR.KILK) FROM PRODAZH RRR GROUP BY RRR.POKUP )

Зовнішні з'єднання (JOIN … ON … LEFT/RIGHT)

Приклад. Побудувати зовнішнє з'єднання по таблиці PRODAZH з таблицею POKUPCI, тобто показати покупця, відповідного кожній витраті.

SELECT PR.DAT_PROD, PR.TOVAR, PR.KILK, PO.POKUP, PO.MISTO

FROM PRODAZH PR LEFT JOIN POKUPCI PO ON PR.POKUP = PO.POKUP

або

SELECT PR.DAT_PROD, PR.TOVAR, PR.KILK, PO.POKUP, PO.MISTO

FROM POKUPCI PO RIGHT JOIN PRODAZH PR ON PR.POKUP = PO.POKUP

Приклад. Побудувати зовнішнє з'єднання по таблиці POKUPCI з таблицею PRODAZH, тобто показати всі витрати по кожному покупцеві.

SELECT PO.POKUP, PO.MISTO, PR.DAT_PROD, PR.TOVAR, PR.KILK

FROM POKUPCI PO LEFT JOIN PRODAZH PR ON PR.POKUP = PO.POKUP

або

SELECT PO.POKUP, PO.MISTO, PR.DAT_PROD, PR.TOVAR, PR.KILK

FROM PRODAZH PR RIGHT JOIN POKUPCI PO ON PR.POKUP = PO.POKUP

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

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

1.

SELECT PR.* FROM PRODAZH PR

WHERE PR.TOVAR CONTAINING 'Цукор'

2.

SELECT PR.* FROM PRODAZH PR

WHERE PR.KILK >= 3000

3.

SELECT PR.* FROM PRODAZH PR

WHERE PR.POKUP = 'Ліра, ТзОВ'

Проведемо об'єднання три результуючих наборів даних.

SELECT PR.* FROM PRODAZH PR WHERE PR.TOVAR CONTAINING 'Цукор'

UNION SELECT PR.* FROM PRODAZH PR WHERE PR.KILK >= 3000

UNION SELECT PR.* FROM PRODAZH PR WHERE PR.POKUP = 'Ліра, ТзОВ'

Використання IS NULL

Приклад. Показати всі факти відвантаження товарів з складу, для яких не вказаний покупець.

SELECT * FROM PRODAZH WHERE POKUP IS NULL

Використання операції зчеплення рядків (II)

Приклад. Видати у вигляді єдиного поля імена покупців і назви їх міст.

SELECT POKUP || ' (' || MISTO || ')' FROM POKUPCI