Практична робота 11 на тему Формування запитів і звітів для однотабличної бази даних, Основи інформатики, НУДПСУ

Практична робота 11 на тему Формування запитів і звітів для однотабличної бази даних

Мета: Засвоїти методику формування запитів і звітів для однотабличної бази даних  СУБД Access. 

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

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

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

Всі запити за принципом дії поділяють на три групи:

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

2. запити на дії, що вносять зміни в джерело і результати роботи цих запитів відмінити не можна;

3. запити SQL (запити на об’єднання, запити до серверу, управляючі запити, підлеглі запити).

До запитів першої групи відносять:

а) запит на вибірку, який виконує відбір деяких даних з таблиці чи декількох зв’язаних таблиць за певним критерієм, і який не змінює ці дані. Наприклад, можна вивести дані про прізвища доцентів, стаж роботи яких більше 15 років. Можна також використовувати запит на вибірку, щоб задавати нові поля в таблиці, які будуть створені шляхом обчислень, або щоб згрупувати записи для обрахування сум, середніх значень, перерахунку та інших дій. Наприклад, використовуючи запит на вибірку, можна отримати дані про середній стаж доцентів і професорів. Отримані дані представляються у вигляді таблиці, що дозволяє також створювати форми на основі запитів для представлення інформації в зручному вигляді. На відміну від фільтрів, які працюють лише в межах однієї таблиці і включають в результат всі її поля, запити дозволяють вибирати довільну кількість полів, причому в одному запиті можна розміщати дані з різних зв’язаних таблиць. Запит на вибірку є самим поширеним типом запитів;

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

в) перехресний запит – відображає результати статистичних розрахунків (такі, як суми, кількості записів, середнє значення), виконаних за даними з одного поля таблиці. Ці результати групуються за двома наборами даних, один з яких розташований в лівому стовпці таблиці, а другий — у верхньому рядку. Використовується, якщо потрібно знайти дещо спільне в декількох зв’язаних таблицях наприклад, потрібно з’ясувати середній стаж роботи асистентів, доцентів і професорів на різних кафедрах (на основі таблиці Викладачі). Перехресний запит дозволить вирішити цю задачу, створивши таблицю, в якій заголовками рядків будуть служити посади, заголовками стовпчиків — назви кафедр, а в комірках будуть розраховані середні значення стажу викладачів;

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

До запитів другої групи відносять:

• запит на зміну, який призначений для зміни або переміщення даних; до цього типу запитів відносяться:

° запит на доповнення записів;

° запит на знищення записів;

° запит на поновлення;

a) запит на доповнення — використовується в тому випадку, коли треба об’єднати однорідні дані з двох таблиць однакової структури, тобто добавляє групу записів з одної або декількох таблиць в кінець одної або декількох таблиць. Для цього спочатку створюється запит–вибірка на основі тієї таблиці, з якої треба перенести дані до іншої таблиці, і після зміни типу запита на запит на доповнення у вікні діалогу вказати назву таблиці, до якої треба додати відповідні записи. Наприклад, з’явилося декілька нових викладачів, а також база даних, яка містить відомості про них. Щоб не вводити всі дані вручну, їх можна добавити в таблицю Викладачі;

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

c) запит на поновлення — дозволяє автоматично змінювати значення деяких полів. Після встановлення типу запиту на поновлення в бланку запиту з’являється додатковий рядок – Обновление, в якому треба задати які поля повинні змінюватись і яким чином. Після виконання запиту дані таблиці–джерела будуть автоматично змінені, а повторний запуск запиту може призвести до небажаних розрахунків. Наприклад, потрібно збільшити заробітну плату асистентів на 10%.

Особливістю запитів на дії є те, що створення і виконання їх складається з двох етапів — спочатку створюється запит–вибірка, що містить потрібні поля та записи, що відповідають певній умові, і лише після цього можна змінити тип запиту, перетворити його в запит-дію (при виборі запитів на дії поруч з типом запиту стоїть „!”) та виконують ще раз. Це дозволяє уникнути помилок, оскільки результати роботи запитів-дій відмінити не можна.

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

При створенні запиту в режимі конструктора треба спочатку задати, на основі яких таблиць він буде побудований, після чого з’являється бланк запиту, який містить декілька рядків: Поле, Имя таблицы, які задають назву поля, що буде використовуватись в запиті, та таблиці, в якій воно знаходиться, Сортировка, де можна вказати порядок сортування (за зростанням, спаданням, або сортування відсутнє), Вывод на экран, дозволяє відображати чи приховувати в режимі таблиці поля, що використовуються в запиті (це особливо зручно, коли треба одне й те ж поле повторити декілька разів, щоб задати умову, зв’язану логічним И), Условие отбора, Или – дозволяють задавати умови, за якими необхідно проводити відбір даних з бази. Умови, що вказані в різних рядках бланку запиту, пов’язуються логічним ИЛИ, а умови, що записані в одному рядку – логічним И.

Крім типових логічних операторів, в Access використовують додаткові оператори для створення умов.

Оператор BETWEEN дозволяє задавати діапазон, що є умовою відбору. Наприклад, BETWEEN 10 And 20.

