« Назад

Мета: придбати навички роботи зі створення результуючої таблиці, у якій відображаються тільки потрібні за умовою запиту дані з базових таблиць.

Хід роботи

Як і інші об'єкти, запити можна створювати автоматично за допомогою Конструктора або засобами SQL стандарту.

Для створення запитів до баз даних існує спеціальна мова запитів. Він називається SQL (Structured Query Language - структурована мова запитів). Але є також простий засіб, який називається бланком запиту за зразком. З його допомогою можна сформувати запит простими прийомами, перетягуючи елементи запиту між вікнами.

Пойменовані запити, збережені як окремі об'єкти бази даних, а також до яких можна звертатися в процесі роботи клієнтської частини як до таблиць називаються уявленнями (Views).

Підготовчий етап для будь-якого запиту - вибір базових таблиць для запиту.

1. Вибрати в Object Explorer вашу БД, в ній вибрати розділ Views (Уявлення). У контекстному меню уявлень вибрати пункт меню New View (Нове уявлення). Створення уявлення в режимі Конструктора починають з вибору тих таблиць бази даних, на яких буде заснований запит. Вибір таблиць виконують у вікні Add table (Додавання таблиці) натисканням миші спочатку на назві потрібної таблиці, а потім на кнопці <Add> (<Додати>). Вибрані таблиці заносяться у верхню половину бланка запиту.

У вікні Додавання таблиці зверніть увагу на наявність чотирьох вкладок: Таблиці, Уявлення, Функції, Синоніми. Вони говорять про те, що запит не обов'язково засновувати тільки на таблицях. Якщо раніше вже було створено уявлення, то новий запит можна засновувати і на ньому. Після додавання таблиць(і) закрити вікно Додавання таблиці. Результат додавання таблиць до бланку запиту показаний на рис. 1.

Рис. 1 - Приклад доданих трьох таблиць, між якими існують зв'язки

Запустити запит на виконання можна комбінацією клавіш CTRL + R. Заповнення бланка запиту за зразком:

- Бланк запиту за зразком має чотири розділи. Розділи можна включати і вимикати, викликавши в будь-якому місці конструктора уявлення контекстне меню і вибравши пункт Pane, а далі підпункт Diagram (перший розділ на рис. 1), Criteria (другий розділ на рис. 1), SQL (третій розділ на рис. 1 ), Results (четвертий розділ на рис. 1).

- У першому розділі (рис. 1) розташовані таблиці та зв'язки, на яких ґрунтується запит. У цьому розділі можна викликати контекстне меню і вибрати пункт ADD Table, щоб додати ще об'єкти в запит, або пункт Execute SQL, щоб виконати запит.

- У другому розділі (рис. 1) розташовані назви колонок (Columns), з яких інформація буде виводитися в запит, якщо поставити позначку в стовпці Output.

- У другому розділі (рис. 1) колонка Sort Type (Вид сортування) дозволяє вказати на те, що результат запиту буде сортуватися за значеннями зазначеного поля за зростанням (ASC), за зменшенням (DESC), без сортування (UnSorted).

- У другому розділі (рис. 1) колонка Sort Order (Порядок сортування) дозволяє вказати, у разі сортування за кількома полями, номерами черговість сортування. Наприклад, 1 - за вказаним полем, в першу чергу, будуть розсортовані результуючі записи запиту, 2 - якщо у полі першого сортування були однакові значення, то відповідні блоки записів будуть розсортовані по другому полю сортування і т.д.

- У другому розділі (рис.1) колонка Filter (Фільтр) дозволяє задати логічну умову, за якою відбиратимуться тільки ті записи, в яких зазначене поле, на яке накладено Фільтр (іншими словами Умова), відповідні фільтри.

- У другому розділі (рис. 1) колонки OR ... (АБО ...)

- У третьому розділі (рис. 1) автоматично на дії у другому розділі формується структура SQL запиту. Можна її правити «вручну», якщо знати правила формування операторів в SQL стандарті.

- У четвертому розділі (рис. 1) після запуску запиту формується результат.

- Запуск запиту виконують, використовуючи <CTRL> + <R>. При запуску утворюється результуюча таблиця.

Щоб повторно відкрити закрите уявлення, потрібно в його контекстному меню вибрати пункт Modify.

Завдання:

Завдання 1. Створення простого запиту. Створити нове уявлення. Додати в нього таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. Зберегти запит з назвою ЗАПИТ 1 (<CTRL> + <S>). Виконати запит (<CTRL> + <R>). Перевірити результат. Закрити запит.

Завдання 2. Створення запиту з параметром. Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. У колонці Фільтр навпроти зазначеного в картці поля набрати зазначену умову. Зберегти запит з назвою ЗАПИТ 2. Виконати запит. Визначити для себе, чим відрізняється результат від попереднього запиту. Закрити запит.

Завдання 3. Створення запиту з параметром LIKE. Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. У колонці Фільтр навпроти зазначеного в картці поля набрати зазначену умову (наприклад, LIKE '% ва%', що означає вибрати всі записи у яких в зазначеному полі в будь- якому місці зустрічається комбінація букв «ва»). Зберегти запит з назвою ЗАПИТ 3. Виконати запит 6 разів, змінюючи варіанти, вибрані з картки, після команди LIKE.

