Лабораторна робота № 4

Тема: «Запити на з’єднання таблиць. Групові запити та агрегатні функції».

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

Зміст роботи: створити запити мовою SQL з використанням INNER JOIN, LEFT JOIN, RIGHТ JOIN, UNION, з використанням GROUP BY, HAVING, агрегатних функцій SUM, MIN, MAX, AVG, COUNT

Форма звіту:

1. створені запити

2. протокол лабораторної роботи

Теоретичні відомості

Часто в запитах необхідно одержувати дані з декількох таблиць. Для цього використовуються з'єднання таблиць.

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

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

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

Нехай є 2 таблиці - А і В.

Таблиця А Таблиця В

Стовпець Р1	Стовпець Р2	Стовпець Р3
a	x	400
b	x	200
c	y	500
d

Стовпець Р1	Стовпець Р2
x	1
y	2
z	2

Тоді виконання оператора

SELECT А.Р1, А.Р2, В.Р2

FROM А, В

WHERE А.Р2 = В.Р1

приведе до наступного результату

Стовпець А.Р1	Стовпець А.Р2	Стовпець В.Р2
a	x	1
b	x	1
c	y	2

Прикладом запиту на з’єднання таблиць може бути наступне завдання.

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

SELECT Студенти.Прізвище, Спеціальності.Назва

FROM Студенти, Спеціальності

WHERE Студенти.[Код спеціальності]=Спеціальності.[Код спеціальності]

Результат цього запиту представлено на рисунку 4.1.

Рисунок 4.1 – Результат запиту на з’єднання таблиць

В інструкції SELECT часто використовуються наступні агрегатні функції:

AVG - повертає середнє значення окремого поля,

COUNT - повертає кількість записів в таблиці,

SUM - повертає суму значень окремого поля,

MAX - повертає найбільше значення в окремому полі,

MIN - повертає найменше значення в окремому полі.

Агрегатні функції оперують із значеннями в єдиному стовпці таблиці і повертають єдине значення. Функції COUNT, MAX, MIN застосовуються як до числових, так і до нечислових полів. Функції SUM, AVG можуть використовуватися тільки у разі числових полів.

Дуже важливо відзначити, що агрегатні функції можуть використовуватися тільки в списку пропозиції SELECT і у складі пропозиції HAVING. У всіх інших випадках використання цих функцій неприпустимо.

Наприклад, наступний запит повертає дату народження наймолодшого студента:

SELECT MAX([Дата народження])

FROM Студенти

Щоб отримати загальну кількість студентів, необхідно виконати запит:

SELECT COUNT(Прізвище)

FROM Студенти

Інструкція GROUP BY

Запит з інструкцією GROUP BY називається груповим запитом. У ньому групуються дані, одержані в результаті виконання інструкції SELECT. При використанні GROUP BY кожен елемент списку в інструкції SELECT повинен мати єдине значення для всієї групи. Всі імена стовпців, приведені в списку інструкції SELECT, повинні бути присутніми і в інструкції GROUP BY - за винятком випадку, коли ім'я стовпця використовується в агрегатній функції.

Прикладом використання інструкції GROUP BY може бути наступне завдання – необхідно отримати кількість дисциплін, які викладаються студентам у кожному семестрі. Результат запиту представлено на рисунку 4.2.

SELECT Семестр, COUNT(Назва) as [Кількість дисциплін]

FROM Дисципліни

GROUP BY Семестр

Рисунок 4.2 – Вікно результату виконання запиту з інструкцією GROUP BY

Інструкція HAVING

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

Інструкція HAVING є необов'язковою. HAVING схожа на інструкцію WHERE, яка визначає, які записи повинні бути відібрані.

Слід відмітити, що умови в HAVING і в Where мають такі відмінності:

1. HAVING виключає з результуючого набору даних групи з результатами агрегованих значень;

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

3. в умові пошуку Where не можна указувати агрегатні функції.

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

SELECT Семестр, COUNT(Назва) AS [Кількість дисциплін]

FROM Дисципліни

GROUP BY Семестр

HAVING COUNT(Назва)>2 ;

Рисунок 4.3 – Результат виконання запиту з інструкцією HAVING

Контрольні питання

1. Що таке з’єднання таблиць?

2. Як в запиті задається з’єднання таблиць?

3. Що таке агрегатні функції?

4. Які існують агрегатні функції?

5. Чим відрізняється функція COUNT від функції SUM?

6. Що таке груповий запит?

7. Чим відрізняється GROUP BY та ORDER BY?

8. Коли слід використовувати HAVING?

9. Де у запитах записується інструкція HAVING?

10. Чим відрізняються умови в HAVING і в Where?

Виконання роботи

1. Створіть запити мовою SQL:

1. Отримати за абеткою список студентів-чоловіків 3-го курсу спеціальності СКС

2. Отримати всю інформацію про успішність заданого студента

3. Отримати кількість студентів на кожній спеціальності (врахувати всі спеціальності)

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

5. Отримати список студентів спеціальності КСС, які отримали більше 130 балів з дисципліни «Вища математика»

6. Отримати максимальну оцінку з дисципліни «Організація баз даних»

2. Виконайте запити.

3. Збережіть запити в базі даних.

Зміст звіту:

Титульний лист

Постановка завдання

Запити мовою SQL

Результати виконання запитів

Відповіді на контрольні питання

Висновки