Тема. Створення та використання звичайних запитів на вибірку даних

Лабораторна робота № 5.

Тема.	Створення та використання звичайних запитів на вибірку даних.
Мета.	Формування вмінь та навичок створення запитів на вибірку даних в режимі конструктора для аналізу даних таблиць. Усвідомлення послідовності етапів виконання запитів на вибірку даних. Вдосконалення вмінь створення та коригування об'єктів додатків СУБД MS Access.

1. 
   Завантажте Access, відкрийте розроблену раніше БД ТуристичніМаршрути.
   
2. 
   Для зберігання інформації про клієнтів туристичної фірми створіть таблицю Клієнти в режимі конструктора з наступними полями:
   

   Назва поля	Тип даних
   КодКлієнта	Счетчик
   НазваКлієнта	Текстовый
   Адреса	Текстовый
   Характеристика	Поле MEMO
   Фотокартка	Поле объекта OLE
   ДатаРеєстрації	Дата/Время

   1. 
      Для створених полів задайте властивості Подпись та Описание;
      
   2. 
      Задайте додаткові властивості полів таблиці Клієнти, забезпечивши:
      
      1. 
         Обов’язкове введення назви клієнта
         
      2. 
         Можливість швидкого сортування та пошуку згідно з полем НазваКлієнта;
         
      3. 
         Встановлення активної дати по замовчуванню в полі ДатаРеєстрації при введенні даних нового клієнта.
         
   3. 
      Задайте для поля КодКлієнта властивість ключового поля, відмітивши в контекстному меню поля властивість Ключевое поле
      
3. 
   Завершіть створення таблиці, закривши її вікно. Підтвердіть збереження таблиці та дайте їй назву Клієнти.
   
4. 
   Для реєстрації замовлень турів клієнтами туристичної фірми створіть таблицю ЗамовленняТурів з наступною структурою:
   

   Назва поля	Тип даних
   КодЗамовлення	Счетчик
   НомерЗамовлення	Числовой
   ДатаЗамовлення	Дата/время
   КодКлієнта	Числовой
   КодТуру	Числовой
   ДатаВиїзду	Дата/время
   Вартість	Денежный
   ВнесенийПлатіж	Денежный

   1. 
      Для редагованих полів задайте властивості Подпись та Описание;
      
   2. 
      Задайте додаткові властивості полів таблиці ЗамовленняТурів, забезпечивши:
      

1. 1. 
      Встановлення активної дати по замовчуванню в полі ДатаЗамовлення при введенні даних нового замовлення;
      
   2. 
      Значення по замовчуванню для поля ДатаЗамовлення
      
   3. 
      Підстановку у поле КодКлієнта значення з таблиці Клієнти;
      
   4. 
      Підстановку у поле КодТуру значення з таблиці Тури;
      
   5. 
      Порожнє значення по замовчуванню для полів НомерЗамовлення, КодКлієнта та КодТуру;
      
   6. 
      Можливість швидкого сортування та пошуку згідно з полем ДатаВиїзду;
      
   7. 
      Задайте для поля КодЗамовлення властивість ключового поля, відмітивши в контекстному меню поля властивість Ключевое поле
      
2. 
   Завершіть створення таблиці, закривши її вікно. Підтвердіть збереження таблиці та дайте їй назву ЗамовленняТурів.
   
3. 
   Відкрийте схему даних. Якщо у ній відсутні таблиці Клієнти та ЗамовленняТурів, добавте ці таблиці у схему даних та забезпечте цілісність даних.
   
4. 
   Перейдіть на закладку Формы.
   
5. 
   Створіть з допомогою майстра форми для таблиць Клієнти та ЗамовленняТурів. Внесіть дані про 5-7 клієнтів та 5-7 замовлень турів.
   
6. 
   Перейдіть на закладку Запросы.
   

10. 
    Створіть запит АлфавітнийСписокКраїн в режимі конструктора для формування алфавітного списку назв країн. Для цього
    
    1. 
       Перейдіть в режим конструктора для створення запиту одним з двох способів двічі клацніть по піктограмі Создание запроса в режиме конструктора або натисніть кнопку Создать та оберіть в списку варіант створення запиту Конструктор
       
    2. 
       Для формування джерела даних запиту у вікні Добавление таблицы виділіть таблицю Країни та натисніть кнопку Добавить. Закрийте вікно Добавление таблицы
       
    3. 
       Для формування списку країн в перший стовпець бланку запиту внесіть поле НазваКраїни одним з двох способів перетягніть поле при натиснутій лівій кнопці мишки з образу таблиці Країни у верхній частині вікна в бланк запиту у нижній частині вікна або виберіть назву поля зі списку в рядку Поле першого стовпця бланку запиту;
       
    4. 
       В рядку Сортировка для поля НазваКраїни оберіть зі списку значення по возрастанию для відповідного впорядкування назв країн при виконанні запиту
       
    5. 
       Закрийте вікно конструктора та збережіть запит під назвою АлфавітнийСписокКраїн.
       