Пояснення для LIKE.

Спеціальні символи	Позначення
_	Будь-який одиночний символ (наприклад, LIKE 'П_шк_н' - вибрати всі значення полів, у яких 2 і 5 символ будь-який).
%	Будь-яка кількість символів або відсутність символу
[список]	Будь-який одиночний символ, що входить в список
[^список]	Будь-який одиночний символ, крім тих, що входить в список (наприклад, LIKE '[^пл]%' або LIKE '[^п,л]%' - вибрати всі значення полів, крім тих, у яких перша буква «п» або «л» ).

Список можна оформляти через кому - виходить конкретна вибірка або через дефіс - виходить діапазон.

Зберегти запит. Закрити запит.

Завдання 4. Обчислення в запитах. У запитах можна додавати нові поля, які будуть обчислювати результати на основі даних з інших полів (наприклад, є поля "ціна за одиницю" і "кількість товару", за ними можна отримати через множення загальну вартість кожного товару).

Поле, вміст якого є результатом розрахунку по вмісту інших полів, називається обчислюваним полем, слід звернути увагу на те, що поле, що обчислюється існує тільки в результуючій таблиці-запиті.

Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці.

Заповнити бланк полями, зазначеними у картці.

У першій порожній комірці колонки з назвами полів у другому розділі бланка запиту, введіть формулу з картки. У сусідній комірці колонки Alias (альтернативне ім'я колонки) введіть назву нової колонки, зазначене в картці. Встановити прапорець у колонці Output (виведення на екран). Виконати запит. Переглянути результат в новому стовпці. Зберегти запит з назвою ЗАПИТ 4.

Завдання 5. Запит за двома таблицями. Такі запити допомагають замість кодів полів підставляти значення з іншої таблиці.

Створити нове уявлення. Додати на бланк запиту по черзі таблиці, зазначені у картці. Заповнити бланк полями, зазначеними у картці. Виконати запит. Проаналізувати результат. Зберегти запит з назвою ЗАПИТ 5.

Завдання 6. Підсумкові запити (Запити з угрупованням)

Запити дозволяють не тільки відбирати потрібну інформацію з таблиць і обробляти її шляхом створення нових (обчислюваних) полів, а й виробляти так звані підсумкові обчислення.

Прикладом підсумкового обчислення може служити сума усіх значень в якійсь групі записів або їхнє середнє значення, хоча крім суми і середнього значення існують і інші підсумкові функції.

Оскільки підсумкові функції для одного запису не мають сенсу і існують тільки для групи записів, то попередньо записи треба згрупувати за якоюсь ознакою.

Підсумкові запити створюють на основі відомого нам бланка запиту за зразком, тільки тепер в ньому з'являється додатковий рядок - Групування.

Для введення цієї колонки в бланк треба в першому або другому розділі клацнути в контекстному меню на ADD group by (Додати групування). Якщо хочете відмовитися від групування, то знову викличте контекстне меню і виберіть пункт Remove group by (Видалити групування).

Далі після додавання групування все відбувається дуже просто.

- У тих полях, за якими проводиться групування, треба встановити (або залишити) функцію Group by (групування).

- У тих полях, за якими слід провести підсумкове обчислення, треба в колонці групування відповідного поля розкрити список і вибрати одну з декількох підсумкових функцій.

- В комірці Групування можна вказати лише одну підсумкову функцію. А як бути, якщо треба знайти і суму, і середнє, і максимальне значення, і ще щось по одному і тому ж полю? Рішення просте: одне і те ж поле можна включити в бланк запиту за зразком декілька разів.

Приклади підсумкових обчислень (агрегатних функцій): AVG - середнє по групі;

MIN - мінімальна в групі; MAX - максимальне в групі;

COUNT - кількість записів в групі; SUM - сума по групі.

В одну групу об'єднуються записи, у яких в поле, на якому встановлено групування, зберігаються однакові значення.

Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. Додати колонку Group By (Групові операції). Встановити в цій колонці для кожного поля зазначені в картці значення. Виконати запит. Проаналізувати результат. Зберегти запит з назвою ЗАПИТ 6. Закрити запит.

Завдання складних умов відбору

Якщо в умовах відбору використовується кілька полів, то вони можуть з'єднуватися оператором "І" або "АБО". Якщо умови пошуку записані в колонці Filter, то вважається, що вони з'єднані оператором "І" ("AND"). Якщо умови пошуку записані в стовпцях OR, то вважається, що вони з'єднані оператором "АБО" ("OR").

При створенні запиту для вказівки конкретних типів значень використовуються:

- Прямі апострофи (' ') для строкових значень і для дат, при цьому формат дати наступний 'dd-mon-yyyy', де dd - число місяця цифрами, mon - назва місяця трьома першими літерами англійської назви місяця, yyyy - рік цифрами.

- Квадратні дужки [ ] використовуються для зазначення в них назв полів і таблиць, в яких присутні прогалини.

Завдання 7. Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. Наберіть умову відбору, вказану в картці. Зберегти запит з назвою ЗАПИТ 7. Виконати запит, проаналізувати результат. Закрити запит.

Завдання 8. Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. Наберіть умову відбору, вказану в картці. Наберіть умову АБО, вказану в картці. Зберегти запит з назвою ЗАПИТ 8. Виконати запит, проаналізувати результат. Порівняти результат з попереднім запитом. Закрити запит.

Завдання 9. Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. Наберіть умову відбору, вказану в картці. Виконайте запит. Поверніться в конструктор. Змініть умову, як зазначено в картці.

Виконайте запит. Проаналізуйте відмінності між двома варіантами виконання. Зберегти запит з назвою ЗАПИТ 9. Закрити запит.

Завдання 10. Створити нове уявлення. Додати на бланк запиту таблицю, зазначену в картці. Заповнити бланк полями, зазначеними у картці. Наберіть умову відбору, вказану в картці. Виконайте запит. Поверніться в конструктор. Змініть умову, як зазначено в картці.

Виконайте запит. Проаналізуйте відмінності між двома варіантами виконання.

Поверніться в конструктор. Змініть умову, як зазначено в картці.

Виконайте запит. Проаналізуйте відмінності між трьома варіантами виконання.

Зберегти запит з назвою ЗАПИТ 10. Закрити запит.

 

Картка завдань до лаб. раб. №3

Варіант 2

Завдання 1. Таблиця Комплектуючі.

Поля Назва обладнання, Дата покупки, Вартість покупки.

Завдання 2. Таблиця Комплектуючі.

Поля Назва обладнання, Серійний номер, Вартість покупки.

Умова відбору для поля Вартість покупки: (>100)AND(<1000)

Завдання 3. Таблиця Комплектуючі.

Поля Назва обладнання, Призначення, Період зносу.

Варіанти умов запуску для команди LIKE (набирати на російській):

Варіант 1. П%

(Означає, вивести в результат усі записи, у яких найменування починається на ‘П’).

Варіант 2. _о%

(Означає, вивести в результат усі записи, у яких в найменуванні друга літера ‘о’).

Варіант 3. [М, У]%

(Означає, вивести в результат усі записи, у яких найменування починається на ‘М’ або на ‘У’).

Варіант 4. [Л-С]%

(Означає, вивести в результат усі записи, у яких найменування починається на ‘Л’, ‘М’,’Н’, ‘О’, ‘П’, ‘Р’, ‘С’).

Варіант 5. [^Л-С]%

(Означає, вивести в результат усі записи, у яких найменування починається на любую літеру крім діапазону літер ‘Л’-’С’).

Варіант 6. %[ю,у]%

(Означає, вивести в результат усі записи, у яких в найменуванні зустрічається літера ‘ю’ або ‘у’).

Завдання 4. Таблиця Комплектуючі.

Поля Назва обладнання, Вартість покупки, Період зносу.

У пустій комірці колонки Column: [Вартість покупки]/ [Період зносу] У відповідній комірці колонки Alias: Амортизація

Завдання 5. Таблиці Комплектуючі, Довідник постачальника.

Поля із таблиці Товари: Назва обладнання, Код постачальника.

Поля із таблиці Довідник виробника: Постачальник, Телефон постачальника.

Завдання 6.

Таблиця Комплектуючі.

Цей запит покаже, які комплектуючі за призначенням у нас є (згрупує дані за призначенням), і для кожної групи комплектуючих підрахує середню вартість (функція AVG) і саму ранню дату покупки (функція MIN) якого-небудь комплектуючого із групи.

Завдання 7. Таблиця Комплектуючі.

Поля Призначення, період зносу, Назва обладнання.

Умова відбору для поля Призначення: Between 'к' And 'п' (Вивести всі товари, найменування яких знаходяться в діапазоні від ‘к’ до ‘п’)

Завдання 8. Таблиця Комплектуючі.

Поля Призначення, Вартість покупки, Назва обладнання.

Умова відбору для поля Вартість покупки: >=25000

В рядку ИЛИ для поля Призначення: Between 'к' And 'п'

В рядку Сортування для поля Призначення: за зростанням

Завдання 9. Таблиця Довідник постачальника. Поля: Постачальник, Телефон постачальника. Умова відбору для поля Постачальник: 'Ч%'

Нова умова відбору для поля Постачальник: <'Ч%'

Завдання 10. Відкрийте таблицю Довідник постачальника в Режимі таблиці. Наберіть довільні дати створення фірм. Збережіть зміни і закрийте таблицю.

Запит по таблицям Комплектуючі, Довідник постачальника.

Поля Назва обладнання, Постачальник, Дата створення постачальника. Умова відбору для поля Дата створення постачальника: >'12-may-2002' Змінена умова відбору для поля Дата створення постачальника: <'12-may-2002'

Змінена умова відбору для поля Дата створення постачальника: Between '12-may-2002' And GetDate()

де функція GetDate() - повертає значення поточної дати.