Факультет інформаційних технологій
Кафедра комп'ютерної инжинер
Дисципліна: Проектування і адміністрування бази даних
Лабораторна робота № 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/2023 ',' 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 ')
-->p>
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/2023', 'Avon', 'Nurzhanova Asel')
insert into Cosmetics
values ​​(201, 'MaxFactor', 'Eyelash', 2300,209, '7/8/2023 ',' 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