Transact-SQL.pdf

c c

Transact-SQL   !"##  $% &S  $% #'  # (  ) # ( ## )  ! '   *+   # ( )  ,  +  -.  / S #* )  *     * # #       $%  *###*##0# 

c

S

ntos d
Transact-SQL c

c *0( 1

c -c

c # Æata Contro Languag
(ÆCL) )    #)+"#'#

c -c

c#Æata Æ
nton Languag
(ÆÆL) )  *(*##c

-c

c # Æata Manu aton Languag
(ÆML) )   0#'#c

-c

cS

c  
c 
c
 
c  c  
c 

c   
c

 c
c  c
c cc 
 c

c

c c

c

Æ
c ar aco n
s d
Æata Co nt r o Lan gu ag
(ÆCL)  c

##  * #    #  * # #  *  #*  ##  c

c c S
nt
nca

Æ
scrcn

GRANT

  #   ## #  )        *(         #      (     

ÆSNY

#####  ####0")2 2 3# "+# 0

RS S

  "###

c

c


#'2!*sysadn2dbcr
ator2db_own
r  db_s
curtyadn #(# 

 S
 o



S( #  *¦
ub c

 

 c

S
S 
  
 
cc

c c S
 





 
SS



 




c




Æ
 
  n  Æ Æ  n   nLn u  (ÆÆL )


c


  


 
 


 


 

 
  

  
!


 
"
 
" 




 

  


 1


c M

-c

j SSc•
  c

M

-c

S c•
  c

M

-c

Æ
c•
  c

c c c 
  #
 
 

 

y n#

  #
n #

  n 
"

  

S
 #



 
$

 



 
 
 
" 




 

  
  
 

  
" 




 #

%

"

 
 
  
  


 

 
" 
S


   
 &


& 
c

c S






  


S
 
S S
'S

-

#

  
 
-45/#


  -65/#
 (
&  
-75/
/
c

Æ
 
  n  Æ n u   nLn u (ÆL)
c


  

 "
 






 

  

 c
-c SS

-c S

-c  S

-c SS S


c c


 
 
 

 

y n#

  #
n 
   
"

c

c

c S





  



 ##

)  


c

0

S


S




 



 

 


cc

c c

arab
s oca
s y g oba
s c

c "*#() # "
#*



c

d
      

c c

"*#'S 8S2"   #!S20 # # #!2*32 # )'##"*#'9* :) # *

 

S 8S:•
 

   2S :•
  ; •

c S
 o



S (     "*  : 2    "2 0 *##   ') " # "* c

#: 3-65/ : ;<"< ##2*2 =3 ;: 

c

2 '3 #

c c arab
g o ba (r o or c o nada or
sst
 a)  c

"** #'#0# "S  #  * #  " #  "* * 3#"**  "##)3#  ##  , " )  " ' #2    '! *  .!  "* *  #'#  # 9*: ##*   "* *  ,  "'  ##  # #   #  "
 ( 2  ) "'  , # "! #  "2 #      "* * ::"!#"",#"!#") (#

c S
 o



S( "* * @@trancount #  .  ! *  # (    # #  "* @@trancount    )    , #  )   *  c

c #:

Ê c

3-65/

S
S 
  
 
cc

c c : ;<"< ##2*2 2 '3 # =3 ;: 

'-::>5/  8-< #(##!<2?52?/ 8 S#

c 
rador
s c

c   #  9* ) #  (! #   2 # # #2 0     20"*    # # * 0    .  # *,)# #  *2  #  )   #  *()9 #'#



c

T os d
o 
rado r
s  c

 "      #  #1 +2 #  !2##20!

 

 
r a d o r
s a r  t  t  c os 



 #++    # "1 #-@/2 #"! -A/2 0 !# -B/    # + #  #"!   0   ##"1 -C/20*!- / c

c c c  
r a d o r
s d
c o   a r ac   n 



S  #   # .     #    "*2 2 0 .  #      ## !01)-D/20)->/2 -;/20#* *

 

 
r a d o r d
c o n c at
n a c  n d
c a d
n as 



S  # # ! # # -C/ #  " #    
#'2#"9-E/  *
 ( 2 <*< C  << C <#'<  #  <* #'<   # "9","

c c

La s
nt
nca SSLSCT c

c
#      '! #  * # # #  Ana  ador d
Consu tas (Qu
ry Ana y
r) #  " ##  ' # Adnstrador Cororat o (Snt
rrs
Manag
r) #  "2 9   # 9 # #  osq  0   ) *+ # 32   )  0 MSQu
ry 0 Sng sh Qu
ry *+ #  # 3 0  # 

Õ c

S
S 
  
 
cc

c c  


 
  
 


 (
SS +SS

 (

,



1
SS

#
*



c

c





¯SLST@
-
  


 *
 
 .
+SS
      /





-c c 
u nc 



 

  





 


 
âc
   
 

 


 c

c -c


n 
nc 

, (
(
$

$



  
 
$
 

$

&


, (

&  c

c
 (

 

,
 






¯SLST.

-
 
/
.


.
S S
/
.
+ 
S
/
/
0 
.
 
&0
/
*

0 
.
+SS
 (0 
/
.

'1
. /
, (0  0
#

/
.
+ 
'S
-

/
/
.
 
 (0  
/
.
S
'1
  0 
. 
-
S
/
#2
/
.
 S

-

-
 3
-

-


-, (/
/


c c c c c c

     
 n 
  n  c
c

S

, (
(
$
 

   c 

 #


 
  
 


  (#
 

  
4 c

c LIKS


 
  (



  

 
  
c



9STWSS


 
(






& 
 
& 


!  #

#
)

) 


I


 
(





 "

& 
 
& 


!  #

#
)

) 


c c  
 
&

 


'

)  

11 S




?
  
  
 
 

 
 


@

 

%
  


  
55#
 


 
 


#
 
@?4

5    5

 





3 c

S S


'






cc

c c 6


  
 


$ 

 

  

 
%5555

 
 


#
 
@?4

5    5

 

6) 
  @?4

7
%5555




4


  
 


$ 

 

  

 
%5555

  

#
 
@?4

5    5

 

6) 
  @?4
8
  @?4
7
%5555

7
  
  
 
 

 
 

 
  

@

 




F
  
 


 

) 
%

@


6) 
) 9%

G
  
  
 

 


?$HG

@


6) 
):<5? 5? ?$HG<

H

 
 
 



 
?%55

?H55


 
 


@

 
6) 
 
6
?%55


?H55




@

 
6) 
  
-< ) 
c c

SRÆRLIKS

c <
$
=

  
0
 

 
&
.
/
 

 "

  
$
!


 )
.
>
/
4

 
&
$

!


 )

S?S1
LIKS

$ c

n¶
 
  
 
  
$

 



S S


'






cc

c c SIS
&1L S µ%

n%#)# SIS
&1L S µ_
n #)0* SIS
&1L S µ[C % ]#)   J SIS
&1L S µ[S-]ng*#40 "0 * SIS
&1L S µM[^c]% #)  0# 309#

c  08 @' #   6*2  #

2 2#' # ""6 

  75 *2 75B# #

2 2#' # ""

  %=3*2 2 2#' # =3#;<3*< #*0  ""% #3* 

c

c

luncon
s c

c     3')#""'!# *## #'!*  "# #0") #.       .  *  # #  '!    SS 1

c c

* *3 @'#-/ *K0-#1?%A65A%55F/  A@?@A"-"32#-/2?/ 5$A?6A5H A@%@A"-"32#-/2%/ H5$?6 A@6@A"-"32#-/26/ ?6A5$A5H A@4@A"-"32#-/24/ ?65$5H A@7@A"-"32#-/27/ ?6 5$ 5H A@F@A"-"32#-/2F/ ?6 5H A@G@A"-"32#-/2G/  ?625H A@H@A"-"32#-/2?5/ 5$ ?6 5H A@$@A"-"32#-/2??/ 5HA5$A?6 A@?5@A"-"32#-/2?5?/ 5$A?6A%55H A@??@A"-"32#-/2?5%/ %55H5$?6*&8&< <  A@?%@A"-"32#-/2?56/ ?6A5$A%55H*&8&<
S8< A@?6@A"-"32#-/2?54/ ?65$%55H

; c

S
S 
  
 
cc

c c A@?4@A




 & -& ) #
