AVG, count, max, min, sum

Приклад. Визначити середній оклад економістів:

SELECT АVG (Оклад)

FROM Співробітники

WHERE Посада = 'Економіст'

Приклад. Визначити загальний оклад і загальну премію у всіх інженерів:

SELECT SUM (Внесок), 8ІМ (Премія)

FROM Співробітники

WHERE Посада = 'Інженер'

Групові функції можуть бути використані в аналітичних виразах.

Приклад:

SELECT АVG (Оклад + Премія) * 12

FROM Співробітники

WHERE Посада = 'Інженер'

Приклад. Визначити в організації мінімальні і максимальні оклади, і їх різницю: SELECT МАХ (Оклад), МIN (Оклад), МАХ (Оклад) - МIN (Оклад) AS Результат

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

Приклад. Отримати прізвище і оклад співробітника з максимальним окладом, використовуючи підзапит:

SELECT Прізвище, Оклад

FROM Співробітники

WHERE Оклад = (SELECT МАХ (Оклад) FROM Співробітники)

Той же результат видасть і наступний запит:

SELECT Прізвище, МАХ (Оклад).

Приклад. Підрахувати кількість співробітників 30-го відділу, які отримують премію:

SELECT COUNT (Премія)

FROM Співробітники

WHERE Відділ = 30 AND (Премія> 0 OR Премія NOT NULL)

COUNT можна використовувати зі словом DISTINCT

Приклад. Підрахувати кількість посад співробітників 30-го відділу:

SELECT COUNT (DISTINCT Посада)

FROM Співробітники

WHERE Відділ = 30

COUNT (*) підраховує кількість рядків, що задовольняють умовам пошуку вибору.

Приклад:

SELECT COUNT (*)

FROM Співробітники

WHERE Відділ = 30

Використання агрегативних функцій в запиті разом з операндом GROUP BY. GROUP BY служить для вибірки підсумкових даних по групам:

Приклад. Визначити середній оклад співробітників відділів № 10, № 20, № 30:

SELECT АVG (Оклад)

FROM Співробітники

GROUP BY Відділ = 10 (20,30)

GROUP BY поділяє таблицю на групу рядків із співпадаючими значеннями в одному і тому ж стовпці або стовпцях.

Приклад. Вибрати номери відділів та середній оклад по відділу:

SELECT Відділ АVG (Оклад)

FROM Співробітники

GROUP BY Відділ

Якщо в запиті використовується GROUP BY, то в пропозиції SELECT разом з груповими функціями можна вказати груповий стовпець, оскільки значення в груповому стовпці є атрибутом групи рядків (наприклад, у всієї групи один номер відділу, так як рядки групуються по відділу).

Пропозиція GROUP BY завжди має слідувати за WHERE, якщо воно є, а якщо WHERE немає - то за FROM

Для розподілу рядків по групах можна використовувати значення стовпців.

Приклад:

SELECT Відділ, Посада, COUNT (*), АVG (Оклад)

FROM Співробітники

GROUP BY Відділ, Посада

В одному запиті спільно з груповими функціями можна використовувати умови з'єднання

таблиць. Наприклад, додамо в попередній приклад номер відділу і назва відділу.

SELECT НазвВід, Посада, COUNT (*),АVG (Оклад)

FROM Співробітники, Відділи

WHERE Сотруднікі. Відділ = Відділи. Відділ

GROUP BY НазвВід, Посада

Пропозиція HAVING

Може задавати умову пошуку для груп (як для окремих рядків SELECT):

HAVING поле GROUP BY

Тут порівнюються атрибути груп

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

SELECT Посада, COUNT (*),АVG (Оклад) * 12

FROM Співробітники

GROUP BY Посада

HAVING COUNT (*)> 2

У даному запиті можуть бути присутніми як WHERE так і HAVING. Спочатку застосовується WHERE. З решти рядків формується група відповідно до GROUP BY. До отриманих групам застосовується пропозицію HAVING.

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

SELECT Відділ

FROM Співробітники

WHERE Посада = 'Інженер'

GROUP BY Відділ

HAVING COUNT (*)> 2

Пропозиція HAVING може порівнювати різні атрибути груп.

Приклад. Скласти список відділів, середня премія в яких перевищує 85% окладу: SELECT Відділ, АVG (Оклад), АVG (Премія), АVG (Оклад) + 0,25

