Завдання

Для таблиці Оцінки, створеної раніше, чи таблиці заданої викладачем, сконструювати різні фільтри i запити згідно з планом роботи. Після виконання кожного фільтра чи запиту занотовувати у звіт номери записів, які задовольняють критерій пошуку.

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

Системи управління базами даних (СУБД) – це програмні засоби, за

допомогою яких можна створювати бази даних, наповняти їх і працювати з ними.

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

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

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

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

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

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

Для створення запитів до баз даних існує спеціальна мова запитів. Вона називається SQL (Structured Query Language – структурована мова запитів). Замість неї в Access 9x є простий засіб, що називається бланком запиту за зразком. З його допомогою можна сформувати запит простими прийомами, перетаскуючи елементи запиту між вікнами.

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

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

У таблицях недоцільно зберігати дані, які можна отримати розрахунковим шляхом. Access не дозволяє виконувати обчислення над даними, що зберігаються в полях таблиць. З цією умовою використовують запити з обчисленнями. Поле, уміст якого є результатом розрахунку по вмісту інших полів, називається полем, що обчислюється. Для створення запиту, що робить обчислення, служить той же самий бланк запиту за зразком. Різниця тільки в тім, що в одному зі стовпців замість імені поля записують формулу. У формулу входять ув'язнені в квадратні дужки назви полів, що беруть участь у розрахунку, а також знаки математичних операцій, наприклад так: Назва нового поля:[поле1]/[поле2]. У вузький стовпець непросто записати довгу формулу, але якщо натиснути комбінацію клавіш SHIFT+F2, то відкривається допоміжне діалогове вікно, що називається Область уведення. У ньому можна ввести як завгодно довгу формулу, а потім клацненням на кнопці ОК перенести її в бланк запиту за зразком.

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

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

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

Для створення нової таблиці, що містить вибрані записи, внесення змін у таблиці (доповнення, оновлення, вилучення чи архівування записів, створення обчислювального поля тощо) використовують запити на перетворення (на внесення змін, на виконання дії з таблицею, action quiries). Тип запиту можна змінювати командами Запит → Тип запиту. Загальна схема роботи з запитами на внесення змін така:

1) виконують звичайний запит на вибірку записів;

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

3) запускають запит на виконання — буде створена нова таблиця з відібраними записами.

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

Щоб створити запит вручну, треба виконати таку послідовність команд, стартуючи з головного вікна бази даних:

Вікно БД* Запити * Створити * Конструктор * OK→ Додають таблицю, наприклад, Оцінки * Закривають вікно Додати таблицю.

Для відшукання потрібних даних використовують фільтри i запити. Фільтри застосовують у випадку нескладних умов пошуку і виведення даних. Доступ до команд роботи з фільтрами можна отримати одним із трьох способів:

1) виконати команду основного меню Запит → Фільтр;

2) виконати команду з контекстного меню;

3) (найкращий) скористатися кнопками команд на панелі інструментів:

· Фільтр за вибраним;

· Змінити фільтр;

· Розширений фільтр;

· Застосувати/Вилучити фільтр.

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

Для шукання даних користувач будує умови: прості i складені.

Прості умови — це числа, тексти, вирази, математичні співвідношення, наприклад: 5; "Артист"; Артист; >3; =4; <>3; < date() — yci дати до вчора включно; Like[A-Г] — прізвища, які починаються на А, Б, В, Г; Like "Ар*" — слова, які починаються на Ар; 69???? — шестизначні номери телефонів, які починаються на 69, тощо. Умови записують відповідно до правил мови SQL (Structured Query Language).

Складені умови — це умови, побудовані з простих за допомогою логічних операцій not (не), and (i), or (або). Наприклад, not 2; between 3 and 4; between date()-30 and date() — дати за минулі 30 днів від сьогоднішньої; between 21/4/99 and 21/5/99 — між двома датами.

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

Отримують вікно конструктора запитів.

Рис. 6. Вікно конструктора запиту.

Конструювання запиту складається з декількох етапів:

1. У вікні таблиці Оцінки вибирають yci чи потрібні поля і перетягують їx у рядок Поле таблиці конструктора (рис. 6). Інший спосіб: рядок Поле заповнюють, клацнувши у ньому i вибравши назви полів з запропонованого списку.

