Изменения и удаление данных в SQL. Использование встроенных функций. Пункт 1 Создаём новую базу данных с названием Test

Название	Пункт 1 Создаём новую базу данных с названием Test
Анкор	Изменения и удаление данных в SQL. Использование встроенных функций
Дата	17.05.2023
Размер	2.15 Mb.
Формат файла
Имя файла	LR-6.docx
Тип	Документы #1138964

Пункт 1:

Создаём новую базу данных с названием Test. За шаблон новой БД возьмём нашу udb_Test, то есть создадим 2 файла для данных по 3 Mb (в разных файловых группах) и 1 файл для журнала весом 1 Mb.

Затем скопируем в неё таблицы Titles и Sales при помощи SELECT … INTO.

SELECT * INTO Test.[dbo].titles FROM [udb_Test].[dbo].titles

SELECT * INTO Test.[dbo].Sales FROM [udb_Test].[dbo].Sales

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

SELECT * FROM Test.[dbo].titles

SELECT * FROM Test.[dbo].Sales

Все данные совпадают

Чтобы проверить наличие первичных и внешних ключей у новых таблиц получим скрипт создания этих таблиц для его изучения.

USE [Test]

GO
/****** Object: Table [dbo].[Sales] Script Date: 01.12.2022 20:14:26 ******/

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON

GO
CREATE TABLE [dbo].[Sales](

[stor_id] [char](4) NOT NULL,

[ord_num] [varchar](20) NOT NULL,

[ord_date] [datetime] NOT NULL,

[qty] [smallint] NOT NULL,

[payterms] [varchar](12) NOT NULL,

[title_id] [varchar](6) NOT NULL

) ON [PRIMARY]

GO

USE [Test]

GO
/****** Object: Table [dbo].[titles] Script Date: 01.12.2022 20:14:08 ******/

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON

GO
CREATE TABLE [dbo].[titles](

[title_id] [varchar](6) NOT NULL,

[title] [varchar](80) NOT NULL,

[type] [char](12) NOT NULL,

[pub_id] [char](4) NULL,

[price] [money] NULL,

[advance] [money] NULL,

[royalty] [int] NULL,

[ytd_sales] [int] NULL,

[notes] [varchar](200) NULL,

[pubdate] [datetime] NOT NULL

) ON [PRIMARY]

GO

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

ALTER TABLE Test.[dbo].Sales ADD PRIMARY KEY CLUSTERED ([stor_id],[ord_num],[title_id])

ALTER TABLE Test.[dbo].titles ADD PRIMARY KEY CLUSTERED ([title_id])

ALTER TABLE Test.[dbo].Sales ADD CONSTRAINT svaz FOREIGN KEY(title_id) REFERENCES Test.[dbo].titles(title_id)

Проверяем наличие первичных и внешних ключей при помощи диаграммы базы данных.

Пункт 2:
При помощи запроса SELECT title FROM titles INNER JOIN Sales ON Sales.title_id = titles.title_id выведем все названия книг, у которых есть продажи Повторы??

Попытаемся удалить всю информацию о книге, с названием «The Busy Executive's Database Guide» следующим запросом:
DELETE FROM titles WHERE title = 'The Busy Executive''s Database Guide'

В результате мы получим ошибку «Конфликт инструкции DELETE с ограничением REFERENCE "svaz". Конфликт произошел в базе данных "Test", таблица "dbo.Sales", column 'title_id'», а запрос на удаление не будет выполнен.

Данная ошибка появляется по причине того, что столбец title_id таблицы titles является внешним ключом для таблицы Sales. Из-за этой связи мы не можем удалить книгу из таблицы titles.

Каким образом устанавливается ссылочная целостность данных таблиц? Её поддерживает поле title_id, являющееся первичным ключом для таблицы titles и внешним для Sales.

Запросом последовательно удалим сначала все записи о продаже книги в таблице Sales, и только затем удалим запись о самой книге из таблицы tittles. Таким образом, мы не нарушим ссылочную целостность, потому что в SQL Server выполнение команд идёт по очереди.

DECLARE @id varchar(30)

SELECT @id = title_id FROM titles WHERE title = 'The Busy Executive''s Database Guide'
DELETE FROM Sales WHERE title_id = @id

