Практична робота 4: Створення запитів і фільтрів в середовищі SQL Server Management Studio

Мета: навчитися створювати запити і фільтри.

Перейдемо до створення статичних запитів. У оглядачі об'єктів "Microsoft SQL Server 2008" всі запити БД знаходяться в папці "Представления" (рис. 4.1).

Рис. 4.1.

Створимо запит "Запрос Студенты+Специальности", що зв'язує таблиці "Студенты" і "Специальности" по полю зв'язку "Код специальности". Для створення нового запиту необхідно в оглядачі об'єктів у БД "Студент" клацнути ПКМ по папці "Представления", потім в меню вибрати пункт "Создать представления ". З'явиться вікно "Добавление таблицы", призначене для вибору таблиць і запитів, що беруть участь в новому запиті (рис. 4.2).

Рис. 4.2.

Додамо в новий запит таблиці "Студенты" і "Специальности". Для цього у вікні "Добавление таблицы" виділіть таблицю "Студенты" і натисніть кнопку "Добавить". Аналогічно додайте таблицю "Специальности". Після додавання таблиць закрийте вікно "Добавление таблицы" натиснувши кнопку "Закрить". З'явиться вікно конструктора запитів.

Рис. 4.3.

Зауваження: Вікно конструктора запитів складається з наступних панелей:

1.   Схема данных - відображає поля таблиць і запитів, що беруть участь у запиті, дозволяє вибирати відображувані поля, дозволяє встановлювати зв'язки між учасниками запиту за спеціальними полях зв'язку. Ця панель вмикається та вимикається наступною кнопкою на панелі інструментів

2.   Таблица отображаемых полей - показує відображувані, дозволяє задавати їм псевдоніми, дозволяє встановлювати тип сортування записів по одному або декількох полях, дозволяє задавати порядок сортування, дозволяє задавати умови відбору записів у фільтрах. Також ця таблиця дозволяє змінювати порядок відображення полів у запиті. Ця панель вмикається та вимикається наступній кнопкою на панелі інструментів

3.   Код SQL - код створюваного запиту на мові T-SQL. Ця панель вмикається та вимикається наступній кнопкою на панелі інструментів

4.   Результат - показує результат запиту після його виконання. Ця панель вмикається та вимикається наступній кнопкою на панелі інструментів

Зауваження: Якщо необхідно знову відобразити вікно "Добавляние таблицы" для додавання нових таблиць або запитів, то для цього на панелі інструментів "Microsoft SQL Server 2008" потрібно натиснути кнопку

Зауваження: Якщо необхідно видалити таблицю або запит зі схеми даних, то для цього потрібно клацнути ПКМ і в меню вибрати пункт "Удалить".

Тепер перейдемо до зв'язування таблиць "Студенты" і "Специальности" по полях зв'язку "Код специальности". Щоб створити зв'язок необхідно в схемі даних перетягнути мишею поле "Код специальности" таблиці "Специальности" на таке ж поле таблиці "Студенты". Зв'язок відобразиться у вигляді ламаної лінії з'єднує ці два поля зв'язку.

Зауваження: Якщо необхідно видалити зв'язок, то для цього необхідно клацнути по ній ПКМ і в меню вибрати пункт "Удалить".

Зауваження: Після зв'язування таблиць (а також при будь-яких змінах в запиті) в області коду T-SQL буде відображатися T-SQL код редагованого запиту.

Тепер визначимо поля, відображувані при виконанні запиту. Відображувані поля позначаються галочкою (зліва від імені поля) на схемі даних, а також відображаються в таблиці видимі поля. Щоб зробити поле відображуваним при виконанні запиту необхідно клацнути мишею по порожньому квадрату (зліва від імені поля) на схемі даних, в квадраті з'явиться галочка.

Зауваження: Якщо необхідно зробити поле невидимим при виконанні запиту, то потрібно прибрати галочку, розташовану зліва від імені поля на схемі даних. Для цього просто клацніть мишею по галочці.

Зауваження: Якщо необхідно відобразити всі поля таблиці, то необхідно встановити галочку зліва від пункту "* (Все поля)", що належить відповідній таблиці на схемі даних.

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

Або клацніть ПКМ в будь-якому місці вікна конструктора запитів і в меню виберіть пункт " Выполнить SQL ". Результат виконання запиту з'явитися у вигляді таблиці в області результату.

Зауваження: Якщо після виконання запиту результат не з'явився, а з'явилося повідомлення про помилку, то в цьому випадку перевірте, чи правильно створена зв'язок. Ламана лінія зв'язку повинна з'єднувати поля "Код Специальности" в обох таблицях. Якщо лінія зв'язку з'єднує інші поля, то її необхідно видалити і створити заново, як це описано вище.

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

       

розташованої у верхньому правому куті вікна конструктора (над схемою даних). З'явиться вікно з питанням про збереження запиту (рис. 4.4).

