- •Міністерство фінансів України
- •1. Програма навчальної дисципліни
- •1.1. Опис навчальної дисципліни «Інформатика»
- •Інструментальні:
- •Міжособистісні:
- •Системні:
- •Спеціальні:
- •1.2. Тематичний план навчальноії дисципліни Тематичний план навчальноії дисципліни
- •1.3. Зміст навчальної дисципліни «Інформатика» (3-й семестр)
- •Тема 13. Microsoft Оffice. Субд access.
- •Тема 14. Програмування на мові sql
- •Приклад реляційної таблиці
- •1. Аналіз концептуальних вимог і інформаційних потреб.
- •2. Виявлення інформаційних об’єктів і зв’язків між ними.
- •3. Побудова концептуальної моделі предметної області й проектування концептуальної схеми бд.
- •Питання для самоконтролю
- •Бібліографічний список до теми:
- •Тема 14. Програмування на мові sql
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Компоненти інструкції update
- •Питання для самоконтролю
- •Бібліографічний список до теми
- •3. Методичні рекомендації до практичних занять
- •Тема 13. «Microsoft Office. Субд Access»
- •Додаткові характеристики поля таблиці
- •Структури інших полів таблиці даних
- •8.1. I спосіб:
- •8.2. II спосіб:
- •11.1. I спосіб:
- •11.2. II спосіб:
- •11.3. III спосіб:
- •13.6.1. I спосіб:
- •13.6.2. II спосіб:
- •14.2.1. I спосіб:
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •Структура таблиці Клієнти
- •Структура таблиці Пропоновані моделі
- •Дані таблиці Пропоновані моделі
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •Перелік статистичних функцій
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •Перелік кнопок “Панели элементов”
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •Параметри відображення номерів сторінок
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •Структура таблиці даних в Microsoft Excel
- •Параметри полів імпортованої таблиці даних
- •Параметри полів імпортованої таблиці
- •Параметри полів імпортованої таблиці даних
- •Таблиця для виконня обчислення підсумкових даних
- •Бібліографічний список до практичного заняття:
- •Тема 13. «Microsoft Office. Субд Access»
- •7. Створіть структури таблиць бд“Накладная”. Назви таблиц, і’мя полів та дані, які потрібно ввести в таблиці файлу бд, наведено у табл.. 3.15-3.19.
- •Структура та дані таблиці «Контрагенты»
- •Структура та дані таблиці «Спецификация»
- •Структура та дані таблиці «Товары»
- •Структура та дані таблиці «Города»
- •Бібліографічний список до практичного заняття:
- •Тема 14. Програмування на мові sql
- •I. Мінімальний синтаксис інструкції select.
- •II. Вибір неповторюваних значень.
- •III. Зміна назви полів та збереження даних запиту у окрему таблицю.
- •IV. Упорядкування результатів запитів
- •Бібліографічний список до практичного заняття
- •Тема 14. Програмування на мові sql
- •I. Створення запитів з умовами
- •II. Створення запитів з полями, що обчислюються
- •Бібліографічний список до практичного заняття
- •Тема 14. Програмування на мові sql
- •I. Використання статистичних функцій
- •II. Групування даних
- •Бібліографічний список до практичного заняття
- •Тема 14. Програмування на мові sql
- •I. Створення запитів із двох таблиць
- •Бібліографічний список до практичного заняття
- •Тема 14. Програмування на мові sql
- •I. Об’єднання запитів
- •II. Використання запитів при рішенні економічних задач
- •Бібліографічний список до практичного заняття
- •4. Методичні рекомендації до виконання індивідуального завдання
- •Завдання до теми 13
- •Завдання до теми 14
- •5. Підсумковий контроль
- •6. Список рекомендованої літератури
- •Спиридонов Володимир Миколайович інформатика
II. Групування даних
Якщо треба підрахувати значення по групам записів, то для цього треба застосувати групування даних.
Групування даних здійснюється за допомогою речення GROUP BY, після якого записується ознаки групування записів (послідовність ознак не має значення).
Слід особо відзначити, що у списку полів для запиту крім статистичних функцій слід використовувати (якщо потрібно) тільки ті поля, які є ознакою групи.
7. Завдання 3: Розрахувати суми по всіх накладних.
7.1. Рішення:
SELECT НомНак, SUM(Кол*Цена) as СумНак
FROM Спецификация
GROUP BY НомНак;
Результат запиту приведений на рис. 20.
Рис. 20. Результат запиту завдання 3
7.3. Збережіть створений запит з назвою Запит_13_3.
8. Завдання 4: Розрахувати загальні суми та суми ПДВ по всіх накладних та проаналізувати результат його виконання:
8.1. Рішення:
SELECT НомНак, SUM(Кол*Цена) as СумНак,
SUM(Кол*Цена)*0.2 as ПДВнак
FROM Спецификация
GROUP BY НомНак;
8.2. Результат запиту приведений на рис. 21.
Рис. 21. Результат запиту завдання 4
8.3. Збережіть створений запит з назвою Запит_13_4.
9. Результати виконання практичного завдання представте викладачеві для оцінки Вашої роботи.
10. Виконайте завершення роботи.
11. Виключіть ПК (за вказівкою викладача).
12. Оформіть дану роботу.
Бібліографічний список до практичного заняття
[ 3 ], [ 7 ], [ 20 ], [ 24 ], [ 25 ].
Практичне заняття №14
Тема 14. Програмування на мові sql
Мета заняття: Закріпити теоретичні знання студентів і набути практичні навички програмування на мові SQL відповідно до плану заняття.
План заняття
1. Ознайомлення з теоретичними основами інструкції SELECT.
2. Створення запитів з двох таблиць.
3. Створення запитів з використанням операції INNER JOIN (завдання 1).
4. Створення запиту для розрахунку реалізації товару (завдання 2,3).
Обладнання: персональний комп’ютер.
Методичні рекомендації до практичного заняття
Для виконання практичних завдань слід уважно ознайомитися з методичними рекомендаціями до самостійної роботи з теми. При необхідності слід використовувати додаткову літературу з бібліографічного списку до теми та довідкову систему 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: Розрахувати загальні суми тільки приходних накладних. Результати вивести у выгляді табл. 3.24.
Таблиця 3.24
Структура таблиці результатів запиту завдання 1
Номнак |
СумНак |
|
|
5.1. Рішення:
Суму для кожної із накладних можна розрахувати, використовуючи таблицю “Спецификация”. Це було зроблено в попередніх завданнях. Для того, щоб зробити розрахунки тільки для приходних накладних, треба разом з таблицею “Спецификация” застосовувати ще й таблицю “ОснСведНак”, яка містить поле “Операція”, значення якого визначає, чи є накладна приходною (значення “+”).
Таблиці “Спецификация” та “ОснСведНак” мають постійний зв’язок по полю “НомНак”. Його доцільно використовувати і для створення запиту за допомогою інструкції SELECT.
Таким чином, маємо:
SELECT Спецификация.НомНак,
Sum(Спецификация.Кол*Спецификация.Цена) AS СумНак
FROM ОснСведНак INNER JOIN Спецификация
ON ОснСведНак.НомНак = Спецификация.НомНак
WHERE ОснСведНак.Операция=“+”
GROUP BY Спецификация.НомНак;
або
SELECT Спецификация.НомНак, Sum(Кол*Цена) AS СумНак
FROM ОснСведНак INNER JOIN Спецификация
ON ОснСведНак.НомНак = Спецификация.НомНак
WHERE ОснСведНак.Операция=“+”
GROUP BY Спецификация.НомНак;
5.2. Результат запиту приведений на рис. 22.
Рис. 22. Результат запиту завдання 1
Пояснення: Поля запиту формуються тільки з полів таблиці Специфікація, але для вибору записів перевіряється значення поля ОснСведНак.Операция. Тож у запиті повинно використовувати обидві таблиці, які пов’язані між собою через поле НомНак.
Інструкцію SELECT для виконання поставленного завдання можна переписати в іншому вигляді.
SELECT Спецификация.НомНак, Sum(Кол*Цена) AS СумНак
FROM ОснСведНак INNER JOIN Спецификация
ON ОснСведНак.НомНак = Спецификация.НомНак
GROUP BY Спецификация.НомНак, ОснСведНак.Операция
HAVING ОснСведНак.Операция=“+”;
Якщо в першій інструкції вибираються записи, які відповідають тільки приходним накладним (умова на записи WHERE ОснСведНак.Операция=“+”), то в другому запиті перевірки записів нема, але записи групуються за двома груповими ознаками (GROUP BY Спецификация.НомНак, ОснСведНак.Операция), після чого встановлюється умова на групу (HAVING ОснСведНак.Операция=“+”). Тобто розрахунок проводиться для всіх встановлених груп, але вибираються тільки ті групи, які відповідають вказаній умові на групу.
5.3. Збережіть створений запит з назвою Запит_14_1.
6. Завдання 2: Самостійно створіть інструкцію SELECT для розрахунку сум реалізації товару по покупцях у вигляді: табл 3.25.
Таблиця 3.25
Структура таблиці результатів запиту завдання 2
Покупець |
Сума закупки |
|
|
6.2. Результат запиту повинен бути таким (рис. 23).
Рис. 23. Результат запиту завдання 2
6.3. Збережіть створений запит з назвою Запит_14_2.
7. Завдання 3: Самостійно створити інструкцію SELECT для розрахунку сум реалізації товару по покупцях у вигляді:
Таблиця 3.26
Структура таблиці результатів запиту завдання 3
Товар |
Кількість приходу |
|
|
Результати упорядкувати по назві товару.
7.1. Результат запиту повинен бути таким (рис.24):
Рис. 24. Результат запиту завдання 3
7.3. Збережіть створений запит з назвою Запит_14_3.
8. Результати виконання практичного завдання представте викладачеві для оцінки Вашої роботи.
9. Виконайте завершення роботи.
10. Виключіть ПК (за вказівкою викладача).
11. Оформіть дану роботу.