Попередня сторінка          Зміст           Наступна сторінка          Електронні посібники ВНТУ

ЛАБОРАТОРНА РОБОТА № 2
СТВОРЕННЯ ЗАПИТІВ

Мета роботи: навчитися створювати запити.

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

2.1.1 Необхідність запитів

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

Наприклад, припустимо, що на великому підприємстві є БД “Кадри”, що містить найдокладніші відомості про кожного співробітника. Крім формальної інформації база може містити і конфіденційну. Інформація зберігається в базових таблицях. Працювати з БД “Кадри” можуть різні підрозділи підприємства, і усім їм потрібні різні дані. Не все те, що дозволено знати службі безпеки підприємства, повинно бути доступно головному лікарю, і навпаки. Тому доступ користувачів до базових таблиць закривають. Для доступу до даних є інший, гнучкий і зручний засіб - запити. Для однієї і тієї ж таблиці можна створити багато різних запитів, кожний із яких зможе добувати з таблиці лише частину інформації, яка необхідна у даний момент.

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

Важливою властивістю запитів є те, що при створенні результуючої таблиці можна не тільки вибирати інформацію з бази, але й обробляти її. При роботі запиту дані можуть упорядковуватися (сортуватися), фільтруватися (відсіюватися), об'єднуватися, розділятися, змінюватися, і при цьому ніяких змін у базових таблицях може не відбуватися. Результати обробки позначаються тільки на змісті результуючої таблиці, вона має тимчасовий характер, і іноді її навіть називають моментальним знімком.

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

2.1.2 Вибір базових таблиць для запиту

Створення запиту до бази починається з відкриття вкладки “Створити” і натиснення лівої клавіші миші на кнопці “ Створити ”.

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

Вибір таблиць виконують у діалоговому вікні “Додавання таблиці”. У ньому відображаються всі таблиці, наявні в базі. Обрані таблиці заносять у верхню половину бланка “запиту за зразком” натисненням лівої клавіші миші на кнопці “Додати”.

