Тема роботи: Робота із запитами

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

План:

1.Запити з полями, що обчислюються.

2.Групові операції в запитах.

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

4.­Запити з параметрами.

ТЕОРЕТИЧНІ ВІДОМОСТІ

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

Розглянемо створення обчислювального поля доход = оклад + премія (15% окладу). Для цього скористаємось конструктором запитів (). В вікні Конструктора додамо таблицю «Зарплата»:

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

В лівій частині вікна Построитель выражений необхідно вибрати таблицю «Зарплата». Після цього праворуч з’являться поля цієї таблиці:

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

 

Щоб закінчити роботу з Построителем выражений слід натиснути .

Для створення імені поля, яке буде обчислюватись для всіх записів поля «Зарплата» необхідно в бланку запиту в рядку , скористатись контекстним меню, пункт , а потім:

 

Після збереження запиту в вікні поточної бази даних в області переходів з’явиться посилання на цей запит.

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

Рис. 5.1. Підсумки завантаження запиту

на створення нового поля Дохід

 

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

Для виконання таких розрахунків потрібно створити запит на вибірку, а потім у вікні конструктора активізувати рядок Групповые операции. Для цього досить натиснути правою кноп­кою миші на рядок Сортировка і в контекстному меню вибрати пункт . Рядок Групповые операции стане над рядком Сор­тировка.

Новий рядок містить такий список гру­пових операцій:

 

Группировка	вибираються певні поля, які надалі обробля­тимуться як група.
sum	підсумок по даному полю
Avg	середнє по даному полю
Min	мінімум по даному полю
Мах	максимум по даному полю
Count	кількість ненульових полів
StDew	стандартне відхилення
Var	середнє відхилення
First	значення поля з першого запису
Last	значення поля з останнього запису
Выражение	починається зі знаку =
Условие	умова на обмеження

Результат буде однорядковим. Для всіх полів, до яких були застосовані опції рядка Групповые операции, Access створює заголовки, що складаються з імені опції та імені поля. Ці назви за бажанням можна замінити, перейменувавши поля.

Створимо запит, який дозволяє обчислити середнє значення поля оклад таблиці «Зарплата»:

Рис. 5.2. Вікно конструктора запитів і підсумки виконання запиту з груповою операцією Avg

 

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

 

Підсумки виконання цього запиту такі, як на Рис. 5.3.

 

 

Рис. 5.3. Підсумки виконання запиту з груповою операцією

 

 

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

Підсумки завантаження цього запиту будуть такими, як на рисунку:

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

Для створення звіту наступного зразка:

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

В вікні конструктора запитів повинно бути:

 

 

 

Можна змінити підпис поля , яке містить підсумкову суму по рядках, на будь-який інший. Для цього необхідно в вікні Конструктора викликати контекстне меню цього поля, виконати Свойства і ввести:

­Запити з параметрами. Дозволяють уникнути створення окремого запиту для кожного маленького завдання.

Наприклад, необхідно видати список працівників, дата народження яких менше певного числа, яке заздалегідь не відоме. Відомості видати у вигляді: дата народження, прізвище, ініціали, таб_ном. Для цього потрібно спочатку активізувати вікно запитів, створити запит, відбуксирувавши необхідні поля в бланк запиту, а потім як умову ввести параметр, вміщений у квадратні дужки: <=[введіть дату]:

Рис. 5.5. Бланк запиту з параметром

Під час виконання запиту формується вікно для введення параметра:

 

 

Результат виконання запиту з’являється на екрані тільки

після введення значень необхідних параметрів:

 

Рис. 5.6. Підсумки виконання запиту з параметром

 

ЗАВДАННЯ ДО ПРАКТИЧНОЇ РОБОТИ:

Використовуючи особисту реляційну базу даних, сконструювати наступні запити:

· з полем, що обчислюється (згідно з індивідуальним варіантом); зберегти запит з ім’ям запрос с вычисляемым полем; надрукувати підсумки як на Рис 5.1:

