Подякувати Студентському архіву довільною сумою
Admin
26.02.2023 12:38
Дякуємо, що користуєтесь нашим архівом!
Створення параметричних запитів та запитів з обрахунками
Інформація про навчальний заклад
ВУЗ:
Національний університет Львівська політехніка
Інститут:
Не вказано
Факультет:
Електронні апарати
Кафедра:
Не вказано
Інформація про роботу
Рік:
2005
Тип роботи:
Методичні вказівки до лабораторної роботи
Предмет:
Бази даних в інформаційно-комп'ютерних технологіях
Частина тексту файла (без зображень, графіків і формул):
Міністерство освіти і науки України
Національний університет "Львівська політехніка"
EMBED Word.Picture.6
“Створення параметричних запитів та запитів з обрахунками”
Методичні вказівки до лабораторного заняття з дисципліни
“Бази даних в інформаційно-комп'ютерних технологіях”
для студентів базового напрямку 6.091 “Електронні апарати”
Затверджено на засіданні кафедри ЕЗІКТ
Протокол № 2 від 12 жовтня 2009 р.
Львів 2009
Створення параметричних запитів та запитів з обрахунками. Методичні вказівки до лабораторного заняття з дисципліни "Бази даних в інформаційно-комп'ютерних технологіях” для студентів базового напрямку 6.091 “Електронні апарати”/ Укл. Л.К.Гліненко, Є.І.Яковенко.-Львів; Вид-во Нац. ун-ту "Львівська політехика" , 2009. - 16 с.
Укладачі: Л.К.Гліненко, канд. техн. наук, доц.
Є.І.Яковенко, канд. техн. наук, доц.
Відповідальний за випуск – Т.А.Смердова , канд. техн. наук, доц.
Рецензенти: В.М.Фаст, канд. техн. наук, доц.
Б.С.Вус, канд. техн. наук, доц.
Створення параметричних запитів на вибірку
1. Мета роботи
Одержання навичок роботи зі створення параметричних запитів
2. Теми для попереднього вивчення
Типи запитів на вибірку і технологія їхньої розробки; запити з параметрами та з полями, що обчислюються.
2. Стислі теоретичні відомості
2.1. Запити з параметрами
Запити з параметрами, або параметричні запити – це різновид запитів на вибірку Microsoft Access, які використовуються для вибору даних з таблиць за певною умовою (умовою відбору), яка задає один з атрибутів сутності, конкретне значення якого задається у процесі запиту. Ці запити створюються на основі певного базового запиту “з параметром”, у якому зазначений лише параметр, за яким відбирати дані, а конкретне значення параметру вводиться за запитом у процесі виконання параметричного запиту. Запити з параметрами являють собою варіанти базового запиту і відрізняються один від одного лише конкретним значенням умови відбору для того самого поля.
2.2. Створення запитів з параметрами
Запит у Access є об'єктом, що зберігається у файлі бази даних і може багаторазово повторюватися. Усі запити, що ми створювали дотепер, містили конкретні значення дат, назв, імен і т.д. Якщо потрібно повторити такий запит з іншими значеннями в умовах відбору, його потрібно відкрити в режимі Конструктора, змінити умову і виконати. Щоб не робити багаторазово цих операцій, можна створити запит з параметрами (“параметричний запит”). При виконанні такого запиту видається діалогове вікно Введіть значення параметра (Введите значение параметра, Enter Parameter Value), у якому користувач може ввести конкретне значення і потім одержати потрібний результат.
Запит з параметром може бути створений самостійно або на основі раніше створеного запиту з відповідною умовою відбору за тим самим параметром. Наприклад нехай раніше був створений запит "Відсортований список товарів", у якому на основі трьох таблиць “Постачальники”, “ТовариПостачальники” і “Товари” було створено запит з полями “КодТовару”, “МаркаТовару”, “НазваПостачальника” та “ТипТовару”. Цей запит виводить всі товари з усіма постачальниками, які постачають кожний з товарів. Припустимо, що періодично треба виводити інформацію лише про товари, які постачаються конкретним постачальником, причому яким саме, заздалегідь невідомо. Перетворимо базовий запит на запит про товари, що поставляються певним постачальником. Для цього слід:
відкрити базовий запит у режимі Конструктора.
щоб визначити параметр запиту, слід ввести у рядок Умова відбору (Условие отбора, Criteria) для стовпця "НазваПостачальника" замість конкретного значення слово чи фразу й укладіть їх у квадратні дужки, наприклад [Постачальник:] . Ця фраза буде виводитися у вигляді запрошення в діалоговому вікні при виконанні запиту.
Якщо ви хочете, щоб Access перевіряла дані, що вводяться як параметр запиту, потрібно вказати тип даних для цього параметра. Звичайно в цьому немає необхідності при роботі з текстовими полями, тому що за замовчуванням параметру присвоюється тип даних Текстовий (Текстовый, Text). Якщо ж дані в поле запиту являють собою чи дати числа, рекомендується тип даних для параметра визначати. Для цього клацніть правою кнопкою миші на вільному полі у верхній частині запиту і виберіть з контекстного меню команду Параметри (Параметры, Parameters) чи виконайте команду меню Запит, Параметри (Query, Parameters). З'являється діалогове вікно Параметри запиту (Параметры запроса, Query Parameters), представлене на рис. 1.
Рис. 1. Діалогове вікно Параметры запроса
У стовпець Параметр (Parameter) потрібно ввести назву параметра точно так, як він визначений у бланку запиту (легше всього це зробити шляхом копіювання через буфер обміну), тільки можна не вводити квадратні дужки. У стовпці Тип даних (Тип данных, Data Type) виберіть зі списку, що розкривається, необхідний тип даних. Натисніть кнопку ОК.
Щоб виконати запит, треба натиснути кнопку Запуск (Run) на панелі інструментів. Можна й просто зберегти запит, а потім запустити його з вкладки Запити вікна бази даних. При виконанні запиту з'являється діалогове вікно Введіть значення параметра (Введите значение параметра, Input Parameter Value) (рис. 2), у яке потрібно ввести значення, у даному випадку – одну з назв постачальників.. Результат виконання запиту буде містити лише ті товари, що поставляються даним постачальником.
В одному запиті можна ввести кілька параметрів. При виконанні такого запиту для кожного з параметрів будуть по черзі виводитися діалогові вікна Введіть значення параметра (Введите значение параметра) в тім порядку, у якому параметри перераховані в бланку запиту.
Рис. 2. Діалогове вікно Введите значение параметра
При використанні запиту з параметрами треба робити зрозумілими їхні імена, тому що саме вони будуть виводитися в діалоговому вікні для користувача. Крім того, користувачеві повинен бути зрозумілий зміст запиту. Наприклад, якщо в базі у таблиці “Клієнти” у полі ПІБ записані повністю ім'я, прізвище та по-батькові клієнта, то у такому порядку їх і слід вводити
2.3. Створення у запитах полів, що обчислюються
Створення запитів цікаво не тільки тим, що ви можете у вигляді однієї таблиці представити дані з декількох зв'язаних таблиць і відібрати потрібні записи з цих таблиць. Ви можете створювати стовпці в запиті, що є результатом обчислень над значеннями інших стовпців. Такі стовпці називаються такими, що обчислюються. Це істотно розширює можливості запитів. Найпростішим прикладом поля, що обчислюється, у запиті може бути поле, що поєднує ім'я і прізвище людини. Щоб створити поле, що обчислюється, потрібно увести вираз, що обчислює необхідне значення, у рядок Поле (Field) вільного стовпця бланка запиту. У даному прикладі цей вираз являє собою конкатенацію полів, що містять ім'я і прізвище співробітника, із пробілом між ними. У цьому виразі ми використовуємо посилання на поля таблиці, що у виразі укладаються у квадратні дужки. Перед виразом потрібно написати ім'я поля: ПІБ і відокремити його двокрапкою від виразу.
Вирази дуже широко використовуються при роботі з об'єктами Access. Зокрема, вирази активно використовуються в запитах для опису критеріїв вибірки записів, а також для отримання результатів обчислень з даними, які містять таблиці. Кожен вираз може містити один чи кілька операторів і одну чи кілька констант, чи ідентифікаторів функцій:
Константи — характеризують незмінні значення. Їх часто використовують для створення значень за замовчуванням і для порівняння значень у полях таблиць. Значення констант визначаються користувачем при уведенні виразу. (Наприклад, 09, країна фірми виготовлювача - Швеція.)
Ідентифікатори — це імена об'єктів у Access (наприклад, полів таблиць чи запитів), що при обчисленні виразів заміняються їхніми поточними значеннями (для визначення цієї операції звичайно використовується термін повертають). Наприклад, ідентифікатор імені поля [Household Inventory] у виразі повертає значення поля Household Inventory з поточного виділеного запису. Іменовані константи і змінні, застосовувані в програмах Visual Basic для додатків (VBA), теж є ідентифікаторами. Існує кілька вбудованих іменованих констант, що служать ідентифікаторами: True, False, Yes, No, is Null.
Функції повертають у вираз значення замість імені функції. На відміну від ідентифікаторів, більшість функцій вимагають узяти в дужки свої аргументи — ідентифікатори чи значення підвиразів. Наприклад, функція Date (), що має порожній список аргументів, повертає поточну дату.
Оператори — звичайні значки арифметичних операцій (+, -, *, /) і інші символи й абревіатури. Більшість з них еквівалентні операторам традиційних мов програмування типу Basic. Деякі — специфічні для Access чи SQL, наприклад Between чи Like. Використовувані в операції константи, ідентифікатори і функції називаються операндами.
Вираз може бути як завгодно складним. Для створення виразів у Access існує шість категорій операторів: арифметичні, оператори присвоювання, логічні оператори, оператори конкатенації, ідентифікації і порівняння зі зразком. Оператори можна продивитися, відкривши Построитель выражений.
Якщо вираз довгий, його незручно писати в рядку Поле (Field). Натисніть комбінацію клавіш <Shift>+<F2>. З'явиться діалогове вікно Область введення (Область ввода, Zoom), у якому уводити вираз зручніше. Можна також використовувати Построитель выражений, викликавши його клацанням по кнопці Побудувати (Построить, Build) на панелі інструментів або обравши команду Построить з контекстного меню, що відкривається при клацанні по будь-якому полі на рядку Условие отбора..
2.4. Створення виразів за допомогою Построителя выражений
Построитель выражений (Expression Builder) викликається всякий раз, коли в поле властивості об'єкта Access, наприклад в комірці бланка Конструктора запитів, ви клацаєте кнопку Построителя (кнопка з трьома точками) чи натискаєте кнопку Построить (Build) на панелі інструментів. Для цього слід:
Відкрити запит у режимі Конструктора запиту.
Помістіти курсор на рядок Условие отбора (Criteria) поля, для якого ви хочете установити критерій вибірки.
Натиснути кнопку Построителя выражений Построить (Build) на панелі інструментів Access для виведення вікна Построитель выражений (Expression Builder). Іншій спосіб — натисніть праву кнопку миші в рядку Условие отбора (Criteria) і виберіть з меню команду Построить (Build).
У поле виразів угорі вікна Построителя выражений уведіть необхідний вираз. Для прискорення введення оператору і запобігання синтаксичних чи орфографічних помилок можна скористатися відповідною кнопкою оператора під вікном уведення виразів або увести оператор з переліку операторів у вікні об'єктів Access. Наприклад, оператор Between вводиться саме так.
Рис. 2. Діалогове вікно Построитель выражений
Натисніть кнопку ОК для повернення в бланк запиту. У поле, де розташований курсор, Построитель выражений поміщає побудований вираз. Якщо ви зробите помилку в синтаксисі виразів, Access сповістить про неї повідомленням про помилку.
Для відображення результатів виконаного запиту натисніть кнопку Запуск (Open) на панелі інструментів.
При бажанні змінити умову відбору поверніться до режиму Конструктора, виділіть непотрібний вираз і зітріть його натисканням клавіші <Delete>.
2.5. Статистичні функції для виконання розрахунків
Статистичні функції використовуються в запитах головним чином для обчислення всіляких підсумкових значень, наприклад для числового поля таблиці можна обчислити середнє чи значення суму значень для всіх чи відібраних записів, можна порахувати кількість записів, що повертаються запитом.
Статистичні функції, які можна використовувати в запитах Access, входять до складу Access SQL. Опис цих функцій, а також типів полів, до яких вони можуть бути застосовані, наведений в таблиці 1 нижче. Перерахованих функцій звичайно досить для рішення більшості задач. Проте, при необхідності користувач може самостійно написати функцію мовою VBA, що буде виконувати складні статистичні, наукові чи інженерні розрахунки над даними, і використовувати цю функцію в запитах.
Усі статистичні функції Access SQL підтримуються стандартом ANSI SQL, але не усі з них підтримуються іншими СУБД. Наприклад, не всі серверні бази даних підтримують функції StDev(), StDev(), Var () і Var (). Тому, якщо ви плануєте надалі перенести свою базу даних на сервер, перш ніж використовувати ці функції, потрібно переконатися, чи підтримуються вони сервером. Інакше потім доведеться змінювати всі об'єкти, у яких зустрічаються ці функції.
Таблиця 1
Статистичні функції, які можна використовувати в запитах Access
Використання статистичних функцій для розрахунку підсумкових значень тісно зв'язано з застосуванням групових операцій у запиті. Групові операції дозволяють задати групи, для яких виконуються обчислення.
2.6. Обчислення на всьому діапазоні записів
Приклад запиту з обчисленням на всьому діапазоні записів можна створити на основі існуючого у базі даних "Борей" запиту"Сведения о заказах" (Oreder Details Extended), а також таблицями "Клиенты" (Customers) та"Заказы" (Orders). Такий запит зможе відповисти на питання: : "Скільки замовлень і на яку суму розмістив кожен клієнт?". Для цього нам оведеться побудувати багатотабличний запит. Для цього необхідно:
створити новий запит і додати в нього таблиці "Клиенты", "Заказы" та запит "Сведения о заказах".
перетягнути в бланк замовлення поле "Название" з таблиці "Клиенты", поле "КодЗаказа" з таблиці "Заказы", а потім поле "Отпускная цена" з запиту "Сведения о заказах";
натиснути кнопку Групповые операции (Totals) на панелі інструментів. У бланк запиту додається рядок Групповая операция (Total), який за замовчуванням містить у кожній комірці операцію Группировка (Group By);
задати в рядку Групповая операция (Total) необхідні статистичні функції. Для поля "Название" слід лишити значення Группировка (Group By), для поля "КодЗаказа" - вибрати зі списку функцію count, а для поля "Отпускная цена" — Sum (рис. 3);
натиснути кнопку Запуск (Run) на панелі інструментів, щоб подивитися результати запиту. Буде отримана таблиця, що містить список клієнтів, і для кожного клієнта вказується кількість замовлень, що він зробив, і загальна сума цих замовлень;
Запит можна зберегти під ім'ям "Підсумки по клієнтах" при його закритті.
Рис. 3. Використання статистичних функцій у запиті
2.7. Обчислення на виділених записах таблиці
Часто потрібно виконати обчислення над певним набором даних і, ґрунтуючись на них, скласти статистику. Для цього потрібно додати в запит умови для вибірки потрібного набору записів. Наприклад, ми можемо змінити попередній запит таким чином, щоб одержати дані не по всіх клієнтах, а тільки по тих, що знаходяться в Німеччині. Для цього потрібно:
відкрити запит "Підсумки по клієнтах", побудований в попередньому прикладі, у режимі Конструктора;
перетягнути поле "Страна" (Country) з таблиці "Клиенты" (Customers) в бланк запиту. В рядку Групповая операция (Total) для цього поля з'являється елемент Группировка (Group by);
замінити елемент Группировка (Group by) на елемент Условие (Where), вибравши його із списку (рис. 8.23). При цьому автоматично скидається прапорець (Show) у даному стовпці. Це означає, що в результат запиту поле "Страна" не потрапить. Якщо ви спробуєте установити цей прапорець, то при виконанні запиту буде виведене повідомлення про помилку. Дійсно, якщо ми хочемо, щоб поле "Страна" відобразилося в результаті запиту, його потрібно додати в бланк запиту ще раз, при цьому в рядку Групповая операция (Total) даного стовпця потрібно залишити елемент Группировка (Group by) та установити прапорець Вывод на экран (Show);
увести слово Германия в комірку Условие отбора (Criteria) першого стовпця "Страна" (того, який не виводиться на екран);
обрати команду Вид, Режим SQL (View, SQL View) чи клацнути по стрілці на кнопці Вид (View) і вибрати значення Режим SQL (SQL View) для виведення інструкції SQL. Вона буде виглядати в такий спосіб: SELECT Клиенты.Название, Count(Заказы.КодЗаказа) AS [CountOfKoд3a каза], Sum([Сведения о заказах].ОтпускнаяЦена) AS [SumOfОтпускная Цена], Клиенты.Страна, Клиенты.Страна FROM Клиенты INNER JOIN ([Сведения о заказах] INNER JOIN Заказы ON [Сведения о заказах].КодЗаказа = Заказы.КодЗаказа) ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE (((Клиенты.Страна)="Германия")) GROUP BY Клиенты.Название, Клиенты.Страна; У ній використовуються дві статистичні функції— Count () і Sum () — і речення WHERE, що містить умову відбору записів.
змінити це речення, замінивши назви полів, у яких ведуться розрахунки: AS [Число заказов] замість AS [CountOfКодЗаказа], AS [Общая сумма] замість AS [Sum()£ОтпускнаяЦена].
натиснути на панелі інструментів кнопку Запуск (Run) для виведення результату.
Статистичні функції можна використовувати також у полях запиту, що обчислюються. Наприклад, для створення запиту "Підсумки по клієнтах" ми могли б замість запиту "Сведения о заказах" безпосередньо використовувати таблицю "Заказано". Для цього достатньо було створити поле "Стоимость", що обчислюється. У вираз для цього поля включена функція Sum ():
Стоимость: Sum(CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100)
Тоді в рядку Групповая операция (Total) для цього стовпця треба обрати елемент Выражение (Expression). При використанні групування по полю "КодЗаказа", поле "Стоимость" буде містити загальну вартість замовлення.
Можна створити й інше поле, що обчислюється:
Стоимость: CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100,
а функцію Sum () використовувати в рядку Групповая операция (Total). Обидва варіанти будуть правильні.
При застосуванні статистичних функцій слід брати до уваги, що статистичні функції при обчисленнях не обробляють записи, що мають значення Null у тому полі, до якого вони застосовуються. Тому потрібно бути обережним при використанні функції Count (). Вона буде рахувати тільки ті записи, що не містять порожніх значень. Якщо потрібно порахувати всі записи в результуючому наборі чи групі, використовуйте функцію Nz (<ім'я поля>), що перетворить порожнє значення в 0, і тільки потім підсумовуйте.
2.8. Виконання запиту
Щоб подивитися результати запиту, досить відкрити його в режимі Таблиці. Для цього необхідно:
У вікні бази даних на панелі об'єктів вибрати ярлик Запити (Запросы, Queries).
Виділити потрібний запит у списку запитів і натиснути на кнопку Відкрити (Открыть, Open) чи двічі клацнути лівою кнопкою миші на потрібному запиті.
Після цього на екрані з'являється таблиця, що містить тільки ті записи, що задовольняють критеріям добору, зазначеним у запиті, і ті поля, що зазначені в бланку запиту. Якщо в запиті зазначений порядок сортування записів, вони виводяться на екран у відповідному порядку.
Зовні відображення результатів запиту не відрізняється від відображення таблиці. Усі режими роботи з таблицею застосовні і до результуючого таблиці запиту. З нею можна працювати так само, як і зі звичайною таблицею, зокрема не тільки переглядати, але і змінювати дані. Зміни зберігаються в записах базової таблиці, на основі якої побудований запит. Якщо запит створений на основі двох і більш зв'язаних таблиць, то не завжди стовпці в запиті можна редагувати.
Для виконання запиту з режиму Конструктора досить просто переключитися в Режим таблиці (Datasheet View) за допомогою кнопки Вигляд (Вид, View) на панелі інструментів, як ми це вже не раз робили, чи за допомогою команди Режим таблиці (Режим таблицы, Datasheet View) з меню Вид (View).
У Access 2002 запит можна відкрити не тільки в режимі Таблиці. Ви можете виконати обробку результатів запиту й одержати на їхній основі зведену таблицю чи діаграму, аналогічні тим, які можна створити на базі таблиці Excel.
3. Практичні завдання
3.1. Статистичні розрахунки у запитах
Часто виникає потреба пошуку та отримання інформації за узагальненими даними з таблиць чи розрахунку даних безпосередньо у запитах. Для цих задач застосовують групування записів за певним полем таблиці та підсумовуючі функції для записів, що групуються.
Завдання 1. Необхідно визначити суми продажів по днях. Для цього створіть запит з однієї таблиці Замовлення і відберіть у рядок Поле два поля – ДатаЗамовлення і ЗагальнаСума. Згрупуйте записи, натиснувши на панелі інструментів кнопку з зображенням суми, а для поля ЗагальнаСума параметр Группировка замініть його на функцію Sum із списку, що розкриється при клацанні лівої кнопки миші по позиції Группировка.. В результаті при перегляді запиту суми продажів будуть обраховані по днях. Запит збережіть під назвою Продажі по днях. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт.
Завдання 2. Необхідно визначити, на які суми зробили покупки різні клієнти з метою можливості надання їм знижок. Відсортуйте клієнтів за сумами замовлень, створивши запит на основі двох таблиць – Замовлення та Клієнти. У запит слід включити поля Загальна сума з таблиці Замовлення та ПІБ з таблиці Клієнти. Можна включити і КодКлієнта, якщо він десь фігурує у звітах. Аналогічно попередньому завданню, згрупуйте записи, але отримайте суми не по Датах, а по Клієнтах. В результаті при перегляді запиту суми продажів мають бути обраховані по окремих клієнтах, бажано їх відсортувати за сумою покупок, встановивши відповідну опцію у полі Сортировка. Запит збережіть під назвою Купівельна активність клієнтів. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт.
Завдання 3. Для проведення аналізу ефективності обрання певного асортименту необхідно визначити, скільки одиниць кожного товару продано за звітний період та на яку суму з подальшим виявленням впливу ціни товару на інтенсивність продажів. Створіть запит, який дасть змогу обрахувати обсяги продажів окремих номенклатурних позицій у натуральних та грошових одиницях. Базовими таблицями для цього запиту будуть таблиці Товари та ЗамовленоТовару. У запит варто включити поля НазваТовару (з таблиці Товари), Сума та Кількість (з таблиці ЗамовленоТовару) та Ціна з таблиці Товари. У полі Товари встановіть параметр Группировка, а у полях Сума та Кількість замініть його на функцію Sum із списку, що розкриється. Відсортуйте дані за сумою продажів (за убуванням), встановивши відповідну опцію у полі Сортировка. Збережіть запит під назвою Продажі окремих товарів. Запустіть запит і проаналізуйте результати. Дайте відповідь на питання, чому у звітному списку товарів відсутня інформація про товар “Тостер”. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт.
Завдання 4. Модифікуйте запит, зробивши так, щоб у результуючу множину запиту була включена і інформація про товари, які зовсім не продавалися у звітний період. Для цього скористайтеся іншим типом з'єднання базових таблиць запиту (перелік типів з'єднання виводиться у діалоговому вікні при активації зв'язку між таблицями у верхній частині вікна Конструктора запитів). Збережіть запит під назвою Продажі окремих товарів ПС. Запустіть запит і проаналізуйте результати. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт.
Завдання 5. Аналогічно модифікуйте запит Купівельна активність клієнтів так, щоб можна було отримати інформацію і про клієнтів, що не здійснили жодної покупки. Запит збережіть під назвою Купівельна активність клієнтів ПС. Запустіть запит і проаналізуйте результати. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт.
Завдання 6. На основі запиту Продажі окремих товарів ПС, створеного при виконанні завдання 4, створіть запит “Холодні товари”, який виводив би інформацію лише про найменування товарів, які жодного разу не продалися за звітний період. Для цього за допомогою Майстра запитів Або Конструктора запитів створіть запит, джерелом інформації для якого буде запит Продажі окремих товарів ПС. (Якщо ви використовуєте Майстра запитів, то у передостанньому діалоговому вікні створення простого запиту, перед або після задання його імені, але перед натисканням кнопки Готово, слід встановити перемикач «изменение структуры запроса» і перейти у вікно Конструктора запитів. У поле результуючого запиту слід включити поля НазваТовару та Sum_Кількість, а в умові відбору цього поля встановити умову is Null. Запустіть запит і переконайтеся, що він вивів потрібну Вам інформацію. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт.
3.2. Запити з параметрами
Як значення в умові відбору запиту можна задавати параметри. За кожного виконання запиту буде висуватися вимога ввести конкретне значення параметру і у залежності від уведеного значення результат вибірки буде різними.
Завдання 1. Створення запиту з параметрами (параметричного запиту)
Необхідно продивитися замовлення за певний період (у діапазоні певних дат), причому цей період завчасно невідомий. Побцудуйте запит на виведення інформації про ці замовлення.
Для цього на основі таблиць Клієнти та Замовлення побудуйте запит Замовлення за період, включивши у нього поля КодЗамовлення, ДатаЗамовлення і ЗагальнаСума з таблиці Замовлення та ПІБ з таблиці Клієнти. У рядок “Условие отбора” поля ДатаЗамовлення введіть оператор: Between [Початкова дата] And [Кінцева дата], де [Початкова дата] та [Кінцева дата] – параметри запиту. Оператор Between означає “між”. Цей текст можна або ввести з клавіатури, або побудувати за допомогою Построителя выражений. Оскільки запит завчасно “не знає” значень, він попросить Вас увести значення цих параметрів та видасть записи у діапазоні уведених дат. Запустіть запит, перевірте зовнішній вигляд вікна параметрів. У звіт включіть вигляд вікна Конструктора запитів при побудові запиту, вигляд вікна параметрів та результат виконання запиту для певного діапазону дат.
Завдання 2. Створення запиту з параметрами (параметричного запиту)
На базі запиту Прізвище_c, створеного у попередній лабораторній роботі для БД “Відділ кадрів”, створити запит, у результаті виконання якого буде виводитися Прізвище, Ім'я, По батькові й Ідентифікаційнй код визначеного співробітника. Для цього:
При обраній вкладці Запит (Запрос) клацнути по кнопці .INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_create.gif"Відкривається вікно Новий запит (Новый запрос), у якому вибрати режим створення запиту Конструктор, потім - INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_OK.gif";
Відкривається вікно Запит2: запит на вибірку (Запрос2: запрос на выборку), а потім активізується вікно Додавання таблиці (Добавление таблицы), у якому вибрати зі списку таблиць таблицю Співробітник клацанням миші по імені таблиці, а потім клацнути по кнопці INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_add.gif", після чого закрити вікно Додавання таблиці (Добавление таблицы);
За допомогою миші перемістити потрібні поля з обраної таблиці;
У стовпці Прізвище в рядку Умова відбору (Условие отбора ) ввести в квадратних дужках [Уведіть прізвище] (повідомлення, що буде виводитися на екран при виконанні запиту).
Вибрати з меню Запит (Запрос) підпункт Параметри (Параметры). У вікні Параметри запиту (Параметры запроса , що з'явилося, в стовпці Параметр (Параметр) увести те ж повідомлення без квадратних дужок. У стовпці Тип даних (Тип данных) вибрати тип Текстовий (Текстовый), Клацнути по кнопці INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_OK.gif";
Закрити запит, на питання про збереження відповісти позитивно, зберегти запит з іменем Ідентифікаційний код;
Створений запит буде мати вигляд, представлений на рисунку 4.
Виконати запит, клацнувши по кнопці INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_open.gif". У вікні, що з'явиться на екрані, Уведіть значення параметра (Введите значение параметра) ввести прізвище співробітника, інформацію про яке необхідно отримати, натиснути INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_OK.gif".
На екрані з'явиться таблиця з даними про обраного співробітника. Завершивши перегляд, закрити вікно.
Модифікуйте отриманий звіт, забезпечивши можливість вказати як прізвище, так і ім'я співробітника. Вигляд запиту у вікні конструктора та результат виконання запиту занесіть у звіт.
Завдання 3. Створення запиту з параметрами (параметричного запиту) та статистичними обрахунками
Умови завдання аналогічні умовам завдання 2 п. 3.1, з тою різницею, що нас цікавитимуть суми покупок клієнтів за певний, завчасно невідомий період. Взявши за основу запит Купівельна активність клієнтів, відкрийте його у режимі Конструктора та модифікуйте його, додавши поле ДатаЗамовлення з таблиці Замовлення, та встановивши для цього поля умову відбору Between [Початкова дата] And [Кінцева дата]. Збережіть запит під назвою Купівельна активність клієнтів за період. При спробі зберегти запит Вас повідомлять про необхідність зняти виведення на екран параметру ДатаЗамовлення, оскільки це суперечить структурі підсумкового звіту. Зніміть цей прапорець і збережіть звіт. Виконайте його. Макет запиту у вікні Конструктора та результати виконання запиту включіть у звіт. У звіт включіть вигляд вікна Конструктора запитів при побудові запиту, вигляд вікна параметрів та результат виконання запиту для певного діапазону дат.
Завдання 4. Створення запиту з параметрами (параметричного запиту) та статистичними обрахунками
Умови завдання аналогічні попередньому, але нас цікавлять лише клієнти, що здійснили за певний період покупки на певну суму, наприклад, на суму більше 100 грн. Отримайте запит Купівельна активність клієнтів 100 за період, модифікувавши запит Купівельна активність клієнтів за період введенням у рядок Умова відбору поля ЗагальнаСума відповідної умови (>100). Виконайте запит. У звіт включіть вигляд вікна Конструктора запитів при побудові запиту, вигляд вікна параметрів та результат виконання запиту для певного діапазону дат.
Модифікуйте запит так, щоб граничну величину суми покупки можна було обирати при запиті. Для цього в рядок Умова відбору поля ЗагальнаСума введіть умову >[Сума покупки у гривнях більше за]. Збережіть запит під назвою Активність клієнтів за період. Запустіть запит і оцініть результат. У звіт включіть вигляд вікна Конструктора запитів при побудові запиту, вигляд вікна параметрів та результат виконання запиту для певного діапазону дат.
Завдання 5. Створення запиту для складного звіту про співробітників
Для БД “Кадри” створити запит, який у подальшому можна буде використати при створенні підсумкового звіту про співробітників, їх посади та оклади.
При обраній вкладці Запит (Запрос) клацнути по кнопці INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_create.gif"
Відкривається вікно Новий запит (Новый запрос), у якому вибрати режим створення запиту Конструктор, потім - INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_OK.gif";
Відкривається вікно Запит3: запит на вибірку (Запрос3: запрос на выборку), а потім активізується вікно Додавання таблиці (Добавление таблицы), у якому вибрати зі списку таблиць таблицю Співробітник клацанням миші по імені таблиці, а потім клацнути по кнопці INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/b_add.gif", аналогічно додати таблиці Склад родини і Штатний розклад, після чого закрити вікно Додавання таблиці (Добавление таблицы);
З першої таблиці вибрати поля Прізвище, Ім'я, По_батькові, Посада. За допомогою миші перемістити поля на бланк запиту.
З другої таблиці вибрати поле Ідент_код, помістити його на бланк запиту.
З третьої таблиці вибрати поля Назв_Підр та Оклад і також помістити їх на бланк.
Додати в бланк розділ Групова операція (Вид -> Групові операції) (Групповая операция (Вид -> Групповые операции) ) чи виконати клацання по відповідній піктограмі INCLUDEPICTURE \d \z "Базы данных_ Лабораторные работы5_files/sigma.gif".
У стовпці Ідент_код клацнути мишею в рядку Групова операція (Групповая операция), з'явиться кнопка розкриття списку. Клацнути по ній мишею і вибрати зі списку функцію Сount (кількість записів з однаковими значеннями для даного поля).
Вікно запиту буде мати вигляд, представлений на Рисунку 5.
Закрити запит, виконавши його збереження під іменем Запит для звіту.
Виконати запит.
Результат виконання представлений на рис. 6.
Модифікуйте запит, створивши на його основі запит, що дасть змогу виводити інформацію по підрозділах. Вигляд запиту у вікні Конструктора та результат виконання включіть у звіт.
Завдання 6. Створення запиту з параметрами (параметричного запиту) та статистичними обрахунками
Умови завдання аналогічні умовам завдання 3 п. 3.1, з тією різницею, що нас цікавитимуть суми продажів певних товарів за певний, завчасно невідомий період. Створіть запит Продажі окремих товарів за період аналогічно запиту Купівельна активність клієнтів за період на основі модифікування запиту Продажі окремих товарів.
Завдання 7. Створення запиту з параметрами (параметричного запиту) та статистичними обрахунками
Необхідно продивитися суми замовлень, зроблених певним клієнтом за запитом самого клієнта, причому цей клієнт заздалегідь невідомий. Для виконання завдання візьміть за основу запит Купівельна активність клієнтів, створений у попередній роботі і параметризуйте його за полем ПІБ. Для цього в умову відбору цього поля введіть відповідний текст у квадратних дужках, маючи на увазі, що при відповіді на питання вікна параметру повинно бути зрозуміло, що слід ввести інформацію про ПІБ клієнта так, як вона міститься у таблиці Клієнти.
3.3. Запити з полями, що обраховуються
У підсумкову таблицю, яка видається запитом, можна включати додаткові поля, що обраховуються.
Завдання 1. Перевірити правильність розрахунку поля Сума у таблиці ЗамовленоТовару. Для цього побудуйте запит на основі таблиць Товари та Замовлення, включивши у нього поля КодЗамовлення, Кількість, Сума з таблиці ЗамовленоТовару та Ціна з таблиці Товари, і додайте поле НаСуму, яке має містити вираз [Кількість]*[Ціна]. Для цього у рядок поле нового, додаваного стовпця введіть НаСуму: [Кількість]*[Ціна]. При перегляді запиту значення, що зберігаються у полі Сума, мають збігатися зі значеннями обчисленого поля НаСуму. Очевидно, що поле Сума є надлишковим у таблиці ЗамовленоТовару, оскільки ці значення завжди можуть бути обраховані за допомогою запитів. Базовою для запиту є таблиця ЗамовленоТовару.
Завдання 2. Необхідно визначити суми продажів по датах способом, відмінним від попереднього пункту. Для цього створіть запит з таблиці ЗамовленоТовару та Замовлення. Відберіть поле ДатаЗамовлення з таблиці Замовлення. Згрупуйте замовлення товарів по датах. Для цього у рядку Условие отбора оберіть команду Групповые операции, у результаті з'явиться рядок Групповые операции зі значенням Группировка. Як і у попередньому завданні, додайте поле НаСуму, яке має містити вираз Sum([Кількість]*[Ціна]). Для цього у рядок поле нового, додаваного стовпця введіть На суму: Sum([Кількість]*[Ціна]). У рядку Групповые операции цього поля із списку, що розкриється, оберіть значення Выражение В результаті при перегляді запиту суми продажів окремих товарів будуть обраховані по днях.
У звіт включіть вигляд вікна Конструктора запитів при побудові запиту, вигляд вікна параметрів та результат виконання запиту для товару з певною ціною. Модифікуйте запит для обрахунку продажів окремих товарів по днях так, щоб виводилася назва товару. Для цього додайте у запит таблицю Товари і введіть з неїх у запит поле Назва. Зробіть запит з параметром Назва товару. Модифікуйте запит для обрахування загальних сум продажу по днях, включіть результати у звіт. Результати включіть у звіт (вигляд вікна Конструктора запитів, вікна параметрів та результати виконання звіту).
Завдання 3. Розрахувати суми продажів по клієнтах. Для цього додайте у запит таблицю Клієнти замість Товари, а у підсумковій таблиці поле ДатаЗамовлення замініть на поле ПІБ з таблиці Клієнти. Створіть запит з параметром ПІБ Клієнта.
У звіт включіть вигляд вікна Конструктора запитів при побудові запиту, вигляд вікна параметрів та результат виконання запиту для товару з певною ціною.
Навчальне видання
Методичні вказівки
Л.К.Гліненко, канд. техн. наук, доц.
Є.І.Яковенко, канд. техн. наук, доц.
Редактор
Ділись своїми роботами та отримуй миттєві бонуси!
0%
Оголошення від адміністратора