Материал: ЛР 4
Министерство образования Республики Беларусь
Учреждение образования
БелорусскиЙ государственный университет
информатики и радиоэлектроники
Инженерно-экономический факультет
Кафедра экономической информатики
ОТЧЕТ
по лабораторной работе №6
«Маниуплирование данными с помощью команд языка transact-sql»
Студент |
|
|
Проверил |
|
А. И. Рудак |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Минск 2022
Общая постановка задачи
Цель работы: изучить команды языка манипулирования данными, oсвоить основные команды языка определения данных.
Задачи:
Выборка данных из таблиц и представлений.
Обновление данных в таблицах и представлениях.
Удаление данных из таблиц и представлений.
Изменение структуры таблицы.
Удаление таблиц из базы данных.
Методические указания
При помощи пользовательского меню 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, из которых будет формироваться сценарий, выделены ниже при помощи стрелок ( и (.
Выполнение работы
/* выборка всех столбцов и всех строк таблицы Регион */
SELECT * from Регион;
/* выборка некоторых столбцов и всех строк (вертикальный фильтр) */
SELECT Город, Адрес, Факс FROM Регион
/* выборка всех столбцов и некоторых строк (горизонтальный фильтр) */
sELECT * FROM Регион WHERE Страна = 'Беларусь' AND Город != 'Минск'
/* выборка некоторых столбцов и некоторых строк (вертикальный и горизонтальный фильтры) */
SELECT Город, Адрес, Факс FROM Регион WHERE Страна = 'Беларусь' AND Город != 'Минск'
/* выборка с сортировкой строк по столбцу Город, а при совпадении городов – по столбцу Адрес */
SELECT * FROM Регион ORDER BY Город, Адрес
/* выборка из двух таблиц путем их внутреннего соединения по столбцу КодРегиона */
SELECT Поставщик.ИмяПоставщика, Регион.Город, Регион.Факс,
Поставщик.КодПоставщика FROM Регион INNER JOIN Поставщик ON Регион.КодРегиона = Поставщик.КодРегиона ORDER BY Поставщик.ИмяПоставщика
/* выборка данных из трех таблиц */
SELECT Клиент.ИмяКлиента, Регион.Город, Регион.Факс, Заказ.Количество, Заказ.ДатаЗаказа
FROM Регион
INNER JOIN Клиент ON Регион.КодРегиона = Клиент.КодРегиона
INNER JOIN Заказ ON Клиент.КодКлиента = Заказ.КодКлиента
WHERE Заказ.Количество >= 2
ORDER BY Клиент.ИмяКлиента, Заказ.ДатаЗаказа DESC
/* та же операция выборка данных из трех таблиц с использованием псевдонимов таблиц */
SELECT К.ИмяКлиента, Р.Город, Р.Факс, З.Количество, З.ДатаЗаказа
FROM Регион Р
INNER JOIN Клиент К ON Р.КодРегиона = К.КодРегиона
INNER JOIN Заказ З ON К.КодКлиента = З.КодКлиента
WHERE З.Количество >= 2
ORDER BY К.ИмяКлиента, З.ДатаЗаказа DESC
/* в SQL Server допускается опускать имена псевдонимов таблиц для тех столбцов, имена которых уникальны в пределах объединяемых таблиц. Поэтому предыдущую операцию выборки данных можно записать еще и так: */
SELECT ИмяКлиента, Город, Факс, Количество, ДатаЗаказа
FROM Регион Р
INNER JOIN Клиент К ON Р.КодРегиона = К.КодРегиона
INNER JOIN Заказ З ON К.КодКлиента = З.КодКлиента
WHERE Количество >= 2
ORDER BY ИмяКлиента, ДатаЗаказа DESC
/* выборка данных с формированием вычисляемого столбца Стоимость */
SELECT Товар.Наименование, Товар.Цена, Заказ.Количество,
Товар.ЕдиницаИзм, Товар.Цена * Заказ.Количество AS Стоимость
FROM Товар
INNER JOIN Заказ ON Товар.КодТовара = Заказ.КодТовара
ORDER BY Стоимость
/* подсчет итоговых данных для столбца Количество в таблице Заказ */
SELECT
SUM(Количество) AS [Общее кол-во], AVG(Количество) AS Среднее,
MAX(Количество) AS Максимум, MIN(Количество) AS Минимум
FROM Заказ
/* выборка данных с их группировкой по столбцу КодТовара и подсчетом для
каждой группы итоговых данных */
SELECT КодТовара,
SUM(Количество) AS [Общее кол-во], AVG(Количество) AS Среднее,
MAX(Количество) AS Максимум, MIN(Количество) AS Минимум
FROM Заказ
GROUP BY КодТовара
/* предыдущая операция выборки данных c группировкой, дополненная условием отбора данных (предложение WHERE) и условием отбора итоговых данных (предложение HAVING) */
SELECT КодТовара,
SUM(Количество) AS [Общее кол-во], AVG(Количество) AS Среднее,
MAX(Количество) AS Максимум, MIN(Количество) AS Минимум
FROM Заказ
WHERE Количество > 5
GROUP BY КодТовара
HAVING SUM(Количество) < 30
/* выборка данных из представления Запрос1 */
SELECT *
FROM Запрос1
WHERE ЕдиницаИзм = 'штука'
/* Список учетных записей, которым разрешен доступ к серверу */
USE master -- переключаемся на системную базу данных master
SELECT name, dbname, password, language
FROM syslogins
USE СКЛАД_111 -- переключаемся обратно на базу данных Склад_ХХХ
/* Список учетных записей, включенных в фиксированные роли сервера */
EXEC sp_helpsrvrolemember
/* Список пользователей базы данных Склад_ХХХ */
EXEC sp_helpuser
/* Список ролей (как фиксированных, так и пользовательских) базы данных Склад_ХХХ */
EXEC sp_helprole
/* Членство ролей и пользователей в ролях базы данных Склад_ХХХ */
EXEC sp_helprolemember
UPDATE Клиент
SET КодРегиона = 301
WHERE КодРегиона IS NULL
SET DATEFORMAT dmy
DELETE FROM Заказ
WHERE СрокПоставки < '01.01.2013'
GO
/* Добавление новых полей ГОСТ и Размер в таблицу Товар */
ALTER TABLE Товар ADD ГОСТ VARCHAR(20) NULL
ALTER TABLE Товар ADD Размер INT NULL
GO
/* Изменение типа данных поля Размер в таблице Товар с INT на TINYINT */
ALTER TABLE Товар ALTER COLUMN Размер TINYINT NULL
GO
/* Установка проверочного ограничения для поля Размер в таблице Товар */
ALTER TABLE Товар ADD CONSTRAINT CK_Товар_Размер
CHECK (Размер BETWEEN 36 AND 46)
GO
/* Удаление столбца ГОСТ из таблицы Товар */
ALTER TABLE Товар DROP COLUMN ГОСТ
GO
/* Удаление столбца Размер из таблицы Товар (сначала удаляется проверочное
ограничение) */
ALTER TABLE Товар DROP CONSTRAINT CK_Товар_Размер
ALTER TABLE Товар DROP COLUMN Размер
GO
/* Удаление ограничения внешнего ключа FK_Товар_Валюта из таблицы Товар*/
ALTER TABLE Товар DROP CONSTRAINT FK_Товар_Валюта
/* Добавление ограничения внешнего ключа FK_Товар_Валюта в таблицу Товар */
ALTER TABLE Товар ADD CONSTRAINT FK_Товар_Валюта FOREIGN KEY
(КодВалюты) REFERENCES Валюта ON UPDATE CASCADE
GO
/*Из таблицы Клиент выберите все строки, для которых значение
поля ФИОРуководителя содержит подстроку «гор» и значение поля КодРегиона
не принадлежит диапазону от 101 до 200 или неизвестно*/
/*Вариант 1: */
SELECT *
FROM Клиент
WHERE ФИОРуководителя LIKE '%гор%' AND КодРегиона NOT BETWEEN 101 AND 200 OR КодРегиона IS NULL
/*Вариант 2: */
SELECT *
FROM Клиент
WHERE ФИОРуководителя LIKE '%гор%' AND КодРегиона NOT BETWEEN 101 AND 200 OR КодРегиона = NULL
/*Из таблицы Поставщик выберите все строки, для которых значение поля
УсловияОплаты не равно «Предоплата» или значение поля КодРегиона
не попадает в интервал значений от 101 до 200 и не попадает в интервал
значений от 301 до 400*/
SELECT *
FROM Поставщик
WHERE УсловияОплаты != 'Предоплата' OR (КодРегиона NOT BETWEEN 101 AND 200 OR КодРегиона NOT BETWEEN 301 AND 400)
/*Из таблицы Регион выберите все строки, относящиеся к России
(но не связанные с городом «Москва») или к Беларуси
(но не связанные с городами «Минск» и «Гомель»)*/
SELECT *
FROM Регион
WHERE (Страна = 'Россия' AND Город!='Москва') OR (Страна='Беларусь' AND Город!='Минск' OR Город!='Гомель')
/*Из таблицы Товар выберите все строки, связанные с валютой
«Доллары США», для которых значение цены лежит в диапазоне от 200 до 800,
а также все строки, связанные с валютой «Евро», для которых значение цены
лежит в диапазоне от 100 до 500 или неопределено*/
SELECT *
FROM Товар
WHERE (КодВалюты='USD' AND Цена BETWEEN 200 AND 800) OR (КодВалюты='EUR' AND Цена BETWEEN 100 AND 500 OR Цена IS NULL)
/*В таблице Заказ найдите все те строки, для которых значение поля Количество не определено или не попадает в интервал
значений от 2 до 20. Однако на экран наряду с полями КодКлиента, КодТовара и КодПоставщика выведите также поля ИмяКлиента,
Наименование и ИмяПоставщика, которые снабдят малоинформативные коды содержательными наименованиями.*/
SELECT Заказ.КодКлиента, Заказ.КодТовара, Заказ.КодПоставщика, Клиент.ИмяКлиента, Товар.Наименование, Поставщик.ИмяПоставщика
FROM Заказ
INNER JOIN Клиент ON Заказ.КодКлиента = Клиент.КодКлиента
INNER JOIN Товар ON Заказ.КодТовара=Товар.КодТовара
INNER JOIN Поставщик ON Заказ.КодПоставщика = Поставщик.КодПоставщика
WHERE Количество IS NULL OR Количество NOT BETWEEN 2 AND 20
SELECT Заказ.КодТовара, Заказ.КодКлиента,Товар.Наименование, Клиент.ИмяКлиента
FROM Заказ
INNER JOIN Товар ON Заказ.КодТовара=Товар.КодТовара /*INNER JOIN <Название таблицы из которой нам нужны столбцы>
ON <Название главной таблицы.Атрибут с кодом> = <Название таблицы из которой нам нужны столбцы.Атрибут с кодом>*/
INNER JOIN Клиент ON Заказ.КодКлиента = Клиент.КодКлиента
INNER JOIN Регион ON Клиент.КодКлиента = Регион.КодРегиона
WHERE Регион.Страна = 'Россия' OR Регион.Страна='Украина'
ORDER BY Заказ.ДатаЗаказа, Клиент.ИмяКлиента, Заказ.Количество DESC
/* выборка данных из представления Запрос1 */
SELECT *
FROM Запрос1
WHERE(Наименование LIKE '%тер%' OR Наименование LIKE '%гор%' OR Наименование LIKE '%a') AND ( Количество BETWEEN 5 AND 10 OR ЕдиницаИзм = 'штука' OR ЕдиницаИзм = 'литр')
UPDATE Клиент
SET ИмяКлиента = 'ГП "Верас-М"', ФИОРуководителя = NULL
WHERE ИмяКлиента = 'ГП "Верас"'
UPDATE Поставщик
SET УсловияОплаты = 'По договору поставки'
WHERE ИмяПоставщика LIKE '%н' OR ИмяПоставщика LIKE '%т' OR ИмяПоставщика LIKE '%л' OR ИмяПоставщика NOT LIKE 'ЗАО%' OR ИмяПоставщика NOT LIKE 'ОАО%'
UPDATE Товар
SET КодВалюты = 'RUR', Цена = Цена/285
WHERE КодВалюты = 'BYR' AND Цена BETWEEN 100000 AND 1000000
UPDATE Товар
SET КодВалюты = 'USD', Цена = Цена/9100
WHERE КодВалюты = 'BYR' AND Цена>1000000
SET DATEFORMAT dmy
UPDATE Заказ
SET СрокПоставки = ДатаЗаказа + 14
WHERE ДатаЗаказа < '15.10.2020'
UPDATE Заказ
SET СрокПоставки = ДатаЗаказа + 10
WHERE ДатаЗаказа > '15.10.2020'
UPDATE Заказ
SET СрокПоставки = ДатаЗаказа + 20
WHERE КодПоставщика IS NULL
INSERT INTO Валюта
VALUES ('GRV', 'Украинские гривны', 0.01, 250)
GO
INSERT INTO Товар
VALUES (666, 'ПК-клавиатура', 'штука', 2630, 'GRV', 'Да')
INSERT INTO Товар
VALUES (777, 'Разъём USB', 'штука', 135, 'GRV', 'Да')
INSERT INTO Товар
VALUES (888, 'Принтер Lexmark', 'штука', 12790, 'GRV', 'Да')
GO
ALTER TABLE Заказ DROP CONSTRAINT FK_Заказ_Поставщик
INSERT INTO Заказ (КодКлиента, КодТовара, Количество,КодПоставщика)
VALUES (4, 666, 17,345)
INSERT INTO Заказ (КодКлиента, КодТовара, Количество,КодПоставщика)
VALUES (5, 777, 5,234)
INSERT INTO Заказ (КодКлиента, КодТовара, Количество,КодПоставщика)
VALUES (3, 888, 14,456)
INSERT INTO Заказ (КодКлиента, КодТовара, Количество,КодПоставщика)
VALUES (5, 666, 9,345)
GO
ALTER TABLE Товар DROP CONSTRAINT FK_Товар_Валюта
DELETE FROM Валюта WHERE КодВалюты='GRV'
GO
EXEC sp_fkeys 'Регион'
GO
EXEC sp_fkeys @fktable_name = 'Регион'
GO
ALTER TABLE Клиент DROP CONSTRAINT FK_Клиент_Регион
ALTER TABLE Поставщик DROP CONSTRAINT FK_Поставщик_Регион
DROP TABLE Регион
GO
DROP TABLE Поставщик
GO
DROP VIEW IF EXISTS dbo.Запрос1
GO
Выполнение запроса SELECT * FROM Регион
Выводы
Был изучен процесс выборки данных из таблицы с помощью SELECT, вставки данных с помощью INSERT и использование иных простейших конструкций языка Transact-SQL.