Створення та обробка баз даних на ПЕОМ (стр. 22 )

SELECT ПІБ, 2003-РікНародж as Вік

FROM СТУДЕНТИ

Створюваному полю, що обчислюється, привласнюється ім'я Вік.

8.1.2. Застосування групових (агрегатних) функцій

Дуже часто потрібно зробити обчислення деяких підсумкових даних по всьому наборі даних чи по яких-небудь групах даних. У цьому випадку використовуються групові (агрегатні) функції: Max, Min, Count, Avg, Sum (див. розділ 8).

Нехай, наприклад, є таблиця СТУДЕНТИ, що має наступну структуру:

СТУДЕНТИ (КодСтуд, КодГр, ПІБ, …).

Підрахувати кількість усіх студентів, про яких зберігаються дані у таблиці СТУДЕНТИ, можна за допомогою наступного оператора:

SELECT Count(ПІБ) as Кільк_Студ

FROM СТУДЕНТИ

Очевидно, що даний запит повертає єдине значення – кількість студентів (поле Кільк_Студ), яка у даному випадку збігається з числом записів у таблиці.

Розглянемо більш цікавий приклад – обчислимо середній бал по кожному зі студентів. Нехай, наприклад, інформація про оцінки міститься в таблиці УСПІШНІСТЬ, що має структуру:

УСПІШНІСТЬ (КодСтуд, КодПр, Дата, Оцінка).

Текст запиту, що обчислює середній бал для кожного зі студентів, може мати наступний вигляд:

SELECT С.ПІБ, Avg(У.Оцінка) as Серед_Бал

FROM СТУДЕНТИ as С LEFT JOIN УСПІШНІСТЬ as У

ON С.КодСтуд = У.КодСтуд

GROUP BY С.ПІБ

Оскільки тут добір даних утворюється з двох таблиць (поле ПІБ береться з таблиці СТУДЕНТИ, а поле Оцінка, що бере участь в обчисленнях, - з таблиці УСПІШНІСТЬ), у запиті створюється з'єднання таблиць. Завдяки тому, що створюється ліве зовнішнє з'єднання, у результуючий набір даних будуть включені і прізвища студентів, для яких немає оцінок у таблиці УСПІШНІСТЬ.

В інструкцію GROUP BY повинні включатися всі поля, імена яких зазначені в інструкції SELECT і не беруть участь в агрегатних функціях. У нашому прикладі це поле С.ПІБ.

В інструкції SELECT для поля, що обчислюється, одержуваного як результат застосування групової (агрегатної) функції Avg до поля Оцінка, створюється псевдонім Серед_Бал. Цей псевдонім на замовчення буде відображатися в заголовку стовпця середнього бала.

Якщо в запиті, у якому застосовуються групові функції, ще потрібно зробити відбір записів по деякій умові, застосовуваній до підсумкових (групованих) даних, тоді для завдання умови потрібно використовувати інструкцію HAVING (а не WHERE).

Нехай у розглянутому прикладі потрібно в результуючий НД помістити тільки записи, що відносяться до студентів із середнім балом 3,6 і більше. Тоді приведений вище текст запиту потрібно доповнити інструкцією:

HAVING Avg(У.Оцінка) >= 3,6

8.2. Вкладені запити

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

Нехай, наприклад, вирішується задача визначення середнього бала для студента Іванова. Але при цьому в таблиці УСПІШНІСТЬ, у якій зберігаються дані про оцінки, немає поля, що містить прізвища, але є тільки поле КодСтуд, яке містить внутрішні коди студентів, що користувачу невідомі. З використанням вкладеного запиту вирішити дану задачу можна в такий спосіб:

SELECT Avg(Оцінка)

FROM УСПІШНІСТЬ

GROUP BY КодСтуд

HAVING КодСтуд =

