Спецкурс “Основи баз даних”

О. Завадського

операції можна виконувати над згрупованими рядками електронної таблиці?
Опишіть алгоритм, за

яким виконується функція DSUM (рос. БДСУММ) табличного процесора?
Опишіть загальну структуру вибіркового запиту SQL.

певні критерії. Але в багатьох випадках вибрати записи – значить

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

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

що записи учнів, які навчаються в одному класі розташовано поруч. Інакше кажучи, таблицю згруповано за полем клас: спочатку розташовано групу учнів 10А класу, потім — 10Б, 11, і нарешті — 11Б класу.

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

наймолодшого з них.

Наприклад, у табл. На слайді 5 показано результат обчислення для

кожного класу кількості учнів і дати народження наймолодшого з них.
Групування шляхом сортування ми виконували в табличному процесорі Microsoft Excel.
У СКБД Microsoft Access для групування записів сортувати таблицю не потрібно.
Групування і обчислення підсумкових операцій здійснюють за допомогою конструктора запитів.
У вікні конструктора потрібно клацнути правою кнопкою миші поле, за значеннями якого будуть виділятися групи, і вибрати у контекстному меню команду Групповые операции (Групові операції). Після цього в бланк запиту буде додано рядок Групповая операция.

СКБД дещо відмінні. У СКБД результатом запиту з групуванням є

таблиця, де кожна група записів початкової таблиці «згортається» в один запис (наприклад, на рис. замість двох записів «11Б» ми бачимо один). Власне об'єднання кількох записів в один в реляційних базах даних і називається групуванням.

Група — це набір записів з однаковим значенням певного поля. Під групуванням розуміють процес об'єднання групи записів в один; поле, значення якого однакові, називається полем групування.

певного поля підсумків.
Зауважте, що це не поле групування, а

якесь інше поле числового типу, грошового типу або типу дата/час (наприклад, полем групування є клас, полем підсумків — дата народження, а підсумковою операцією — обчислення максимуму (у наймолодшого учня дата народження максимальна)).

▼, розташованою в клітинках рядка Групповая операция справа.

групі, то в конструкторі запитів ми також маємо вибрати певне

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

дату народження наймолодшого учня в кожному класі.

дату народження наймолодшого учня в кожному класі.
Поле підсумків можна використовувати

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

за кількома таблицями, з'єднуючи їх так само, як і в

запитах без групування.
У запитах із групуванням можна вказувати лише поля групування та підсумків, адже тільки вони мають одне значення для кожної групи і тому можуть бути подані у вигляді одного запису. «Звичайного» поля не може бути, оскільки в кожній групі його значень багато і незрозуміло, яке з них відобразити в таблиці результатів.

народилися до 1 січня 1998 року.

навчає.
Вікно конструктора запиту зображено на рис. Прізвища та паспорти вчителів

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

якому зберігається середній бал в табелі за минулий навчальний рік.

Запит є таким: визначити прізвища класних керівників тих класів, середня успішність у яких нижча за 8 балів.
Вікно конструктора запиту зображено на рис. У запиті два поля групування: прізвище вчителя та назва класу. Групувати записи за назвою класу потрібно тому, що саме для всіх учнів кожного класу обчислюється середня успішність, а за прізвищем вчителя — тому, що значення цього поля відображається.

поле має бути полем групування або підсумків. Прізвище вчителя —

це не підсумкове значення, а отже, відповідне поле має бути полем групування, а не підсумків.
В цьому запиті на результат підсумкової операції накладено умову відбору (середня успішність нижча 8 балів). Зрозуміло, що ця умова перевірятиметься для вже згрупованої таблиці, тобто відповідати чи не відповідати їй будуть цілі класи.

запиту зображено на рис.
У цьому запиті умову відбору (учитель

— Сошко Катерина) накладено на поле групування. Можна вважати, що запит виконується так: після з'єднання таблиць відбираються записи, що стосуються Сошко Катерини, а потім обчислюється середнє значення поля успішність в отриманій таблиці

з прикладів 2 і 3 додайте до таблиці Учні поле

успішність.

учнів додано поле успішність).
а) Визначити найвищу успішність учнів 11А класу.
б) Для кожного

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

дату народження наймолодшого учня в кожному класі), у режимі SQL.

Його текст має бути таким.
SELECT Учні.клас, Мах(Учні. [дата народження]) AS
[Мах-дата народження]
FROM Учні
GROUP BY Учні.клас;
Нове ключове слово — GROUP BY, за допомогою якого, власне, і реалізують групування. Назви полів групування перелічують після цього слова, розділяючи комами, якщо таких полів кілька.
Фразу GROUP BY записують після фрази WHERE або після фрази FROM, якщо фраза WHERE відсутня.

цій фразі вказують хоча б одне поле групування. (Якщо жодного

поля групування у фразі SELECT не вказати, у результаті запиту ми не побачимо груп і запит стане безглуздим.)
2. Крім поля групування у фразі SELECT можна зазначати підсумкові функції, перелічені вище. Аргумент такої функції — назва поля підсумків, яку записують у дужках. Наприклад, у нашому запиті функція називається Мах, а її аргумент — Учні, [дата народження].
3. Полю, де відображаються підсумки, можна надати назву, записавши її після ключового слова AS. Зазначимо, що за допомогою цього слова можна перейменовувати будь-які поля в таблиці результатів запиту.
4. Крім полів групування та підсумкових функцій у фразі SELECT запиту з групуванням не можна вказувати жодних інших полів. Щоб зрозуміти, чому так, припустимо, що в розглянутому щойно запиті ми відображуємо ще прізвище учня:
SELECT Учні.клас, Учні.прізвище
...
GROUP BY Учні.клас
Оскільки для кожної групи (у даному випадку — класу) в таблиці результатів відводиться один рядок , а в класі може бути кілька учнів, то їх прізвища просто не вмістяться в одній клітинці (нагадаємо, що кожна клітинка реляційної БД містить тільки одне значення). Саме тому така конструкція неприпустима.

