: (DML)

, ' . '. ' , .

. , 㳿 -

. . ......3 ......4 ......6 ......8

. Գ ', , , .

. "" . . 29 9 2010 . , . "" .

:

2 3

(DML)

: .

: .

- 2010

Parfums(_name varchar(20),

parfum_id int,

parfum_type varchar(20),

int,

use_time datetime,

varchar(20),

(parfum_id)

)

('Eclat', 100,'Cold aroma', 5600,' 10/5/2010',' ')

Parfums

('Nina Richy', 101,'Sweet aroma', 11500,' 7/15/2012',' ')

Parfums

('Calvin Klein', 102,'Hot aroma', 8900,' 3/21/2011',' ')

Parfums

('', 104,'Sweet aroma', 4200,' 11/30/2014',' ')

Parfums

(c_id int,

cosmetic_name varchar(20),

cosmetic_type varchar(20),

int,

int,

use_time datetime,

firma varchar(20),

representive varchar (20)

)

(200,'Garnier Light','Day cream', 1900,120,' 3/8/2011',' Avon','Nurzhanova Asel')

(201,'MaxFactor ³' ',', 2300,209,' 7/8/2010','Oriflame','Smailova Saya')

(202,'Pharma','Makeupremover', 3000,260,' 11/18/2010','Maybeeline','Tanabaeva Gulzada')

(203,'Baby Body','Lotion', 300,80,' 9/20/2012','Nivea','Erimbetova Laura')

1)CTE-

,

(lect *

Parfums,

' '

)

*

;

2) CTE

maxi, (в (max ()), v ² ),

mini, (в (min ()), v ² )

cosmetic_id, cosmetic_name, ,

² , co, mini, maxi,

co.amount=maxi.v co.price= mini.v;

3) CTE

alphavit, ( ECT ASCII('A'),

(ASCII('A'))

+1, (+1) ² alphavit,

+1 <ASCII('Z')

)

, cosmetic_name ² alphavit, ,

cosmetic_name +' %';

4) PIVOT

в cosmetic_name, [Avon],[Oriflame],[Nivea]

- - tmpUnpivot

²

(um()

[firma] Ͳ [Avon](,[Oriflame],[Nivea])

)PVT;

5) UNPIVOT

в cosmetic_name, firma,

² tmpUnpivot pvt

PIVOT (ount firma

Ͳ[Avon](,[Oriflame],[Nivea])

)unpvt;

6) CROSS APPLY

parf (@cos_id int)

ֲ,

(1) cosmetic_name, cosmetic_type

² ,

cosmetic_id=@cos_id;

SELECT mro.*,

² APPLY Parfums

parf(parfum_id), mro;

7) OUTER

в mro.*,

² Ͳ APPLY Parfums

parf(parfum_id), mro;

8) parfum_name ,

, , Rank()

( DESC),

Parfums

9) parfum_name DENSE_RANK(),

, , DENSE_RANK()

( DESC),

Parfums

10) parfum_name Row_Number(),

, , Row_Number()

( DESC),

Parfums

11) parfum_name Ntile(),

, , Ntile(3)

( DESC),

Parfums

12) TOP

@var1, int, @var2, int;

Ͳ (@var1*@var2) *

² Parfums;)(

13) tableSAMPLE

@var1=1;

SET @var2=2;

SELECT SELECT *

² tableSAMPLE (100 Ҳ);

в parfum_name, parfum_type

² Parfums p tableSAMPLE(100 )

' c tableSAMPLE(100 )

c.cosmetic_id=p.parfum_id

14) CREATE c

saya,

SELECT * BEGIN

BEGIN

² Parfums;

ʲ

ERROR_NUMBER() BEGIN

ײ,

ErrorNumber,

ERROR_SEVERITY(), ErrorSeverity,

ERROR_STATE(), ErrorState,

ERROR_PROCEDURE(), ErrorProcedure,

ERROR_LINE(), ErrorLine,

ʲ ʲί ײ

ERROR_MESSAGE();

ϲIJ

exec saya

15) c , CREATE

, ,

SELECT * BEGIN

² ;

ʲ

,

(XACT_STATE())= ROLLBACK -1 ;

(XACT_STATE())= 1 ;

ʲ