SELECT НомНак, Дата, Контрагент, Операция
FROM ОснСведНак
WHERE Дата<=#01/05/2007#
OR (Дата BETWEEN #01/15/2007# AND #01/16/2007#)
ORDER BY ДатаDESC;
Примітка: В приведеному рішенні дати записуються в форматі ММ/ЧЧ/РРРР (місяць/число/рік).
Результат запиту приведений на рис. 14.
Рис. 14. Результат запиту завдання 3
7.3. Збережіть створений запит з назвою Запит_7_3.
II. Створення запитів з полями, що обчислюються
Створення полів, що обчислюються, розглянемо на прикладі.
8. Запит 4: Для накладної за номером 3 вивести специфікацію у вигляді табл. 2.19. Результати відсортувати по назві товару.
Таблиця 2.19
Структура таблиці результатів запиту 4
| Товар | Кількість | Ціна | Сума |
8.1. Рішення:
SELECT Товар, Кол AS Кількість, Цена AS Ціна, Кол*Цена AS Сума
FROM Спецификация
WHERE НомНак=“3”
ORDER BY Товар;
8.2. Результат запиту приведений на рис. 15.
Рис. 15. Результат запиту 4
Пояснення: Як видно з рішення, полем, що обчислюється, є поле “Сума”. Його значення обчислюється як добуток кількості на ціну товару. Якщо не задавати ім’я для поля, що обчислюється (а це допускається синтаксисом інструкції SELECT), то система сама його визначить, але воно не буде мати ніякого змісту. Тому для полів, що обчислюються, бажано задавати ім’я при створенні запиту.
8.3. Збережіть створений запит з назвою Запит_7_4.
9. Запит 5: За допомогою SELECT-інструкції зробити наступний запит та проаналізувати результат його виконання:
SELECT Контрагент, НомНак&“\”&Операция as Накладная
FROM ОснСведНак
ORDER BY Контрагент;
Примітка: Збережіть створений запит з назвою Запит_12_5.
10. Результати виконання практичного завдання представте викладачеві для оцінки Вашої роботи.
11. Виконайте завершення роботи.
Бібліографічний список до практичного заняття
[ 3 ], [ 7 ], [ 20 ], [ 24 ], [ 25 ].
Завдання №8
Тема 14. Програмування на мові SQL
Мета завдання: Закріпити теоретичні знання студентів і набути практичні навички програмування на мові SQL відповідно до плану завдання.
План завдання
1. Ознайомлення з теоретичними основами інструкції SELECT.
2. Створення запитів з використанням статистичних функцій.
3. Створення запитів з використанням функції SUM.
4. Створення запиту MIN, MAX, AVG.
5. Створення запиту з групуванням даних.
Обладнання: персональний комп’ютер.
Методичні рекомендації до практичного завдання
Для виконання практичних завдань слід уважно ознайомитися з методичними рекомендаціями до самостійної роботи з теми. При необхідності слід використовувати додаткову літературу з бібліографічного списку до теми та довідкову систему Access.
Практичні завдання
1. Увімкніть ПК.
2. Виконайте запуск Access.
3. Виконайте активізацію файлу Бази Даних “Накладна”, якийзнаходиться за адресою:C:\ Мои документы \ Папка з шифром Вашої групи\Накладна \ (місце знаходження файлу БД уточніть у викладача).
4. Ознайомтесь з теоретичними основами інструкції SELECT. Перелік статистичних функцій, які використовуються в запитах, наведено в табл. 2.20.
Таблиця 2.20
Перелік статистичних функцій
| Функція | Призначення |
| Avg(вираз) | обчислення середнього значення |
| Min(вираз), Max(вираз) | обчислення мінімального та максимального значень |
| Sum(вираз) | обчислення суми значень |
| Count (*) або Count(вираз) | визначення кількості записів |
Якщо інструкція SELECT не передбачає групування даних (групування даних розглядається далі), то результатом виконання статистичних функцій буде один запис замість тих записів, які приймають участь у запиті. Це треба обов’язково пам’ятати при їх застосуванні.
I. Використання статистичних функцій
5. Запит 1: Розрахувати суму накладної за номером 3.
5.1. Рішення:
SELECT SUM(Кол*Цена) as СумНак
FROM Спецификация
WHERE НомНак=“3”;
5.2. Результат запиту приведений на рис. 16.
Рис.16. Результат запиту завдання 1
Пояснення: Приведена інструкція SELECT виконує дії у наступній послідовності – спочатку із таблиці “Спецификация” вибираються записи, які відповідають умові НомНак=“3”, після чого для вибраних записів розраховується загальна сума добутків “Кол*Цена”, результат якої записується у поле “СумНак”.
Одночасно можна використовувати декілька статистичних функцій.
5.3. Збережіть створений запит з назвою Запит_13_1.
6. Запит 2: Розрахувати мінімальну, середню та максимальну ціни товарів для накладної за номером 3.
6.1. Рішення:
SELECT MIN(Цена) as МінЦіна, AVG(Цена) as CерЦіна,
MAX(Цена) as МаксЦіна
FROM Спецификация
WHERE НомНак=“3”;
6.2. Результат запиту приведений на рис. 17.
Рис. 17. Результат запиту 2
6.3. Збережіть створений запит з назвою Запит_8_2.
II. Групування даних
Якщо треба підрахувати значення по групам записів, то для цього треба застосувати групування даних.
Групування даних здійснюється за допомогою речення GROUP BY, після якого записується ознаки групування записів (послідовність ознак не має значення).
Слід особо відзначити, що у списку полів для запиту крім статистичних функцій слід використовувати (якщо потрібно) тільки ті поля, які є ознакою групи.
7. Запит 3: Розрахувати суми по всіх накладних.
7.1. Рішення:
SELECT НомНак, SUM(Кол*Цена) as СумНак
FROM Спецификация
GROUP BY НомНак;
Результат запиту приведений на рис. 18.
Рис. 18. Результат запиту 3
7.3. Збережіть створений запит з назвою Запит_8_3.
8. Запит 4: Розрахувати загальні суми та суми ПДВ по всіх накладних
8.1. Рішення:
SELECT НомНак, SUM(Кол*Цена) as СумНак,
SUM(Кол*Цена)*0.2 as ПДВнак
FROM Спецификация
GROUP BY НомНак;
8.2. Результат запиту приведений на рис. 19.
Рис. 19. Результат запиту 4
8.3. Збережіть створений запит з назвою Запит_8_4.
9. Результати виконання практичного завдання представте викладачеві для оцінки Вашої роботи.
10. Виконайте завершення роботи.
11. Оформіть дану роботу.
Бібліографічний список до практичного заняття
[ 3 ], [ 7 ], [ 20 ], [ 24 ], [ 25 ].
Завдання №9
Тема 14. Програмування на мові SQL
Мета завдання: Закріпити теоретичні знання студентів і набути практичні навички програмування на мові SQL відповідно до плану завдання.
План завдання
1. Ознайомлення з теоретичними основами інструкції SELECT.
2. Створення запитів з двох таблиць.
3. Створення запитів з використанням операції INNER JOIN.
4. Створення запиту для розрахунку реалізації товару.
Обладнання: персональний комп’ютер.
Методичні рекомендації до практичного завдання
Для виконання практичних завдань слід уважно ознайомитися з методичними рекомендаціями до самостійної роботи з теми. При необхідності слід використовувати додаткову літературу з бібліографічного списку до теми та довідкову систему Access.
Практичні завдання
1. Увімкніть ПК.
2. Виконайте запуск Access.
3. Виконайте активізацію файлу Бази Даних “Накладна”, якийзнаходиться за адресою:C:\ Мои документы \ Папка з шифром Вашої групи\Накладна \ (місце знаходження файлу БД уточніть у викладача).
4. Ознайомтесь зтеоретичними основами інструкції SELECT.
I. Створення запитів із двох таблиць
Дотепер створювались запити на підставі однієї таблиці. Але, як правило, запити створюються на основі декількох таблиць.
Розглянемо створення запитів з двох таблиць.
По-перше, якщо вибираються поля з різних таблиць, то в іменах полів бажано застосовувати і назву таблиці (яка записується перед ім’ям поля, після чого ставиться крапка) в якій розташовані ці поля. Якщо дві таблиці мають поля з однаковими назвами, то ця рекомендація стає обов’язковою. Інакше система не зможе визначити поле якої таблиці повинне входити у запит.
По-друге, всі таблиці повинні бути зв’язаними деяким чином (не обов’язково, щоби ці зв’язки збігалися з постійними зв’язками, які визначені для таблиць на етапі створення структури бази даних).
У найпростішому випадку зв’язок між двома таблицями встановлюються одним із способів:
FROM Таблиця1 INNER JOIN Таблиця2 ON Таблиця1.поле1= Таблиця2.поле2
FROM Таблиця1 LEFT JOIN Таблиця2 ON Таблиця1.поле1= Таблиця2.поле2
FROM Таблиця1 RIGHT JOIN Таблиця2 ON Таблиця1.поле1= Таблиця2.поле2
Операція INNER JOIN об’єднує записи із двох таблиць таким чином, що до запиту ввійдуть тільки ті записи, для яких пов’язані поля мають однакові значення.
Операція LEFT JOIN об’єднує записи із двох таблиць таким чином, що до запиту ввійдуть всі записи першої таблиці і тільки ті записи другої таблиці, для яких пов’язане поле має відповідні значення із першої таблиці.
Операція RIGHT JOIN об’єднує записи із двох таблиць таким чином, що до запиту ввійдуть всі записи другої таблиці і тільки ті записи першої таблиці, для яких пов’язане поле має відповідні значення із другої таблиці.
Слід відмітити, що зв’язок:
FROM Таблиця1 LEFT JOIN Таблиця2 ON Таблиця1.поле1= Таблиця2.поле2
еквівалентний зв’язку:
FROM Таблиця2 RIGHT JOIN Таблиця1 ON Таблиця1.поле1= Таблиця2.поле2
Умова Таблиця1.поле1= Таблиця2.поле2 є найпростішою умовою зв’язку для двох таблиць. Умова може бути складною і містити в собі декілька простих умов. Але далі будуть розглядатися тільки прості умови зв’язку двох таблиць.
5. Запит 1: Розрахувати загальні суми тільки приходних накладних. Результати вивести у вигляді табл. 2.21.
Таблиця 2.21
Структура таблиці результатів запиту завдання 1
| Номнак | СумНак |
5.1. Рішення:
Суму для кожної із накладних можна розрахувати, використовуючи таблицю “Спецификация”. Це було зроблено в попередніх завданнях. Для того, щоб зробити розрахунки тільки для приходних накладних, треба разом з таблицею “Спецификация” застосовувати ще й таблицю “ОснСведНак”, яка містить поле “Операція”, значення якого визначає, чи є накладна приходною (значення “+”).
Таблиці “Спецификация” та “ОснСведНак” мають постійний зв’язок по полю “НомНак”. Його доцільно використовувати і для створення запиту за допомогою інструкції SELECT.
Таким чином, маємо: