Материал: BD_Laboratornyi_774_praktikum

Раздел V. Удаление таблицы из базы данных.

Синтаксис команды удаления таблицы (см. [1], стр. 970):

DROP TABLE table_name

Однако, прежде чем удалить таблицу из базы данных, необходимо разорвать все связи этой таблицы с ее дочерними таблицами, если такие имеются. В противном случае удалить таблицу будет невозможно.

Например, требуется удалить таблицу Товар. Сначала необходимо провести анализ, есть ли таблицы, для которых таблица Товар является родительской. Для этого можно, например, использовать системную хранимую процедуру sp_fkeys, позволяющую получить информации о связях между таблицами посредством первичных и внешних ключей, которая имеет следующий синтаксис (см. [1], стр. 979):

sp_fkeys [ @pktable_name = ] 'pktable_name'

[ , [ @pktable_owner = ] 'pktable_owner' ]

[ , [ @pktable_qualifier = ] 'pktable_qualifier' ]

{ , [ @fktable_name = ] 'fktable_name' }

[ , [ @fktable_owner = ] 'fktable_owner' ]

[ , [ @fktable_qualifier = ] 'fktable_qualifier' ]



/* Информация о связях родительской таблицы Товар с ее дочерними таблицами */

EXEC sp_fkeys 'Товар'

GO



Теперь ясно, что у таблицы Товар имеется одна дочерняя таблица – таблица Заказ. Та же информация подтверждается, если проанализировать с помощью процедуры sp_fkeys, какие родительские таблицы имеются у таблицы Заказ:



/* Информация о связях дочерней таблицы Заказ с ее родительскими таблицами */

EXEC sp_fkeys @fktable_name = 'Заказ'

GO



После этого становится ясно, что для удаления из базы данных таблицы Товар, нужно выполнить следующие две команды:



/* Удаление из базы данных таблицы Товар */

ALTER TABLE Заказ DROP CONSTRAINT FK_Заказ_Товар -- первая команда

DROP TABLE Товар -- вторая команда

GO



Примечание. Сначала была разорвана связь между таблицами Товар и Заказ путем удаления в последней ограничения внешнего ключа FK_Заказ_Товар. Только после этого была удалена таблица Товар.

Задание к работе

Предварительно выполните следующие действия.

• Закройте (без сохранения данных) текущее окно Query и удалите базу данных Склад_ХХХ.

• Загрузите сценарий из файла D:\Work\X7230ХХХ\script.sql и создайте базу данных Склад_ХХХ заново.

• Обновите данные на панели Object Explorer и сделайте базу данных Склад_ХХХ видимой.

• Закройте окно со сценарием script.sql и откройте новое пустое окно Query, предназначенное для формирования сценария выполнения задания к лабораторной работе.

Выборка данных из таблиц и представлений

1. Из таблицы Клиент выберите все строки, для которых значение поля ФИОРуководителя содержит подстроку «гор» и значение поля КодРегиона не принадлежит диапазону от 101 до 200 или неизвестно.

2. Из таблицы Поставщик выберите все строки, для которых значение поля УсловияОплаты не равно «Предоплата» или значение поля КодРегиона не попадает в интервал значений от 101 до 200 и не попадает в интервал значений от 301 до 400.

3. Из таблицы Регион выберите все строки, относящиеся к России (но не связанные с городом «Москва») или к Беларуси (но не связанные с городами «Минск» и «Гомель»).

4. Из таблицы Товар выберите все строки, связанные с валютой «Доллары США», для которых значение цены лежит в диапазоне от 200 до 800, а также все строки, связанные с валютой «Евро», для которых значение цены лежит в диапазоне от 100 до 500 или неопределено.

5. В таблице Заказ найдите все те строки, для которых значение поля Количество не определено или не попадает в интервал значений от 2 до 20. Однако на экран наряду с полями КодКлиента, КодТовара и КодПоставщика выведите также поля ИмяКлиента, Наименование и ИмяПоставщика, которые снабдят малоинформативные коды содержательными наименованиями.

6. В таблице Заказ найдите все строки, относящиеся к поставщикам из России или Украины, а из них, в свою очередь, выберите те строки, для которых с момента заказа прошло более 45 дней. Кроме того, в полученном наборе строк дополните столбцы КодТовара и КодКлиента столбцами Наименование и ИмяКлиента, расшифровывающими коды товаров и клиентов соответственно. Результирующий набор строк отсортируйте по полю ДатаЗаказа в порядке убывания, далее (для совпадающих значений), – по полю ИмяКлиента и, далее (если и здесь будут совпадения), - по полю Количество в порядке убывания.