У вікні “Додавання таблиці” зверніть увагу на наявність вкладок: “Таблиці”, “Запити”, “Запити і таблиці” (запит не обов'язково створювати тільки на основі таблиць).

2.1.3 Бланк запиту за зразком

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

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

Рядок “Ім’я таблиці” заповнюється автоматично при перетягуванні поля.

Якщо натиснути на рядок “Сортування”, з'явиться кнопка списку, що розкривається, який містить види сортування. Якщо призначити сортування по якомусь полю, дані в результуючій таблиці будуть відсортовані по цьому полю.

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

Найцікавіший рядок у бланку запиту за зразком називається “Умови відбору”, де записують ті критерії, по якому вибирають запис для включення в результуючу таблицю. По кожному полю можна створити свою умову відбору.

Запуск запиту виконують натисненням лівої клавіші миші на вкладці “Конструктор”, у групі “Результати” клацніть елемент “Виконати”, утвориться результуюча таблиця.

2.1.4 Обчислення в запитах

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

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

У вузький стовпець непросто записати довгу формулу, але якщо натиснути комбінацію клавіш SHIFT+F2, то відкривається допоміжне діалогове вікно, що називається “Область введення”, в якому можна ввести формулу, а потім натисненням лівої клавіші миші на кнопці ОК перенести її в бланк запиту за зразком.

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

2.1.5 Запити на вибірку

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

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

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

2.1.6 Запити з параметром

Запити з параметрами – це запит, який при виконанні відображає у власному діалоговому вікні запрошення ввести дані, наприклад умова для повернення записів або значення, яке потрібно вставити в поле.

Можна розробити запит, що виводить запрошення на введення декількох одиниць даних, наприклад двох дат, потім Microsoft Access може повернути всі записи, що припадають на інтервал часу між цими датами. Запити з параметрами також зручно використовувати в якості основи для форм, звітів і сторінок доступу до даних.

Наприклад, припустимо, що в БД є таблиця, у якій містяться всі результати чемпіонатів світу з футболу. Наша задача: створити запит, за допомогою якого користувач може визначити, у якому році та або інша команда займала перше місце, причому вибір цієї команди - його особиста справа.

Для цієї мети служить спеціальна команда мови SQL, що виглядає так: LIКЕ [...]. У квадратних дужках можна записати будь-який текст, звернений до користувача.

Команду LIКЕ треба помістити в рядку “Условие отбора” і в те поле, по якому робиться вибір. У нашому випадку це стовпець збірних, що займали перші місця в чемпіонатах світу з футболу.

Після запуску запиту відкривається діалогове вікно, у якому користувачу пропонується ввести параметр. Якщо в якості параметра ввести слово “Бразилія”, то видається результуюча таблиця, що містить запису по тим чемпіонатам, коли збірна Бразилії ставала чемпіоном. Якщо ввести слово “Італія”, то результуюча таблиця буде іншою.

2.1.7 Запити на зміну

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

Запити на зміну – запит, який за одну операцію змінює або переміщує кілька записів. Існують запити:

На видалення запису – видаляє групу записів з однієї або декількох таблиць (можна видаляти тільки весь запис, а не окремі поля всередині нього).

На оновлення запису – вносить загальні зміни в групу записів однієї або декількох таблиць (дозволяє змінювати дані в існуючих таблицях).

На додавання записів – додає групу записів з однієї або декількох таблиць в кінець однієї або декількох таблиць.

На створення таблиці – створює нову таблицю на основі всіх або частини даних з однієї або декількох таблиць. Запит на створення таблиці корисний при створенні таблиці для експорту в інші бази даних Microsoft Access або при створенні архівної таблиці, яка містить старі записи.

На об'єднання – дозволяє об'єднати дані з двох таблиць з аналогічними структурами.

Логіка використання запитів на зміну така:

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

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

Натиснення лівої клавіші миші на кнопці “Виконати” дозволяє переконатися, що запит працює як треба і створює результуючу таблицю, більш повну ніж базова. Тепер можна дати команду на створення нової базової таблиці, рівній результуючій: вкладка “Створити” у пункті “Запит”, що доступно тільки в режимі “Конструктора”.

У тому ж меню присутні команда для створення запитів на відновлення даних, на додавання записів і на вилучення записів.

2.1.8 Перехресні запити

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

Способи створення перехресного запиту

• Використання майстра перехресних запитів, де більшість роботи він виконує самостійно, але деякі параметри в майстрі відсутні.
• Робота в режимі конструктора, який надає повніший контроль над структурою запиту та підтримує функції, які недоступні в майстрі.
• Створення запиту в режимі SQL. Проте в режимі SQL не можна вказувати типи даних параметрів. Якщо в перехресному запиті потрібно використовувати параметр, слід вказати тип даних параметра, змінивши запит у режимі конструктора.

Створення перехресних запитів за допомогою майстра

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

1. На вкладці “Створити” у групі “Макроси та код” натисніть кнопку “Майстер запитів”.
2. У діалоговому вікні “Новий запит” виберіть пункт “Майстер перехресних запитів” і натисніть кнопку ОК.
3. На першій сторінці майстра виберіть таблицю або запит, які потрібно використовувати для створення перехресного запиту.
4. На наступній сторінці виберіть поле, що містить значення, які потрібно використовувати як заголовки рядків.
5. На наступній сторінці виберіть поле, що містить значення, які потрібно використовувати як заголовки стовпців.
6. Якщо для заголовків стовпців вибрати поле «Дата/час», на наступній сторінці майстра буде запропоновано вказати інтервал для групування дат. Можна вказати Рік, Квартал, Місяць, Дата або Дата/час. Якщо для заголовків стовпців не вибрано поле «Дата/час», майстер пропустить цю сторінку.
7. На наступній сторінці виберіть поле та функцію, які будуть використовуватися для обчислення зведених значень. Вибраний тип даних поля визначає доступні функції.

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

Наприклад, поле з текстовим типом даних може містити як текст, так і числа, а поле з числовим типом даних — лише числові дані.

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

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

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

Створення перехресного запиту в режимі конструктора

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

1. На вкладці “Створити” у групі “Макроси та код” натисніть кнопку “Конструктор запиту”.
2. У діалоговому вікні “Відображення таблиці” двічі клацніть кожну таблицю або запит, які потрібно використовувати як джерело записів.

Якщо використовується кілька джерел записів, переконайтеся, що таблиці або запити об'єднані за спільними для них полями. Об'єднання - зв'язок  між полем однієї таблиці або запиту й полем іншої таблиці або запиту, які мають однаковий тип даних. Невідповідні записи можуть бути як включені, так і виключені, залежно від типу об'єднання.

3. Закрийте діалогове вікно Відображення таблиці.
4. На вкладці “Конструктор” у групі “Тип запиту” натисніть кнопку “Перехресний”.
5. У вікні конструктора запитів двічі клацніть кожне поле, яке потрібно використовувати як джерело заголовків рядків. Для заголовків рядків можна вибрати до трьох полів.
6. У бланку запиту в рядку “Перехресний” для кожного поля заголовка рядка виберіть пункт “Заголовок рядка”.

Щоб обмежити результати для цього поля, можна ввести вираз у рядку “Критерії”. Крім того, можна визначити порядок сортування для поля в рядку “Сортування”.

7. У вікні конструктора запитів двічі клацніть поле, яке потрібно використовувати як джерело заголовків стовпців. Для заголовків стовпців можна вибрати лише одне поле.
8. У бланку запиту в рядку “Перехресний” для поля заголовка стовпця виберіть пункт “Заголовок стовпця”.

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

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

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

1. У вікні конструктора запитів двічі клацніть поле, яке потрібно використовувати для обчислення зведених значень. Для зведених значень можна вибрати лише одне поле.
2. У бланку запиту в рядку підсумків для поля зведених значень виберіть агрегатну функцію для обчислення значень.
3. У рядку “Перехресний” для поля зведених значень виберіть “Значення”.
4. Вказувати критерії для поля зведених значень або виконувати сортування за цим полем не можна.
5. На вкладці “Конструктор” у групі “Результати” натисніть кнопку “Запуск”.

Створення перехресного запиту в режимі SQL

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

1. На вкладці “Конструктор” у групі “Макроси та код” натисніть кнопку “Конструктор запиту”.
2. Закрийте діалогове вікно “Відображення таблиці”.
3. На вкладці “Конструктор” у групі “Результати” натисніть кнопку SQL.
4. На вкладці “Об’єкт SQL” введіть або вставте такі SQL-оператори:

TRANSFORM

SELECT

FROM

GROUP BY

PIVOT

У першому рядку, після оператора TRANSFORM, введіть вираз, який буде використано для обчислення зведених значень, наприклад Сума([Обсяг]).

У другому рядку, після оператора SELECT, введіть список полів або виразів полів, які потрібно використати для заголовків стовпців. Розділяйте елементи списку за допомогою ком, наприклад [Бюджет].[Код_відділу], [Витрати].[Тип].

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

У четвертому рядку, після оператора GROUP BY, введіть той самий список полів, який було використано в реченні SELECT у кроці 6.

У п'ятому рядку, після оператора PIVOT, введіть ім’я поля або вираз, який потрібно використовувати для заголовків стовпців, наприклад PIVOT [Бюджет].[Рік].

2.2 Завдання до лабораторної роботи

В лабораторній роботі необхідно створити п’ять таблиць запитів згідно з варіантом (табл. Б.2 Додаток Б). Матеріалом для створення запитів буде слугувати БД про робітників підприємства, що була створена в лабораторній роботі №1 (табл. А.2 Додаток А).

Перша таблиця запиту – запит на вибірку. Умова відбору для нього наведена в табл. Б.2 (Додаток Б) в стовпці “Запит типу 1”.  

Необхідно зробити вибірку по полю (будь-яке числове) яка б задовольняла умову > або < або = …

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

В третьому запиті необхідно виконати обчислення. В графі “Запит типу 3” вказані номера пунктів з «відомостей до завдань для лабораторної роботи №2» (завдання наведені в наступному підпункті). Використовуючи ці номери необхідно створити запит, який буде виконувати обчислення згідно варіанту завдання, а потім виводити їх в таблицю.

В наступному завданні треба створити підсумковий запит (цей запит необхідно виконати для табл. А.2 Додаток А).

В останньому завданні необхідно створити запит на зміну. Для виконання цих завдань потрібно скористатись теоретичними відомостями до даної лабораторної роботи.

2.2.1 Відомості до завдань для лабораторної роботи № 2

1.Нарахування премії (табл. А.2 Додаток А)

Нарахування премії співробітникам підприємства виконується згідно коефіцієнта. Для визначення розміру премії необхідно виконати наступні операції:

а) визначити заробітну плату співробітника за місяць: якщо вид оплати в графі “Оплата” рівний 1, то зарплата визначається ставкою, що вказана в графі “Ставка”; якщо вид оплати рівний 2, то тарифна ставка, що вказана в графі “Ставка”, множиться на кількість годин (“Стаж”) і кількість робочих днів (“Відроблені”);

