Завдання для самостійної роботи

1. Складіть запит до таблиці STUDENT таким чином, щоб вихідна таблиця містила один стовпець, що містить послідовність розділених символом «;» (крапка з комою) значень всіх стовпців цієї таблиці, і при цьому текстові значення повинні відображатися прописними символами (верхній регістр), тобто бути представленими в наступному виді: 10;КОВАЛІВ;БОРИС;0;БЕРДЯНСЬК;8/12/1986;10.

2. Складіть запит для таблиці STUDENT таким чином, щоб вихідна таблиця містила всього один стовпець у наступному виді: Б. КОВАЛІВ; місце проживання - БЕРДЯНСЬК; народився-8.12.86.

3. Складіть запит для таблиці STUDENT таким чином, щоб вихідна таблиця містила всього один стовпець у наступному виді: б. ковалів; місце проживання - бердянськ; народився:8-дек-1986.

4. Складіть запит для таблиці STUDENT таким чином, щоб вихідна таблиця містила всього один стовпець у наступному виді: Борис Ковалів народився в 1986 році.

5. Вивести прізвища, імена студентів і величину одержуваних ними стипендій, при цьому значення стипендій повинні бути збільшені в 100 разів.

6. Те ж, що й у завданні 4, але тільки для студентів 1, 2 і 4-го курсів і таким чином, щоб прізвища й імена були виведені прописними буквами (верхній регістр).

7. Складіть запит для таблиці UNIVERSITY таким чином, щоб вихідна таблиця містила всього один стовпець у наступному виді: Код-10; ВДУ - м.ВІННИЦЯ: Рейтинг=296.

8. Те ж, що й у завданні 7, але значення рейтингу потрібно округлити до першого знака (наприклад, значення 382 округляється до 400).

3.5. Лабораторна робота №5

Тема: Агрегування й групові функції

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

Порядок виконання роботи

1. Ознайомитися з теоретичними положеннями до лабораторної роботи.

2. Виконати всі наведені приклади в середовище СУБД ACCESS.Для цього необхідно:

• завантажити СУБД ACCESS;

• виконати режим відкриття бази даних «ВНЗ SQL»;

• у вікні БД «ВНЗ SQL» вибрати вкладку «Запити»;

• в діалоговому вікні вказати режим «Додати таблицю» і вибрати необхідні дані;

• на панелі інструментів вибрати пункт меню «Вид» і вказати режим роботи SQL;

• в поточному діалоговому вікні ввести текст запиту в форматі SQL;

• виконати введений запит, для чого натиснути кнопку «Запуск» (!) на панелі інструментів;

• отриманий результат виконання запиту занести в звіт та перевірити на відповідність умовам прикладу;

• зберегти створений запит, надавши йому власне ім’я.

3. Виконати індивідуальне завдання (дивись завдання для самостійної роботи). Номери індивідуальних завдань видаються викладачем.

4. Результати виконання індивідуальних завдань занести у звіт.

5. Захистити звіт.

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

Агрегуючі функції дозволяють одержувати з таблиці зведену (агреговану) інформацію, виконуючи операції над групою рядків таблиці. Для завдання в SELECT-запиті агрегуючих операцій використовуються наступні ключові слова:

• COUNT визначає кількість рядків або значень поля, обраних за допомогою запиту, що не є NULL-значеннями;

• SUM обчислює арифметичну суму всіх обраних значень даного поля;

• AVG обчислює середнє значення для всіх обраних значень даного поля;

• МАХ обчислює найбільше із всіх обраних значень даного поля;

• MIN обчислює найменше із всіх обраних значень даного поля.

В SELECT-запиті агрегуючі функції використовуються аналогічно іменам полів, при цьому останні (імена полів) використовуються як аргументи цих функцій.

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

Приклад 1. Для визначення середнього значення поля MARK (оцінки) по всіх записах таблиці EXAM_MARKS можна використати запит з функцією AVG наступного виду:

SELЕCT AVERAGE (MARK)

FROM EXAM_MARKS;

Приклад 2. Для підрахунку загальної кількості рядків у таблиці варто використовувати функцію COUNT із зірочкою:

SELECT COUNT(*)

FROM EXAM_MARKS;

Аргументи DISTINCT і ALL дозволяють, відповідно, виключати й включати дублікати оброблюваних функцією COUNT значень, при цьому необхідно враховувати, що при використанні опції ALL значення NULL однаково не увійдуть до числа підраховуваних значень:

SELECT COUNT (DISTINCT SUBJ_ID)

FROM SUBJECT;

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

Приклад 3. Потрібно знайти максимальне значення оцінки, отриманої кожним студентом. Запит буде виглядати таким чином:

SELECT STUDENT_ID, MAX(MARK)

FROM EXAM_MARKS

GROUP BY STUDENT_ID;

Вибрані з таблиці EXAM_MARKS записи групуються за значеннями поля STUDENT_ID, зазначеного в конструкції GROUP BY, і для кожної групи знаходиться максимальне значення поля MARK. Конструкція GROUP BY дозволяє застосовувати агрегуючі функції до кожної групи, обумовленої загальним значенням поля (або полів), зазначених у цьому виразі. У наведеному запиті розглядаються групи записів, згруповані по ідентифікаторах студентів.

У конструкції GROUP BY для групування може бути використане більше одного стовпця.

Приклад 4.: Згрупувати дані.

SELECT STUDENT_ID, SUBJ_ID, MAX (MARK)

FROM EXAM_MARKS

GROUP BY STUDENT_ID, SUBJ_ID;

У цьому випадку рядки спочатку групуються за значеннями першого стовпця, а усередині цих груп — у підгрупи за значеннями другого стовпця. Таким чином, GROUP BY не тільки встановлює стовпці, по яких здійснюється групування, але й вказує порядок розбивки стовпців на групи.

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

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

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

Приклад 5. Використання конструкції HAVING:

SELECT SUBJ_NAME, MAX (HOUR)

FROM SUBJECT

GROUP BY SUBJ_NAME

HAVING MAX (HOUR) >= 72;

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