Лаборторна робота 19 - СУБД Microsoft Access. Створення та використання запитів і SQL запити, НУДПСУ
« Назад
ЛАБОРТОРНА РОБОТА №19Тема: СУБД Microsoft Access. Створення та використання запитів. SQL запитиМета роботи: навчитись розробляти запити, використовуючи засоби СУБД Microsoft Access та мову SQL. Хід роботи Запити є потужним засобом СУБД, що дозволяє здійснювати вибір записів у більш складних умовах, ніж це дозволяють зробити фільтри. За допомогою запитів можна переглядати, аналізувати і змінювати дані не тільки з однієї, але і з декількох таблиць одночасно. Запити можуть використовуватися як джерело даних для форм і звітів. Існують такі групи запитів:
За замовчуванням активним є тип запиту Запит на виборку. Інший тип запиту обирається в режимі Конструктора з меню Запрос або із списку Тип запроса, доступного після натискання відповідної кнопки панелі інструментів вікна програми Microsoft Access (Рис.1). Завдання 1. Створити запити на виборку В СУБД Microsoft Access існують такі способи створення запитів на виборку як вказані на Рис.2: та ще запити мовою SQL. 1.1. Створення простого запиту 1) У вікні БД перейти на вкладку Запросы; у вікні вкладки натиснути кнопку Создать. 2) У вікні Новый запрос у списку виділити Простой запрос і натиснути кнопку ОК. 3) У вікні Создание простых запросов виділити таблицю Адреси, вибрати поле Місто, і помістити його в бланк Выбранные поля; Потім натиснути кнопку Далее. 4) У наступному вікні ввести ім’я запиту Міста_простий запит і натиснути кнопку Готово. MS Access виводить у вікні запиту список міст, в яких розташоване фірми-клієнти даної фірми. 5) Закрити вікно запиту. 1.2. Створення запиту з параметрами 1) У вікні БД перейти на вкладку Запросы; у вікні вкладки натиснути кнопку Создать. У вікні Новый запрос у списку виділити Конструктор і натиснути кнопку ОК. 2) Вибрати в бланк запиту таблицю Адреси. 3) У верхній частині вікна Конструктора виділіть поле Місто і перемістіть його шляхом перетягування (при натиснутій лівій кнопки Миші) у нижню частину вікна у рядок Поле, аналогічно перемістити поля Назва фірми і Телефон. 4) Встановіть значення параметру Условия отбора по полю - Назва фірми: у рядку Условия отбора введіть у квадратних дужках текст [Введіть назву фірми] (Рис.3), після чого закрийте вікно запиту, дайте ім’я запиту Запит_з параметром_Фірма. 5) Відкрийте запит Запит з параметром_Фірма; задайте назву фірми у діалоговому вікні Введите значение параметра (рис.30.4). MS Access виводить у вікні запиту список, що відповідає умові відбору. Перегляньте таблицю і закрийте її. 6) Аналогічно створіть запит з параметром по полю Місто. Збережіть його з ім’ям Запит з параметром_Місто. Задаючи різні назви міст у діалоговому вікні, перевірте правильність роботи запиту. 1.3. Створення простого запиту з розрахунковим полем Результати обчислень, що виводяться в поле, не запам’ятовуються в базовій таблиці. Замість цього, обчислення знову виконується кожного разу, коли виконується запит, тому результати завжди відтворюють поточний зміст бази даних. Оновити обчислені результати вручну неможливо. Для визначення поля, що обчислюється, можна використовувати вбудовані функції MS Access або вирази, створені користувачем. Вбудовані функції дозволяють знайти такі підсумкові значення для груп записів або для всіх записів: суму значень поля, середнє, число значень у поле, мінімальне значення, максимальне значення, середньоквадратичне відхилення або дисперсію. Для кожного поля, що обчислюється, варто вибрати один вираз. 1) У вікні бази даних База Навчальна створити запит Нараховано в режимі Конструктор на базі таблиці Фірми, з полями Прізвище, Ім’я, По батькові, Оклад і Премія та розрахунковим полем СУМА. 2) У бланку запиту (у нижній частині вікна) встановити курсор введення в чергову порожню комірку рядка Поле. 3) Ввести ім’я поля, що обчислюється, додати двокрапку і вираз для розрахунку цього: СУМА:[Оклад]+[Премія]; імена полів слід включати в квадратні дужки (Рис.5). Зауваження. При веденні виразів необхідно дотримуватися прийнятого в MS Access синтаксису, інакше будуть з’являтися вікна повідомлень про помилки. 4) Закрити вікно Конструктора, відповівши «Да» на запит про збереження змін. 5) В вікні бази даних БазаНавчальна відкрити запит Нараховано; у вікні запиту, що відкрилося, з’явиться розрахункове – поле СУМА з обчисленими значеннями сум відповідних полів таблиці Фірми (Рис.6). 6) Відкрийте вікно таблиці Фірми і внесіть зміни в поля Оклад і Премія деяких записів, закрийте вікно таблиці і збережіть зміни. 7) Відкрийте запит Нараховано: у полі СУМА відображаються значення згідно з новими даними таблиці Фірми. 8) Закрийте таблицю запиту. 1.4. Створення запиту з груповою операцією 1) У вікні БД перейти на вкладку Запросы; у вікні вкладки натиснути кнопку Создать. 2) У вікні Новый запрос у списку виділити Конструктор і натиснути кнопку ОК. 3) Додайте таблиці Адреси і Замовлення. Натисніть кнопку Групповая операция на панелі інструментів вікна програми. . Визначте вказані на Рис.7 поля і вираз для обчислення суми замовлень, а також задайте параметри у рядку Групповая операция бланку запиту. При обробці даних буде здійснюватись групування по полю Назва фірми. Для створення виразу використайте Построитель выражений. Для цього у вільному полі введіть символи сума:, а потім натисніть кнопку Построитель выражений на панелі інструментів вікна програми. У діалоговому вікні Построитель выражений створити вираз для обчислення вартості замовлення, наведений у вікні на Рис.8, використовуючи відповідні інструменти і кнопки, після чого натиснути ОК. 4) Закрийте вікно конструктора. На запитання про збереження відповісти „Да”, дайте ім’я запиту Сума замовлень. 5) Відкрийте запит в режимі таблиці, перегляньте і проаналізуйте наведені дані. Таблиця запиту буде мати вигляд, схожий на наведений на рис. 22.9. Завдання 2.Створення запиту на вилучення записів 1. У вікні БД перейти на вкладку Запросы; у вікні вкладки натисніть кнопку Создать. 2. У вікні Новый запрос у списку виділіть Конструктор і натисніть кнопку ОК. Додати в бланк запиту таблицю Адреси і закрити діалогове вікно Добавление таблицы. 3. Виберіть тип запиту Удаление. 4. Задайте у бланку запиту умови вилучення запису про фірму Марс (Рис.10). При цьому потрібно усвідомлювати, що оскільки таблиця Адреси є головною, то автоматично відомості про цю фірму будуть вилучені з усіх зв’язаних таблиць. 5. Закрийте вікно бланку запиту. Збережіть запит з ім’ям Запит на вилучення. 6. Відкрийте запит з ім’ям Запит на вилучення. При цьому з’явиться діалогове вікно (Рис.11). Після натискання кнопки Да, з’явиться нове діалогове вікно (Рис.12). Натисніть кнопку Да. 7. Відкрийте таблицю Адреси і переконайтесь, що запис з назвою фірми Марс відсутній. 8. Відкрийте таблиці Замовлення, Список та Фірми і перевірте наявність записів з назвою фірми Марс. Зробіть висновки. Закрийте всі таблиці. Завдання 3. Створення запиту на оновлення записів 1. У вікні БД перейти на вкладку Запросы; у вікні вкладки натисніть кнопку Создать. 2. У вікні Новый запрос у списку виділіть Конструктор і натисніть кнопку ОК. Додайте в бланк запиту таблицю Замовлення і закрийте діалогове вікно Добавление таблицы. 3. Виберіть тип запиту Обновление. За допомогою цього запиту ціни будуть знижені на 10 %. 4. Задайте у бланку запиту умови оновлення значень поля Ціна (Рис.13). 5. Закрийте вікно бланку запиту. Збережіть запит з ім’ям Запит на вилучення. 6. Відкрийте запит з ім’ям Запит на вилучення. З’явиться інформаційне діалогове вікно (Рис.14), в якому натисніть кнопку Да. Після цього в наступному інформаційному діалоговому вікні (Рис.15), зважившись і ретельно подумавши, натисніть кнопку Да. Значення поля Ціна таблиці Замовлення будуть оновлені. 7. Відкрийте таблицю Замовлення і переконайтесь, що значення поля Ціна зменшились на 10%.. 8. Зробіть висновки. Закрийте всі таблиці. Завдання 4. Створення нової таблиці за допомогою запиту Нову таблицю можна створити як у поточній базі даних так і в іншій базі даних. Крім того, можна замінити існуючу таблицю, якщо створити таблицю з ім’ям вже існуючої у цій базі даних таблиці. 1. У вікні БД перейти на вкладку Запросы; у вікні вкладки натисніть кнопку Создать. 2. У вікні Новый запрос у списку виділіть Конструктор і натисніть кнопку ОК. Додати в бланк запиту таблиці Замовлення і Адреси, закрийте діалогове вікно Добавление таблицы. 3. Виберіть тип запиту Создание таблицы. Зміст запиту: за допомогою цього запиту у поточній базі даних буде створена нова таблиця Асортимент замовлень на основі таблиць Замовлення і Адреси з обчисленням кількості різновидів замовлених товарів. 4. У діалоговому вікні Создание таблицы у полі Имя таблицы ввести ім’я Асортимент замовлень (Рис.16) і натисніть кнопку ОК. 5. У вікні бланку запиту створіть поля нової таблиці з груповою операцією Count по полю Назва товару як на Рис.17. 6. Закрийте вікно конструктора, збережіть запит з ім’ям Запип на створення нової таблиці. 7. Відкрийте запит Запип на створення нової таблиці в режимі таблиці. З’явиться інформаційне діалогове вікно (Рис.18), в якому уважно вивчіть попередження інатисніть кнопку Да. Після цього в наступному інформаційному діалоговому вікні (Рис.19), зважившись і ретельно подумавши, натисніть кнопку Да. 8. Перейдіть на вкладку Таблицы поточної бази даних. Переконайтесь у наявності нової таблиці Асортимент замовлень. Відкрийте і проаналізувати зміст таблиці.Вона буде схожа на наведену на Рис.20. Зробіть висновки. 9. Закрийте таблицю. Завдання 3.Створення перехресного запиту 1. У вікні БД перейти на вкладку Запросы; у вікні вкладки натисніть кнопку Создать. 2. У діалоговому вікні Новый запрос у списку виділіть Перекрёстный запрос і натисніть кнопку ОК. 3. У діалоговому вікні Создание перекрестных таблиц (Рис.21) встановіть перемикач в положення Запросы, натисніть кнопку Далее. 4. У наступному діалоговому вікні (Рис.22) перемістіть поле Місто із списку «Доступные поля» у список «Выбранные поля». 5. В наступному діалоговому вікні (Рис.23) натисніть кнопку Далее. 6. У наступному діалоговому вікні (Рис.24) у списку Функции виберіть Сумма і натисніть кнопку Далее. Ця функція буде обчислювати суму замовлень всіх фірм даного міста. 7. У наступному діалоговому вікні, нічого не змінюючи, натисніть кнопку Готово. На екрані з’явиться таблиця, схожа на наведену на Рис.25, з важливою інформацією, яку у разі потреби можна надрукувати, натиснувши відповідну кнопку на панелі інструментів вікна програми. 8. Перегляньте і проаналізуйте таблицю. Закрити вікно таблиці. Завдання 7. Вивчення створених запитів в режимі SQL.Відкрийте кожний із створених запитів в режимі SQL. Вивчіть код кожного із створених запитів. Перепишіть коди запитів у звіт. Для цього відкрийте кожний запит у режимі Конструктор, далі меню Вид => Режим SQL. Наприклад, запит на виборку Сума замовлень мовою SQL має такий код: SELECT Адреси.Місто, Адреси.[Назва фірми], Sum(Замовлення!Ціна* Замовлення! [Кількість одиниць]) AS сума FROM Адреси INNER JOIN Замовлення ON Адреси.[Назва фірми] = Замовлення.[Назва фірми] GROUP BY Адреси.Місто, Адреси.[Назва фірми]; Приклад відображення запиту на виборку Сума замовлень в режимі SQL наведено на Рис.26. Завдання 8. Захистіть виконану роботу.
Перелік питань до захисту практичної роботи
З повагою ІЦ “KURSOVIKS”! |