Питання вхідного контролю

Лабораторна роботи № 5

 

Тема: Створення запитів з використанням сортування і групування даних

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

 

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

Сортування даних за допомогою виразу ORDER BY. Дані при виконанні запиту виводяться в тому порядку, в якому вони були задані в таблиці. SQL дозволяє сортувати дані за допомогою виразу ORDER BY. Вираз ORDER BY сортує записи по одному або декількох полях. Сортування можна проводити як по зростанню, так і по спаданню. По замовчуванню сортування полів символьного типу зазвичай здійснюється без врахування регістру.

Вираз ORDER BY вимагає імені поля, значення якого сортуються. Для сортування по зростанню використовується параметр ASCENDING або ASC, для сортування по спаданню використовується параметр DESCENDING або DESC. Сортування по зростанню виконується за замовчуванням.

Формат команди сортування по зростанню:

 

ORDER BY ім’я поля1 [ASC] [, ім’я поля2[ASC], …]

 

Наприклад:

 

select * 

from name_table

ORDER BY f1 asc;  або ORDER BY f1 ; 

 

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

Формат команди сортування по спаданню:

 

ORDER BY ім’я поля1 DESC [, ім’я поля2  DESC, …]

 

Наприклад:

 

select * 

from name_table

ORDER BY f1 DESC;

 

Записи по різних полях можна сортувати як по зростанню, так і по спаданню. Це показує наведений нижче приклад:

 

select * 

from name_table

ORDER BY f1 asc,  f2 DESC; 

Якщо значення поля f1 двох або більше записів будуть однаковими, то відповідні записи будуть відсортовані по полю f2 у зворотному порядку.

Групування даних за допомогою виразу GROUP BY. Групування результатів запитів має важливе значення. При використанні параметра GROUP BY всі записи в заданому полі з ідентичними значеннями об'єднуються в один елемент. Наприклад, однакові імена в наборі даних будуть занесені лише один раз. Параметр GROUP BY є уточнюючим і не обов'язковим. Наприклад, наведений запит для одержання вибірки наявних оцінок дасть результат:

 

Таблиця oz	Запит	Результат

 

Групові функції. З групуванням результату пов’язані і групові функції. Групові функції для роботи з таблицями баз даних, наведено в табл. 1.

Таблиця 1 Групові функції

Функція	Призначення
avg(стовпець)	Повертає середнє значення для стовпця
sum(стовпець)	Повертає суму значень для стовпця
min(стовпець)	Повертає найменше значення для стовпця
mах(стовпець)	Повертає найбільше значення для стовпця
std(стовпець)	Повертає середньоквадратичне відхилення для стовпця
count(*)	Повертає число записів для стовпця

 

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

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

Для таблиці oz запитз використанням функції count(), що обчислює кількість оцінок заданої категорії, дасть результат:

Таблиця oz	Запит	Результат

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

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

Для таблиці oz запитвибирає студентів з кодом ks, що мають середній бал вище 4, по всій таблиці oz.

Таблиця oz	Запит	Результат

 

Полю результату (AVG (ox) AS cer) необхідно присвоїти ім’я. Деякі СУБД можуть використовувати агрегатні функції в фразі HAVING, але в MySQL це не можна.

GROUP BY з виразом HAVING подібний до SELECT з виразом WHERE. Вираз WHERE використовується в запиті для перевірки умов, що стосуються окремих рядків, а умови HAVING застосовуються до груп.

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

 

Таблиця oz	Запит	Результат

 

MySQL дозволяє також сортувати порядок груп. За замовчуванням заданий зростаючий порядок. Щоб в останньому запиті результати були представлені в спадаючому порядку, використовується параметр desc.

Для таблиці oz запитвибирає студентів з кодом ks і виводить їх по спаданню, з середнім балом.

Таблиця oz	Запит	Результат

 

Питання вхідного контролю

1 Які ви знаєте засоби сортування даних?

2 Які ви знаєте засоби групування результатів?

3 В чому полягає необхідність сортування результатів?

5 Для чого призначений вираз HAVING?

 

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

 

В базі даних УСПІШНІСТЬ, що складається з шести таблиць:

– STUDENTY(kod_studenta, prizvyshche_studenta, imia_studenta, kod_hrupy);

– HRUPA(kod_hrupy, nazva_hrupy, kurs, chyslo_studentiv, prizvishche_kuratora);

– KAFEDRA(kod_kafedry, nazva_kafedry, zaviduvach_kafedry, korpus, fond_kafedry);

– VYKLADACH(kod_vykladacha, kod_kafedry, prizvishche_vykladacha, imia_pobatkovi_vykladacha, posada_vykladacha, zarplata, stash);

– DYSTSYPLINA(kod_dystsypliny, nazva_dystsypliny, kod_vykladacha);

– OTSINKY(poriadkovyi_nomer, kod_studenta, otsinka, kod_dystsypliny)

побудувати наступні запити:

 

1 Для таблиці HRUPA (створити запити на мові SQL:

- Вивід списку прізвищ кураторів у порядку зростання числа студентів

- Вивід полів таблиці, відсортованих по прізвищу і іменах кураторів в зростаючому порядку.

2 Для таблиці VYKLADACH створити запити на мові SQL:

- Вивід наявних посад на кафедрі;

- Вивід кількості співробітників на кожній посаді в порядку зростання

- Вивід списку співробітників у порядку зростання їх зарплати;

- Вивід списку співробітників в спадному порядку їх стажу роботи;

- Вивід списку співробітників (прізвище та ім'я), які обіймають посаду "програміст" або "розроблювач Web" і відсортувати їхні прізвища за алфавітом.