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

1. Що таке запит?

2. Які способи створення запитів на вибірку є в СУБД ACCESS?

3. В якому режимі вносяться зміни до створеного запиту?

4. Які ключові слова використовуються для введення умов?

Практична робота № 4

ТЕМА: Створення підсумкових запитів та запитів в режимі SQL.

МЕТА: Навчитися створювати запити з групуванням записів та запити в режимі SQL.

Теоретичні відомості

Для створення підсумкового запиту за допомогою майстра (Создание, Другие, Мастер запросов: Простой запрос) після вибору полів у вікні, яке зображено на рис. 10,

Рис. 10. Створення підсумкового запиту в режимі майстра

треба вибрати опцію «итоговый», натиснути кнопку «Итоги», та ввімкнути прапорець для потрібної групової операції.

У режимі конструктора запитів для додавання групових операцій використовується кнопка (Конструктор, Показать или скрыть). Заповнюючи клітинку в рядку Групповая операция в бланку запиту (рис. 11), можна виконати розрахунки для груп записів і обчислити одну з статистичних функцій:

Функція	Результат	Тип поля
Avg	Середнє від значень поля.	Числовой, Дата/время, Денежный и Счетчик
Sum	Сума
Min	Найменше значення поля.	Текстовый, Числовой, Дата/время, Денежный и Счетчик
Max	Найбільше значення поля.
Count	Число значень поля без врахування пустих значень	Текстовый, Числовой, Дата/время, Денежный, Счетчик, Логический, Поле объекта OLE
StDev	Середнє квадратичне відхилення від середнього значення поля.	Числовой, Дата/время, Денежный и Счетчик
Var	Дисперсія значень поля.
First	Перше значення поля
Last	Останнє значення поля

Рис. 11. Групування записів у режимі конструктора запитів

Якщо групування проводиться у полі, що обчислюється, то конструктор запитів сам змінює формулу. Так, на рис. 11 у формулу розрахунку обсягу додана функція SUM.

При виконанні розрахунків допускаються складні вирази. Наприклад,

Sum([НаСкладі]+[Замовлено]).

Якщо у виразі були використані арифметичні оператори (+, -, *, /), а значення одного з полів є порожнім, результат усього виразу теж буде порожнім. Якщо які-небудь записи в одному з використаних у виразі полів містять порожнє значення, то це порожнє значення можна перетворити в нульове за допомогою функції Nz; наприклад:

Nz([ТоварыНаСкладе])+Nz([ЗаказанныеТовары])

Крім конструктора та майстра запити можна створювати вручну, використовуючи команди мови SQL (Structured Query Language – структурована мова прямих звернень). Запити створені за її допомогою, можна використовувати не тільки в ACCESS, але й у інших сучасних СУБД, таких як INFORMIX, ORACLE, SYBASE, SQL Server без змін.

Для створення запита в режимі SQL, необхідно виконати команди: Создание, Другие, Конструктор, Режим SQL.

Основна команда SQL-запитів – SELECT має загальний вигляд (параметри, виділені підкресленим шрифтом, є обов’язковими):

SELECT список полів AS псевдоним

FROM список таблиць INNER JOIN назва пов’язаної таблиці

ON пов’язане поле = пов’язане поле пов’язаної таблиці

WHERE умова AND вираз-об’єднання таблиць

GROUP BY список полів

ORDER BY список полів

де

список полів – імена полів, які будуть включені до запиту (* означає всі поля),

AS задає псевдоним – назву поля у запиті,

FROM задає список таблиць - імена таблиць які будуть включені до запиту,

INNER JOIN вказує з якою таблицею пов’язана таблиця,

ON вказує поля якими таблиці пов’язані,

WHERE– задає умова відбору даних,

GROUP BY задає перелік полів для групування;

ORDER BY - для сортування даних: ASC – у порядку зростання (по замовчуванню),

DESС – у порядку зменшення.

