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

Лабораторна робота №2 на тему Запити на вибірку

Мета роботи: навчитися створювати запити на вибірку в Access.

2.1 Створення запитів на вибірку

Запити на вибірку вибирають дані з однієї таблиці або зі зв’яза­них таблиць. Відібрані дані задовольняють вказаним умовам. Такі за­пити не використовують для змінення даних таблиць.

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

Для того, щоб отримати запит у такому вигляді як на рис. 1.4, у вікні бази даних виберемо пункт Создать – Конструктор або двічі клікнемо на пункті Создание запроса в режиме конструктора.

Відкриються вікно конструктора запитів та вікно Добавление таблицы. Відзначимо таблиці tVidom та tZarobitok і натиснемо кнопку Добавить. У верхній половині вікна конструктора запитів з’являться ці таблиці. Між ними буде автоматично встановлений зв’язок.

Перетягнемо в нижню частину конструктора поля tn та fio з таб­лиці tVidom і поля dat та zar  з таблиці tZarobitok. Для поля dat таблиці tZarobitok в рядку Сортировка виберемо варіант по возрастанию. Збережемо запит під іменем vsiroboty. Результат цього запиту – на рис. 1.4, а конструктор запиту – на рис. 2.1.

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

В конструкторі запитів в рядку Условие отбора можна запису­вати умови на значення, за тими ж правилами, що й в таблицях. 

Рисунок 2.1 – Вікно конструктора запиту 2.1

Задача 2.2. Створити запит про заробітки, які знаходяться в ме­жах від 30 грн. до 50 грн.

Використаємо існуючий запит vsiroboty. Відкриємо його в ре­жимі конструктора та збережемо під іменем between 30 i 50. Для поля zar в рядку Условие отбора запишемо умови >=30 And <=50 (див. рис.2.2). Для цього ж поля відкриємо вікно Свойства і встановимо у властивості поля Формат поля значенняденежный.

Той же самий результат отримаємо, якщо включимо поле zar до запиту двічі. В першому стовпчику zar запишемо умову >=30, а в тому ж рядку другогостовпчика zar – умову <=50. Тоді в другомустовпчи­ку zar треба зняти прапорець в рядку Вывод на экран. 

Рисунок 2.2 – Фрагмент вікна конструктора запиту between 30 i 50

Задача 2.3. Створити запит про заробітки робітника Грудова.

Створимо для цього запит по таблиці tZarobitok. У вікні кон­структора для поля tn запишемо значення 24, тому що в таблиці збері­гається табельний номер, а відображується прізвище. Конструктор запиту – на рис. 2.3, а результат – на рис. 2.4. 

Рисунок 2.3 – Фрагмент вікна конструктора запиту 2.3 

Рисунок 2.4 – Результат запиту 2.3

2.1.2 Створення обчислювальних полів

Задача 2.4.Створити запит про заробітки, які були нараховані в серпні, тобто в 8-му місяці.

Знов відкриємо в ре­жимі конструктора запит vsiroboty. Запи­шемо в полі dat умову Month( [ tzarobitok] ! [dat] ) = 8 (див. рис. 2.5). 

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

Умову зручніше вводити за допомогою будівельника виразів. Поставимо курсор на місце, де треба ввести вираз і натиснемо кнопку Построить  на панелі інструментів. Відкриється вікно (див. рис. 2.6), в якому можна вибирати існуючі функції та поля таблиць і запитів. 

Рисунок 2.6 – Фрагмент вікна будівельника виразів

Ту ж саму умову можна записати трохи інакше (див. рис. 2.7). Створимо обчислювальне поле. Для цього у вільному полі запишемо вираз Month( [ tzarobitok] ! [dat] ), в рядку Условие отбора:  8 та знімемо прапорець Вывод на экран. Результат роботи – на рис. 2.8. 

Рисунок 2.7 – Фрагмент конструктора запиту 2.4 

Рисунок 2.8 – Результат запиту 2.4

Обчислювальне поле може мати заголовок. Він записується пе­ред виразом та відокремлюється від нього двокрапкою.Наприклад, 

місяць: Month ([ tzarobitok] ! [dat])

2.1.3 Запити з параметром

Задача 2.5. Створити запит про заробітки вказаного робітника. Прізвище робітника вводити як параметр.

Знов використаємо запит vsiroboty. Залишимо потрібні 3 поля (див. рис. 2.9) та в рядку Условие отбора для поля tvidom. fio запише­мо в квадратних дужках пояснювальний текст [Введіть прізвище]. 

Рисунок 2.9 – Фрагмент конструктора запиту 2.5

Після запуску запиту з’явиться вікно з поясненням і текстовим полем, до якого необхідно записати потрібне прізвище (див. рис. 2.10). 

Рисунок 2.10 – Вікно для введення параметру

За замовчанням Access встановлює для параметрів запиту текс­товий тип даних. Щоб його змінити, треба в режимі конструктора за­питу вибрати в меню пункт Запрос, а потім – Параметры.

2.1.4 Пошук записів, що не мають зв'язаних в іншій таблиці

Задача 2.6. Знайти відомості про робітників, які ще не працю­вали, тобто їхні заробітки відсутні в таблиці tzarobitok.

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

На першому етапі необхідно вказати ім’я запиту або таблиці, з якої треба вибрати записи, що не мають зв’язаних записів в іншій таб­лиці. Виберемо зі списку таблицю tvidom.

