Файл: Пункт 1 Создаём новую базу данных с названием Test.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 01.12.2023
Просмотров: 33
Скачиваний: 2
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Пункт 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