б) отриману зарплату помножити на коефіцієнт премії (“Премія”).

2.Нарахування за лікарняним листом (табл. А.2 Додаток А)

Для визначення розміру виплат за лікарняним листом необхідно виконати такі операції:

а) визначити денний заробіток працівника; якщо вид оплати (“Оплата”) рівний 1, то денний заробіток визначається діленням ставки, що вказана в графі “Ставка” на кількість робочих днів (22); якщо вид оплати рівний 2, то денний заробіток визначається множенням погодинної тарифної ставки (“Ставка”) на тривалість робочого дня (8 годин);

б) денний заробіток помножити на кількість лікарняних днів (графа “Лікарняні”);

в) отриманий результат помножити на коефіцієнт, що визначається стажем роботи (графа “Стаж”): якщо значення цієї графи менше 3, то коефіцієнт дорівнює 0.5; якщо значення графи більше або дорівнює 3, але менше 8, то коефіцієнт дорівнює 0.7; якщо значення графи більше або дорівнює 8, то коефіцієнт дорівнює 1.

3.Нарахування відпускних

Для нарахування відпускних потрібно виконати наступні операції:

а) визначити денний заробіток працівника (п. 2.а);

б) денний заробіток помножити на тривалість відпустки (24 дня).

4.Нарахування 13-ї зарплати (табл. А.2 Додаток А)

