Лабораторна робота №3 на тему Підсумки в запитах, Бази даних та інформаційні системи, ЗНТУ

Лабораторна робота №3 на тему Підсумки в запитах

Мета роботи: навчитися створювати перехресні запити та запи­ти, що знаходять підсумки даних в СУБД Access.

3.1 Використання підсумків у запитах

Задача 3.1. Створити запит про заробітки всіх робітників.

Це підсумковий запит. Спочатку створимо запит на вибірку з полями tVidom . tn, tVidom. fio та tZarobitok . zar. На панелі іструментів натиснемо кнопку å (Групповые операции). В конструкторі з’явить­ся рядок з такою ж назвою (див. рис. 3.1). Для полів tn і fio залишимо значення Группировка, а для поля zar виберемо зі списку варіант Sum (сума). Перетягнемо ще раз поле zar в нижню частину конструктора запитів і виберемо для нього зі списку варіант Avg (середнє значення). 

Рисунок 3.1 – Конструктор запиту 3.1

Встановимо властивості поля zar (див. рис. 3.2), щоб задати під­писи та формат відображення даних. Змінимо тип об’єднання таблиць. Виділімо лінію зв’язку між таблицями та у вікні Параметры объеди­нения відзначимо другий перемикач як на рис. 2.12. Запишемо запит під іменем pidsumki. Результат роботи запиту – на рис.3.3. 

Рисунок 3.2 – Вікно властивостей поля

В підсумкових запитах можна використовувати одну з функцій: Sum (сума), Avg (середнє), Count (кількість записів), Min (мінімум), Max (максимум), First (перше значення), Last (останнє значення). Якщо підсумки знаходяться тільки для тих записів, що задовольняють певній умові, то для поля, в якому записане Условие отбора записів, в рядку Групповая операция треба вибрати значення Условие. 

Рисунок 3.3 – Результат запиту 3.1

Задача 3.2. Створити запит про заробітки тих робітників, які заробили більше 200 грн.

Використаємо попередній запит pidsumki. В конструкторі для поля zar запишемо Условие отбора >200. В результаті роботи будуть виведені відомості тільки про двох робітників: Грудова та Петренка.

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

Для розв’язку цієї задачі спочатку створимо допоміжний запит на вибірку з полями tVidom. fio та tZarobitok . zar. Для поля tZarobitok . zar запишемо Условие отбора > 40, тобтобудуть виводитисьлише за­писи, що задовольняють умові. Збережемо запит під іменем більше 40.

Далі створимо підсумковий запит. Виберемо всі поля допоміж­ного запиту більше 40, натиснемо кнопку Групповые операции і вста­новимо для поля fio варіант Группировка, а для поля zar – виберемо функцію Count (див. рис. 3.4). Для поля zar у вікні Свойства запише­мо Подпись: Кількість днів.

Результат роботи запиту – на рис.3.5. 

Рисунок 3.4 – Конструктор запиту 3.3 

Рисунок 3.5 – Результат запиту 3.3

Другий спосіб. В режимі конструктора (див. рис. 3.6) виберемо таблиці tvidom і tzarobitok, а в них поля  fio і zar. В рядку Групповая операциявиберемо для поля fio варіант Группировка, а поле zar запи­шемо до бланку запиту двічі. В рядку Групповая операция виберемо для нього варіант Count (кількість записів), а в стовпчику, де вибраний варіант Условие, запишемо Условие отбора > 40і знімемо прапорець Вывод на экран. Результат роботи запиту той же самий (див. рис.3.5). 

Рисунок 3.6 – Конструктор запиту 3.3 (другий спосіб)

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

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

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

Спочатку створимо допоміжний запит на вибірку під назвою dlakvartalз полями tvidom . pidrozdil , tzarobitok . dat та tzarobitok . zar.

Потіму вікні конструктора БД натиснемо на кнопку Создать, і виберемо пункт Перекрестный запрос. Починає роботу майстер.

На першому етапі необхідно вказати ім’я запиту або таблиці, з якої треба вибрати записи. Встановимо перемикач Запросы і виберемо запит dlakvartal.

На другому етапі виберемо поле pidrozdil для заголовків рядків.

На третьому етапі виберемо поле dat, щоб використати його значення для заголовків стовпчиків. Тому що це поле містить дані ти­пу дата, з’являється додатковий етап, на якому можна вказати інтер­вал часу для підсумків. Відзначимо Квартал. На наступному етапі для поля zar виберемо зі списку підсумкову функцію Сумма та збере­жемо запит під іменем kvartal .

Відкриємо запит в режимі конструктора (див. рис. 3.7), щоб вдо­сконалити його вигляд. Для Заголовков столбцов запишемо вираз:

Выражение1: "Квартал " & Format( [dat] ;"q")

Змінимо обчислювальне поле для обчислення підсумків в рядку на Загальна сума: zar.Результат роботи запиту – на рис.3.8. 

Рисунок 3.7 – Конструктор запиту 3.4 

Рисунок 3.8 – Результат запиту 3.4

3.3 Контрольні питання

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

2. Які групові операції використовуються при створенні підсум­кових запитів?

3. Перехресні запити. Робота майстра.

4. Які елементи повинен мати перехресний запит?

3.4 Індивідуальні завдання

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

• там, де потрібно, створити обчислювальні поля;

• там, де потрібно, створити допоміжні запити;

• відкоригувати створені запити в конструкторі, щоб поліпшити їх зовнішній вигляд.

Варіант 1

1. Вивести кількість замовлень за кожен місяць поточного року.

2. Підрахувати загальну суму до сплати для кожного замовника.

3. Створити перехресний запит, в якому для кожної назви добри­ва та кожного замовника вказати дату останнього замовлення.

Варіант 2

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

2. Обчислити загальну вартість перевезень за кожний місяць.

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

Варіант 3

1. Підрахувати кількість готелів у кожному району міста.

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

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

Варіант 4

1. Для кожного з матеріалів вказати назву і марку та вивести дату останнього замовлення.

2. Для кожного постачальника вказати назву і підрахувати за­гальну суму замовлень щокварталу.

3. Створити перехресний запит, в якому для кожного виробника та назви матеріалу вивести код матеріалу.

Варіант 5

1. Для кожної пільгової категорії (вказати її назву) підрахувати кількість споживачів.

2. Для кожного споживача (вказати прізвище) підсумувати всі сплати за поточний рік.

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

Варіант 6

1. Підрахувати для кожного магазину кількість замовлень на про­дукцію щомісяця.

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

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

Варіант 7

1. Для кожного постачальника (вказати його назву) підрахувати кількість найменувань вузлів.

2. Для кожної назви вузла підрахувати загальну вартість замов­лення щокварталу.

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

Варіант 8

1. Підрахувати кількість рейсів з кожного аеропорту вильоту.

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

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

Варіант 9

1. Для кожної категорії хірургів підрахувати їх кількість.

2. Для кожного хірурга підрахувати середню вартість лікування пацієнтів першої пільгової категорії, яких він оперував.

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

Варіант 10

1. Вивести для кожної книги обсяг найбільшого замовлення.

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

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

Варіант 11

1. Вивести для кожного цеху об’єм останньої партії продукції, що зберігається на складі.

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

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

Варіант 12

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

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

3. Створити перехресний запит, в якому для кожного дня та для кожної пільгової категорії вивести розмір максимальної спла­ченої суми з урахуванням пені та пільг.

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