11. 
    Виділіть створений запит та завантажте його на виконання різними способами
    
    1. 
       За допомогою кнопки Открыть
       
    2. 
       Натисненням клавіші Enter
       
    3. 
       Подвійним натисненням лівої кнопки мишки на зображенні перед назвою запиту
       
    4. 
       Перейдіть в режим конструктора, натиснувши кнопку Конструктор, та оберіть в меню Вид підменю Режим таблицы.
       
12. 
    Перегляньте текст створеного запиту в режимі SQL. Для цього перейдіть в режим конструктора та виберіть пункти меню Вид – Режим SQL. Обґрунтуйте структуру тексту запиту.
    
13. 
    Завантажте запит в режимі конструктора та натисніть кнопку . З'ясуйте призначення всіх пунктів списку даної кнопки.
    
14. 
    Самостійно створіть аналогічні запити
    
    1. 
       АлфавітнийСписокНаселенихПунктів – для відображення в алфавітному порядку назв населених пунктів з зазначенням типу населеного пункту, країни та місця розташування
       
    2. 
       АлфавітнийСписокТуристичнихПунктів – для відображення в алфавітному порядку назв туристичних пунктів з зазначенням відповідного населеного пункту.
       

15. 
    Створіть запит ВартістьТуруНаОднуОсобу з переліком назв турів, дат встановлення вартості, умов проїзду, загальної вартості, кількості місць, та вартості туру на одну особу. Для цього:
    
    1. 
       Розпочніть створення запиту в режимі конструктора
       
    2. 
       Сформуйте джерело даних запиту з записів таблиць Тури та ЦіниНаТури;
       
    3. 
       Перетягніть в перший стовпець бланку запиту поле НазваТуру з таблиці Тури
       
    4. 
       У другий стовпець перетягніть поле ДатаВстановлення з таблиці ЦіниНаТури
       
    5. 
       У третій стовпець перетягніть поле Вартість з таблиці ЦіниНаТури;
       
    6. 
       У четвертий стовпець перетягніть поле КількістьМісць з тієї ж таблиці;
       
    7. 
       В рядку поле п’ятого стовпця бланку запиту введіть формулу Вартість/КількістьМісць;
       
    8. 
       Завантажте запит для перевірки його дієздатності. Знову поверніться в режим конструктора. Чому перед формулою з'явився підпис Выражение1? Яку роль він відіграє?
       
    9. 
       Відредагуйте текст п’ятого стовпця до вигляду
       НаОднуОсобу: [Вартість]/[КількістьМісць]. Перевірте функціональність запиту
       
    10. 
        Для заокруглення числа до двох знаків після коми відредагуйте текст п’ятого стовпця до вигляду НаОднуОсобу: Round([Вартість]/[КількістьМісць];2). Перевірте функціональність запиту;
        
    11. 
        Задайте сортування по зростанню записів запиту по полях НазваТуру та ДатаВстановлення;
        
    12. 
        Закрийте запит та збережіть внесені зміни.
        
16. 
    Створіть запит ЗалишкиОплатиПоЗамовленнях з переліком назв турів, клієнтів, вартостей турів, сум внесених платежів та неоплачених залишків (джерело даних запиту сформуйте на основі таблиць Тури, ЗамовленняТурів, Клієнти).
    

17. 
    Створіть запит для відображення недоплачених повністю замовлень. Для цього:
    
    1. 
       Відкрийте запит ЗалишкиОплатиПоЗамовленнях в режимі конструктора;
       
    2. 
       В рядку Условие отбора поля Залишок введіть значення >0;
       
    3. 
       Задайте сортування за спаданням по полю Залишок;
       
    4. 
       Збережіть даний запит з назвою НедоплаченіЗамовлення.
       

18. 
    Створіть запит КількостіЗамовленьПоТурах для відображення алфавітного списку назв турів та кількостей замовлень по турах, розміщених у порядку спадання, виконуючи наступні дії
    
    1. 
       Для створення запиту відкрийте конструктор та сформуйте джерело даних запиту з таблиць ЗамовленняТурів та Тури
       
    2. 
       В перший стовпець бланку запиту перетягніть поле НазваТуру з таблиці Тури
       
    3. 
       В другий стовпець бланку запиту перетягніть поле КодЗамовлення з таблиці ЗамовленняТурів
       
    4. 
       Для другого стовпця встановіть ознаку сортування за спаданням;
       
    5. 
       Для визначення кількості замовлень по кожному туру згрупуйте записи джерела даних за назвою туру і підрахуйте для кожної групи кількість відповідних кодів замовлень. Для цього
       
       1. 
          Натисніть кнопку (Групповые операции) на панелі інструментів Конструктор запросов, в контекстному меню бланку запиту чи в підменю Вид головного меню щоб сформувати саме підсумковий запит
          
       2. 
          В рядку Групповая операция, що з'явиться на екрані, оберіть зі списку для поля НазваТуру значення Группировка (встановлюється по замовчуванню), а для поля КодЗамовлення – значення Count.
          
    6. 
       Перегляньте текст сформованого запиту в режимі SQL. Обґрунтуйте його структуру
       
    7. 
       Збережіть створений запит та переконайтеся в його дієздатності.
       