DELETE FROM titles WHERE title = 'The Busy Executive''s Database Guide'

Пункт 3:

Чтобы установите каскадный способ поддержания ссылочной целостности данных в таблицах Titles и Sales используем конструкцию ON DELETE CASCADE при создании внешнего ключа.

Для замены внешнего ключа удалим старый внешний ключ «svaz» и создадим новый «svazi» с конструкцией ON DELETE CASCADE.

Удаление внешнего ключа «svaz»:

ALTER TABLE Test.[dbo].Sales DROP CONSTRAINT svaz

Добавление внешнего ключа «svazi»:

ALTER TABLE Test.[dbo].Sales ADD CONSTRAINT svazi FOREIGN KEY

(title_id) REFERENCES Test.[dbo].titles(title_id) ON DELETE CASCADE

Для проверки корректности работы каскадного способа попытаемся удалить всю информацию о книге «But Is It User Friendly?» только из таблицы titles.

DELETE FROM titles WHERE title = 'But Is It User Friendly?'

Как мы видим, запрос выполнен успешно. Если проверить таблицу Sales, то будет видно, что из неё также удалены все связанные записи о продажах этой книги.

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

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

ALTER TABLE Test.[dbo].Sales DROP CONSTRAINT svazi

ALTER TABLE Test.[dbo].Sales ADD CONSTRAINT svaz FOREIGN KEY

(title_id) REFERENCES Test.[dbo].titles(title_id)

Пункт 4:

Напишите и выполните отладку команды для уменьшения цен у книг, изданных ранее заданного года (N) на Х%. Значения Х и N задайте через переменные:
DECLARE @N date??, @X real;

SELECT @N = '1991/06/15', @X = 25;
SELECT (price - (price * (@X / 100))) AS 'Новая цена' FROM titles WHERE pubdate < @N;

Постройте запрос, возвращающий текущее количество поставок книг (связанных строк в таблице Sales) в следующем виде:

SELECT titles.title AS 'Название книги', CONCAT_WS(' ', 'Книга поставлялась', count(*), CASE count(*) WHEN 1 THEN 'раз'

WHEN 2 THEN 'раза'

WHEN 3 THEN 'раза'

WHEN 4 THEN 'раза'

WHEN 5 THEN 'раз' А как 6 и более раз??

ELSE ' ' END) AS 'Количество поставок' FROM sales, titles WHERE titles.title_id in (SELECT titles.title_id WHERE sales.title_id = titles.title_id) GROUP BY sales.title_id, titles.title
SELECT title AS 'Название книги', CONCAT_WS(' ', 'Книга вообще не поставлялась в магазины', ' ') AS 'Количество поставок' FROM titles WHERE NOT EXISTS (select title_id from sales where sales.title_id = titles.title_id)
Напишите и выполните отладку команды, которая по заданному в переменной названию книги увеличивает на один месяц дату поставки (sales.ord_date) экземпляров этой книги для её будущих (от текущей даты) поставок:
DECLARE @title varchar(30);

SELECT @title = 'The Gourmet Microwave';
SELECT dateadd(month, +1, ord_date) AS 'Дата' FROM sales, titles, titleauthor WHERE ord_date > getdate() AND sales.title_id in (select title_id from titleauthor where sales.title_id = titleauthor.title_id AND titleauthor.title_id in (select title_id from titles where titleauthor.title_id = titles.title_id AND title = @title))

Пункт 5:

В переменных записаны старый и новый ключ книги. Напишите явную транзакцию, гарантирующую целостность базы при замене Title_id у этой книги:

DECLARE @Old varchar(6), @New varchar(6);

SELECT @Old = 'FR2748', @New = 'HG7845';
ALTER TABLE sales NOCHECK CONSTRAINT ALL;

UPDATE titles SET title_id = @New WHERE title_id = @Old;

UPDATE sales SET title_id = @New WHERE title_id = @Old;

ALTER TABLE sales CHECK CONSTRAINT ALL;

Пункт 6:

Индивидуальное задание

Вариант № 3

Номера студенческих билетов оканчиваются на 11, поэтому мы берём остаток от деления 11 на 8, то есть 3.
По индивидуальному заданию была введена книга автора Иоанны Хмелевской «Стечение обстоятельств» с title_id = PC2947, поэтому далее вся работа будет происходить с данными только об этой книге.
1. Замену даты публикации книги, введенной по индивидуальному заданию, на 30 мая 2007г., если при этом не известен установленный формат даты, который нельзя изменить.