FROM Співробітники

GROUP BY Відділ

HAVING АУО (Премія)> 0.25 * АVG (Оклад)

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

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

SELECT Посада, АVG (Оклад)

FROM Співробітники

GROUP BY Посада

HAVING АVG (Оклад)> (SELECT АVG (Оклад)

FROM Співробітники

WHERE Посада = 'Завідувач')

11.6. ВКЛАДЕНІ ЗАПИТИ.

Підзапит - це додатковий метод маніпулювання з кількома таблицями. Це оператор SELECT, вкладений:

в пропозицію WHERE, HAVING або SELECT іншого оператора SELECT;

в оператор INSERT, UPDATE або DELETE;

в інший підзапит.

Саме можливість вкладення операторів SQL один в одного є причиною, по якій SQL спочатку був названий Structured Query Language. Термін підзапит часто використовується для посилання на всю сукупність операторів, яка включає один або декілька підзапитів, а також на окреме вкладення. Кожен включає оператор - наступний по старшинству рівень в підзапитів - являє собою зовнішній рівень для внутрішнього підзапиту.

Спрощений синтаксис підзапиту.

Умови пошуку, які відносяться до другого запиту, також можуть зустрічатися у реченні WHERE зовнішнього запиту - до або після внутрішнього запиту.

SELECT [DISTINCT]

<Список полів> або *

FROM <Список таблиць> Початок зовнішнього оператора SELECT

WHERE

{вираз {[NOT] IN | оператор порівняння [ANY | ALL]} | [NOT] EXISTS}

(SELECT [DISTINCT] список вибору підзапиту

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

WHERE умови)

[GROUP BY <Список полів для групування>]

[HAVING <Умова групування>]

[ORDER BY <Список полів для сортування>]

Як працює підзапит?

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

У корелювати підзапитів внутрішній запит не може бути реалізований негайно: він посилається на зовнішній запит і виконується по черзі для кожного рядка в зовнішньому запиті. Як корельовані, так і некорельованими запити бувають трьох типів, залежно від елементів у реченні WHERE зовнішнього запиту.

Підзапити, які не повертають жодного або повертають кілька елементів (починаються з IN або з оператора порівняння, містять ключові слова ANY або ALL). Підзапити, які повертають єдине значення (починаються з простого оператора порівняння).

Підзапити, які являють собою тест на здійснення (починаються з EXISTS).

Приклад. Знайти всіх співробітників, у яких посада як у Реброва, а саме завідувач (некорельований підзапит):

SELECT Прізвище, Посада

FROM Співробітники

WHERE Посада IN (

SELECT Посада

FROM Співробітники

WHERE Прізвище = 'Ребров')

Підзапити, що повертають набір значень.

Підзапит може не повертати значення або повертати декілька значень. Ця група включає підзапити, що починаються з IN, NOT IN або оператора порівняння з ключовими словами ANY або ALL.

Підзапити, що починаються з ключового слова IN, мають таку загальну форму:

Початок операторів SELECT, INSERT, UPDATE, DELETE або підзапиту:

WHERE вираз [NOT] IN (підзапит)

[Кінець операторів SELECT, INSERT, UPDATE або підзапит]

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

Коли той чи інший підзапит починається з ключового слова EXISTS, цей підзапит функціонує як "тест на існування". Ключове слово EXISTS у реченні WHERE виконує перевірку на існування (чи неіснування) даних, які задовольняють критеріям відповідного підзапиту, тобто EXISTS виконує перевірку на наявність або відсутність "порожнього набору" рядків. Якщо підзапит повертає хоча б один рядок, цей результат оцінюється як "істина". Якщо підзапит повертає порожній набір, цей результат оцінюється як "брехня".

Підзапити, що починаються з ключового слова EXISTS, мають таку загальну форму:

Початок операторів SELECT, INSERT, UPDATE, DELETE або підзапит

WHERE вираз [NOT] EXISTS (підзапит)

[Кінець операторів SELECT, INSERT, UPDATE або підзапит]

В іншому вигляді підзапитів, які не повертають або повертають кілька рядків, використовують оператор порівняння, модифікований ключовими словами ANY або ALL. Підзапити, що починаються з модифікованого оператора порівняння, мають загальну форму такого вигляду:

Початок операторів SELECT, INSERT, UPDATE, DELETE або підзапит