-/#
?57/



?6 5$ %55H

A@?7@A




 & -& ) #
-/#
?5F/




?6

%55H

A@?F@A




 & -& ) #
-/#
?5G/




?6#
%55H


A@?G@A




 & -& ) #
-/#
??5/



5$ ?6 %55H

A@?H@A




 & -& ) #
-/#
???/




%55HA5$A?6

* 


 0-/#
0-/#) 0-/#-/



  
4
 &



4 
5@ ?6
%61?%1664FG





%55H

SS
 0-/#
0-/#) 0-/#
 & -& ) #
-/#
?56/
8<
<8
 & -& ) #
-/#
H/



  
4
 &



4 
?6A5@A%55H
%61?4154








* S
S  

@




& 
 
 


 -@/








  



  
  
 
 


R c

S S


'






cc

c c -*/'   ")30 0  6  - /'  

luncon
s d
r
su
n (aggr
gat
) c

  ' )  '   ! + )   " #   # #  *2  )   " #    # , ##   '! #   ")#*2###   c

luncn d
r
su
n

Æ
scrcn

L


##". ! +

&-@/

,#'#

M

S".#. !

 

S"9#. !



#. +

luncn d
r
su
n

Æ
scrcn

S

"!#9##"

S


"!#9 # *!

8

!#9##"