Для нарахування 13-ї зарплати необхідно виконати наступну послідовність дій:

а) визначити місячний заробіток працівника (п.1, а);

б) визначити розмір 13-ї зарплати шляхом множення місячного заробітку на коефіцієнт, що залежить від стажу роботи (графа “Стаж”): якщо значення графи “Стаж” менше 3, то коефіцієнт дорівнює 0.7; якщо значення більше або дорівнює 3, але менше 5, то коефіцієнт рівний 0.9; якщо значення графи більше або дорівнює 5, але менше 10, то коефіцієнт дорівнює 1.1; якщо стаж більше або дорівнює 10, то коефіцієнт дорівнює 1.5.

5.Нарахування вихідної допомоги (табл. А.2 Додаток А)

Для нарахування вихідної допомоги необхідно виконати такі операції:

а) визначити місячний заробіток працівника (п. 1, а)

б) визначити вихідну допомогу за формулою:

Вихідна допомога=місячний заробіток*”Допомога”, якщо Місячна зарплата < “Середня оплата”; Вихідна допомога = “Середня оплата” * ”Допомога”, якщо Місячна зарплата > “Середня оплата”, де “Допомога” – коефіцієнт вихідної допомоги, “Середня оплата” – середня зарплата.

6.Нарахування авансу (табл. А.2 Додаток А)

Для нарахування авансу необхідно виконати такі дії:

а) визначити денний заробіток працівника (п. 2, а)

б) визначити аванс за формулою:

Аванс=”Денний заробіток” * (“Відроблені”+15)/2, де “Відроблені” – кількість днів, відроблених на 15-е число поточного місяця.

2.3 Контрольні запитання:

1. Що таке запит?
2. Назвіть призначення запитів. В чому відмінність запитів від таблиць БД.
3. Види запитів.
4. Назвіть види запитів на вибірку та їх особливості.
5. Коли використовуються і як створюються запити з параметрами.
6. Яке призначення та особливості створення перехресного запиту?
7. Способи створення запитів.
8. На основі яких об'єктів бази даних може формуватися запит?
9. Опишіть процес створення запитів за допомогою «Майстра запитів» програми MS Access.
10. Назвіть основні етапи при створенні запитів за зразком.
11. Що таке умова відбору? Як задається проста умова для відбору даних?
12. Як створити обчислюване поле в запиті? Порядок роботи з Будівником виразів MS Access.