7. Из представления Запрос1 выберите все те строки, для которых одновременно выполняются 2 следующих условия:

- значение поля Наименование содержит в себе подстроку «тер» или «тор» или же заканчивается буквой «а»;

- значение поля Количество попадает в диапазон значений от 5 до 10 или значение поля ЕдиницаИзм равно «штука» или «литр».

Обновление данных в таблицах

1. В таблице Клиент замените имя клиента «ГП ”Верас”» на «ГП ”Верас‑М”», а фамилию ее руководителя сделайте неопределенной.

2. В таблице Поставщик в поле УсловияОплаты установите значение «По договору поставки» для всех поставщиков, названия которых заканчиваются буквой «н», «т», «л» и не начинаются префиксом «ЗАО» или «ОАО».

3. В таблице Товар во всех записях, где цена от 100000 до 1000000 белорусских рублей, замените код валюты на «RUR», а цену уменьшите в 285 раз, а во всех записях, где цена больше 1000000 белорусских рублей, замените код валюты на «USD», а цену уменьшите в 9100 раз.

4. В таблице Заказ обновите поле СрокПоставки следующим образом: если дата заказа раньше 15 октября текущего года, то срок поставки будет равен дате заказа, увеличенной на 14 дней, однако, после 15 октября срок поставки должен быть больше даты заказа на 10 дней. Если же значение поля КодПоставщика не определено, то срок поставки должен быть равен дате заказа, увеличенной на 20 дней.

Удаление данных из таблиц

1. Добавьте приведенные ниже данные в соответствующие таблицы базы данных с помощью команды INSERT.

Таблица Валюта

Таблица Товар

Таблица Заказ

Теперь удалите из таблицы Валюта строку с кодом валюты GRV (Украинские гривны).

Примечание. Предварительно проанализируйте свойства связей между указанными выше таблицами, в частности, разрешено или запрещено каскадное (CASCADE) удаление данных в таблицах Товар и Заказ. Свойства связей между таблицами задаются в команде CREATE TABLE или в команде ALTER TABLE.

Изменение структуры таблиц

1. Внесите такие изменения в таблицу Товар, чтобы для выполнения предыдущего задания, связанного с удалением строки из таблицы Валюта, достаточно было использовать только одну команду удаления данных из таблицы Валюта.

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

Удаления таблиц из базы данных

1. Удалите из базы данных таблицу Регион.

2. Удалите из базы данных таблицу Поставщик.

3. Используя литературные источники, изучите самостоятельно команду удаления представления (VIEW) из базы данных и удалите существующее представление Запрос1.

Сохраните текущий сценарий в файле D:\Work\X7230ХХХ\script2.sql. Затем удалите базу данных Склад_ХХХ. Для этого в ее контекстном меню выберите команду Delete и затем в появившемся окне Delete Object установите флажок Close Existing Connections.

Лабораторная работа №6. Программирование на языке Transact-sql

Цель работы

1. Ознакомиться с основами программирования на языке Transact-SQL.

2. Освоить способы создания и использования хранимых процедур.

3. Изучить способы создания и использования определяемых пользователем функций.

Задачи

1. Написание кода, использующего переменные языка Transact-SQL и временные таблицы.

2. Создание хранимых процедур.

3. Создание пользовательских функций трех типов (Scalar, Inline Table-valued, Multi-statement Table-valued).

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

Методические указания

При помощи пользовательского меню Windows запустите утилиту SQL Server Management Studio, после чего на панели Object Explorer в древовидной структуре раскройте папку Databases.

С помощью команды меню File►Open►File загрузите сценарий из файла D:\Work\X7230ХХХ\script.sql в окно Query.

Выполните сценарий, нажав на панели инструментов кнопку Execute (или клавишу F5). В результате будет создана база данных Склад_ХХХ.

Обновите данные на панели Object Explorer. Для этого используйте команду Refresh в контекстном меню папку Databases или соответствующую кнопку в верхней части панели. В результате база данных Склад_ХХХ станет видимой на панели Object Explorer.

