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