Попередня сторінка          Зміст           Наступна сторінка          Електронні посібники ВНТУ

ЛАБОРАТОРНАЯ РАБОТА № 7
ВИКОРИСТАННЯ ОПЕРАТОРІВ МАНІПУЛЮВАННЯ ДАНИМИ В MICROSOFT SQL SERVER

Мета: навчитися використовувати оператори маніпулювання даними Select, Insert, Update, Delete.

7.1 Пояснення до лабораторної роботи

7.1.1 Оператори маніпулювання даними

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

Оператор SQL складається з набору команд, що виконують певні дії над об’єктами бази даних або даними, що зберігаються в ній. Реляційні БД підтримують три типи операторів SQL: мову визначення даних (Data Definition Language, DDL), мову маніпулювання даними (Data Manipulation Language, DML) і мову управління даними (Data Control Language, DCL).

Мова маніпулювання даними використовується для отримання, занесення, редагування та видалення даних, що містяться в об’єктах, визначених за допомогою DDL. Як основні оператори маніпулювання даними використовуються оператори: SELECT, INSERT, UPDATE і DELETE.

Оператор SELECT здійснює вибірку інформації, що зберігається в БД і дозволяє вибрати один або декілька кортежів з однієї або декількох таблиць. Оператор INSERT додає в таблицю новий кортеж, оператор UPDATE служить для редагування даних, оператор DELETE видаляє кортежі з таблиці.

Оператор SELECT є фактично найважливішим для користувача й самим складним оператором SQL. Він призначений для вибірки даних із таблиць, тобто він і реалізує одне з основних призначень БД – надавати інформацію користувачеві.

Оператор SELECT завжди виконується над деякими таблицями, що входять у БД. Насправді в БД можуть бути не тільки постійно збережені таблиці, а також тимчасові таблиці й так звані подання. Подання – це SELECT – вираз, що зберігається в БД. З погляду користувачів подання – це таблиця, яка не зберігається постійно в БД, а “виникає” у момент звертання до неї. З погляду оператора SELECT і постійно збережені таблиці, і тимчасові таблиці, й подання виглядають зовсім однаково. Звичайно при реальному виконанні оператора SELECT системою враховуються розбіжності між збереженими таблицями й поданнями, але ці розбіжності приховані від користувача. Результатом виконання оператора SELECT завжди є таблиця.

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

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

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

Оператор UPDATE дозволяє змінювати значення деяких або всіх полів таблиці.

Наприклад, нехай необхідно замінити назву предмета навчання “Математика” (subj_id = 43) на назву “Вища математика”, при цьому ідентифікаційний номер необхідно зберегти

UPDATE subject1

SET subj _name = ‘Вища математика’ , HOUR = 36, SEMESTER= 1

WHERE subj_id =  43;

Усунення стрічок із таблиці здійснюється за допомогою команди DELETE. Можна усувати всі стрічки таблиці. В результаті таблиця стає пустою, після чого вона може бути усунена командою DROP TABLE.

Для усунення декількох стрічок застосовується умова WHERE:

DELETE FROM STUDENT1

WHERE CITY = ‘Київ’ ;

В умові WHERE команди DELETE можна застосовувати підзапити. В атрибутах умови FROM підзапиту не можна посилатися на таблицю, з якої здійснюється усунення. Однак можна посилатися на поточну стрічку, що є кандидатом на усунення тобто на стрічку, яка на даний час перевіряється в умові основного запиту.

7.2 Завдання до лабораторної роботи:

Створити нову БД з назвою DB_Books за допомогою оператора Create Database, створити в ній перераховані таблиці c допомогою операторів Create table за прикладом лабораторної роботи № 6. Зберегти файл програми з назвою ПрізвищеСтудента_Лаб_6_DB_Books. В утиліті SQL Server Management Studio за допомогою кнопки «Створити запит» створити окремі програми по кожному запиту, які зберігаються на диску з назвою: ПрізвищеСтудента_Лаб_7_№_завдання. Можна зберігати всі виконані запити в одному файлі. Для перевірки роботи операторів SELECT попередньо створіть програму, яка з допомогою операторів INSERT заповнить всі таблиці БД DB_Books декількома записами, збережіть програми з назвою ПрізвищеСтудента_Лаб_7_Insert. Перелік завдань, які потрібно виконати, наведено в табл. Д.1 (Додаток Д).

