Тема: запити і звіти (запити на вибірку даних, конструювання умов відбору за даними однієї та кількох таблиць, параметричні запити, звіти).
Мета: сформувати уміння знаходити у базі дані за певними критеріями відбору, створювати прості вибіркові запити в автоматизованому режимі, застосовувати майстри для створення таблиць запитів і звітів, створювати звіти. Дотримуватися санітарно-гігієнічних норм і правил техніки безпеки праці
Обладнання: комп'ютери з встановленими ОС та LibreOffice; (дана) інструкція, база даних, створена в LibreOffice Base.
Структура уроку
Хід уроку
1. Організаційний момент
Вітання з класом. Перевірка присутності і готовності учнів до уроку. Перевірка виконання домашнього завдання.
2. Актуалізація опорних знань
Означити або розтлумачити поняття й перевірити правильність відповіді.
База даних — це структурованна сукупність взаємопов'язаних даних певної предметної області.
Tипи даних у базі даних: текстовий, дата/час, числовий тощо.
Елементи таблиці бази даних: назва поля, типи даних, опис, додаткові властивості.
Первинний ключ — це зазвичай найкоротший за довжиною ключ, який використовують для ідентифікації кожного запису таблиці.
Розширення, притаманне БД LibreOffice Base як усталено, — .odb.
Cпособи створення таблиць у базі даних: у режимі дизайну, з використанням помічника, cтворенням нового представлення таблиці.
3. Вивчення нового матеріалу
Запровадимо такі поняття.
Запит — це формулювання користувачем своєї інформаційної потреби до деякої бази даних, найважливіший інструмент будь-якої системи управління базами даних (СУБД).
Запит (як категорія БД) — це спеціальний об’єкт, призначений для вибірки даних з таблиць бази, а також для виконання обчислень та інших операцій з базовими таблицями, включаючи їхнє перетворення. На відміну від реальної таблиці, цей набір записів реально не існує у базі даних. У результаті запиту утворюється таблиця. Її інформація є актуальною, тобто відбиває останні зміни даних.
Відповідно до функцій виділяють такі типи запитів:
Запит на вибірку – вибирає дані з однієї або декількох таблиць і подає їх підсумковою таблицею. У складних запитах на вибірку можлива наявність обчислюваних віртуальних полів. Запити на вибірку є основним видом запитів, на їх основі побудовано решту запитів. Їх поділяють на:
групові або підсумкові запити — підсумовують дані з однієї або декількох таблиць у формі електронної таблиці, слугують для аналізу даних і побудови діаграм і графіків, є незмінним, статичним набором даних.
перехресний запит — це груповий запит, який відображає вибіркові дані в компактному і легкому для читання вигляді, у вигляді двовимірної матриці, так само як і зведені таблиці в табличному редакторі.
Запити на зміну — вносять в таблиці значні зміни, відмінити які не можна. В LibreOffice Base такі запити часто заблоковані, тобто на виконання таких запитів необхідно мати специфічні права доступу. Запити такого типу поділяють підтипи:
Окремо говорять про параметричні запити або запити з параметрами. Ці запити при запуску пропонують ввести необхідний параметр (параметри) у відповідному діалоговому вікні (вікнах). Параметр може бути умовою для відбору записів. Таким чином, користувач замість багатьох запитів на вибірку з різними умовами використовує один запит з параметром, який залежно від величини параметра виводить різні дані. Наприклад, параметр може бути невідомою величиною для розрахунків. У цьому випадку результат розрахунків залежить від заданої величини параметра. Будь-який запит, що містить умови можна перетворити в запит з параметром.
Виконаний запит — це віртуальна таблиця, що відображає найсвіжіші дані, вибрані з базових таблиць згідно з умовами. Це тимчасовий об'єкт, утворений у пам'яті машини на момент виконання запиту. Запити оперують завжди зі свіжими даними, тому всі розрахунки здійснюють у запитах. Складні запити містять віртуальні поля, де йде опрацювання даних. На диску у базі даних при потребі зберігають інструкцію запиту як окремий об’єкт.
Оновлюваний запит — це динамічний набір даних. В оновлюваних запитах можна редагувати дані й долучати нові, всі зміни можна буде зберегти у базових таблицях, віртуальні поля буде перераховано. Ознака оновлюваного запиту — символ * (новий запис) в кінці таблиці. Зазвичай це запити на вибірку.
Неоновлюваний запит — це статичний набір даних, призначений лише для перегляду. Якщо змінити властивості запиту на вибірку, можна перетворити його на статичний набір даних.
Записати умови запиту можна по-різному. LibreOffice Base підтримує типи запитів QBE і SQL.
Запит QBE — запит за зразком, ангійською Query By Example. Його формують заповненням бланка запиту у вікні Конструктора запитів.
Подають (графічним) бланком, який містить імена полів однієї або декількох таблиць у вигляді заголовків стовпців. У стовпцях можуть бути вирази. У рядках бланка указують ім'я таблиці, порядок впорядкування, виведення на екран, умови відбору. Бланк запиту нагадує бланк розширеного фільтру. На відміну від розширеного фільтру в запиті вказують конкретні поля для виведення у підсумковій таблиці. При заповненні бланка запит можна виконати («програти»), щоб переконатися у правильному відборі записів. Для цього використовують кнопку Виконати на панелі інструментів. Готовий запит зберігають, вказавши назву. Як усталено буде Запит1.
Запит SQL — послідовність вказівок мовою структурованих запитів (англійською Structured Query Language). Такі вказівки потрібно виконати зі вхідним набором даних для створення вихідного набору. У запит можуть входити вирази і статистичні функції. Запит до серверу чи запит на об'єднання можна створити лише мовою SQL.
Будь-який запит, незалежно від способу його створення, можна проглянути у вигляді SQL інструкції, використавши вказівку меню Зміни / Редагувати у режимі SQL...
Вкладений запит — запит, дані якого використовує інший запит. У цьому випадку спочатку буде опрацьовано вкладений запит, а потім — зовнішній (основний). При цьому всі властивості вкладеного запиту впливають на виконання. Наприклад, якщо вкладений запит був параметричним, з'явиться повідомлення для користувача щодо величин. Кількість вкладень не обмежена, але кожне вкладення сповільнює опрацювання.
Опис побудови запитів далі здійснено на прикладі бази даних, яку буде надано для опрацювання у ході практичної роботи. Ця база даних вже містить чотири
таблиці: Дисципліни, Оцінки, Студенти, Форма оплати, зв'язані між собою.
На вкладці Запити можна обрати один з трьох варіантів створення запиту в розділі Завдання:
Створення запитів з використанням Помічника має такі переваги: простота й наочність. Недолік — неможливість вказати умову відбору. Створення запиту — послідовне виконання всіх вказівок Помічника. Базові таблиці або запити, з яких витягують дані одночасно, мають бути зв’язані між собою. Створення запиту за допомогою Помічника виконують покроково:
Крок 1 — вибір з наявних таблиць (запитів) таких полів, які мають складати майбутню таблицю запиту.
Поля обирають у список (Поля у запиті):
;
.
;
.Крок 2 — визначення порядку та типу впорядкування даних таблиці-результату.
Крок 3 — визначення умови відбору даних з вихідних таблиць.
Крок 4 дає можливість обрати степінь докладності подання інфомації таблицею-результатом: докладний запит (відображати всі записи запиту) чи підсумковий (відображати лише записи агрегатних функцій, де функції можна обрати у прихованому списку з відповідною назвою).
Кроки 5 і 6 визначають наявність та умову групування. Вони часто неактивні навіть при наявності записів в таблиці, які можна групувати.
Крок 7 дає можливість дати нові назви (псевдоніми) обраним полям.
Крок 8 — визначення назву запиту та можливість переглянуду загальної інформації щодо властивостей таблиці запиту у полі Огляд.
Створення запиту в режимі дизайну
У діалоговому вікні Додати таблицю чи запит додати у вікно запиту ті таблиці або запити, на основі яких створюють запит.
Таблиці або запити мають бути зв'язані між собою, інакше запит не буде виконано. Якщо зв'язки вказано за допомогою Схеми Даних, їх буде автоматично відображено в усіх запитах. Інакше зв'язки необхідно створити безпосередньо у вікні запиту за тим же принципом, що і на схемі даних. При виконанні запиту на основі таблиць з непрямими зв'язками необхідно додати в запит весь ланцюжок зв'язаних між собою таблиць у потрібній послідовності.
Вибрати поля для запиту.
Це можна зробити кількома способами:
вибрати із списку, що розкривається, назву таблиці а потім — назву поля у відповідних рядках бланку запиту;
двічі клацнути в табличці на назві поля;
перетягнути на стовпець бланку назву одного поля чи кількох або символ * (всі поля). При цьому назва таблиці автоматично з'явиться на бланку.
Визначити виведення на екран полів запиту у відповідному рядку бланку. Іноді поле бере участь у запиті, але його не відображають на екрані. Наприклад, якщо воно існує лише для коректного запису умови. Видимі поля позначають прапорцями, а невидимі поля залишаються без прапорців.
Вказати впорядкування за потрібними полями, вибравши із списку, що розкривається, варіанти: за збільшенням, за зменшенням, відсутній. Як усталено впорядкування не здійснюють.
Замовити умови відбору в одному рядку або в кількох рядках. Якщо умови вказано в одному рядку, то їх сполучено логічною операцією «І»
якщо в різних рядках — логічною операцією «АБО».
Проглянути результат роботи: виконати запит, використовуючи кнопку 
Виконати запит або натиснувши клавішу F5.
Відредагувати запит у режимі роботи з бланком у Конструкторі або в режимі SQL. Можна змінити таке:
послідовність стовпців (виділенням і перетягуванням);
ширину стовпців (перетягуванням за заголовок);
властивості поля запиту (підпис, заголовок, формат, маску) через контекстне меню стовпця. Змінити назви-підписи полів не вдасться, якщо поля вже має підпис — властивість, вказану в базовій таблиці.
Зберегти запит як об'єкт, давши йому назву (як усталено Запит1).
Створення запиту в SQL-поданні вимагає знання мови SQL і специфічного синтаксису формування запитів. Для створення запитів у текстовому форматі в LibreOffice Base існує SQL-подання, яке буде подано чистим аркушем, якщо запит ще не створено, або аркушем з SQL-кодом, якщо запит відкрито для редагування у SQL-поданні.
Після визначення інструменту створення запитів можна приступати до знайомства з процесом створення різних типів запитів. Створення запитів за допомогою режиму дизайну дає більше можливостей для варіацій з умовами й функціями та не вимагає набуття знань щодо SQL, тому надалі приклади подано саме для цього режиму.
Запит на вибірку без умови (ще називають простий запит) дає можливість витягнути з таблиці / іншого запиту (з кількох зв'язаних між собою таблиць / запитів) дані вказаних користувачем полів за всіма записами в них. Для цього потрібно лише визначити видимими ті поля з таблиць, які цікавлять користувача.
Наприклад, для визначення оцінок кожного студента з кожного предмета необхідно виділити такі поля: Прізвище, Ім'я, Назва_дисципліни, Оцінка, які розташовано у трьох різних таблицях.
Результатом цього запиту буде така таблиця:
Запит на вибірку з умовою дає можливість витягнути з таблиці / іншого запиту (з кількох зв'язаних між собою таблиць / запитів) дані вказаних користувачем полів за всіма записами, які задовольняють попередньо заданій умові. Для цього потрібно визначити видимими ті поля з таблиць, які цікавлять користувача, та в рядку Критерій ввести умову.
Для визначення студентів, у яких оцінки з визначених у базі даних предметів не менші за 4, необхідно:
виділити поля: Прізвище, Ім'я, Назва_дисципліни, Оцінка, які розташовано у трьох різних таблицях;
записати в рядку Критерій поля Оцінка: >=4.
Результатом цього запиту буде така таблиця:
Для визначення студентів, у яких оцінки з математики не перевищують 4, необхідно:
виділити поля: Прізвище, Ім'я, Назва_дисципліни, Оцінка, розташовані у трьох різних таблицях;
записати в рядку Критерій поля Оцінка: <=4;
записати в рядку Критерій поля Назва дисципліни: Математика (звертайте увагу на регістр і розмір літер в текстовому записі).
Результатом цього запиту буде така таблиця:
Таким самим чином можна сформувати умови із знаками: =, <, >, >=, <= та використанням логічних функцій OR (або), AND (і), NOT (заперечення).
Наприклад, для визначення студентів, ім'я яких починається на літеру О, необхідно:
виділити поля: Прізвище, Ім'я ;
записати в рядку Критерій поля Ім'я: LIKE'*O*'.
Результатом цього запиту буде така таблиця:
Запит з параметром можна отримати перетворенням будь-якого запиту. При цьому в рядку Критерій замість конкретної умови вводять повідомлення про введення параметра: одним словом або кількома словами через символ підкреслення. Повідомлення вводять після двокрапки. Воно вкаже у майбутньому користувачеві на тип інформації, яку потрібно буде ввести у діалоговому вікні бази даних.
Під час запуску запиту з параметром на екрані буде повідомлення про введення параметра Ввід параметру, де у полі Параметри відображено текст, записаний раніше в рядку Критерій, а в розділі Значення користувач має ввести відповідну величину за даним критерієм.
Замість кількох запитів для різних груп студентів доцільно створити один
запит з параметром — номером групи. Якщо необхідно створити пошук за
конкретною дисципліною з виведенням списку оцінок, то Критерій виглядатиме так:
:Введіть_номер_групи;
:Дисципліна.
Результатом такого запиту буде таблиця:
Запит з обчислюваними полями отримують таким чином. Для обчислюваних віртуальних полів у стовпець замість назви поля вводять дію, яку треба виконати з відповідними полями. Причому назви полів вводять у лапках, а дію – математичним символом без пропусків. Назву обчислюваного поля вводять у рядку Псевдонім. Наприклад, для обчислення стипендії, яку отримають студенти з урахуванням пільгових надбавок, використовують таку форму запису: "Розмір_стипендії"+"Розмір_пільг".
Результатом описаного запиту буде така таблиця:
Після виконання описаних вище вказівок щодо створення запитів база даних буде містити 7 запитів, виконання яких буде відображатися в розділі Документ вікна бази даних. На поданому нижче малюнку у цьому розділі відображено відповідь на запит, який виділено у переліку запитів.
Звіт призначено для подання інформації баз даних. При використанні звіту можна, крім відображення вмісту полів з таблиць і запитів, групувати дані, вставляти у звіт верхні і нижні колонтитули тощо. Аналогічно створенню форм, створення звітів можна виконувати як за допомогою помічника для створення звітів, так і у режимі дизайну. Зазвичай, помічник використовують для створення основної частини звіту, тобто структури, а режим дизайну — для удосконалення зовнішнього
вигляду звіту.
Розглянемо, наприклад, створення звіту для подання списку студентів (прізвище, ім'я,по батькові, телефон) за групами для наявної бази даних. Потрібно спочатку відкрити цю базу даних у середовищі LibreOffice Base. Клацнути по елементу Звіти в області База даних і двічі клацнути в області Завдання по рядку Використати помічника для створення звіту.
Крок 1. Вибір полів. У вікні діалогу Помічник звітів вибрати поля з таблиць і запитів, вміст яких буде відображено у звіті. У нашому випадку потрібно вибрати з таблиці Студенти поля прізвище, ім'я, по батькові, телефон і група. Після завершення вибору натиснути кнопку Далі.
Крок 2. Поля позначок. При потребі вказати позначення полів. Для цього потрібно заповнити рядки у переліку полів. Але в даному випадку достатньо лише натиснути кнопку Далі вікна діалогу Помічник звітів.
Крок 3. Групування. Додати необхідні рівні групування. Для цього перемістити поля звіту зі списку Поля у список Групування за допомогою кнопки зі стрілкою (>). У даному прикладі це потрібно зробити з полем Номер групи, бо дані про студентів потрібно виводити по групам. Натиснути кнопку Далі.
Крок 4. Параметри сортування. Визначити порядок впорядкування записів для відображення у звіті. Встановити впорядкування за групою, потім — за прізвищем, і нарешті — за іменем (на випадок наявності студентів з однаковими прізвищами). Натиснути кнопку Далі.
Крок 5. Вибір стилю. Вибрати розмітку й орієнтацію сторінки. Наприклад, колонковий вигляд і книжну орієнтацію. Натиснути кнопку Далі.
Крок 6. Створити звіт:
вказати заголовок створеного звіту (Звіт Студенти);
вибрати типу звіту (Динамічний звіт — для збереження звіту у вигляді шаблону. При відкритті такого звіту його буде відображено з поточним вмістом);
у групі Що ви збираєтеся робити після створення звіту? вибрати Створити звіт зараз — для збереження звіту і завершення роботи.
Натиснути кнопку Завершити. Звіт буде відкрито у вікні LibreOffice Writer у режимі Лише для читання.
4. Інструктаж з ТБ
5. Закріплення вивченого матеріалу
У середовищі LibreOffice Base відкрити базу даних students.odb.
Користуючись Помічником, створити запит Оцінки_cтудентів. Видимі поля: Прізвище, Ім'я, Дисципліна, Оцінка.
Користуючись Помічником створити запит на вибірку лише тих студентів, які навчаються у 151 групі. Видимі поля: Прізвище, Ім'я, Номер групи. Назвіть таблицю результату Група_151.
Користуючись Помічником, створити запит Вибірка_за_абеткою на вибірку лише тих студентів, прізвище яких починається на літеру «К». Видимі поля: Прізвище, Ім'я, Номер група, Стипендія. Впорядкуйте таблицю за зростанням за полем Прізвище.
У режимі дизайну створіть запит Трієчники на вибірку усіх студентів, які мають оцінку «3» з математики. Вилимі поля: Прізвище, Ім'я, Номер групи, Дисципліна.
У режимі дизайну створіть запит Група з параметром за полем Група. Видимі поля: Прізвище, Ім'я, Номер групи, Стипендія.
У режимі дизайну створіть запит Перерахунок_стипендії на створення таблиці з обчислюваними полями. Видимі поля: Прізвище, Ім'я, Номер групи, Стипендія, Перерахована_стипендія (розрахункове поле). У полі Перерахована_стипендія записати вираз, що відповідає збільшеній на 25% стипендії.
6. Підбиття підсумків уроку
Виставлення оцінок.
7. Домашнє завдання
Вивчити матеріал уроку і підготуватися до опитування.
На основі отриманих на уроці знань і вмінь створити порівняльну таблицю щодо створення запитів за допомогою Помічника й у режимі дизайну такого вигляду.
| Критерії | Помічник | Режим дизайну |
|---|---|---|
| Послідовність | ||
| Зручність | ||
| Наочність | ||
| Результативність |
Текст упорядкувала Шафран Світлана Миколаївна, учитель СЗШ № 9 міста Києва, під час виконання випускної роботи на курсах підвищення кваліфікації з 03.11.2014 до 23.11.14.
Використано випускні роботи курсантів Голуб Євгенії Сергіївни (на курсах з 01.10.2012 до 08.12.12) і Музичук Тетяни Вікторівни (на курсах з 01.10.2012 по 08.12.2012).