WHERE вираз оператор порівняння [ANY | ALL] (підзапит)

[Кінець операторів SELECT, INSERT, UPDATE або підзапит]

Що таке ANY і ALL. Якщо в якості прикладу скористатися операторами порівняння ">", то "> ALL" означає "більше, ніж кожне значення" (іншими словами, "більше, ніж найбільше значення"). Таким чином, "> ALL (1,2,3)" означає "більше, ніж 3". "> ANY" означає "більше, ніж, принаймні, одне значення» (іншими словами, "більше, ніж найменше значення"). Таким чином, "> ANY (1,2,3)" означає "більше, ніж 1 ". Таблиця 10.2 ілюструє відмінність між ключовими словами ANY і ALL.

Таблиця 10.2 Різниця між ключовими словами ANY і ALL

ALL	Результат	ANY	Результат
> ALL (1, 2, 3)	> 3	> ANY (1, 2, 3)	> 1
< ALL (1, 2, 3)	<1	<ANY (1, 2, 3)	<3
= ALL (1, 2, 3)	=1 чи =2 чи =3	= ANY (1, 2, 3)	=1 чи =2 чи =3

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

SELECT DISTINCT Оклад, Прізвище, Посада, Відділ

FROM Співробітники

WHERE Оклад> ANY (SELECT Оклад FROM Співробітники WHERE Відділ = 30)

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

Приклад. Необхідно дізнатися прізвища співробітників, посада і оклад яких збігаються з

даними співробітника Даніна:

SELECT DISTINCT Оклад, Посада, Прізвище

FROM Співробітники

WHERE (Посада, Оклад) = (SELECT Посада, Оклад

FROM Співробітники WHERE Прізвище = 'Данін')

Складові запити з декількома підзапитів.

Приклад. Скласти список співробітників, посада яких збігається з посадою співробітника Реброва або отримують стільки ж або більше ніж співробітник Данин:

SELECT Оклад, Посада, Прізвище

FROM Співробітники

WHERE Посада = ANY (SELECT Посада

FROM Співробітники

WHERE Прізвище = 'Ребров'

OR Оклад> = ALL (SELECT Посада

FROM Співробітники

WHERE Прізвище = 'Данин')

ORDER BY Посада, Оклад

Приклад. Знайти прізвище та займану посаду всіх співробітників відділу 10, посада яких збігається з посадою якого-небудь співробітника відділу торгівлі:

SELECT Посада, Прізвище

FROM Співробітники

WHERE Посада IN (SELECT Посада

FROM готелів, Співробітники

WHERE Назв відділу = 'Торгівля' and

Співробітники. Відділ = Відділи. Відділ)

Синхронізація повторюваних підзапитів

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

Select Відділ, Прізвище, Оклад

From Співробітники S

Where Оклад> (Select AVG (оклад)

From Співробітники

Where відділ = S.відділ)

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

Номер відділу з цього рядка передається під вкладений підзапит і використовується замість S.отдел. Виконується вкладений підзапит, що обчислює середню зарплату для заданого відділу. Середній оклад передається в основний запит і використовується в реченні Where.

Приклад використання підзапитів з умовою з'єднання.

Знайти дані про співробітників, що працюють в Твері, посада яких така ж, як у

Євдокимова:

Select Прізвище, Оклад, Посада, Місто

From Співробітники, Відділи

Where Місто = "Тверь" AND

Співробітники.отдел = Відділи.відділ AND

Посада = ANY (Select Посада

From Співробітники

Where Прізвище = "Євдокимов")

ORDER BY Прізвище

Комбінація декількох команд Select

Запити і підзапити можуть комбінуватися з декількох команд Select за допомогою операторів:

• UNION - об'єднання. Результат виконання - не дублюються записи, отримані в результаті першого і другого запиту.

• INTERSECT - перетин. Результат - записи, отримані як у першому, так і в другому запитах.

• EXCEPT - виняток. Результат - записи, витягнуті в першому запиті за винятком записів, отриманих у другому запиті.

Приклад. Видати співробітників з прізвищем Іванов і отримують більше, ніж 100 рублів:

Select Прізвище, Оклад

From Співробітники

Where Прізвище = 'Іванов'

UNION

Select Прізвище, Оклад

From Співробітники

Where Оклад> 100

Зауваження:

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

Дата добавления: 2014-01-11