Сортування

1. Вибрати всі відомості про книги з таблиці Books і впорядкувати результат за кодом книги (поле Code_book).
2. Вибрати з таблиці Books коди книг, назви і кількість сторінок (поля Code_book, Title_book і Pages), впорядкувати результат за назвами книг (поле Title_book по зростанню) і по полю Pages (по спаданню).
3. Вибрати з таблиці Deliveries список постачальників (поля Name_delivery, Phone і INN), впорядкувати результат по полю INN (по спаданню).

Зміна порядку полів

4. Вибрати всі поля з таблиці Deliveries таким чином, щоб в результаті порядок стовпців був наступним: Name_delivery, INN, Phone, Address, Code_delivery.
5. Вибрати всі поля з таблиці Publishing_house таким чином, щоб в результаті порядок стовпців був наступним: Publish, City, Code_publish.

Вибір деяких полів з двох таблиць

6. Вибрати з таблиці Books назви книг і кількість сторінок (поля Title_book і Pages), а з таблиці Authors вибрати ім’я відповідного автора книги (поле Name_ author).
7. Вибрати з таблиці Books назви книг і кількість сторінок (поля Title_book і Pages), а з таблиці Deliveries вибрати ім’я відповідного постачальника книги (поле Name_delivery).
8. Вибрати з таблиці Books назви книг і кількість сторінок (Поля Title_book і Pages), а з таблиці Publishing_house вибрати назву відповідного видавництва і місця видання (поля Publish і City).

Умова неточного збігу

9. Вибрати з довідника постачальників (таблиця Deliveries) назви компаній, телефони та ІПН (поля Name_company, Phone і INN), у яких назва компанії (поле Name_company) починається з “ВАТ”.
10. Вибрати з таблиці Books назви книг і кількість сторінок (поля Title_book і Pages), а з таблиці Authors вибрати ім’я відповідного автора книги (поле Name_ author), у яких назва книги починаєтся зі слова “Мемуари”.
11. Вибрати з таблиці Authors прізвища, імена, по батькові авторів (поле Name_ author), значення яких починаються з “Іванов”.

Відсутність точного співпадання значень одного з полів.

12. Вивести список назв видавництв (поле Publish) з таблиці Publishing_house, які не перебувають в місті “Харків” (умова по полю City).
13. Вивести список назв книг (поле Title_book) з таблиці Books, які випущені будь-якими видавництвами, крім видавництва “ПітерСофт” (поле Publish з таблиці Publishing_house).

Вибір записів відповідно до діапазону значень (Between)

14. Вивести прізвища, імена, по батькові авторів (поле Name_author) з таблиці Authors, у яких дата народження (поле Birthday) знаходиться в діапазоні 01.01.1840 - 01.06.1860.
15. Вивести список назв книг (поле Title_book з таблиці Books) і кількість примірників (поле Amount з таблиці Purchases), які були закуплені в період з 12.03.2016 по 15.06.2016 (умова по полю Date_order з таблиці Purchases).
16. Вивести список назв книг (поле Title_book) і кількість сторінок (поле Pages) з таблиці Books, у яких обсяг в сторінках належить діапазону 200–300 (умова по полю Pages).
17. Вивести список прізвищ, імен, по батькові авторів (поле Name_author) з таблиці Authors, у яких прізвище починається на одну з букв діапазону “В”–“Г” (умова по полю Name_author).

Вибір записів відповідно до діапазону значень (In).

18. Вивести список назв книг (поле Title_book з таблиці Books) і кількість (поле Amount з таблиці Purchases), які були надані постачальниками з кодами 3, 7, 9, 11 (умова по полю Code_delivery з таблиці Purchases).
19. Вивести список назв книг (поле Title_book) з таблиці Books, які випущені такими видавництвами: “Київ-Софт”, “Альфа”, “Наука” (умова по полю Publish з таблиці Publishing_house).
20. Вивести список назв книг (поле Title_book) з таблиці Books, які написані наступними авторами: “Леся Українка”, “Іван Франко”, “Тарас Шевченко” (умова по полю Name_author з таблиці Authors ).