8


!#9##"  *!



c

c


#   ' #    #! SS    *!L8&
 N

  . ! #  '! &-@/2 #  ' #  ''OS8S'! & -@/"#''OS8S c

 
rador
s d
SQL:

c

-c

!1  2&82&

-c 

  !1

;!  D!

 c

S
S 
  
 
cc

c c >!0 D>!' >;!0 D;!   #  "-/L&S' #  0## # .-/L&S' #  ## # -/L&S' #   #  -/L&S' #   # P #)?4#P  -/@?4L&S' #  # 0  -@/.#S #/2 -/.#/' # 

La c usu a GR

BY 

c

c  L8&
 N      '  * ## 20( !)* # #     '#
 ( 2 #  L8&
 N   #' 20'# ##

 

SSLSCT 02* &08SS- /*8&* L8&
 N0 . L#!0 /

c

8c

 
• L8&
 N#*)SS 

8c

  L8&
 N     '  *   " #    2 0  '   !#*# #



c   -c

 L  '# ##* 8c ##



8c

#L8&
 N2#1

 -c

" ##" ##  !#'#L8&
 N




c

S
S 
  
 
cc

c c -c

4
 &
 
 


 

 
$
 A

&&

 (




-c



 
$

 




'1






SS 


-c





+SS#
4
 &
 
 


$
 

 (



+SS


-c






'1

 
$
 
& 
#
! 

 
 

  












-c



 
&

 



'1
 

 


 
& 
 


 #







 


+SS


c

c c 
@

 

@

 



 -@/

.

S /#

-  /

. 
 /


 
6) 
945
 





c




 
 u uR9 
 n 
 u uI  

 


+SS


 



#


 


 



  







 #
  

1

-c




 
 
 



'1
 
 

 (


-c



)
?%H
  



 

 
  
=#

  
 
 
  
( 
- #
#

 /


-c



 

)
  

$ 


 
$
 



SS 


-c




 
&

 


 











c




 
&







#,-  /

. 
, /

 
 




c



 
&





   




65



#,-  /

. 
, /

 
 



)&


:

65


c

 c

S S


'






cc

c c



   ¦      

Las consu tas corr
aconadas 8c

 

 (   #    )  ##*20,(# #

-c -c -c

SSLSCT 0 *8&*?  0(I& *%&#!0#0(

 -c

0    #      # #    # ) #   '#    #  *    

-c

 0(#(- S8/2.-&S8/ 8c - 8&/

-c

#!0#0(. !)#*)+## #**!





c

c

~ 
  c c

c



cÃ
 Ã 
c c  c 
 c
c  cc c c
c
c 
 c
cc c c

c Æ
       c 
c c 1c Ventasc Lópezc 2c Finanzasc Morac 3c Personalc Castroc Õc  c  c c 
 c 
!c

c c c c c

c

c

SS  #0 2  2# (' *8& #  S8I& # #  & #0# ;# #0# 

c

 Æ   c   c 
c  c  
c  c  c  c  c  Õc  c  c $
c  c 
"c  c #

c

c

0 c


 Æ  c   c 
c    c 101c Quispec 2c 102c Soriac 1c 103c Osorioc 2c 104c Aliagac 3c 105c Verac 2c 
c  c
c 107c Márquezc 1c 108c Cáceresc 2c

c c

"c c #c c

"c c

S
S 
  
 
cc

c c


  c c

c    • #)#') #20#1  -c

'##*)#-S*&S8I& /2

-c

'##*#3-8 L&S8I& /2

-c

'##**-*&S8I& /

   #*)#2)) *8& c

c c c c c c c

      ~c c

c Æ
       c 
c c 1c Ventasc Lópezc 2c Finanzasc Morac 3c Personalc Castroc Õc  c  c c 
 c 
!c

c c c c

c c

c

  c 101c 102c 103c 104c 105c 
c 107c 108c


 Æ  c


c    c Quispec 2c Soriac 1c Osorioc 2c Aliagac 3c Verac 2c  c
c Márquezc 1c Cáceresc 2c

SS  #0 2  2# (' *8& #  S*&S8I& # #  & #0# ;# #0# 

c

c c c

 Æ   c   c 
c  c  
c  c  c  c  c  Õc  c  c $
c  c 
"c  c #

c 106c Barriosc

c

c c

"c c #c c

"c c NULLc

c

 G #)##  .2 0 #   # 
  ) ' #   # .   
   * )#2   #*  #  ' #  * 
 A  #2  ' # 0  '  #

Ê c

S
S 
  
 
cc

c c c

c c c c

  ~    ~c c

c Æ
       c 
c c 1c Ventasc Lópezc 2c Finanzasc Morac 3c Personalc Castroc Õc  c  c c 
 c 
!c

c c c c c

c

c


 Æ  c   c 
c    c 101c Quispec 2c 102c Soriac 1c 103c Osorioc 2c 104c Aliagac 3c 105c Verac 2c 
c  c
c 107c Márquezc 1c 108c Cáceresc 2c

SS  #0 2  2# (' *8& #  8 L&S8I& # #  & #0# ;# #0# 

c

c c c

 Æ   c   c 
c  c  
c  c  c  c  c  Õc  c  c $
c  c 
"c  c #

c NULLc NULLc NULLc NULLc

c

c c

c c

"c c #c c

"c c Alvac Ludeñac

     G  # )  #  #  .2 0 #   (' # #  )   #

 # *)#2#*# ' #  *  # )  !   * # 2 0 #'#*# )!*  #

c c c c c c

c c c c

Õ c

S
S 
  
 
cc

c c c

      ~c c

c Æ
       c 
c c 1c Ventasc Lópezc 2c Finanzasc Morac 3c Personalc Castroc Õc  c  c c 
 c 
!c

c c c c c

c

c

  c 101c 102c 103c 104c 105c 
c 107c 108c


 Æ  c


c    c Quispec 2c Soriac 1c Osorioc 2c Aliagac 3c Verac 2c  c
c Márquezc 1c Cáceresc 2c

SS  #0 2  2# (' *8& #  *&S8I& # #  & #0# ;# #0# c

c c

 Æ   c   c 
c  c  
c  c  c  c  c  Õc  c  c $
c  c 
"c  c #

c 106c Barriosc NULLc NULLc NULLc NULLc

c

c c

c c

"c c #c c

"c c NULLc Alvac Ludeñac

G #)##  .2 0 #    # #  #  .2 0   (' # #  )    #   

c c c c c c c c c c c c c c c c c c c c c c c

3 c

S
S 
  
 
cc

c c c

j
  c c

 (#)*##' ##*#'#*  )., ((  c

c Æ
       c 
c c 1c Ventasc Lópezc 2c Finanzasc Morac 3c Personalc Castroc 4c Logísticac Alvac 5c Marketingc Ludeñac

c c c c c

c


 Æ  c   c 
c    c 101c Quispec 2c  c  c c  c  c c  Õc  c c  c $
c c 
c  c
c  c 
"c c  c #

c c

c 

SS  #0 2  2# (' *8& #  8&I& # # 

c

c c c  Æ   c   c 
c 101c Quispec 101c Quispec 101c Quispec 101c Quispec 101c Quispec  c  c  c  c  c  c  c  c  c  c  c  c  c c  c c  c c  c 
"c  c #

c  c #

c  c #

c  c #

c  c #

c

c

c Lópezc Morac Castroc Alvac Ludeñac

"c c #c  c 
!c

"c c c c 
!c

"c c #c  c 
!c

c c c c c c c c c c

$ c

S
S 
  
 
cc

c c c

c

          c c

c   c 1c 2c 3c 4c

c

j~   
c — cc #
  c
c Îc#%&c c c c

c c c

c

 
 c 1001c 1002c 1003c 1004c 1005c

d

  c 2c 2c 4c 1c 1c 1c

c c

c

   c  
 c 1002c 1004c 1001c 1001c 1003c 1005c

  c 
c  c  c  c  c  c

SS *2 # 2"## *8& S8I& "" &#0;"#0

S8I&  #  &"#0 #; #0 #

c

c c c

Î
 
c #
  c
c #
  c
c c c c — cc — cc — cc

c c


  c


c 
cc 
c  c 
c  c 
c 
c

  c 
c  c  c  c  c  c

c c c c c c

; c


Æ j c


  c 
c  c


c 
c 
cc 
 c

S
S 
  
 
cc

c c

 
n y

nu 










  


c  
  



c  6





R c

S S


'






cc

c c    

 

 
&61
S

 
u n

 

 
 
 
 
 

 






1
 



 

 
&
B 
 
C Q

   

 
,
B 

C Q
 

 












 
 
&
 
C 

  
&6
   

 
 A
 
 
 1
  #
  #
  

 


D

 


 


 c

S S


'






cc

c c






) 



 

#
 
$

  



  


 

) 
   

 

  
&6
 

ë  
   


  



 

 






c

0


S S


'






cc

c c



) 
 
 
  

 
 n 


 

 

  

 
uR¯ 


 


 

 

  
&6


 



$

 
 


) 
 

  
 
  




 
$ 


* %





 &

' ?0 ;-' 


"#
' 


S& /

' ? ;
























6
$  













 

6
$ 















$















 6


c

0

S S


'






cc

c c











   
9
 , 
<



 , 












-/












    ,
9
5
@

 C5












    
9
  ,












    
9













 6?  -/













9
  S, -/













  6
9

)
















+)
 





















9
6
 6




















 ,
9
 - -5//




















 -  - -5   5///




















 -  - -%///




















 6? - /
















S
+)












S













   -/












  -/












 -/








)
,

S, 












' ,) 6-,/








S





S






 &

' %0 ;-' 


"#
' 


S& /

' % ;








  -/




S

S





  
 
*7

 
 
 



c

00

S S


'






cc

c c



c c c c c c c c c c c

c

0Ê

S S


'






cc

c c

RLRIÆTRIÆ





 
$ 


* ?




 &


$ 




 &








 &

* ?0 -' 


"#
' 


S& /

' 






















 , 

6
$  












 ,    
9
5 & 9- /A
 0  9A

 9 5













< 














9
6
$  -5
@

5#
 , /



c

0Õ

S S


'






cc

c c












  

6
$ ' - /














9
6
-/












 *-#
55/













<


 












    
9













  
9
55








)
,

S, 












' ,) 6-,/








S





S






 &

 0 ;-' 


"#
' 


S& /

  ;








  -/




S

S



         

c

03

S S


'






cc

c c

RLRIS¯TRÆSTLLS






 
$ 


c

0$

S S


'






cc

c c 

 







6





 &

 0 -' 


"#
' 


S& /

' 









 , 

6
$  








 ,    
9
5 & 9- /A
 0  9A

 9 5








     ,
9
5
5








    '; 
9
 








    *  
9
 +)









 

6
$  -5
@

5#
 , /









  

6
$  -5
@

 5#
 , /









 *-#
55/








  *-#
5 5/









  -50 5#
 -55/ -55/#
 -5 5/ -55//









    
9










  
9
55





S









 &

' ?0 ;-' 


"#
' 


S& /

' ? ;








  -/




S

S



c

c

0;

S S


'






cc