Ім’я іншої таблиці, з якою перша має співпадаючі поля, потріб­но вказати на другому етапі. Виберемо зі списку таблицю tzarobitok. На третьому етапі вкажемо в таблицях імена полів, що мають спів­падаючі значення, тобто tn (табельний номер). Далі вкажемо поля, що увійдуть до запиту, та збережемо запит під іменем Null.

Якщо відкриємо запит в режимі конструктора, то побачимо ав­томатично створене Условие отбора = IsNull для поля tzarobitok . tn(див. рис. 2.11). Крім того, змінений тип об’єднання таблиць. Щоб пе­ревірити це, виділімо лінію зв’язку між таблицями та в контекстному меню виберемо пункт Параметры объединения. Відкриється вікно з перемикачами (див. рис. 2.12), в якому вибраний другий варіант. 

Рисунок 2.11 – Фрагмент конструктора запиту 2.6 

Рисунок 2.12 – Настроювання типу об’єднання таблиць

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

1. Для чого використовуються запити?

2. Назвіть елементи вікна конструктора запитів.

3. Як можна змінити властивості полів, що включені до запиту?

4. Умови відбору в запитах. Функції Between, In( ), Like( ).

5. Створення полів, що обчислюються.

6. “Построитель выражений” і його застосування.

7. Створення і використання параметричного запиту.

8. Типи об'єднання таблиць у запиті.

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

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

• настроїти властивості полів;

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

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

• відкоригувати типи зв’язків між таблицями.

Варіант 1

1. Для кожного замовника вивести його назву, телефон, дату за­мовлення, назву добрива та площу для обробки.

2. На певну дату постачання (яка може бути введена як параметр) вивести інформацію про всі замовлення.

3. Для кожного замовлення розрахувати необхідну кількість доб­рива, його вартість, суму до сплати (вартість замовлення – пільги + 20%ПДВ).

4. Які добрива не користуються попитом (замовлення на них від­сутні за останній квартал)?

Варіант 2

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

2. Вивести перелік транспортних засобів, які задовольняють умо­вам певної ваги чи певного об’єму (вводити, як параметр).

3. Для кожного замовлення підрахувати вартість перевезення та суми до сплати (відстань * вартість т/км* маса – пільги).

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

Варіант 3

1. Для кожного готелю вивести його назву, рівень сервісу, прі­звище гостя та дату його поселення.

2. Вивести перелік готелів, які мають певний рівень сервісу, із за­значенням району (рівень сервісу задавати як параметр).

3. Для кожного гостя обчислити загальну вартість проживання (вартість за одну добу*термін проживання*пільги + 20%ПДВ).

4. Які готелі не користуються попитом, тобто не мають жодного замовлення за останній місяць?

Варіант 4

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

2. На певну дату постачання (яка може бути введена як параметр) вивести перелік усіх замовлень.

3. Для кожного замовлення розрахувати вартість замовлення та суму до сплати (вартість замовлення +20% ПДВ).

4. На які будівельні матеріали зовсім відсутні замовлення?

Варіант 5

1. Для кожного споживача вивести його прізвище, ім’я та по батькові і інформацію про показники лічильника.

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

3. Вивести перелік усіх сплачених сум на певну дату, яку вводи­ти як параметр.

4. Які споживачі зовсім не сплачували за електроенергію за два останні місяці?

Варіант 6

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

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

3. Для кожного замовлення вивести вартість замовленої партії продукції, суму до сплати (вартість замовлення +20%ПДВ).

4. Яка продукція не користуються попитом, тобто замовлення на неї відсутні протягом останнього місяця?

Варіант 7

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

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

3. Для кожного замовлення підрахувати його вартість та суму до сплати (вартість замовлення +20%ПДВ).

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

Варіант 8

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

2. Вивести перелік рейсів до певного пункту (назву пункту мож­на вводити, як параметр). Пункти посадок не враховувати.

3. Для кожного зареєстрованого продажу авіаквитків підрахувати їх вартість та суму до сплати (вартість квитка* кількість квит­ків * пільги +20%ПДВ).

4. Який рейс не користується попитом (продаж квитків на нього не відбувалась за останні три доби)?

Варіант 9

1. Вивести список пацієнтів із зазначенням їх категорії, назви та дати операції, вказати прізвище та категорію лікаря.

2. Вивести на певну дату операції (яка може бути введена як па­раметр) усі прізвища лікарів та назви операцій.

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

4. Який хірург не робив операцій протягом останнього місяця?

Варіант 10

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

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

3. Підрахувати для кожного замовлення вартість замовленої пар­тії продукції та суму до сплати (вартість замовлення * пільги + 20% ПДВ).

4. Яка книга не користуються попитом, тобто замовлення на неї відсутні протягом останніх 6 місяців?

Варіант 11

1. Для кожного цеху вивести телефон начальника, назву продук­ції та вартість одиниці продукції кожного ґатунку.

2. Вивести повну інформацію про наявність на складі певної про­дукції (назву продукції вводити як параметр).

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

4. Яка продукція зовсім відсутня на складі?

Варіант 12

1. Для всіх абонентів вивести прізвище, номер телефону, назву пільгової категорії та відсоток сплати.

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

3. Вивести на певну дату всі суми, внесені абонентами (дату вво­дити як параметр).

4. Які абоненти ще не сплачували за телефон цього місяця?

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