(SELECT КодСтуд

Вкладений FROM СТУДЕНТИ

запит WHERE Фам = “Іванов”

Дану задачу можна було б вирішити і без вкладеного запиту, створивши в запиті з'єднання таблиць СТУДЕНТИ й УСПІШНІСТЬ.

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

Оператор SELECT, що вирішує цю задачу, виглядає в такий спосіб:

SELECT Фам, Avg(Оцінка)

FROM СТУДЕНТИ С INNER JOIN УСПІШНІСТЬ У

ON С.КодСтуд = У.КодСтуд

GROUP BY Фам

HAVING Avg(Оцінка) >

(SELECT Avg(Оцінка)

FROM УСПІШНІСТЬ

Вкладений GROUP BY КодСтуд

запит HAVING КодСтуд =

(SELECT КодСтуд

Вклад. FROM СТУДЕНТИ

запит WHERE Фам = “Іванов” ))

Тут застосований вкладений запит з двома рівнями вкладеності. Взагалі кількість рівнів вкладених запитів не обмежується.

8.3. Створення запитів у Access

У Access маються могутні і зручні інструментальні засоби створення різних запитів. Для створення простих так званих “запитів за зразком” можна використовувати Майстер запитів, що послідовно запитує у користувача потрібну інформацію (найменування таблиць, які будуть використовуватися у запиті; імена полів, які потрібно включити в результуючий НД і інш.) і потім автоматично генерує SQL-запит. Створений за допомогою Майстра запит відразу можна виконати і зберегти для подальшої його доробки й удосконалення за допомогою Конструктора запитів.

Для створення більш складних запитів необхідно використовувати Конструктор запитів. Розглянемо більш добірно технологію створення запитів в Access за допомогою Конструктора запитів. Для створення запиту в режимі Конструктора потрібно виконати наступне:

1)  У вікні вашої БД відкрити вкладку “Запити” і натиснути кнопку Створити. Відкриється діалогове вікно “Новий запит”, у якому потрібно вибрати спосіб створення запиту. Виберіть спосіб “Конструктор” і натисніть кнопку ОК.

2)  Відкриється вікно Конструктора запитів і відразу ж на його фоні відкриється діалогове вікно “Додавання таблиці”. Виберіть кнопкою миші потрібні для запиту таблиці і натисніть кнопку Додати. Після цього закрийте вікно “Додавання таблиці” кнопкою Закрити.

3)  Вікно Конструктора запитів має вид, показаний на Рис. 8.1. Вікно розділене на 2 панелі: верхню і нижню. Верхня панель називається схемою даних запиту, на якій розміщені списки полів таблиць, що були додані в запит. У списках полів верхній рядок відзначений зірочкою *, що позначає всі поля. Access автоматично встановлює зв'язок між полями з однаковими іменами і типом даних.

Рис. 8.1. Вид вікна Конструктора запитів

Нижня панель називається бланком запиту, що заповнюється в такий спосіб:

§  у рядок “Поле” помістити імена полів, що повинні бути включені в запит. Зробити це можна, наприклад, способом перетаскування потрібного поля зі Схеми даних у рядок “Поле”;

§  у рядку “Сортування” вибрати порядок сортування для поля, по якому потрібно сортування;

§  у рядку “Вивід на екран” відзначити поля, що повинні бути виведені в результуючу таблицю;

§  у рядках “Умови відбору” і “чи” задати умови відбору записів.

Умови відбору даних (записів) задаються виразами, що можуть складатися з операндів і операторів (операцій) порівняння. У якості операндів можуть використовуватися:

-  літерали: “Іванов”, “СОБД”, “А*”, #1.09.99# ;

-  константи: True, False, Null ;

-  ідентифікатори – посилання на поля: [Ім'я таблиці] .[Ім'я поля].

У виразах можуть застосовуватися наступні операції і предікати:

= , < , > , <> , And, Or, Not, Between, In, Like.

4)  Зачинити вікно Конструктора запитів. При цьому відкривається діалогове вікно, у якому потрібно увести ім'я запиту, що був створюваний. Вид діалогового вікна зображено на Рис. 8.2. Після введення потрібного імені натисніть кнопку ОК.

Рис. 8.2. Вікно для введення імені запита

Створений запит можна запустити на виконання подвійним щигликом миші на значку запиту у вікні БД чи натисканням кнопки Відкрити.

У режимі Конструктора запит можна запустити на виконання командою меню Запит½Запуск чи натиснувши кнопку (“Запуск”) на панелі інструментів.

9. Створення форм у Access

9.1. Види та призначення форм

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

Основні елементи керування у формі: поле, напис, група, перемикач, позначка, вимикач, поле зі списком, список, кнопка, рисунок, приєднана рамка об'єкта, вільна рамка об'єкта, набір вкладок, підпорядкована форма, розрив сторінки, лінія, прямокутник, додаткові елементи АсtivеХ.

Графічний елемент керування у форми може забезпечити:

-  перегляд записів і їх оброблення (додання, вилучення, друкування,

відновлення);

-  роботу з формою та зі звітами (відкривання, перегляд, закривання,

друкування );

-  роботу з додатками (запускання додатка, якій був виконаний у Word, Ехсеl );

-  відкривати OLE - об’єкти ( рисунки, графіки, фото).

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