№ варіанту	Умови запиту на створення поля, яке обчислюється
1.	Загальна вартість кожного замовлення = норма використання добрива на 1 га вартість однієї тони площа для оброблення
2.	Вартість однієї додаткової хвилини кожного абонента = (сума платежу - абонентська плата)/кількість додаткових хвилин розмов
3.	Сума доходу кожного готелю міста за добу = кількість номерів «люкс» вартість проживання у номерах «люкс» + кількість номерів 1-го класу вартість проживання у номерах 1-го класу
4.	Сумарна вартість кожного замовлення = кількість вартість одиниці продукції + вартість транспортних видатків
5.	Розмір боргу кожного споживача за попередній період = сплачена сума - (кінцевий показник лічильника - попередній показник лічильника) розмір тарифу
6.	Сумарна вартість кожного замовлення = кількість вартість одиниці продукції + сума транспортних видатків
7.	Сумарна вартість кожного замовлення = кількість вартість одиниці продукції + вартість транспортних видатків
8.	Загальний дохід від одного будь-якого рейсу = кількість місць «бізнес класу» вартість квитків на рейс у «бізнес-класі» + кількість місць у салоні 1-го класу вартість квитків на рейс у салоні 1-го класу
9.	Вартість лікування кожного пацієнта = вартість лікування за добу термін лікування + вартість витрачених ліків
10.	Сумарна вартість кожного замовлення книг = відправлена кількість отпускна ціна + вартість доставки

· містить групові операції (згідно з індивідуальним варіантом); зберегти з ім’ям запрос с групповыми операциями; надрукувати підсумки як на Рис 5.2:

№ варіанту	Умови запиту на використання групових операцій
1.	Вартість кожного добрива за всіма замовленнями.
2.	Середню кількість додаткових розмов (в хвилинах) кожного абоненту.
3.	Загальну кількість днів проживання всіма гостями в кожному готелі.
4.	Кількість замовлень на постачання матеріалів кожним постачальником.
5.	Сплачена сума за спожиту електроенергію кожним споживачем за всіма платежами.
6.	Для кожного магазину-замовника загальну кількість замовлень молочної продукції.
7.	Для кожного постачальника загальну кількість замовлень автомобільних вузлів.
8.	Загальну кількість рейсів кожного літака.
9.	Кількість пацієнтів, які спостерігаються кожним лікарем комерційної лікарні.
10.	Загальну кількість замовлених книг кожним магазином-замовником.

· перехресний запит за умовами запиту, який містить групові операції (попереднє завдання); зберегти з ім’ям перекрестный запрос; надрукувати підсумки як на Рис 5.3:

· запит з параметром (згідно з індивідуальним варіантом); зберегти запит з ім’ям параметрический запрос; надрукувати підсумки як на Рис 5.4, 5.5.

№ варіанту	Умови запиту з параметром
1.	Для господарств – замовників добрив, які задаються параметром, видати відомості у вигляді: назва господарства, код господарства, адреса господарства, дата замовлення ним добрив у постачальника, назва добрива.
2.	Для абонентів АТС, які задаються параметром, видати відомості у вигляді: прізвище, ініціали абонента, № особового рахунку, назва тарифу, абонентська плата, домашня адреса абоненту.
3.	Для гостей міста, які задаються параметром, видати відомості у вигляді: прізвище. Ініціали гостя, країна проживання, з дітьми (так чи ні), назва готелю, рівень сервісу.
4.	Для постачальників будівельних матеріалів, які задаються параметром, видати відомості у вигляді: назва постачальника, адреса, № банківського рахунку, назва матеріалу, найменування виробника.
5.	Для споживачів електроенергії, які задаються параметром, видати відомості у вигляді: прізвище, ініціали споживача, дата народження, назва тарифу, розмір тарифу, № особового рахунку споживача.
6.	Для магазинів – замовників, які задаються параметром, видати відомості у вигляді: назва магазину, код магазину, назва продукції, дата постачання, № банківського рахунку.
7.	Для постачальників, які задаються параметром, видати відомості у вигляді: назва постачальника, адреса, телефон, назва вузла, № банківського рахунку.
8.	Для авіалайнерів, які задаються параметром, видати відомості у вигляді: бортовий номер, тип літаку, дата останнього техогляду, № рейсу, дата продажу.

 

 

№ варіанту	Умови запиту з параметром
9.	Для пацієнтів, які задаються параметром, видати відомості у вигляді: прізвище, ініціали пацієнта, дата народження, дата надходження у лікарню; прізвище, ініціали лікаря, що спостерігає; назва відділення.
10.	Для магазинів-замовників, які задаються параметром, видати відомості у вигляді: назва магазину, код магазину, адреса, назва книги, об’єм сторінок.

 

ПРАКТИЧНА робота №6