19. 
    Самостійно створіть запити:
    
    1. 
       КількостіЗамовленьПоКлієнтах для відображення кількості замовлень турів по кожному з клієнтів;
       
    2. 
       КількостіТурПунктівПоТурах, в якому відображається назва туру, тривалість в днях та кількість туристичних пунктів у даному турі.
       
20. 
    Створіть запит СумиЗамовленьПоКлієнтах для відображення впорядкованих в алфавітному порядку назв клієнтів та загальних сум внесених ними платежів. Для цього
    
    1. 
       Розпочніть створення запиту в режимі конструктора та сформуйте його джерело даних з таблиць Клієнти та ЗамовленняТурів;
       
    2. 
       В перший стовпець бланку перетягніть поле НазваКлієнта з таблиці Клієнти;
       
    3. 
       У другий стовпець перетягніть поле ВнесенийПлатіж з таблиці ЗамовленняТурів;
       
    4. 
       Для першого стовпця встановіть ознаку сортування за зростанням;
       
    5. 
       Для визначення суми внесених платежів по кожному клієнту згрупуйте записи джерела даних за назвою туру. Підрахуйте для кожної групи загальну суму внесених платежів. Для цього
       

1. 1. 1. 
         В рядку Групповая операция, що з'явився на екрані, оберіть зі списку для поля ВнесенийПлатіж значення Sum.
         
   2. 
      Перегляньте текст сформованого запиту в режимі SQL. Обґрунтуйте його структуру
      
   3. 
      Збережіть створений запит та переконайтеся в його дієздатності.
      
2. 
   Самостійно створіть запит РейтингТурів для відображення загальної суми внесених платежів (виведених у порядку спадання) по кожному туру.
   
3. 
   Створіть запит для виведення загальних сум замовлень турів за введений період з зазначенням назв турів. Для цього
   
   1. 
      Відкрийте конструктор та сформуйте джерело даних запиту з таблиць ЗамовленняТурів та Тури
      
   2. 
      В перший стовпець бланку перетягніть поле ВнесенийПлатіж з таблиці ЗамовленняТурів;
      
   3. 
      Оскільки запит повинен аналізувати замовлення лише за введений період, то перетягніть в другий стовпець запиту поле ДатаЗамовлення з таблиці ЗамовленняТурів та вкажіть для нього в рядку Групповая операция значення Условие, а в рядку Условие отбора введіть вираз between [Введіть початкову дату] and [Введіть кінцеву дату];
      
   4. 
      Для забезпечення коректності введених параметрів задайте для них тип даних. Для цього відкрийте вікно Параметры запроса при виборі пункту головного меню Запрос – Параметры...чи аналогічного пункту контекстного меню області джерела даних запиту, заповніть виведену таблицю згідно наведеного зразка та натисніть OK.
      

      Параметр	Тип данных
      Введіть початкову дату	Дата/время
      Введіть кінцеву дату	Дата/время

   5. 
      Завантажте запит на виконання та переконайтеся у функціонуванні перевірки введених параметрів на відповідність вказаним типам даних.
      

23. 
    Стисніть створену БД.
    
24. 
    Завершіть роботу Access.
    

1. 
   Яка різниця між дією запиту та операцією пошуку?
   
2. 
   Як двома способами перейти в режим конструктора для створення запиту?
   
3. 
   Які поля відображаються в результаті виконання запиту? Де записуються назви цих полів в режимі SQL?
   
4. 
   Як здійснити сортування полів при виконанні запиту? Як сортуються дані при вказуванні ознаки сортування для багатьох полів? Де записуються назви цих полів в режимі SQL?
   
5. 
   Як здійснити відбір записів джерела даних при виконанні запиту? Де описуються критерії відбору записів в режимі SQL?
   
6. 
   Як створити обчислювальне поле в запиті? Значення яких полів може використовуватися при формуванні обчислювального поля? Коли слід створювати обчислювальне поле, а коли – задавати параметри відбору записів джерела даних?
   
7. 
   Для чого використовуються параметри при завантаження запиту?
   

1. 
   АлфавітнийСписокКраїн;
   
2. 
   АлфавітнийСписокНаселенихПунктів;
   
3. 
   АлфавітнийСписокТуристичнихПунктів;
   
4. 
   ВартістьТуруНаОднуОсобу;
   
5. 
   ЗалишкиОплатиПоЗамовленнях;
   
6. 
   КількостіЗамовленьПоКлієнтах;
   
7. 
   КількостіЗамовленьПоТурах;
   
8. 
   КількостіТурПунктівПоТурах;
   
9. 
   НедоплаченіЗамовлення;
   
10. 
    РейтингТурів;
    
11. 
    СумиЗамовленьЗаПеріод;
    
12. 
    СумиЗамовленьПоКлієнтах;
    
13. 
    СумиЗамовленьПоТурах.