5. Лабораторна робота №5 на тему Створення подань View

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

Як і до будь-якої таблиці, до подання можна створювати запити, виконувати оновлення, вставку, вилучення і з‘єднання з іншими базо­вими таблицями і поданнями, якщо клієнт має потрібні права доступу.

Задача 5.1 Вивести перелік всіх конференцій, які відбулися у Києві. Упорядкувати список за датою.

Для створення подання треба у вікні проекту на вкладці Запросы вибрати пункти Создать – Конструктор представления. Відкриється конструктор і вікно Добавление таблицы, яке дозволяє добавити таблиці, подання та функції. Добавимо таблицю Conference.

Вікно конструктора може містити частини: Схема, Сетка та SQL (див. рис. 5.1). За допомогою кнопок панелі інструментів можна відмі­няти їхнє відображення. Дії, які виконує клієнт в будь-якій частині, автоматично вносяться до інших частин.

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

Виберемо потрібні поля, в частині Сетка вкажемо поле для сортування – Date_begin, виберемо зі списку тип сортування – по возрастанию та запишемо критерій для поля City (місто Київ). Для запису символьних констант в SQL Server на мові Transact – SQL використовуємо синтаксис: WHERE City = N 'Київ' (див. додаток А). 

Рисунок 5.1 – Конструктор подання задачі 5.1

Можна створювати SQL – код самостійно. Тоді синтаксис коду можна перевіряти за допомогою відповідної кнопки, а в інших части­нах автоматично відбуваються зміни. Результат подання – на рис. 5.2. 

Рисунок 5.2 – Результат роботи подання задачі 5.1

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

Рисунок 5.3 – Конструктор процедури задачі 5.2

Місто вводиться як параметр. Тому на відміну від попередньої задачі створюється не подання, а збережена процедура (див. рис. 5.3). Для використання параметру застосовується вираз:

WHERE (dbo.Conference.City = @введіть_місто)

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

Після запуску процедури виводиться вікно для введення пара­метру – назви міста (див. рис. 5.4). Результат – на рис. 5.5. 

Рисунок 5.4 – Вікно для введення параметру 

Рисунок 5.5 – Результат роботи процедури задачі 5.2

Задача 5.3 Вивести перелік всіх країн, в яких відбулися конфе­ренції з травня по грудень 2009 року.

Для запису умов для року та місяця створимо обчислювальні поля. Функція Month (Conference.Date_begin) визначає номер місяця, а функція Year (Conference.Date_begin) – рік.

В розділіFROM команди SELECT вказуються таблиці і подання для вибірки даних та визначаються типи зв’язків між ними.

При використанні ключових слів INNERJOIN обираються тіль­ки ті рядки з обох таблиць, що задовольняють умові з’єднання. Цей варіант встановлюється за замовчанням.

При використанні ключових слів LEFT [OUTER] JOIN до ре­зультату будуть включені всі рядки з лівої таблиці, незалежно від того, чи є для них відповідні рядки в правій таблиці, і тільки ті рядки з іншої таблиці, що задовольняють умові з’єднання. При цьому, якщо в правій таблиці немає зв’язаних записів, замість відповідних значень будуть відображуватися значення NULL.

При використанні ключових слів RIGHT [OUTER] JOIN до ре­зультату будуть включені всі рядки з правої таблиці, незалежно від того, чи є для них відповідні рядки в лівій таблиці, і тільки ті рядки з іншої таблиці, що задовольняють умові з’єднання. При цьому, якщо в лівій таблиці немає зв’язаних записів, замість відповідних значень будуть відображуватися значення NULL.

При використанні ключових слів FULL [OUTER] JOIN до ре­зультату будуть включені всі рядки з обох таблиць: як ті, що задо­вольняють умові з’єднання, так і ті, що не задовольняють.

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

Наприклад,

SELECT dbo.Country.Country, dbo.Conference.Conference_ID,

dbo.Conference.Place, dbo.Conference.City

FROM dbo.Country CROSS JOIN dbo.Conference

Використаємо RIGHT OUTER JOIN – з’єднання таблиць право­руч. Тоді будуть виведені тільки потрібні країни. Для того, щоб їхні назви не повторювалися, використаємо параметр DISTINCT.

В умовах даної предметної області для кожного запису таблиці Conference існує зв’язаний запис в таблиці Country. Тому, якщо не змінювати тип з’єднання таблиць, а залишити INNERJOIN, результат будетакий же самий.

Тип зв’язку можна задати в команді SQL або виділити лінію зв’язку між таблицями та встановити в контекстно – залежному меню або і вікні Свойства потрібні прапорці:

Включить строки

Все строки с Country

Все строки с Conference