Вибір записів з використанням Like

21. Вивести список авторів (поле Name_author) з таблиці Authors, які починаються на букву “К”.
22. Вивести назви видавництв (поле Publish) з таблиці Publishing_house, які містять в назві поєднання “софт”.
23. Вибрати назви компаній (поле Name_company) з таблиці Deliveries, у яких значення закінчується на “ський”.

Вибір записів відповідно до декількох умов

24. Вибрати коди постачальників (поле Code_delivery), дати замовлень (поле Date_order) і назви книг (поле Title_book), якщо кількість книг (поле Amount) в замовленні більше 100 або ціна (поле Cost) за книгу знаходиться в діапазоні від 200 до 500.
25. Вибрати коди авторів (поле Code_author), імена авторів (поле Name_author), назви відповідних книг (поле Title_book), якщо код видавництва (поле Code_Publish) знаходиться в діапазоні від 10 до 25 і кількість сторінок (поле Pages) в книзі більше 120.
26. Вивести список видавництв (поле Publish) з таблиці Publish-ing_house, в яких випущені книги, назви яких (поле Title_book) починаються зі слова “Праці” і місто видання (поле City) – “Харків”.

Багатотабличні запити (вибірка з двох таблиць, вибірка з трьох таблиць з використанням JOIN)

27. Вивести список назв компаній-постачальників (поле Name_company) і назви книг (поле Title_book), які вони постачали в період з 01.01.2016 по 31.12.2016 (умова по полю Date_order).
28. Вивести список авторів (поле Name_author), книги яких були випущені у видавництві “Мир” (умова по полю Publish).
29. Вивести список постачальників (поле Name_company), які постачають книги видавництва “Махаон” (умова по полю Publish).
30. Вивести список авторів (поле Name_author) і назви книг (поле Title_book), які були поставлені постачальником “ВАТ Книготорг” (умова по полю Name_company).

Обчислення

31. Вивести сумарну вартість партії однойменних книг (використовуючи поля Amount і Cost) і назву книги (поле Title_book) в кожній поставці.
32. Вивести вартість однієї друкованої сторінки кожної книги (використовувати поля Cost і Pages) і назви відповідних книг (поле Title_book).
33. Вивести кількість років з моменту народження авторів (використовувати поле Birthday) і імена відповідних авторів (поле Name_author).

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

34. Вивести загальну суму поставок книг (використовувати поле Cost), виконаних “ЗАТ Оптторг” (умова по полю Name_company).
35. Вивести загальну кількість всіх поставок (використовувати будь-яке поле з таблиці Purchases), виконаних в період з 01.01.2016 по 01.02.2016 (умова по полю Date_order).
36. Вивести середню вартість (використати поле Cost) і середню кількість екземплярів книг (використати поле Amount) в одній поставці, де автором книги є “Олесь Гончар” (поле Name_author).
37. Вивести всі відомості про постачання (всі поля таблиці Purchases), а також назву книги (поле Title_book) з мінімальною загальною вартістю (використовувати поля Cost і Amount).
38. Вивести всі відомості про постачання (всі поля таблиці Purchases), а також назву книги (поле Title_book) з максимальною загальною вартістю (використовувати поля Cost і Amount).

Зміна найменувань полів

39. Вивести назву книги (поле Title_book), сумарну вартість партії однойменних книг (використовувати поля Amount і Cost), помістивши  результат в поле з назвою Itogo, поставки за період з 01.01.2016 по 01.06.2016 (умова по полю Date_order).
40. Вивести вартість однієї друкованої сторінки кожної книги (використати поля Cost і Pages), помістивши результат в поле з назвою One_page, і назви відповідних книг (поле Title_book).
41. Вивести загальну суму поставок книг (використовувати поле Cost) і помістити результат в поле з назвою Sum_cost, виконаних “ВАТ Луч” (умова по полю Name_company).

Використання змінних в умові

42. Вивести список угод (всі поля з таблиці Purchases) за останній місяць (умова з використанням поля Date_order).
43. Вивести список авторів (поле Name_author), вік яких менше заданого користувачем (умова з використанням поля Birthday).
44. Вивести список книг (поле Title_book), яких закуплено менше, ніж зазначено в запиті користувача (умова з використанням поля Amount).