Закройте окно Query, содержащее сценарий script.sql. Затем на панели инструментов нажмите кнопку New Query, и откройте новое пустое окно Query, предназначенное для формирования нового сценария. Готовые к исполнению команды (пакеты) языка Transact-SQL, из которых будет формироваться сценарий, выделены ниже при помощи стрелок  и .

Сделайте активной созданную базу данных Склад_ХХХ:



USE Склад_ХХХ

GO



Раздел I. Основы программирования на языке Transact-sql.

Если возникает потребность работать с переменными, то предварительно нужно эти переменные создать, используя команду объявления переменных, которая имеет следующий синтаксис (см. [1], стр. 1059):

DECLARE  { @local_variable data_type } [ ,...n]



DECLARE @Код INT, @Имя VARCHAR(50), @Цена MONEY

-- Для присвоения значения переменным можно использовать

-- команды SET и SELECT:

SET @Код = 10

SET @Имя = 'MS SQL Server'

SET @Цена = 299.99

-- Команда SELECT отличается от команды SET тем, что позволяет

-- присвоить значения сразу нескольким переменным:

SELECT @Код = 10, @Имя = 'MS SQL Server', @Цена = 299.99

-- Для вывода (на экран монитора) значений переменных также

-- используется команда SELECT:

SELECT @Код, @Имя, @Цена

-- При выводе значений переменных можно снабдить их содержательными

-- именами. При этом идентификаторы, содержащие недопустимые символы,

-- такие как ' ' (пробел), '%', '*' и др., должны быть заключены в

-- квадратные скобки:

SELECT @Код AS [Код товара], @Имя AS Наименование, @Цена AS

[Цена товара]

-- Связку AS можно, при желании, опускать:

SELECT @Код [Код товара], @Имя Наименование, @Цена [Цена товара]

GO



Примечание. Рассмотренные выше переменные являются локальными, т.е. существуют внутри текущего пакета команд (заканчивающегося командой GO) и уничтожаются при выходе из него, поэтому все их нужно выполнять совместно в рамках единого пакета. В отличие от них, глобальные переменные существуют в контексте всего соединения и записываются не с одним, а с двумя лидирующими символами @.

Переменным можно присваивать значения полей из таблиц базы данных. Например:



DECLARE @Код INT, @Имя VARCHAR(50), @Цена MONEY

SELECT @Код = КодТовара, @Имя = Наименование, @Цена = Цена

FROM Товар

WHERE КодВалюты IN ('USD', 'EUR')

SELECT @Код AS [Код товара], @Имя AS Наименование, @Цена AS

[Цена товара]

GO



Здесь переменные @Код, @Имя, @Цена получили значения соответствующих полей последней строки набора данных, выбираемого командой SELECT (сам набор данных на экран не выводится). Поэтому, если, например, необходимо сохранить в переменных сначала сведения о товаре с наибольшей, а затем – с наименьшей ценой применительно к кодам валют USD и EUR, то необходимо выполнить следующую последовательность команд:



DECLARE @Код INT, @Имя VARCHAR(50), @Цена MONEY

-- Выборка данных из таблицы Товар с сортировкой строк

-- по столбцу Цена в порядке возрастания

SELECT @Код = КодТовара, @Имя = Наименование, @Цена = Цена

FROM Товар

WHERE КодВалюты IN ('USD', 'EUR')

ORDER BY Цена

-- Вывод данных о товаре с наибольшей ценой

SELECT @Код AS [Код товара], @Имя AS Наименование, @Цена AS

[MAX цена товара]

-- Выборка данных из таблицы Товар с сортировкой строк по столбцу Цена

-- в порядке убывания

SELECT @Код = КодТовара, @Имя = Наименование, @Цена = Цена

FROM Товар

WHERE КодВалюты IN ('USD', 'EUR')

ORDER BY Цена DESC -- DESC указывает на убывающий порядок сортировки

-- Вывод данных о товаре с наименьшей ценой

SELECT @Код AS [Код товара], @Имя AS Наименование, @Цена AS

[MIN цена товара]

GO



Можно определить также среднюю цену товаров:



DECLARE @Цена MONEY

SELECT @Цена = AVG(Цена)

FROM Товар

WHERE КодВалюты IN ('USD', 'EUR')

-- Вывод средней цены товаров

SELECT @Цена AS [AVG цена товаров]