Конструктор подання матиме такий вигляд, як на рис. 5.6. Після запуску подання буде виведений потрібний перелік (див. рис. 5.7). 

Рисунок 5.6 – Конструктор подання задачі 5.3 

Рисунок 5.7 – Результат роботи подання задачі 5.3

Задача 5.4 Вивести інформацію про конференції, які почина­ються о певній даті, наприклад, 2 листопада.2009 року.

Створимо подання з полями таблиць Conference і Country. Дату (2.11.2009) запишемо в стовпчику Критерий. Вона буде автоматично змінена на текстову константу, в SQL – коді буде використана функція CONVERT (див. додаток А), яка перетворить її до формату дати (datetime), а саме: WHERE (dbo.Conference.Date_begin =

CONVERT(DATETIME, '2009-11-02 00:00:00', 102)) 

Рисунок 5.8 – Конструктор подання задачі 5.4 

Рисунок 5.9 – Результат роботи подання задачі 5.4

Якщо дату задавати як параметр, то треба буде створювати збе­режену процедуру та вводити дату в будь-якому форматі дати.

Задача 5.5 Для конференцій, які відбулися в Києві або Москві, підрахувати кількість доповідей.

Включимо до подання таблиці Conference та Material, а також таблицю Section, через яку ці дві таблиці зв’язані в схемі даних.. Кіль­кість доповідей підрахуємо за допомогою функції COUNT. Для угру­повання виберемо поля Name, Place та City таблиці Conference, а для поля City задамо умови відбору (Київ або Москва). Конструктор подання – на рис. 5.10, результат – на рис. 5.11.

SQL – код матиме такий вигляд:

SELECT dbo.Conference.Name AS назва, dbo.Conference.City

AS місто, dbo.Conference.Place AS [де відбулась],

COUNT(dbo.Material.Material_ID)

AS [кількість доповідей]

FROM dbo.Conference INNER JOIN dbo.Section ON

dbo.Conference.Conference_ID = dbo.Section.Conference_ID

INNER JOIN dbo.Material ON

dbo.Section.Section_ID = dbo.Material.Section_ID

GROUP BY dbo.Conference.Name, dbo.Conference.Place,

dbo.Conference.City

HAVING (dbo.Conference.City = N'Київ') OR

(dbo.Conference.City = N'Москва' )

Рисунок 5.10 – Схема конструктора подання задачі 5.5 

Рисунок 5.11 – Результат роботи подання задачі 5.5

5.2 Збережені процедури

Збережені процедури на відміну від подань дозволяють не тіль­ки вибирати дані з таблиць, а ще й змінювати їх.

Задача 5.6 Створити нову архівну таблицю та записати до неї відомості про конференції, які вже відбулися.

Створимо збережену процедуру. В Конструкторі вкажемо поля з таблиць Country та Conference. В меню Запрос виберемо пункт Запрос на создание таблицы. У вікні, що відкриється, вкажемо ім’я нової таблиці – архів. Для умов відбору використаємо функцію GETDATE(), яка повертає системну дату та час.

SQL – код для задачі 5.6

SELECT dbo.Country.Country AS країна,

dbo.Conference.Conference_ID,

dbo.Conference.Name AS назва,

dbo.Conference.City AS місто,

dbo.Conference.Place AS [де проходила],

dbo.Conference.Date_begin AS початок,

dbo.Conference.Date_end AS кінець,

dbo.Conference.Tel AS телефон

INTO dbo.архів

FROM dbo.Country INNER JOIN dbo.Conference ON

dbo.Country.Country_ID = dbo.Conference.Country_ID

WHERE (dbo.Conference.Date_begin < GETDATE())

Буде створена нова таблиця архів з вибраними полями та вказа­ними заголовками. Далі треба в конструкторі цієї таблиці задати клю­чове поле. Це може бути поле Conference_ID. А можна вставити нове ключове поле Код з властивостями, як на рис. 5.12. Значення до нього будуть заноситись автоматично.

Задача 5.7 Доповнити архівну таблицю відомостями про конфе­ренції, які відбулися в минулому році.

Відкриємо конструктор процедури попередньої задачі та збере­жемо її під іншим іменем. Змінимо тип процедури. В меню Запрос виберемо пункт Добавление. Відкриється вікні, в якому виберемо зі списку таблицю архів, до якої треба вставити дані.

Виберемо всі ті ж самі поля, що і в задачі 5.6. Проте обов’язково треба вставляти тільки поле Conference_ID, тому що в таблиці архів для нього заборонено значення Null. Вибір інших полів не має значення. Псевдоніми теж можна не задавати.

Вибір даних за минулий рік (2009) задамо таким чином:

WHERE (YEAR(dbo.Conference.Date_begin) = 2009) 

Рисунок 5.12 – Конструктор таблиці архів 

Рисунок 5.13 – Сітка конструктора процедури задачі 5.7

Конструктор збереженної процедури та SQL – код матимуть однаковий вигляд (див. рис. 5.13), незалежно від того, чи є в таблиці архів поле Код.

SQL– код для задачі 5.7

INSERT INTO dbo.архів (країна, Conference_ID, назва, місто,

[де проходила], початок, кінець, телефон)

SELECT dbo.Country.Country, dbo.Conference.Conference_ID,

dbo.Conference.Name, dbo.Conference.City,

dbo.Conference.Place, dbo.Conference.Date_begin,

dbo.Conference.Date_end, dbo.Conference.Tel

FROM dbo.Country INNER JOIN dbo.Conference ON

dbo.Country.Country_ID = dbo.Conference.Country_ID

WHERE (YEAR(dbo.Conference.Date_begin) = 2009)

Після запуску процедури до таблиці архів будуть добавлені нові дані. Якщо ключовим полем є поле Conference_ID, то добавити ті ж самі дані ще раз буде неможливо. При цьому буде виведене повідом­лення про помилку (див. рис. 5.14). 

Рисунок 5.14 – Повідомлення про помилку

Якщо ключовим буде встановлено поле Код типу Счетчик, то можна буде дописувати ці дані багато разів.

Задача 5.8 Виключити відомості про конференції, які відбулися в минулому році.

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

В схемі даних треба виділити лінію зв’язку між таблицями, в контекстному меню вибрати пункт Свойства, у вікні, що відкриється, вкладку Связи. Там автоматично встановлений прапорець

Применить связь для INSERT и UPDATE.

Тому, якщо є зв’язані записи, після запуску процедури буде ви­ведене повідомлення про помилку і жодні записи видалені не будуть.

Але якщо встановити прапорці:

Применить связь для INSERT и UPDATE та каскадное удаление связанных полей

то будуть видалені як записи з батьківської таблиці, так і зв’язані запи­си в підпорядкованій таблиці.

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

Аналогічно для змінення даних використовується прапорець

каскадное обновление связанных полей

Для розв’язку задачі відкриємо схему даних та встановимо всі три прапорці на зв’язок таблиці Conference з таблицею Section та зв’я­зок таблиці Section з таблицею Material. В конструкторі збереженої процедури добавимо таблицю Conference та запишемо SQL – код:

DELETE FROM dbo.Conference

WHERE (YEAR(Date_begin) = 2009)

Будуть видалені дані з усіх трьох таблиць про конференції, що відбулися в 2009 році, про їхні секції та матеріали.

Задача 5.9 Замінити код конференції з 1093 на код 1201.

Таке завдання потребує змін в полі Conference_ID у двох табли­цях: Conference та Section. Але завдяки встановленим прапорцям можна створити збережену процедуру тільки для однієї головної таблиці – Conference. В конструкторі збереженої процедури в меню Запрос виберемо пункт Обновление та запишемо SQL – код:

UPDATE dbo.Conference

SET Conference_ID = 1201

WHERE (Conference_ID = 1093)

Старий код буде змінено на новий в обох таблицях.

Задача 5.10 Конференції, які повинні відбутися в листопаді поточного року, перенести на грудень.

Для розв’язку задачі треба перенести на 30 днів уперед всі дати для потрібних конференцій. Тобто треба збільшити значення полів Date_begin, Date_end та Date_art таблиці Conference. Це можна зроби­ти в одній процедурі. Для критерію вкажемо рік та номер місяця – 11.

SQL – код матиме вигляд:

UPDATE dbo.Conference

SET Date_begin = Date_begin + 30, Date_end = Date_end + 30,

Date_art = Date_art + 30

WHERE (MONTH(Date_begin) = 11) AND

(YEAR(Date_begin) = YEAR(GETDATE()) – 1)

5.3 Індивідуальні завдання

Створити подання або збережені процедури для свого варіанту.

Варіант 1

1. Вивести перелік проданих товарів із зазначенням дати прода­жу, назви товару, проданої кількості. Підрахувати вартість кожної покупки. Упорядкувати список за датою.

2. Здійснити пошук інформації про товар за його назвою. Назву товару вводити як параметр.

3. Для кожного дня підрахувати вартість проданих товарів.

4. Збільшити вартість певної марки товару на 10%.

Варіант 2

1. Вивести список квартир із зазначенням району, кількості кім­нат, площі та контактного телефону. Упорядкувати список за районом та кількістю кімнат.

2. Здійснити пошук інформації про наявні квартири за кількістю кімнат. Кількість кімнат вводити як параметр.