Для изменения даты публикации используем запрос:

UPDATE titles SET pubdate ='2007-05-30T00:00:00' WHERE title_id = 'PC2947'

Проверим внесение изменений.

2. Удаление из таблицы Sales строки с наибольшей датой продажи книги, введенной в ЛР №3.

declare @MaxDate datetime;

SELECT @MaxDate = MAX(ord_date) FROM sales WHERE title_id = 'PC2947';

DELETE FROM sales WHERE title_id = 'PC2947' AND ord_date = @MaxDate

3. Заменяющей значение поля City для автора Bennet Abraham на значение поля City для автора Иоанна Хмелевская.

declare @city varchar(30);

SELECT @city = city FROM authors WHERE au_lname = 'Иоанна' AND au_fname = 'Хмелевская';
UPDATE authors SET city = @city WHERE au_lname = 'Bennet' AND au_fname = 'Abraham';

Контрольные вопросы

1. Сколько команд потребуется выполнить для изменения в БД первичного ключа в таблице titles в отсутствие каскадных операций?

4 команды

2. Как определить установленный в соединении формат представления даты (dateformat)?

Можно при помощи командыSELECTgetdate()AS'Дата' вывести текущие дату и время и посмотреть установленный формат соединения.

См.

SELECT*FROMsys.dm_exec_sessions--+++

SELECT@@SPID--+++

3. Напишите программу, которая из таблицы Sales записи с прошедшей датой поставки (ord_date) переносит?? в таблицу Sales_OLD, имеющую аналогичную структуру.

SELECT * INTO [udb_Test].[dbo].sales_OLD FROM [udb_Test].[dbo].sales WHERE ord_date < getdate()

4. Напишите команду, которая заменяет значение поля City для автора фамилия и имя которого заданы в переменных @LN1 и @FN1, Если он переехал в город, где живет автор, фамилия и имя которого заданы в переменных @LN2 и @FN2.

DECLARE @LN1 varchar(30), @FN1 varchar(30), @LN2 varchar(30), @FN2 varchar(30), @city varchar(20)

SET @LN1 = 'Сергей'

SET @FN1 = 'Каратыгин'

SET @LN2 = 'White'

SET @FN2 = 'Johnson'
UPDATE authors SET city = (select city from authors where au_lname = @LN2 AND au_fname = @FN2) WHERE au_lname = @LN1 AND au_fname = @FN1

5. Напишите команду, которая устанавливает равные доли процента авторского гонорара (столбец titleauthor.royaltyper) в написании книги, название которой задано в переменной @N.

DECLARE @N VARCHAR(30), @count int

SET @N = 'Sushi, Anyone?'

SELECT @count = count(au_id) from titleauthor, titles WHERE title = @N AND titles.title_id = titleauthor.title_id
UPDATE titleauthor SET royaltyper = 100 / @count WHERE titleauthor.title_id in (select title_id from titles where title = @N)

Нет тестов??

6. Напишите скрипт полного удаления из базы книги с названием, заданным в переменной @T, при этом авторы книги не удаляются. Если такой книги нет или она не одна, то удаление не выполняется.

DECLARE @T VARCHAR(30), @flag varchar(3);

SELECT @T = 'But Is It User Friendly?';

SET @flag = CASE WHEN (SELECT count(*) FROM titles WHERE title = @T) = 1 THEN 'OK' ELSE '' END;
IF @flag = 'OK'

BEGIN

DELETE FROM titleauthor WHERE title_id in (select title_id from titles WHERE title = @T)

DELETE FROM sales WHERE title_id in (select title_id from titles WHERE title = @T)

DELETE FROM titles WHERE title = @T

END
7. Создайте программу, которая в таблице titles заменяет значение поля ytd_sales – количество проданных экземпляров издания равным сумме значений поля qty – количество экземпляров книги по всем поставкам каждой книги.

SELECT title_id, sum(qty) AS 'ytd_new' INTO #Table FROM sales GROUP BY title_id

UPDATE titles SET ytd_sales = ytd_new FROM titles JOIN #Table on titles.title_id = #Table.title_id