Рис. 4.4.

У даному вікні необхідно натиснути кнопку "Да". З'являється вікно "Выбор имени" (рис. 4.5).

Рис. 4.5.

У даному вікні задамо ім'я нового запиту "Запит Студенты + Специальности" і натиснемо кнопку "Ok". Запит з'явиться в папці "Представления" БД "Студент" в провіднику об'єктів (рис. 4.5).

 

Рис. 4.5.

Перевіримо роботу створеного запиту поза конструктора запитів. Запустимо новостворений запит "Запрос Студенты + Специальности" без використання конструктора запитів. Для виконання вже записаний запит необхідно клацнути ПКМ за запитом та в меню вибрати пункт "Выбрать первые 1000 строк". Виконайте цю операцію для запиту "Запрос Студенты + Специальности".

Перейдемо до створення запиту "Запрос Студенты + Оценки". У оглядачі об'єктів у БД "Студент" клацніть ПКМ по папці "Представления", потім в меню оберіть пункт "Создать представление". З'явитися вікно "Добавление таблицы". У запиті "Запрос Студенты + Оценки" ми пов'язуємо таблиці "Студенты" і "Оценки" по полях зв'язку "Код студента". Отже, у вікні "Добавление таблицы " в новий запит додаємо таблиці "Студенты" і "Оценки". Більш того, в даному запиті таблиця "Оценки" зв'язується з таблицею "Предметы" не по одному полю, а по трьох полях. Тобто поля "Код предмету 1", "Код предмету 2" і "Код предмету 3" таблиці "Оценки" пов'язані з полем "Код предмету" таблиці "Предметы". За цього додамо в запит три екземпляри таблиці "Предметы" (по одному примірнику для кожного поля зв'язку таблиці Оценки). У підсумку в запиті повинні брати участь таблиці "Студенты", "Оценки" і три екземпляри таблиці "Предметы" (в запиті вони будуть називатися "Предметы", "Предметы_1" і "Предметы_2"). Після додавання таблиць закрийте вікно "Добавление таблицы", з'явиться вікно конструктора запитів.

У вікні конструктора запитів встановіть зв'язки між таблицями і визначте відображувані поля (рис. 4.6).

Рис. 4.6.

Тепер змінити порядок полів, що відображаються у запиті, для цього в таблиці видимі поля необхідно перетягнути поля мишею вгору або вниз за заголовок рядка таблиці. Розташуйте відображувані поля в таблиці відображуваних полів (рис. 4.7).

Рис .4.7.

Задайте псевдоніми для кожного з полів, просто записавши псевдоніми в стовпці "Псевдоним" таблиці видимі поля.

Перевірте працездатність нового запиту, виконавши його. Зверніть увагу на те, що реальні назви полів були замінені їх псевдонімами. Закрийте вікно конструктора запитів. У вікні "Выбор имени" задайте ім'я нового запиту "Запрос Студенты + Оценки" (рис. 4.8).

Рис. 4.8.

Перевірте працездатність нового запиту поза конструктора. Для цього запустіть запит. Результат виконання запиту "Запрос Студенты + Оценки"

(рис. 4.9).

Рис. 4.9.

На основі запиту "Запрос Студенты + Специальности" створимо фільтри, що відображають студентів окремих спеціальностей. Створіть новий запит. Так як він буде заснований на запиті "Запрос Студенты + Специальности", то у вікні "Добавление таблицы" перейдіть на вкладку "Представления" і додайте в новий запит "Запит Студенты + Специальности". Потім закрийте вікно "Добавление таблицы" (рис. 4.11).

Рис. 4.11.

У вікні конструктора запитів визначте в якості полів, що відображаються всі поля запиту "Запит Студенты + Специальности" (рис. 4.12).

Рис. 4.12.

Зауваження: Для відображення всіх полів запиту, в даному випадку, ми не можемо використовувати пункт "* (Все поля)". Так як в цьому випадку ми не можемо встановлювати критерій відбору записів у фільтрі, а також неможливо встановити сортування записів.

Тепер встановимо критерій відбору записів у фільтрі. Нехай наш фільтр відображає тільки студентів мають спеціальність "ММ". Для визначення умови відбору записів у таблиці полів, що відображаються в рядку, що відповідає полю, на яке накладається умова, у стовпці "Filter", необхідно задати умову. У нашому випадку умова накладається на поле "Наименование специальности". Отже, у рядку "Наименование специальности", у стовпці "Filter" потрібно задати наступне умова відбору "= 'ММ'".

На завершення налаштуємо сортування записів у фільтрі. Нехай при виконанні фільтра спочатку відбувається сортування записів за зростанням по полю "Очна форма навчання", а потім по зменшенню по полю "Курс". Для установки сортування записів за зростанням, у таблиці визначаються полів, у рядку для поля "Очна форма навчання", у стовпці

"Тип сортировки", задайте "По возростанию", а в рядку для поля "Курс" - задайте "По убыванию". Для визначення порядку сортування для поля "Очна форма навчання" в стовпці "Порядок сортировки" поставте 1, а для поля "Курс" поставте 2. Тобто, при виконанні запиту записи спочатку упорядковано по полю "Очна форма обучения", а потім по полю "Курс".

Зауваження: Після установки умов відбору і сортування записів на схемі даних навпроти відповідних полів з'являться спеціальні значки. Значки

і

позначають сортування по зростанню і спаданню, а значок

показує наявність умови відбору.

Після установки сортування записів у фільтрі перевіримо його працездатність, виконавши його. Закрийте вікно конструктора запитів. Як ім'я нового фільтра у вікні "Выбор имени" задайте "Фильтр ММ" і натисніть кнопку "Ok" (рис. 4.13).

Рис. 4.13.

Фільтр "Фильтр ММ" з'явиться в оглядачі об'єктів. Виконайте створений фільтр поза вікна конструктора запитів (рис. 4.14 ).

Рис. 4.14

Самостійно створіть фільтри для відображення інших спеціальностей. Дані фільтри створюються аналогічно фільтру "Фильтр ММ" (дивися вище). Єдиною відмінністю є умова відбору, що накладається на поле "Наименование Специальности", воно повинно бути не "= 'ММ'", а "= 'Пи'", "= 'СТ'", "= 'МО'" або "= ' БО '". При збереженні фільтрів задаємо їхні імена відповідно їх умовам відбору, тобто "Фильтр ПИ", "Фильтр СТ", "Фильтр МО" або "Фильтр БО". Перевірте створені фільтри на працездатність.

Тепер на основі запиту "Запит Студенты + Специальности" створимо фільтри, які відображатимуть студентів мають окремих батьків. Для початку створимо фільтр для студентів, з батьків тільки "Отец". Створіть новий запит і додайте в нього запит "Запрос Студенты + Специальности". Після закриття вікна "Добавление таблицы" зробіть відображеними всі поля запиту (рис. 4.15).

Рис. 4.15.

У таблиці видимі поля в рядку для поля "Родители", у стовпці "Filter", задайте умова відбору рівне "= Отец". Перевірте роботу фільтра, виконавши його.

Закрийте вікно конструктора запитів. У вікні "Выбор имени" задайте ім'я нового фільтра як "Фильтр Отец" (рис. 4.16).

Рис. 4.16.

Виконайте фільтр "Фильтр Отец" поза конструктора запитів (рис. 4.17).

Рис. 4.17.

Створіть фільтри для відображення студентів з іншими варіантами батьків. Дані фільтри створюються аналогічно фільтру "Фильтр Отец" (дивися вище). Єдиною відмінністю є умова відбору, що накладається на поле "Родители", воно повинно бути не "= Отец", а "= Мать", "= Отец, Мать" або "=" Нет "". При збереженні фільтрів задаємо їхні імена відповідно їх умовам відбору, тобто "Фильтр Мать", "Фильтр Отец и Мать" або "Фильтр Нет родителей". Перевірте створені фільтри на працездатність.

 

Нарешті створимо фільтри для відображення студентів очної та заочної форми навчання. Почнемо з очної форми навчання. Створіть новий запит і додайте в нього запит "Запрос Студенты + Специальности" (рис. 4.18).

Рис. 4.18.

У таблиці видимі поля в стовпці "Filter", у рядку для поля "Очна форма обучения" встановіть умова відбору рівне "= 1"

Зауваження: Поле "Очна форма обучения" є логічним полем, воно може приймати значення або "True" (Істина), або "False" (Брехня). Як синонімів цих значень в "Microsoft SQL Server 2008" можна використовувати 1 і 0 відповідно.

Встановіть сортування за зростанням, по полю курс, задавши в рядку для цього поля, в стовпці "Тип сортировки", значення "По возростанию".

Перевірте роботу фільтра, виконавши його.

Закрийте вікно конструктора запитів. Збережіть фільтр під ім'ям "Фильтр очна форма обучения" (рис.  4.19).

Рис. 4.19

Після появи фільтра "Фильтр очна форма обучения" в провіднику об'єктів виконайте фільтр поза вікна конструктора запитів (рис. 4.20).

Рис. 4.20.

Самостійно створіть фільтр для відображення студентів заочної форми навчання. Даний фільтр створюється точно також як і фільтр "Фильтр очна форма обучения". Єдиною відмінністю є умова відбору, що накладається на поле "Очна форма обучения", воно повинно бути не "= 1", а "= 0". При збереженні фільтра задайте його ім'я як "Фильтр заочная форма обучения". Перевірте створений фільтр на працездатність.

Рис. 4.21.