Використання змінних замість назв таблиць

45. Вивести список назв компаній-постачальників (поле Name_company) і назви книг (поле Title_book), які вони поставили.
46. Вивести список авторів (поле Name_author), книги яких були випущені у видавництвах “СВІТ”, “НАУКА” (умова по полю Publish).
47. Вивести список видавництв (поле Name_company), книги, що були продані за ціною 350 грн. (Поле Cost).

Вибір результату в курсор

48. Вивести список назв книг (поле Title_book) і кількості сторінок (поле Pages) в кожній книзі і помістити результат в курсор з назвою Temp1.
49. Вивести список назв компаній-постачальників (поле Name_company) і помістити результат в курсор з назвою Temp2.
50. Вивести список авторів (поле Name_author) і помістити результат в курсор з назвою Temp3.

Використання функцій спільно з підзапитом

51. Вивести список книг (поле Title_book), у яких кількість сторінок (поле Pages) більше середньої кількості сторінок усіх книг в таблиці.
52. Вивести список авторів (поле Name_author), вік яких менше середнього віку всіх авторів в таблиці (умова по полю Birthday).
53. Вивести список книг (поле Title_book), у яких кількість сторінок (поле Pages) дорівнює мінімальній кількості сторінок книг, представлених в таблиці.

Використання квантора існування в запитах

54. Вивести список видавництв (поле Publish), книги яких були придбані оптом ( “опт” з поля Type_Purchase).
55. Вивести список авторів (поле Name_author), книг яких немає в таблиці Books.
56. Вивести список книг (поле Title_book), які були поставлені постачальником “ЗАТ Квантор” (умова по полю Name_company).

Оператор обробки даних Update

57. Змінити в таблиці Books вміст поля Pages на 300, якщо код автора рівний 56  (поле Code_author) і назва книги (поле Title_book) – “Мемуари”.
58. Змінити в таблиці Deliveries вміст поля Address на “немає відомостей”, якщо значення поля є порожнім.
59. Збільшити в таблиці Purchases ціну (поле Cost) на 20 відсотків, якщо замовлення було оформлено протягом останнього місяця (умова по полю Date_order).

Оператор обробки даних Insert

60. Додати в таблицю Purchases новий запис, причому так, щоб код покупки (поле Code_purchase) було автоматично збільшено на одиницю, а в тип закупівлі (поле Type_purchase) внести значення “опт”.
61. Додати в таблицю Books новий запис, причому замість ключового поля поставити код (поле Code_book), автоматично збільшений на одиницю від максимального коду в таблиці, замість назви книги (поле Title_book) написати “Наука. Техніка. Інновації”.
62. Додати в таблицю Publish_house новий запис, причому замість ключового поля поставити код (поле Code_publish), автоматично збільшений на одиницю від максимального коду в таблиці, замість назви міста – “Київ” (поле City), замість видавництва – “Наука” (поле Publish).

Оператор обробки даних Delete

63. Видалити з таблиці Purchases всі записи, у яких кількість книг в замовленні (поле Amount) = 0.
64. Видалити з таблиці Authors всі записи, у яких немає імені автора в полі Name_Author.
65. Видалити з таблиці Deliveries всі записи, у яких не зазначено ІПН (поле INN порожнє).

7.3 Контрольні запитання:

1. Яке основне призначення оператора SELECT?
2. Які головні властивості результуючого набору описує більшість операторів SELECT?
3. Напишіть загальну структуру конструкцій оператора SELECT.
4. Які конструкції оператора SELECT є обов’язковими та з якою метою вони використовуються?
5. Що таке підзапити і для чого вони використовуються?
6. Дайте визначення курсору.
7. Підтримку яких функцій забезпечують курсори?
8. Назвіть методи додавання інформації в БД.
9. Опишіть призначення, структуру та порядок застосування оператора INSERT.
10. Опишіть призначення, структуру та порядок застосування оператора UPDATE.
11. Опишіть призначення, структуру та порядок застосування операторів, що видаляють дані з базових таблиць.