Главная Учебники - Разные Лекции (разные) - часть 33
Факультет информационных технологий Кафедра компьютерной инжинерий Дисциплина: Проектирование и администрирование базы данных Лабораторная работа №2 и №3 НОВЫЕ ОПЕРАТОРЫ ЯЗЫКА МАНИПУЛИРОВАНИЯ ДАННЫМИ (
DML
)
Выполнила : Смайлова Сая. Проверила: Найзабаева Л. Алматы- 2010 create table Parfums( parfum_name varchar(20), parfum_id int, parfum_type varchar(20), price int, use_time datetime, destination varchar(20), primary key(parfum_id) ) insert into Parfums values ('Eclat',100,'Cold aroma',5600,'10/5/2010','For women') insert into Parfums values ('Nina Richy',101,'Sweet aroma',11500,'7/15/2012','For women') insert into Parfums values ('Calvin Klein',102,'Hot aroma',8900,'3/21/2011','For men') insert into Parfums values ('Princess',104,'Sweet aroma',4200,'11/30/2014','For children') create table Cosmetics( cosmetic_id int, cosmetic_name varchar(20), cosmetic_type varchar(20), price int, amount int, use_time datetime, firma varchar(20), representive varchar (20) ) insert into Cosmetics values(200,'Garnier Light','Day cream',1900,120,'3/8/2011','Avon','Nurzhanova Asel') insert into Cosmetics values(201,'MaxFactor','Eyelash',2300,209,'7/8/2010','Oriflame','Smailova Saya') insert into Cosmetics values(202,'Pharma','Makeupremover',3000,260,'11/18/2010','Maybeeline','Tanabaeva Gulzada') insert into Cosmetics values(203,'Baby Body','Lotion',300,80,'9/20/2012','Nivea','Erimbetova Laura') 1)
CTE
- выражения для упрощения читаемости запросов
with first as ( select * from Parfums where destination like 'for women' ) select * from first order by price; 2)
Однократный
вызов
CTE
WITH maxi AS (SELECT (max (amount))AS v FROM Cosmetics), mini AS (SELECT (min (price))AS v FROM Cosmetics) SELECT cosmetic_id, cosmetic_name, amount, price FROM Cosmetics as co, mini, maxi WHERE co.amount=maxi.v or co.price= mini.v; 3)
Использование
CTE
для рекурсивного прохода по дереву
WITH alphavit AS( SELECT ASCII('A') code, CHAR(ASCII('A')) letter UNION ALL SELECT code+1, CHAR(code+1) FROM alphavit WHERE code+1 <= ASCII('Z') ) SELECT letter, cosmetic_name FROM alphavit,Cosmetics where cosmetic_name like letter+'%'; 4)
Оператор
PIVOT
SELECT cosmetic_name, [Avon],[Oriflame],[Nivea] --INTO tmpUnpivot FROM Cosmetics PIVOT ( sum(amount) FOR [firma] IN ([Avon],[Oriflame],[Nivea]) )PVT; 5)
Оператор
UNPIVOT
SELECT cosmetic_name, firma, amount FROM tmpUnpivot pvt UNPIVOT ( amount FOR firma IN([Avon],[Oriflame],[Nivea]) )unpvt; 6)
Оператор
CROSS APPLY
alter FUNCTION parf (@cos_id as int) RETURNS TABLE AS RETURN SELECT top(1) cosmetic_name,cosmetic_type FROM Cosmetics WHERE cosmetic_id=@cos_id; SELECT mro.*,price FROM Parfums CROSS APPLY parf(parfum_id) as mro; 7)
Оператор
OUTER APPLY
SELECT mro.*,price FROM Parfums OUTER APPLY parf(parfum_id) as mro; 8)
Функции
ранжирования
select parfum_name,destination, price,Rank() over (Partition BY destination order by price DESC) as Rank from Parfums 9) DENSE_RANK()
select parfum_name,destination, price,DENSE_RANK() over (Partition BY destination order by price DESC) as Rank from Parfums 10) Row_Number()
select parfum_name,destination, price,Row_Number() over (Partition BY destination order by price DESC) as Rank from Parfums 11) Ntile()
select parfum_name,destination, price, Ntile(3) over (Partition BY destination order by price DESC) as Rank from Parfums 12)
Оператор
TOP
DECLARE @var1 AS int , @var2 AS int; SET @var1=1; SET @var2=2; SELECT TOP (@var1*@var2) * FROM Parfums; 13)
Использование
предложения
TABLESAMPLE
SELECT * FROM Cosmetics SYSTEM TABLESAMPLE (100 PERCENT); SELECT parfum_name,parfum_type FROM Parfums p TABLESAMPLE(100 percent) join Cosmetics c TABLESAMPLE(100 percent) on c.cosmetic_id=p.parfum_id 14)
Создание хранимой процедуры с использованием нового обработчика ошибок
CREATE PROCEDURE saya AS BEGIN BEGIN TRY SELECT * FROM Parfums; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() END CATCH END; GO exec saya 15)
Создание хранимой процедуры с использованием функции, возвращающей состояние транзакции
CREATE PROCEDURE lovely AS BEGIN TRY SELECT * FROM Cosmetics; END TRY BEGIN CATCH IF (XACT_STATE())= -1 ROLLBACK TRANSACTION; IF (XACT_STATE())= 1 COMMIT TRANSACTION; END CATCH
|