Контрольная работа по предмету "Информатика, программирование"


Новые операторы языка манипулирования данными (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','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




Не сдавайте скачаную работу преподавателю!
Данную контрольную работу Вы можете использовать для выполнения своих заданий.

Поделись с друзьями, за репост + 100 мильонов к студенческой карме :