Варіанти індивідуальних завдань

1 Визначити середнє арифметичне кількості студентів груп ІТ-220, ІТ-210.

2 Визначити кількість студентів груп ІТ-220 і ІТ-210

3 Визначити кількість студентів груп ТО-220 і ОА-21.

4 Визначити суму балів з математики

5 Створити запит, який виводить на екран кількість рядків таблиці Студенти, код студента яких менший від 5-ти.

6 Визначити кількість студентів

7 Визначити кількість рядків таблиці Студенти

8 Визначити максимальну кількість студентів груп ТО-220 і ОА-21.

9 Визначити середній бал з фізики

10 Визначити суму балів з фізики

11 Створити запит, який виводить на екран кількість рядків таблиці Оцінки, код дисципліни яких менший від 5-ти.

12 Визначити мінімальну кількість студентів.

13 Визначити кількість студентів груп ОА-21 і ІТ-210

14 Визначити середній бал з інформатики

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

1. Яка функція повертає найменше значення записів даного поля.?

2. Яка функція повертає суму записів даного поля?

3. Яка функція знаходить середнє значення записів даного поля.?

4. Де записується умова вибору записів для знаходження мінімального значення серед них?

5. Яка функція повертає найбільше значення записів даного поля.?

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

Тема: Використання групування і сортування результатів

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

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

Використання GROUP BY

Наступним з розглянутих нами виразів є GROUP BY. Він дозволяє розподілити стрічки, що виймаються, по групах і є особливо корисним при його використанні в комбінації з функціями, застосовуваними до груп рядків. Тут ми згадуємо тільки одну таку функцію – count().

Розглянемо наступний запит:

select count(*), job

from employee

group by job;

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

+-------------+--------------------------------------+

| count(*) | job                                |

+-------------+--------------------------------------+

|    1 | Адміністратор БД          |

|    2 | Програміст                          |

|    1 | Системний адміністратор |

+------------+---------------------------------------+

3 rows in set (0.04 sec)

Тут слід зазначити, що в MySQL і ANSI SQL вираження GROUP BY працює по-різному.

В ANSI SQL необхідно групувати по всіх стовпцях, зазначених у вихідному виразі SELECT. В MySQL у виразі SELECT дозволяється вказувати додаткові стовпці, що не входять у вираз GROUP BY.

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

select count(*), job

from employee

group by job desc;

Результат буде приблизно наступним:

+------------+-------------------------------------+

 |count (*) | job                                     |

+-------------+------------------------------------+

 |1   | Системний адміністратор |

 |2   | Програміст                       |

 |1   | Адміністратор БД            |

+------------+-------------------------------------+

3 rows in set (0.04 sec)

Як бачите, назви посад тепер приводяться в порядку, зворотному алфавітному. Для вказівки зростаючого порядку можна використовувати ASC, але це значення використовується за замовчуванням, тому вказувати його не потрібно.

Функції у виразах GROUP BY

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

select count(*)

from employee;

Цей запит поверне число рядків таблиці employee.

Зазвичай, такі функції застосовуються до груп. Наприклад:

select job, count(job)

from employee

group by job;

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

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

Групуючі функції

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

Вибір груп за допомогою HAVING

Наступним виразом в операторі SELECT є вираз HAVING. GROUP BY з виразом HAVING подібний до SELECT з виразом WHERE. Наприклад:

select count(*), job

from employee

group by job

having count(*) = l;

Такий запит вибере всі наявні в компанії посади, на яких працює по одному службовцю. Результат виконання запиту буде приблизно наступним:

+--------------+------------------------------------+

| count(*) | job                                  |

+--------------+------------------------------------+

|    1  | Адміністратор БД        |

|    1  | Системний адміністратор |

+--------------+------------------------------------+

2 rows in set (0.05 sec)

Досвід показує, що починаючі користувачі SQL часто плутають вираз WHERE і HAVING. Вираз WHERE використовується в запиті для перевірки умов, що стосуються окремих стрічок, а умови HAVING застосовуються до груп.

Сортування результатів пошуку за допомогою ORDER BY

Наступним виразом в операторі SELECT є вираз ORDER BY. Він дає можливість відсортувати рядки результату по одному або декількох стовпцях. Сортувати при цьому можна як по зростанню (позначається ASC), так і по спаданню (позначається DESC). Наприклад:

select *

from employee

order by job asc, name desc;

У результаті будуть обрані усі рядки з таблиці employee і відсортовані за значенням job за абеткою. Якщо при цьому два або більше службовців будуть мати однакові посади, то відповідні рядки будуть відсортовані за іменем у зворотному порядку.

Упорядкування даних

Розглянемо питання про те, як можна змінити порядок виводу даних, витягнутих з таблиць MySQL, використовуючи вираз ORDER BY оператора SELECT.

Дані, що виймаються до цього, завжди виводилися в тому порядку, у якому вони були збережені в таблиці. У дійсності SQL дозволяє сортувати витягнуті дані за допомогою виразу ORDER BY. Ця пропозиція вимагає ім'я стовпця, на основі якого будуть сортуватися дані. Давайте подивимося, як можна вивести імена співробітників з упорядкованими за алфавітом прізвищ співробітників (у зростаючому порядку).

SELECT l_name, f_name

from employee_data

ORDER BY l_name;

А от так співробітників можна відсортувати за віком.

SELECT f_name, l_name, age

from employee_data

ORDER BY age;

Вираз ORDER BY може сортувати в зростаючому порядку (ASCENDING або ASC) або в спадному порядку (DESCENDING або DESC) залежно від зазначеного аргументу.

Щоб вивести список співробітників в спадному порядку, можна використовувати такий оператор.

SELECT f_name

from employee_data

ORDER by f_name DESC;

Примітка: Зростаючий порядок (ASC) використовується за замовчуванням.

Приклади завдань

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

2. Виведіть список співробітників в спадному порядку їхнього стажу роботи в компанії.

3. Що робить наступний оператор?

SELECT emp_id, l_name, title, age

from employee_data

ORDER BY title DESC, age ASC;

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

Можливі вирішення завдань

mysql> SELECT f_name, l_name, salary

-> from employee_data

-> ORDER BY salary;

mysql> SELECT f_name, l_name, yos

-> from employee_data

-> ORDER by yos;

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

mysql> SELECT emp_id, l_name, title, age

-> from employee_data ORDER BY

-> title DESC, age ASC;

Примітка: Спочатку сортуються посади в спадному порядку. Потім для кожної посади сортується вік співробітників у зростаючому порядку.

mysql> SELECT l_name, f_name from employee_data

-> where title IN ('програміст',

-> 'розроблювач Web') ORDER BY l_name;