3. Підрахувати кількість квартир в ріелтерській конторі для кож­ного району міста.

4. Записати до нової таблиці відомості про квартири, які були переглянути покупцями.

Варіант 3

1. Вивести інформацію про поїздки: марка та держ. номер маши­ни, прізвище водія, дата поїздки, кілометраж. Упорядкувати список за датою та маркою машин.

2. Здійснити пошук повної інформації про автомобіль за його держ. номером. Номер вводити як параметр.

3. Підрахувати загальні витрати пального для кожної машини щомісяця.

4. Записати до нової таблиці відомості про автомобілі, які були виготовлені в минулому або поточному році.

Варіант 4

1. Вивести перелік замовлень на туристичні поїздки. Підрахувати вартість кожного замовлення. Упорядкувати список за назвою країни та датою.

2. Здійснити пошук повної інформації про тури до певної країни. Країну вводити як параметр.

3. Підрахувати найбільшу кількість путівок в одному замовленні для кожної країни.

4. Вилучити інформацію про тури з класом готелю * або **.

Варіант 5

1. Вивести інформацію про результати останньої сесії із зазна­ченням прізвища студента. Упорядкувати список за номером групи та прізвищем.

2. Здійснити пошук повної інформації про студента за номером залікової книжки. Номер вводити як параметр.

3. Підрахувати кількість студентів в групах для кожної з форм навчання.

4. Записати до нової таблиці відомості про студентів, які отриму­ють стипендію.

Варіант 6

1. Вивести перелік дисциплін, які викладаються на кафедрі із зазначенням груп, викладачів та ін. Упорядкувати список за назвою предмету та номером групи.

2. Здійснити пошук повної інформації про дисципліни, які викла­даються у певному семестрі. Семестр вводити як параметр.

3. Для кожного викладача підрахувати кількість предметів, які він викладає.

4. Для предметів з формою контролю Іспит збільшити кількість годин на 17.

Варіант 7

1. Вивести повну інформацію про виконані замовлення. Упоряд­кувати список за назвою замовника та датою виходу реклами.

2. Здійснити пошук інформації про замовлення на певну дату. Дату вводити як параметр.

3. Для кожного замовника підрахувати середній обсяг реклами.

4. Збільшити на 5 % вартість всієї реклами.

Варіант 8

1. Вивести інформацію про продані журнали. Упорядкувати спи­сок за назвою.

2. Здійснити пошук повної інформації про видання за його індек­сом. Індекс вводити як параметр.

3. Для кожного дня підрахувати загальну вартість проданих при­мірників.

4. Створити нову таблицю з інформацією про видання, які вихо­дять 1 раз на місяць.

Варіант 9

1. Вивести повну інформацію про лікарів та їхніх пацієнтів. Упо­рядкувати список за назвою відділення.

2. Вивести повну інформацію про пацієнтів певного відділення. Назву відділення вводити як параметр.

3. Для кожного лікаря підрахувати кількість його пацієнтів.

4. Вилучити дані про виписаних пацієнтів.

Варіант 10

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

2. Здійснити пошук повної інформації про мешканців за прізви­щем. Прізвище вводити як параметр.

3. Для кожного будинку підрахувати загальні сплачені суми за останній місяць.

4. Створити структуру архівної таблиці та доповнити її даними попереднього запиту.

Варіант 11

1. Вивести інформацію про вже відремонтовані прилади. Упоряд­кувати список за датою прийому приладу на ремонт.

2. Здійснити пошук повної інформації про прилад за його назвою та маркою. Їх треба вводити як параметр.

3. Підрахувати кількість приладів, які ще мають гарантію.

4. Збільшити на 10%вартість ремонту для приладів, виготовлених 5 років тому або раніше.

Варіант 12

1. Вивести інформацію про продані квитки. Упорядкувати список за датою номером поїзду та датою поїздки.

2. Здійснити пошук повної інформації про поїзди до певного міста. Назву міста вводити як параметр.

3. Підрахувати загальну кількість та вартість проданих квитків для кожної дати.

4. Вилучити дані про продані квитки на дати, що вже минули.

5.4 Контрольні питання

1. Яку структуру має команда SELECT ? Які розділи є обов’язко­вими? Для чого вони призначені?

2. Використання розділу JOIN. Які типи з'єднання таблиць існують?

3. Використання розділів GROUP BY та HAVING.

4. Як створити нову таблицю для результатів вибірки за допомо­гою команди SELECT ?

5. Яка команда використовується для оновлення даних в таблиці?

6. Використання команди INSERT.

7. Вилучення даних з таблиці за допомогою SQL.

З повагою ІЦ "KURSOVIKS"!