2. Задають, якщо потрібно, режим упорядкування (методом вибору режиму зi списку) i режим відображення полів на екрані.

3. Вводять умови в рядок умов.

4. Щоб запустити запит на виконання, виконують команди Запит → Запуск чи натискають на кнопку запуску, на якій нарисовано знак оклику (!).

5. Для редагування запиту треба повернутися в режим конструктора.

6. У разі потреби змінюють тип запиту.

7. Коли запит готовий, закривають конструктор зі збереженням запиту з деякою назвою у файлі БД.

Хід роботи

1. Запускаю програму Access i відкриваю БД, що містить таблицю Оцінки.

2. Розгляньте таблицю.

3. Виводжу на екран (відфільтровую) записи про студентів, які мають з математики оцінку "5".

Вибираю потрібне поле (клацаю мишею у будь-якому полі з оцінкою "5" з математики будь-якого студента) i натискаю на кнопку Фільтр за вибраним. Переписую номери записів у звіт.

Оцінки
NUMBER	Fname	Oname	Mathematics	Information	Phisikal	Literatel	Leaunguage
	Білик	Зоряна
	Когут	Тетяна
	Іваненко	Андрій
	Романко	Оксана
	Берчинська	Галина
	Романко	Оксана

Відновлюю вигляд таблиці кнопкою Вилучити фільтр.

4. Відфільтровую записи про студентів, що не мають "2" з математики.

Вибираю поле з оцінкою "2" з математики i виконую команду Заперечити вибране.

Оцінки
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage
	Лісовська	Анна
	Долинський	Андрій
	Писар	Галина
	Кулик	Оксана
	Романко	Оксана
	Берчинська	Галина
	Романко	Оксана
	Іваненко	Андрій
	Когут	Тетяна
	Білик	Зоряна

Вилучаю фільтр.

5. Відфільтровую записи про студентів, які мають з математики i фізики "4".

Виконую команду Змінити фільтр. Задаю оцінки (4 i 4) в полях умов для математики i фізики. Застосовую фільтр. Переписую номери записів у звіт.

Оцінки
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage
	Кулик	Оксана

Вилучаю фільтр.

6. Застосовую розширений фільтр, щоб вивести записи про студентів, які вчаться на "4" або "5" з математики або інформатики.

Записи * Фільтр * Розширений фільтр * Вводжу умову в таблицю → Застосувати фільтр → Переписую номери шуканих записів

 

Оцінки
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage
	Долинський	Андрій
	Кулик	Оксана
	Романко	Оксана
	Берчинська	Галина
	Романко	Оксана
	Іваненко	Андрій
	Когут	Тетяна
	Білик	Зоряна

Вилучаю фільтр.

7. Створюю i виконую Запит1 на вибірку записів про студентів, які мають "5" з інформатики.

Переписую номери записів.

Запрос1
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage
	Білик	Зоряна
	Іваненко	Андрій
	Долинський	Андрій
	Берчинська	Галина

Закрийте вікно запиту зі збереженням структури запиту з назвою Запит1.

8. Модифікую Запит1, щоб вибрати записи про студентів, які вчаться на "4" або "5" з математики i мови.

Вікно бази даних → Запити * Запит! * Обновити → Конструктор * ОК. Оцінки "4" i "5" вводжу одну під одною (у двох рядках) в умовах для полів математики i мови. Запускаю запит. Переписую номери записів.

Запрос1
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage
	Білик	Зоряна
	Кулик	Оксана
	Когут	Тетяна
	Іваненко	Андрій
	Романко	Оксана
	Берчинська	Галина
	Романко	Оксана

Закриваю запит без збереження.

9.Створюю i виконую Запит2 на вибірку лише номерів i прізвищ студентів, у яких сьогодні день іменин.

Вибираю ім'я Андрій за умову пошуку. Вимикаю режим виведення на екран оцінок, Виконую i зберігаю Запит2.

10. Створюю Запит3 з параметром для шукання записів про студентів, які мають деяку, задану параметром, оцінку з математики.

Виконую цей пункт методом модифікації Запиту1. У полі математики задаю параметричну умову — вводжу у ньому такий вираз: [Оцінка:]. Зліквідовую числову умову в іншому полі (інформатики).

