Новые операторы языка манипулирования данными (DML)
Факультет информационных технологий
Кафедра компьютерной инжинерий
Дисциплина: Проектирование и администрирование базы данных
Лабораторная работа №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','Makeup remover',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
