Факультетинформационных технологий
Кафедра компьютернойинжинерий
Дисциплина: Проектированиеи администрирование базы данных
Лабораторнаяработа №2и№3
НОВЫЕ ОПЕРАТОРЫ ЯЗЫКАМАНИПУЛИРОВАНИЯ ДАННЫМИ (DML)
Выполнила: Смайлова Сая.
Проверила:Найзабаева Л.
Алматы-2010
createtable Parfums(
parfum_namevarchar(20),
parfum_idint,
parfum_typevarchar(20),
priceint,
use_timedatetime,
destinationvarchar(20),
primarykey(parfum_id)
)
insertinto Parfums
values('Eclat',100,'Cold aroma',5600,'10/5/2010','For women')
insertinto Parfums
values('Nina Richy',101,'Sweet aroma',11500,'7/15/2012','For women')
insertinto Parfums
values('Calvin Klein',102,'Hot aroma',8900,'3/21/2011','For men')
insertinto Parfums
values('Princess',104,'Sweet aroma',4200,'11/30/2014','For children')
/>
createtable Cosmetics(
cosmetic_idint,
cosmetic_namevarchar(20),
cosmetic_typevarchar(20),
priceint,
amountint,
use_timedatetime,
firmavarchar(20),
representivevarchar (20)
)
insertinto Cosmetics
values(200,'GarnierLight','Day cream',1900,120,'3/8/2011','Avon','Nurzhanova Asel')
insertinto Cosmetics
values(201,'MaxFactor','Eyelash',2300,209,'7/8/2010','Oriflame','SmailovaSaya')
insertinto Cosmetics
values(202,'Pharma','Makeupremover',3000,260,'11/18/2010','Maybeeline','TanabaevaGulzada')
insertinto Cosmetics
values(203,'BabyBody','Lotion',300,80,'9/20/2012','Nivea','Erimbetova Laura')
/>
1)CTE— выражения дляупрощения читаемости запросов
withfirst as
(
select*
fromParfums
wheredestination like 'for women'
)
select* from first
orderby price;
/>
2)ОднократныйвызовCTE
WITH
maxiAS (SELECT (max (amount))AS v FROM Cosmetics),
miniAS (SELECT (min (price))AS v FROM Cosmetics)
SELECTcosmetic_id, cosmetic_name, amount, price
FROMCosmetics as co, mini, maxi
WHEREco.amount=maxi.v or co.price= mini.v;
/>
3)Использование CTEдля рекурсивного прохода по дереву
WITHalphavit AS(
SELECTASCII('A') code, CHAR(ASCII('A')) letter
UNIONALL
SELECTcode+1, CHAR(code+1) FROM alphavit
WHEREcode+1
)
SELECTletter, cosmetic_name FROM alphavit,Cosmetics
wherecosmetic_name like letter+'%';
/>
4)ОператорPIVOT
SELECTcosmetic_name, [Avon],[Oriflame],[Nivea]
--INTOtmpUnpivot
FROMCosmetics
PIVOT(
sum(amount)
FOR[firma] IN ([Avon],[Oriflame],[Nivea])
)PVT;
/>
5)ОператорUNPIVOT
SELECTcosmetic_name, firma, amount
FROMtmpUnpivot pvt
UNPIVOT( amount FOR firma
IN([Avon],[Oriflame],[Nivea])
)unpvt;
/>
6)Оператор CROSSAPPLY
alterFUNCTION parf (@cos_id as int)
RETURNSTABLE AS
RETURN
SELECTtop(1) cosmetic_name,cosmetic_type
FROMCosmetics
WHEREcosmetic_id=@cos_id;
SELECTmro.*,price
FROMParfums
CROSSAPPLY parf(parfum_id) as mro;
/>
7)Оператор OUTERAPPLY
SELECTmro.*,price
FROMParfums
OUTERAPPLY parf(parfum_id) as mro;
/>
8)Функцииранжирования
selectparfum_name,destination, price,Rank()
over(Partition BY destination order by price DESC)
asRank
fromParfums
/>
9)DENSE_RANK()
selectparfum_name,destination, price,DENSE_RANK()
over(Partition BY destination order by price DESC)
asRank
fromParfums
/>
10)Row_Number()
selectparfum_name,destination, price,Row_Number()
over(Partition BY destination order by price DESC)
asRank
fromParfums
/>
11)Ntile()
selectparfum_name,destination, price, Ntile(3)
over(Partition BY destination order by price DESC)
asRank
fromParfums
/>
12)ОператорTOP
DECLARE@var1 AS int, @var2 AS int;
SET@var1=1;
SET@var2=2;
SELECTTOP (@var1*@var2) *
FROMParfums;
/>
13)ИспользованиепредложенияTABLESAMPLE
SELECT*
FROMCosmetics SYSTEM TABLESAMPLE (100 PERCENT);
SELECTparfum_name,parfum_type
FROMParfums p TABLESAMPLE(100 percent)
joinCosmetics c TABLESAMPLE(100 percent)
onc.cosmetic_id=p.parfum_id
/>
14)Создание хранимой процедуры с использованием нового обработчика ошибок
CREATEPROCEDURE saya
AS
BEGIN
BEGINTRY
SELECT* FROM Parfums;
ENDTRY
BEGINCATCH
SELECT
ERROR_NUMBER()AS ErrorNumber,
ERROR_SEVERITY()AS ErrorSeverity
,ERROR_STATE()AS ErrorState
,ERROR_PROCEDURE()AS ErrorProcedure
,ERROR_LINE()AS ErrorLine
,ERROR_MESSAGE()
ENDCATCH
END;
GO
execsaya
/>
15)Создание хранимой процедуры с использованием функции, возвращающей состояниетранзакции
CREATEPROCEDURE lovely
AS
BEGINTRY
SELECT* FROM Cosmetics;
ENDTRY
BEGINCATCH
IF(XACT_STATE())= -1 ROLLBACK TRANSACTION;
IF(XACT_STATE())= 1 COMMIT TRANSACTION;
ENDCATCH
/>