11. Запускаю Запит3 двічі, щоб визначити, хто має з математики "5", а хто – "2".

Прізвища й оцінки занотовую у звіт.

Запрос3
NUMBER	FNAME	ONAME	Mathem	Inform	Phisikal	LITERATEL	LEAUNGUAGE
	Білик	Зоряна
	Когут	Тетяна
	Іваненко	Андрій
	Романко	Оксана
	Берчинська	Галина
	Романко	Оксана

Запрос3
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage
	Сілецька	Валентина
	Ковальчук	Галина

12. Закриваю Запит3, зберігаючи його.

13. Конструюю i виконую Запит4 на створення нової таблиці Відмінники, куди треба з таблиці Оцінки скопіювати записи ycix відмінників за алфавітом.

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

14. Доповнюю таблицю Відмінники записами про студентів, що вчаться на "4" або "5" з ycix предметів.

Відкриваю конструктор Запиту4, змінюю умови вибірки, змінюю тип запиту — Доповнення i запускаю його на виконання. Переглядаю таблицю Відмінники. Закриваю запит без збереження.

15. Створюю на базі таблиці Оцінки нову таблицю з назвою Рейтинг i з обчислювальним полем Сума — сумою оцінок студентів з ycix предметів.

Відкриваю Запит1 у режимі конструктора. Зліквідовую умови. Після поля Мова у порожню клітинку нового поля вводжу формулу: =Мат+Інф+Фіз+Літ+Мова.

16. Підписую нове поле.

Виконую команду Властивості цього поля (засобами контекстного меню) i в останньому рядку отриманої таблиці вводжу назву (підпис) поля: Сума.

17. Виконую модифікований Запит1.

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

Повертаюсь в конструктор запиту. Вмикаю режим упорядкування за спаданням для поля Сума. Виконую запит. Переписую номери студентів за спаданням.рейтингу у звіт.

Запрос5
NUMBER	Fname	Oname	Mathem	Inform	Phisikal	Literatel	Leaunguage	SUMM
	Берчинська	Галина
	Іваненко	Андрій
	Білик	Зоряна
	Романко	Оксана
	Романко	Оксана
	Когут	Тетяна
	Долинський	Андрій
	Кулик	Оксана
	Лісовська	Анна
	Сілецька	Валентина
	Ковальчук	Галина
	Писар	Галина

19. Зберігаю отримані результати в таблиці Рейтинг.

Конструктор запиту → Змінюю тип запиту на тип Створення таблиці "Рейтинг" * Запуск запиту * Закриваю запит зі збереженням як Запит5.

20. Виконую запит на відшукання в таблиці Відмінники записів, що повторюються.

Вікно бази даних → Закладка Запити (але нічого не вибирати) * Створити * Вибираю режим Записи, що повторюються * OK * Вибираю таблицю Відмінники → Далі → Задаю поля, де можна виявити небажані повторення, наприклад, Номер, перевівши цю назву у поле пошуку повторень → Готово. Переписую номери записів, що повторюються, враховуючи кратність повторень, у звіт.

Поиск повторений для Відмінники
Mathem	Inform	Phisikal	Literatel	Leaunguage	NUMBER

Закрийте запит як Запит6.

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

Відкриваю Запит1 у режимі конструктора. Доповнюю запит таблицею Відмінники. Вилучаю з конструктора таблицю Оцінки командою Вилучити. В умові пошуку по полю Номер зазначаю один під одним номери записів, які треба вилучити, враховуючи кратність. Запускаю запит на вилучення.

22. Зберігаю таблицю Відмінники як новостворену без записів, що повторюються.

23. Копіюю в нову таблицю Адреси три перші поля ycix записів з таблиці Рейтинг.

Створюю новий Запит7, у який заношу три перші поля. Змінюю тип запиту на Створення нової таблиці. Запускаю запит. 3 запитами роботу закінчили.

24. Відкриваю таблицю Адреси i переглядаю її. Додаю до структури таблиці чотири нові поля: Група, Micтo, Вулиця, Телефон (у режимі конструктора).

25. Відкриваю таблицю Адреси в режимі таблиці i доповнюю записи даними.

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

26. Зберігаю таблицю Адреси у файлі.

27. Закриваю базу даних. Закінчую роботу. Здаю звіти.