Також використовують оператори IN ("умова 1", "умова 2", "умова 3") та LIKE, який дозволяє здійснити відбір записів, в яких є певні символи, наприклад, LIKE "?[a-k]d[0-9]*" – відбирає тільки ті записи, в яких в даному полі першим може стояти будь-який символ, другим – тільки символ з проміжку від „а” до „к” англійського алфавіту, третім – саме літера d, четвертим – цифра від 0 до 9, а далі будь-яка кількість довільних символів.

Крім логічних, при створенні умов відбору можуть застосовуватись умови дати і часу: Day (дата), Month (місяць), Year (рік), Weekday (день тижня), Hour (номер години), Datepart (номер кварталу чи номер тижня, в залежності від формату написання), Date (активна системна дата). При створенні умов для типу дата значення записують між символами # (наприклад, #14.10.2003#).

При створенні критеріїв відбору можуть використовуватись також величини, які отримують в результаті деяких обчислень. Як оператор в будь-якій формулі можуть використовуватись назви полів, які формуються за таким правилом: у випадку, якщо ми працюємо з полями однієї таблиці, причому тієї, що занесена в бланк запиту, то треба записати ім’я цього поля в квадратних дужках (наприклад, [Вартість]), якщо ж для отримання результату в формулі повинно бути використано поле з іншої таблиці,  то перед іменем поля в квадратних дужках повинно бути записано ім’я таблиці також в квадратних дужках та між ними символ „!”. 

Між операторами можуть бути використані операції додавання, віднімання, множення та ділення, а також піднесення до степеня (^), MOD (округляє обидва оператори до найближчого цілого, виконує ділення першого числа на друге та повертає остачу від ділення), & - оператор приєднання, використовується для текстових величин в одну текстову величину. 

Завдання

1. На основі таблиці Викладачі створити простий запит на вибірку, у якому повинні відображатись такі поля: Прізвище, Ім‘я, По Батькові, Посада.

2. Дані запиту відсортувати за посадами.

3. Зберегти запит.

4. Створити запит на вибірку з параметром, у якому відображається прізвище, ім‘я, по батькові викладачів та дисципліни, які вони викладають. У якості параметра задати прізвище викладача і виконати цей запит для викладача Грішина.

5. На основі таблиці Викладачі створити звіт із групуванням даних по посадах.

6. Скласти звіт.

Для створення простого запиту необхідно: 

Технологія роботи

• у вікні бази даних відкрити вкладку Запросы;

• у вікні, що відкрилося вибрати команду Создать;

• з списку пунктів вікна Новый запрос вибрати Простой запрос і натиснути кнопку ОК;

• у вікні, що з’явилося, у рядку Таблицы / запросы вибрати таблицю Викладачі;

• у вікні Доступные поля перевести виділення на параметр Прізвище;

• натиснути кнопку. Слово Прізвище перейде у вікно Выбранные поля;

• аналогічно у вікно Выбранные поля перевести поля Ім‘я, По батькові, Посада (порядок слідування назв полів має важливе значення — у такому порядку будуть виводитись дані);

• натиснути кнопку Далее;

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

• натиснути кнопку Готово;

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

• встановити курсор у будь-якому рядку поля Посада;

• відсортувати дані по зменшенню (аналогічно пункту 13 з практичної роботи №1).

2. Для збереження запиту необхідно:

• натиснути кнопку     на панелі інструментів або виконати команду меню Файл, Сохранить.

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

• створити запит на вибірку для наступних полів таблиці Викладачі: Прізвище, Ім‘я, По батькові, Дисципліна, яка викладається. Запит створити аналогічно пункту 1.

• задати ім’я запиту Дисципліни, які викладаються;

•  натиснути кнопку Готово, на екрані з’явиться таблиця з результатами запиту.

• перейти в режим конструктора, за допомогою кнопки       або виконавши команду меню Вид, Конструктор;

• у рядку параметра Условиеотбора для поля Прізвище введіть речення (дужки також вводити): [Введіть прізвище викладача];

• виконати запит, натиснувши кнопку       на панелі інструментів або виконавши команду меню Запрос, Запуск;

• у вікні, що з’явилося ввести прізвище Грішин і натиснути кнопку ОК. На екрані з’явиться таблиця з даними про викладача Грішина — його ім‘я, по батькові і дисципліна, яку він викладає;

• Зберегти запит.

4. Для створення звіту необхідно:

• відкрити вкладку Отчеты і виконати команду Создать;

• у вікні, що відкрилось вибрати пункт Мастер отчетов;

• в нижній частині вікна натиснути на значку списку;

• з списку, що з’явився, вибрати таблицю Викладачі;

• натиснути ОК. У вікні, що з’явилося,вибрати поля, які будуть присутні у формі. В даному прикладі присутніми будуть всі поля з таблиці, тому потрібно натиснути кнопку;

• натиснути кнопку Далее;

• у вікні, що з’явилося відображено перелік полів. Потрібно активізувати поле Посада;

• натиснути кнопку. Таким чином задано групування даних по посаді;

• натиснути кнопку Далее;

• параметри вікна, що з’явилося залишити без змін, тому потрібно натиснути кнопку Далее;

• у вікні, що з’явилося можна вибрати стиль оформлення звіту (довільний);

• натиснути кнопку Далее;

• у вікні, що з’явилося потрібно ввести назву звіту Викладачі;

• натиснути кнопку Готово. На екрані з’явиться оформлений звіт. Перегляньте, а потім закрийте звіт.

5. Скласти звіт, у якому відобразити послідовність дій і результати цих дії.

З повагою ІЦ "KURSOVIKS"!