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