кожному класі.
Для обчислення кількості використайте функцію Count.
Її аргументом

може бути * (символ, що позначає будь-яке поле), тому що, як зазначалося вище, неважливо, за яким полем рахувати кількість записів.

класи, всі учні яких народилися до 1 січня 1998 року.
SELECT

Учні.клас FROM Учні
GROUP BY Учні.клас
HAVING Мах(Учні.[дата народження])<#1/1/1998#;
У ньому є нове ключове слово HAVING.
Після цього слова записують умову відбору груп записів, тобто фраза НАVING відграє для груп записів ту саму роль, що і фраза WHERE для окремих записів. У цілому наведений запит СКБД виконує так:
Таблиця Учні групується за полем клас: до однієї групи відносяться записи учнів одного класу.
Для кожної групи перевіряється умова Мах (Учні, [дата народження]) <#1/1/1998#, тобто чи народився наймолодший учень до 1 січня 1998 року.
Значення поля клас тих груп, для яких умова виконується, відображується в таблиці результатів.

більше, ніж в одному класі.

групуванням та без нього.
У запитах із групуванням усі записи

в кожній групі об'єднуються в один, а результат запиту без групування — це таблиця, у якій відображено значення всіх полів усіх записів.
Однак у деяких випадках для користувача незручна ні перша, ні друга форма подання інформації. Скажімо, у запиті без групування «Для кожного вчителя визначити прізвища та імена учнів, яких він навчає, доречно було б виконати «м'яке» групування, відобразивши по одному разу прізвище, ім'я та по батькові кожного вчителя, а під ним — список усіх його учнів.

(рос. Отчеты).
Звіти — це екранні форми, що призначені насамперед

для подальшого друку на папері, і тому найчастіше вони подаються у вигляді аркушів паперу формату А4.

таблиць або запитів. Створювати звіти, як і інші об'єкти БД,

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

запиту показано на рис. Зауважте, що прямий зв'язок між таблицею

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

команд Отчёты на вкладці Створення головного вікна бази даних.

форм та запитів. На ньому потрібно вибрати поля, дані з

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

яка — підлеглою, як ми це робили під час побудови

форми за кількома таблицями. Головною таблицею є та, за значеннями полів якої проводитиметься групування, а підлеглою — та, значення полів якої відображатимуться в групах.
У нашому звіті головною буде таблиця вчителів, а підлеглою — таблиця учнів, оскільки для кожного вчителя ми виділятимемо групу учнів

того, якщо серед полів звіту є числові, грошові або поля

дати/часу, то у вікні майстра з'являється кнопка Итоги (Підсумки), за допомогою якої можна задати підсумкову операцію для таких полів.
Наприклад, у нашому запиті можна обчислити середню успішність учнів кожного класу. Залежно від того, який перемикач встановлено у групі Показать, відображатимуться або тільки результати підсумкових операцій (только итоги), або і підсумкові значення, і самі дані (данные и итоги).

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

орієнтацію аркуша.

за допомогою конструктора. Для цього звіт потрібно виділити в головному

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

у яких він викладає, та прізвища й імена учнів, яких

він навчає. Відобразіть також середню успішність учнів кожного класу.

а потім цей запит змінили, на звіті ці зміни ніяк

не відіб'ються.
Щоб дійсно змінити набір даних, які відображаються у звіті, потрібно клацнути правою кнопкою миші індикатор в лівому верхньому куті вікна конструктора звіту, вибрати з контекстного меню команду Свойства і на вкладці Данные вікна властивостей звіту клацнути кнопку, розташовану справа від поля Источник записей. У результаті ви перейдете знайоме вікно конструктора запиту. Але це буде той запит, який справді пов'язаний зі звітом (в області запитів головного вікна бази даних його немає).

імена та спеціальності вчителів, що в ньому викладають, а також

кількість таких учителів.

Під групуванням розуміють процес об'єднання групи записів в один; поле,

значення якого однакові, називається полем групування.
Підсумкова операція полягає в обчисленні в кожній групі записів певної підсумкової характеристики, наприклад, максимуму, суми, середнього. Підсумкову операцію виконують над значеннями якогось поля, що називається полем підсумків.
Поле підсумків повинно мати числовий тип, грошовий тип або тип дати/часу і бути не тим полем, за яким здійснювалося групування.
Операцію визначення кількості записів можна виконувати над значеннями будь-якого поля; її результат від вибору поля не залежить.
Загальний синтаксис SQL-запиту з групуванням такий:
SELECT список полів, значення яких потрібно отримати
FROM список таблиць, з яких вибираються дані
WHERE умова, яку мають задовольняти записи, що вибираються
GROUP BY список полів групування
HAVING умова, яку мають задовольняти групи записів
Звіти — це екранні форми, що подаються у вигляді арку­шів паперу і призначені насамперед для подальшого друку. За допомогою звітів можна візуально групувати записи, не об'єднуючи їх.

яких навчається більше 2 учнів.
б) Визначити прізвища вчителів, які викладають більш,

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