В умові можна використовувати знаки порівняння (<,<=,>,>=,=,<>); математичні (=,-,*,/); логічні (AND, OR, NOT) операції; оператори IN – визначає набір значень, з яких потрібно вибирати; LIKE – для пошуку груп записів; BETWEEN...AND… – в межах.

У SQL-запитах можна використовувати статистичні функції: COUNT (кількість), SUM (сума), AVG (середнє значення) тощо.

ПРИКЛАДИ.

БД складається з таблиці Студенти, яка містить такі поля:

Назва поля	Тип даних
Прізвище	текстовий
Назва факультету	текстовий
Курс	числовий, цілий
Шифр групи	текстовий
Стипендія	грошовий
Оцінка	числовий, цілий

ПРИКЛАД 1. Вибрати з таблиці «Студенти» прізвища студентів економічного факультету:

SELECT Прізвище FROM Студенти

WHERE [Назва факультету]=«Економічний»

ПРИКЛАД 2. Вибрати всіх студентів, які отримують стипендію вищу за 100 грн.:

SELECT * FROM Студенти WHERE Стипендія>100

ПРИКЛАД 3. Вибрати всіх студентів I та II курсу економічного факультету:

SELECT * FROM Студенти

WHERE [Назва факультету]=«Економічний» AND Курс<3

ПРИКЛАД 4. Вибрати всіх студентів, які отримують стипендію 100 або 120 грн.:

SELECT * FROM Студенти WHERE Стипендія =100 OR Стипендія =120

або

SELECT * FROM Студенти WHERE Стипендія IN(100,120)

ПРИКЛАД 5. Вибрати всіх студентів груп ББС-11 та ББС-12

SELECT * FROM Студенти WHERE [Шифр групи] IN(«ББС-11», «ББС-12»)

ПРИКЛАД 6. Вибрати прізвища студентів, які отримали оцінки у першій половині 2005 року

SELECT Прізвища, Оцінка FROM Студенти

WHERE Дата BETWEEN #1-1-2005# AND #6-30-2005#

ПРИКЛАД 7. Вибрати прізвища студентів, які починаються на літеру А

SELECT Прізвища FROM Студенти WHERE LIKE «A*»

ПРИКЛАД 8. Знайти загальну суму виплаченої стипендії:

SELECT SUM(Стипендія) AS [Загальна сума стипендії] FROM Студенти

ПРИКЛАД 9. Знайти середній бал серед всіх студентів:

SELECT AVG(Оцінка) AS [Середній бал] FROM Студенти

ПРИКЛАД 10. Вивести відсортовані за алфавітом прізвища та оцінку студентів:

SELECT Прізвища, Оцінка FROM Студенти ORDER BY Прізвища

ПРИКЛАД 11. Вивести прізвища студентів та оцінку, відсортовану у зворотному порядку:

SELECT Прізвища, Оцінка FROM Студенти ORDER BY Оцінка DESС

ПРИКЛАД 12. Знайти середній бал студентів по кожній групі:

SELECT [Шифр групи], AVG(Оцінка) AS [Середній бал по групі]

FROM Студенти GROUP BY [Шифр групи]

ПРИКЛАД 13. З БД Склад вивести поля «Назва товару» з таблиці «Товари» та «Кількість» з таблиці «Надходження», якщо таблиці пов’язані полями «Код товару»:

SELECT Товари.[Назва товару], Надходження.[Кількість]

FROM Товари INNER JOIN Надходження ON Товари.[Код товару] = Надходження.[Код товару];

ПРИКЛАД 14. Розрахувати вартість товарів, вибравши дані з 3 пов’язаних таблиць БД Склад:

SELECT Надходження.[Дата], Постачальники.[Назва постачальника], Товари.[Назва товару], Товари.[Ціна], Надходження.[Кількість], [Надходження].[Кількість]*[Товари].[Ціна] AS Вартість

FROM Товари INNER JOIN (Постачальники INNER JOIN Надходження

ON Постачальники.[Код постачальника] = Надходження.[Код постачальника])

ON Товари.[Код товару] = Надходження.[Код товару];