11 клас Урок 26

Тема уроку. Вибіркові запити з фразами GROUP BY та HAVING

Для перевірки домашнього завдання виконайте вправи, результати надішліть зручним для вас способом

 Вправа 1 Встановіть відповідність між словесною умовою відбору та її реалізацією у режимі Конструктора запитів.  Уважно читайте назви полів та обирайте зображення. Присутні ЗАЙВІ зображення.

Вправа 2 Дайте назви основним рядкам в бланку запиту, створеного в режимі Конструктора

Мотивація навчальної діяльності

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

Пояснення нового матеріалу

Групування в мові SQL
Відобразіть запит, створений у завданні 10.1 (визначити дату народження наймолодшого учня в кожному класі), у режимі SQL. Його текст має бути таким.
SELECTУчні.клас,Мах(Учні.[дата народження])Мах-дата народження]
FROMУчні
GROUP BYУчні.клас;
Нове для вас ключове слово — GROUP BY, за допомогою якого, власне, і реалізують групування. Назви полів групування перелічують після цього слова, розділяючи комами, якщо таких полів кілька. Фразу GROUP BY записують після фрази WHERE або після фрази FROM, якщо фраза WHERE відсутня.
Звернімо увагу на особливості фрази SELECT запиту з групуванням.
У цій фразі вказують хоча б одне поле групування. (Якщо жодного поля групування у фразі SELECT не вказати, у результаті запиту ми не побачимо груп і запит стане безглуздим.)
Крім поля групування у фразі SELECT можна зазначати підсумкові функції, перелічені вище. Аргумент такої функції — назва поля підсумків, яку записують у дужках. Наприклад, у нашому запиті функція називається Мах, а її аргумент — Учні, [дата народження] .
Полю, де відображаються підсумки, можна надати назву, записавши її після ключового словаЗазначимо, що за допомогою цього слова можна перейменовувати будь-які поля в таблиці результатів запиту.
Крім полів групування та підсумкових функцій у фразізапиту з групуванням неможна вказувати жодних інших полів. Щоб зрозуміти, чому так, припустимо, що в розглянутому щойно запиті ми відображуємо ще прізвище учня:

Оскільки для кожної групи (у даному випадку — класу) в таблиці результатів відводиться один рядок, а в класі може бути кілька учнів, то їх прізвища просто не вмістяться в одній клітинці (нагадаємо, що кожна клітинка реляційної БД містить тільки одне значення). Саме тому така конструкція неприпустима.

Застосування знань, умінь та навичок
Реалізуйте такі запити.
а) Визначити назви класів, у яких навчається більше 2 учнів.
б) Визначити прізвища вчителів, які викладають більш, ніж в одному класі.
в) Визначити дату народження наймолодшого учня.
г) Визначити кількість учителів, що є класними керівниками.
д) Для кожного вчителя визначити максимальну успішність його учнів (використайте поле успішність).
е ) Визначити прізвище наймолодшого учня.
є*) Визначити спеціальності, за якими працюють тільки вчителі-жінки.
 Закріплення нового матеріалу
Загальний синтаксис SQL-запиту з групуванням такий:
SELECTсписок полів, значення яких потрібно отримати
FROMсписок таблиць, з яких вибираються дані
WHEREумова, яку мають задовольняти записи, що вибираються
GROUP ВУсписок полів групування.
HAVINGумова, яку мають задовольняти групи записів.

Домашнє завдання
1. Опрацювати опорний конспект учня

2. Виконати вправу

 

Опорний конспект

Тема уроку: Вибіркові запити з фразами GROUP BY та HAVING.                             Ціль уроку: ознайомити учнів з застосуванням мови SQL для обчислення підсумкових показників, застосування мови SQL для вибирання груп записів.                                                                                                                                                                   Фраза GROUP BY дозволяє вам визначати підмножину значень в особливому полі в термінах іншого поля, і застосовувати функцію агрегату до підмножини. Це дає вам можливість об'єднувати поля і агрегатні функції в єдиній пропозиції SELECT.                                                                                                                Наприклад, припустимо що ви хочете знайти найбільшу суму придбань отриману кожним продавцем.и можете зробити роздільний запит для кожного з них, вибравши MAX (Amt) з таблиці                                                                             Порядків для кожного значення поля snum. GROUP BY, однак, дозволить Вам помістити їх все в одну команду:                                                                           SELECT snum, MAX (amt) FROM Orders GROUP BY snum;                                  GROUP BY застосовує агрегатні функції незалежно від серій груп які визначаються за допомогою значення поля в цілому. В цьому випадку, кожна група складається з усіх рядків з тим же самим значенням поля snum, і MAX функція застосовується окремо для кожної такої групи. це значення поля, до якого застосовується GROUP BY, має, за визначенням, тільки одне значення на групу виводу, також як це робить агрегатна функція. Результатом є сумісність яка дозволяє агрегатам і полях об'єднуватися таким чином. Ви можете також використовувати GROUP BY з численними полями. Удосконалюючи вищезгаданий приклад далі, припустимо що ви хочете побачити найбільшу суму придбань отриману кожним продавцем кожен день. Щоб зробити це, ви повинні згрупувати таблицю Порядків по датах продавців, і застосувати функцію MAX до кожної такої групи, подібно до цього: SELECT snum, odate, MAX ((amt)) FROM Orders GROUP BY snum, odate; Припустимо, що в попередньому прикладі, ви хотіли б побачити тільки максимальну суму придбань значення якої вище $ 3000.00. Ви не зможете використовувати агрегатную функцію в реченні WHERE (якщо ви не використовуєте підзапит, описаний пізніше), тому що предикати оце Нива в термінах одиночного рядка, а агрегатні функції оцінюються в термінах груп рядків. Це означає що ви не зможете зробити що-небудь подібно наступного: SELECT snum, odate, MAX (amt) FROM Oreders WHERE MAX ((amt))> 3000.00 GROUP BY snum, odate; Це буде відхиленням від суворої інтерпретації ANSI. Щоб побачити максимальну вартість придбань понад $ 3000.00, ви можете використати фразу HAVING. Фраза HAVING визначає критерії використання щоб видаляти певні групи з виводу, точно також як пропозиція WHERE робить це для індивідуальних рядків. Правильною командою буде наступною: SELECT snum, odate, MAX ((amt)) FROM Orders GROUP BY snum, odate HAVING MAX ((amt))> 3000.00; Загальний синтаксис SQL-запиту з групуванням такий: SELECT список полів, значення яких потрібно отримати FROM список таблиць, з яких вибираються дані WHERE умова, яку мають задовольняти записи, що вибираються GROUP ВУ список полів групування. HAVING умова, яку мають задовольняти групи записів.