- •Методичні вказівки до виконання практичних занять з дисципліни «сучасні інформаційні комп’ютерні технології на транспорті»
- •Практичне заняття №1 створення машинної бази даних
- •Стисла теоретична довідка
- •Зміст практичного заняття та вихідні дані до його виконання
- •Вказівки до виконання завдання
- •Допустимі символи для специфікації Маска вводу
- •Практичне заняття №2 розробка форм
- •Стисла теоретична довідка
- •Зміст практичного заняття та вихідні дані до його виконання
- •Вказівки до виконання завдання
- •Практичне заняття №3 створення діаграм та пошук інформації
- •Стисла теоретична довідка
- •Зміст практичного заняття та вихідні дані до його виконання
- •Вказівки до виконання завдання
- •Практичне заняття №4 проектування запитів
- •Стисла теоретична довідка
- •Вказівки до виконання завдання
- •Практичне заняття №5 формування звітів
- •Стисла теоретична довідка
- •Зміст практичного заняття
- •Вказівки до виконання завдання
- •Практичне заняття №6 використання параметричних запитів для вказання умов відбору даних у звітах
- •Стисла теоретична довідка
- •Зміст практичного заняття
- •Вказівки до виконання завдання
- •Практичне заняття №7 використання форм для вказання умов відбору даних у звітах та робота з макросами
- •Стисла теоретична довідка
- •Зміст практичного заняття та вихідні дані до його виконання
- •Вказівки до виконання завдання
- •Практичне заняття №8 розробка головної кнопкової форми
- •Стисла теоретична довідка
- •Зміст практичного заняття
- •Вказівки до виконання завдання
- •Теми рефератів
Практичне заняття №4 проектування запитів
Мета заняття: вивчення принципів побудови запитів у реляційній базі даних Microsoft Access та їх використання для пошуку та відбору даних.
Стисла теоретична довідка
Запити призначені для відбору даних з однієї чи декількох таблиць, що задовольняють заданим критеріям (наприклад, даних про кількість і склад клієнтів, що були обслужені автотранспортним підприємством за місяць, дані про загальний обсяг перевезень по видам вантажів, тощо). У СУБД Access 2000 всі запити поділяються на QBE - запити (Query Ву Example) - запити за зразком, параметри яких встановлюються у вікні конструктора запитів, та SQL - запити (Structured Query Language), при створенні яких використовується оператори та функції мови SQL.
QBE-запити.
Запит QBE може бути одним з наступних видів.
1. Запит на вибірку - створюється для відбору даних з таблиці (таблиць), що відповідають заданим критеріям.
2. Запит на додавання - використовують для додавання записів в кінець таблиці з іншої таблиці (таблиць) за вказаним критерієм.
3. Запит на видалення - використовують для видалення записів з таблиці, що відповідають деякій умові відбору.
4. Запит на обновлення - використовують для обновлення значень групи записів, відібраної на основі деякого заданого критерію.
5. Запит на створення таблиці - використовують для створення нової таблиці на основі результуючого набору записів запиту.
Для створення запиту QBE використовують майстер запитів.
SQL-запити.
В основі запитів SQL лежить інструкція SELECT, найважливіші параметри якої наведені у наступному списку:
SELECT < перелік полів >
FROM <імена таблиць >
WHERE <критерії відбору >
ALL, DISTINCT, DІSTlNCTROW
<ім'я таблиці 1> INNER JOIN <ім'я таблиці 2>
GROUP СУ <перелік полів>
HAVING <критерій відбору>
ORDER BY <перелік полів>
Оформлення запиту SQL повинно відповідати наступним вимогам.
1. Зазвичай SELECT є, першою командою запиту.
2. Між іменами полів слід ставити кому. Порядок імен полів у списку відповідає порядку їх обробки та відбиття у результуючому наборі даних.
3. Ім'я поля, що містить пробіл, необхідно брати у квадратні дужки.
4. При наявності однойменних полів у таблицях, що оброблюються, слід наводити повну специфікацію поля, тобто, їм'я_табліці.Імя_поля.
5. Запит обов'язково повинен закінчуватися крапкою з комою (;).
Параметр FROM
Параметр FROM визначає, які таблиці чи запити містять поля, наведені в інструкції SELECT, тобто складають список оброблюваних таблиць та запитів. Слід враховувати, що параметр FROM є параметром інструкції SELECT та завжди вказується за нею.
Використовуючи символ * в якості замінювача імені поля в інструкції SELECT, з таблиці можна вибрати всі поля, наприклад
SELECT * FROM Автомобілі
Параметр WHERE
Параметр WHERE не є обов'язковим, але якщо він наявний у інструкції, то повинен бути вказаний одразу після параметру FROM. Параметр WHERE дозволяє визначити критерій відбору записів таблиць, вказаних у списку FROM. Тобто, будуть обрані тільки ті записи, що відповідають умовам, заданим за допомогою параметру WHERE.
SELECT [Держ номер], [Номер колони] FROM Автомобілі WHERE [Номер колони] > 5
Предикати ALL, DISTINCT, DISTINCTROW
За допомогою параметру WHERE визначається критерій відбору записів вхідного набору. Але у вхідній таблиці можуть бути наявні однакові за змістом записи у обраних полях. В такому випадку, якщо у запиті вказаний предикат ALL (він використовується по умовчанню) у результуючий набір будуть виведені всі дублікати.
SELECT ALL * FROM Автомобілі
Якщо необхідно виводити дублікати у результаті запиту лише один раз, то слід використовувати предикати DISTINCT та DISTINCTROW.
Предикат DISTINCT використовується у випадках, коли необхідно виключити записи, що містять однакові за значенням записи у обраних полях.
SELECT DISTINCT [Марка автомобіля] FROM Автомобілі
Предикат DISTINCTROW використовують у випадках, коли дані вибираються з декількох зв'язаних таблиць, в одній з яких записи є унікальними, а в іншій у полі, по якому зроблений зв'язок, можуть повторюватись.
SELECT DISTINCTROW [Марка автомобіля]
FROM Автомобілі, [Облік роботи]
Автомобілі INNER JOIN [Облік роботи]
ON Автомобілі.[Держ номер]=[Облік роботи].[Держ номер]
GROUP BY [Марка автомобіля]
Операція INNER JOIN
За допомогою операції INNER JOIN створюється спеціальне об'єднання таблиць, що виконується за умови тотожності змісту полів, вказаних після ключового слова ON у записах таблиць, вказаних у операції INNER JOIN. Записи об'єднуються у випадку знаходження співпадаючих значень у вказаних полях.
Автомобілі INNER JOIN [Облік роботи]
ON Автомобілі.[Держ номер]=[Облік роботи].[Держ номер]
У наведеному прикладі встановлюється зв'язок між таблицями Автомобілі та Облік роботи. У вихідний набір даних будуть включені записи з цих таблиць за умови рівності значень Автомобілі.[Держ номер]=[Облік роботи].[Держ номер]
Параметр GROUP BY
При використанні параметру GROUP BY всі записи, що містять у заданому полі однакове значення, об'єднуються в один елемент вихідного набору.
SELECT Автомобілі.*
FROM Автомобілі
GROUP BY [Марка автомобіля]
Параметр HAVING
Параметр HAVING є необов'язковим та використовується тільки з параметром GROUP BY. Він встановлює записи, що згруповані за допомогою параметру GROUP BY, повинні увійти до вихідного набору.
Параметр ORDER BY
За допомогою параметру GROUP BY встановлюється порядок сортування даних у вихідному наборі даних. Сортування даних може виконуватись за декількома полями, в такому випадку вони записуються через кому. По умовчанню порядок сортування виконується в порядку збільшення значень, дат або за абеткою. Для сортування по зменшенню слід використовувати ключове слово DESC після відповідного поля.
SELECT [Прізвище водія]
FROM Автомобілі
ORDER BY [Прізвище водія] DESC
Для значень вихідного набору даних можна використовувати наступні статистичні функції, що обчислюють:
Count — кількість записів, що увійшли до вихідного набору; First/Last - перший (останній) запис, у вихідному наборі; Міп/Мах - мінімальне (максимальне) значення у полі вихідного набору; Avg - середнє арифметичне значення у вказаному полі запиту; StDev/StDevP - зміщене (незміщене) стандартне відхилення поля запиту; Sum - суму значень для вказаного поля вихідного набору.
Приклади SQL-запитів.
1. SELECT [Прізвище водія], [Номер колони]
FROM Автомобілі WHERE [Марка автомобіля]="КАМАЗ" ORDER BY [Прізвище водія], [Номер колони] DESC;
Вибирає з таблиці Автомобілі прізвища та номера колон водіїв, що працюють на автомобілях КАМАЗ. Результат сортується по прізвищам за абеткою та в порядку зменшення номера колони.
2. SELECT Автомобілі.[Прізвище водія], [Облік роботи].Дата, SUM([Облік роботи].Пробіг), АVG([Облік роботи].[Обсяг перевезень]) FROM Автомобілі, [Облік роботи]
WHERE (Автомобілі.[Марка автомобіля] = "МАЗ") AND ([Облік роботи].Дата > #01.01.03#) GROUP BY [Облік роботи].Дата;
Виводить для кожного водія, що працює на автомобілі МАЗ загальний пробіг та середній обсяг перевезень по днях року, починаючи з 01.01.03. Результат сортується в порядку зростання дат.
Зміст практичного заняття та вихідні дані до його виконання
Вихідні дані до практичного заняття вибираються з таблиці 4.1 згідно з номером студента за списком у журналі академічної групи. Для створеної у практичному занятті ] бази даних виконати.
1. Створити за допомогою конструктора запитів QBE— запит (запит 1 вихідних даних).
2. Створити за допомогою конструктора запитів SQL-запит (запит 2 вихідних даних).
Таблиця 4.1
Вихідні дані до практичного заняття №4
Вар. |
Запит 1 |
Запит 2 |
|||
1 |
Місяць року, прізвище водія, місячний час в наряді |
Модель автомобіля, відпрацьований час в наряді, кількість витраченого палива |
|||
2 |
Рік випуску автобуса, кількість автобусів даного року випуску в парку, загальна кількість місіть в усіх автобусах даного року випуску |
Марка автобуса, загальний пробіг всіх автобусів даної марки, середня кількість пасажирів за рейс |
|||
3 |
Місяць року; державний номер автобуса; середня кількість пасажирів, що перевезена за рейс |
Пункт відправлення; марка автобуса; загальна кількість пасажирів, перевезених даною маркою автобуса з даного пункту відправлення |
|||
4 |
Номер маршруту; кількість рейсів, виконаних по даному маршруту; загальний пробіг автобусів на маршруті |
Державний номер автобуса; загальний відпрацьований час на маршрутах; загальні витрати палива |
|||
5 |
Місяць року; кількість автомобілів, що перебували у ремонті; загальний час перебування всіх автомобілів у ремонті |
Найменування несправності; кількість автомобілів, що вийшли з ладу з причини цієї несправності; загальні витрати на ремонт по даній несправності |
|||
6 |
Марка автобуса; загальна кількість місць у автобусах даної марки; загальна кількість перевезених пасажирів |
Номер рейсу; кількість виконаних рейсів; загальна кількість перевезених пасажирів |
|||
7 |
Номер маршруту; загальна кількість обертів; середній виторг за один оберт |
Номер маршруту: загальна кількість перевезених пасажирів; загальний час, роботи рухомого складу на маршруті |
|||
8 |
Дата; код вантажу; кількість виданого вантажу |
Назва вантажу; загальна тривалість вантажних операцій з даним вантажем; загальна вартість виданого вантажу |
|||
9 |
Дата; вид палива; загальна кількість виданого палива |
Дата; марка палива; загальна вартість прийнятого палива |
|
||
10 |
Рік народження: номер бригади; кількість водіїв даного віку у бригаді |
Марка автобуса; номер бригади; загальний фактичний виторг |
|
||
11 |
Модель автомобіля; кількість видів перевезених вантажів: загальний пробіг всіх автомобілів даної моделі |
Назва вантажу; модель автомобіля; загальний виторг за перевезення вантажу автомобілями даної моделі |
|
||
12 |
Місяць року; номер навантажувача; кількість відпрацьованих годин |
Модель навантажувача; кількість зарядок батареї; загальний час на зарядку батареї |
|
||
13 |
Назва замовника; кількість замовлень; час, відпрацьований автобусами у замовника |
Марка автобуса; назва замовника; виторг від надання послуг з перевезень |
|
||
14 |
Дата; кількість водіїв, що працювали на лінії; загальна середня кількість годин, відпрацьованих кожним водієм |
Прізвище водія; загальна кількість перевезеного вантажу; заробітна плата |
|
||
15 |
Дата; кількість виданого вантажу; кількість обслужених одержувачів |
Назва вантажу; вид операції; загальна вартість вантажу |
|
||
16 |
Марка автобуса; кількість маршрутів, що обслуговуються даною маркою автобусів; середня вартість проїзду в автобусах даної марки |
Номер маршруту; назва зупинки; загальний виторг від пасажирів, шо знаходяться в автобусі наданій зупинці |
|||
17 |
Номер бригади; кількість вантажників в бригаді; годинний заробіток бригади |
Дата; кількість відпрацьованих годин всіма вантажниками; загальний заробіток всіх вантажників |
|||
18 |
Дата; загальна кількість виданих вантажних місць; середня кількість вантажників на видачі вантажів |
Назва вантажу; дата; загальна маса виданого вантажу |
|||
19 |
Назва клієнту; кількість замовлень; середній доход від даного клієнта на одне замовлення |
Модель автомобіля; загальний пробіг автомобілів даної моделі; загальний доход від роботи автомобілів даної моделі |
|||
20 |
Дата; номер рейсу; середня вартість одного проданого квитка |
Марка автобуса; середній час стоянки автобуса; середня кількість пасажирів, що прибувають у автобусах даної марки |
|||
21 |
Прізвище водія; кількість сходів з лінії; загальна кількість діб у ремонті |
Модель автомобіля; кількість сходів з лінії; загальна кількість діб у ремонті |
|||
22 |
Вид тари; середня вантажопідйомність тари даного виду; максимальна вартість тари даного виду |
Інвентарний номер; загальний час перебування тари у обороті; загальна вага вантажу, перевезеного у тарі |
|
||
23 |
Дата; кількість автомобілів, що працювали на лінії; загальний пробіг автомобілів на лінії |
Прізвище водія; номер автомобіля; загальний відпрацьований час на лінії |
|
||
24 |
Дата; мінімальне число пасажирів, що вийшли з автобуса; середнє число пасажирів, що увійшли до автобусу |
Назва зупинки; загальний виторг від пасажирів на даній зупинці; середній час посадки-висадки одного пасажира |
|
||
25 |
Дата; кількість автобусів на лінії; загальна кількість виконаних їздок |
Марка автобуса; номер рейсу; загальний пробіг автобусів даної марки на даному рейсі |
|
||
26 |
Одиниця виміру; кількість матеріалів з одиницею виміру; мінімальна вартість матеріалу з одиницею виміру |
Назва матеріалу; загальна вартість виданого вантажу; середня тривалість навантаження одиниці вантажу |
|
||
27 |
Державний номер автомобіля; загальний відпрацьований час; загальна транспортна робота у тонно-кілометрах |
Модель автомобіля; загальний обсяг перевезеного вантажу; середні витрати палива на годину роботи автомобілів даної моделі |
|
||
28 |
Державний номер автобуса; кількість виконаних рейсів; середня відстань перевезень |
Прізвище водія; загальний пробіг автобусів, керованих даним водієм; загальна кількість виконаних рейсів |
|
||
29 |
Рік народження; клас; кількість водіїв даного класу і даного року народження |
Прізвище водія; кількість поломок автобусів, керованих даним водієм; загальна кількість діб у ремонті |
|
||
30 |
Марка автомобіля; кількість заправлених автомобілів; загальна кількість заправленого палива |
Дата; вид палива; виторг від реалізації даного виду палива |
|