[prog] SQL - MSSQL2020. 11. 4. 13:14

Practical SQL Queries for Microsoft SQL Server 2008 R2

 

Contents at a Glance                                                          
Chapter 1 Select: Single Table  
Chapter 2 Where 
Chapter 3 Order By  
Chapter 4 Select: Multiple Tables 
Chapter 5 Aggregates  
Chapter 6 Select: New Tables  
Chapter 7 Except/Intersect/Union  
Chapter 8 Group By  
Chapter 9 System Functions  
Chapter 10 Subqueries 
Chapter 11 Delete/Insert/Update 
Chapter 12 Views/User-Defined Functions 
Chapter 13 Stored Procedures/Programming  
Chapter 14 Data Definition Language (DDL) and Data Control Language (DCL) 
Chapter 15 After You Finish 
Index

1. 사용할 데이터베이스 다운로드

   - 마이크로소프트에서 제공하는 테스트용 DB 를 다운로드 받는다.
   - 구글에서 adventureworksDW2008 database download 검색

 

 

 

 

2. 다운로드 받은 백업 DB 를 SSMS 에서 설치

------------------------------------------------------

Chapter 1 Select: Single Table 

 

 

 

Chapter 11  Delete / Insert / Update 

 

○ Select Into 
   기존에 없는 새 테이블을 만들어서 데이터 입력 
   기존에 있는 테이블에 데이터를 넣으려면 Insert Select 		 
select * 
into Scenario 
from DimScenario


-- Scenario 테이블을 만들어서 
   DimScenario 테이블에 있는 모든 data 를 Scenario 테이블에 복사해 넣는다
-- 새로 만들어지는 Scenario 테이블은 PK, FK 등의 key 속성은 복사하지 않지만
   IDENTITY 속성은 복사한다.

 

DimScenario, Scenario 모두 동일

 

delete Scenario 
-------------------
select * 
into Scenario 
from DimScenario


-- [결과] error  (데이터는 삭제되었지만 Scenario 라는 테이블이 존재하므로)

 

○ Truncate Table  : 테이블은 남기고 데이터만 삭제 
    Drop Table       : 테이블째로 삭제 		 
truncate table Scenario

 

 

○ Insert Into ... Select 1/3 
   - 기존에 있는 테이블에 데이터 입력 
   - Identity 칼럼이 있는 경우 주의 		 
insert into Scenario
select * from DimScenario


-- 결과
메시지 8101, 수준 16, 상태 1, 줄 15
테이블 'Scenario'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.


-- 설명
현재 Scenario 는 빈 테이블인데, 이 중 ScenarioKey 칼럼이 IDENTITY 속성.
IDENTITY 속성인 칼럼에 복사를 하려하면 위처럼 error 발생.

○ Insert Into ... Select  2/3 
   - 일부 칼럼만 복사		 
insert into Scenario
select ScenarioName from DimScenario


-- 결과
3 rows affected


-- 설명
존재하고 있는 Scenario 테이블에 DimScenario 테이블로 부터 ScenarioName 칼럼을 복사해 넣는다

 

○ Insert Into ... Select 3/3 
   - 일부 칼럼만 복사 
   - source 칼럼과 destination 칼럼 모두 명시		 
insert into Scenario (ScenarioName) 
select ScenarioName from DimScenario


-- 설명
존재하고 있는 Scenario 테이블의 ScenarioName 칼럼에
DimScenario 테이블로 부터 ScenarioName 칼럼을 복사해 넣는다

 

결과 화면

 

○ delete 테이블명 / truncate table 테이블명  차이
  - delete    : identity 칼럼 번호를 계속 이어간다
  - truncate : identity 칼럼 번호를 1 로 리셋		 
-- DELETE / truncate 차이

drop table Scenario

select * 
into Scenario 
from DimScenario 


select * from DimScenario

select * from Scenario

delete Scenario
-- truncate table Scenario  -- 이 부분만 바꾸어서 실행

insert into Scenario
select ScenarioName from DimScenario

select * from Scenario
delete 결과 truncate 결과

마지막 테이블 ScenarioKey 칼럼 4 부터 시작

마지막 테이블 ScenarioKey 칼럼 1 부터 시작

 

 

○ Delete 를 하고 identity 번호를 다시 1 부터 넣는 방법
  - dbcc checkident()		 
drop table Scenario

select * 
into Scenario 
from DimScenario ;

select * from DimScenario;  --  첫번째 결과

select * from Scenario;     --  두번째 결과

delete Scenario;

dbcc checkident(Scenario, reseed, 0);

insert into Scenario
select ScenarioName from DimScenario;

select * from Scenario;     -- 세번째 결과

 

identity 칼럼은 일반적인 insert 문이나 update 문으로는 insert 도, update 도 할 수 없다.

 

 

○ Identity 칼럼에 특정 숫자를 insert 하자
  - set identity_insert Scenario on   (off 로 하면 다시 원래대로 못쓰게 함)
  - 이때, 원하는 칼럼리스트를 넣어주어야 한다.		 
set identity_insert Scenario on
--
insert into Scenario                   -- 실패 (칼럼명이 없다)
values(90,'Forecast 2011')
--
insert into Scenario (ScenarioKey, ScenarioName)  -- 성공 (칼럼명이 있다)
values(99,'Forecast 2011')
--
set identity_insert Scenario off       -- 다시 원래대로
--
select * from Scenario

두 번째 쿼리의 결과

 

○ Identity 칼럼은 중간에 번호가 비어도 마지막 번호부터 이어간다. 
select * from [dbo].[Scenario]        -- 결과 1
-------------------
insert into Scenario (ScenarioName)
values ('Forecast 2012')

insert into Scenario (ScenarioName)
values ('Forecast 2013')
-------------------
select * from Scenario                 -- 결과 2

 

◆◆◆◆◆◆◆◆◆◆◆◆◆◆

◆◆◆◆◆◆◆◆◆◆◆◆◆◆

 

Chapter 12  Vuews / User-Defined Functions

** 여기서는 table function 은 다루지 않고 scalar function 만 다룬다.

1. Join Select 문을 View 로

-- 테이블 3개를 join 한 select 문

select EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName 
from DimProduct                  as P
inner join DimProductSubcategory as S
   on P.ProductSubcategoryKey = S.ProductSubcategoryKey
inner join DimProductCategory    as C
   on S.ProductCategoryKey = C.ProductCategoryKey
where EnglishProductCategoryName = 'Bikes' or
      EnglishProductCategoryName = 'Accessories'

 

1. View 만들기
  - Create View 뷰이름 as + select 문		 
-- 위 SQL 문을 VIEW 로 만든다.

create view MyView as            -- 이 부분만 새로 들어간다
select EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName 
from DimProduct                  as P
inner join DimProductSubcategory as S
   on P.ProductSubcategoryKey = S.ProductSubcategoryKey
inner join DimProductCategory    as C
   on S.ProductCategoryKey = C.ProductCategoryKey
where EnglishProductCategoryName = 'Bikes' or
      EnglishProductCategoryName = 'Accessories'

 

 

2. 위에서 만들어진 VIEW 를 테이블처럼 사용한다 - SELECT 
select * from MyView

 

3. VIEW 수정
  - Alter view 뷰이름 as + select 문		 
-- 위 VIEW 를 수정한다.

alter view MyView as             -- Alter VIEW
select EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName 
from   DimProduct                as P
inner join DimProductSubcategory as S
        on P.ProductSubcategoryKey = S.ProductSubcategoryKey
inner join DimProductCategory    as C
        on S.ProductCategoryKey    = C.ProductCategoryKey
where EnglishProductCategoryName = 'Clothing' or
      EnglishProductCategoryName = 'Components'
--
select * from MyView

 

 

4. VIEW 삭제
  - Drop view 뷰이름		 
drop view MyView

 

 

◆◆◆◆◆◆◆◆◆◆◆◆◆◆

 

◆ User-Defined Function

1. 함수 만들기
  - create function 함수명(@파라미터  데이터타입)
    returns 데이터타입
    as
    begin
      함수수식
      return
    end

-- 테스트를 위한 base 쿼리를 만든다.

select EnglishProductName as [Product], round(ListPrice,2) as [Price],
       ceiling(ListPrice * 0.15) as [Tax] 
from   DimProduct
where  ListPrice is not null

** 여기서 Tax 구하는 부분을 함수로 만들어본다.

 

use AdventureWorksDW2008R2
go

create function Tax (@price money)
returns int
as
begin
  return ceiling(@price * 0.15)
end

-- 입력 파라미터 의 데이터타입 : money

    리턴 파라미터의 데이터타입 : int  (그대로 money 써도 되지만 연습을 위해)

 

2. 함수 사용
  - select 스키마명.함수(입력파라미터)		 
select dbo.Tax(20)

-- select Tax(20)  : error

 

select EnglishProductName as [Product], round(ListPrice,2) as [Price],
        dbo.Tax(ListPrice) as Tax 
from   DimProduct         -- 함수 사용
where  ListPrice is not null

 

3. 함수 삭제
  - Drop function 함수명		 
drop function Tax   -- 위 예제에서 사용한 Tax 함수를 삭제한다

 

 

4. 예제]  정수 3개를 받아서 모두 곱한 값을 리터하는 함수(CalculateVolume) 작성 
create function CalculateVolume (@x int, @y int, @z int)
returns int
as
begin
  return @x * @y * @z
end
--
select dbo.CalculateVolume(12,5,3) as Volume       -- 결과 1
--
select dbo.CalculateVolume(12.9,5,3) as Volume     -- 결과 2

-- [모두 확인후 함수 삭제]
drop function CalculateVolume

 

위 결과 1 과 결과 2 는 모두 180 이다.

 - 12.9 * 5 * 3 = 193.5

 - 입력값 데이터 타입이 모두 int 이므로 소수점 이하는 모두 잘라서 입력된다.

 

 

◆◆◆◆◆◆◆◆◆◆◆◆◆◆

◆◆◆◆◆◆◆◆◆◆◆◆◆◆

 

Chapter 13  Stored Procedure / Programming

 

1. WHILE 문
  - END WHILE 은 없다
  - Begin ... End 는 가능		 
-- begin ... end 없는 경우

declare @counter int

set @counter = 1
while @counter < 3
set @counter = @counter + 1
print @counter

-- 결과

   3

-- 설명  : 아래 두 문장이 한 세트 / print @counter 는 while 에 속하지 않는다.
   while @counter < 3
   set @counter = @counter + 1

 

 

-- begin ... end 있는 경우

declare @counter int
set @counter = 1
print @counter

while @counter < 3
begin
  set @counter = @counter + 1
  print @counter
end

-- 결과

   1

   2

   3

 

2. RETURN
  - 프로그램실행을 끝낸다.		 
print 'starting'
return
print 'finished'

-- 결과

  starting

 

 

3. If ... Else
  - Then / End If 없음
프로그램실행을 끝낸다.		 
if 1 <> 0
print 'true'
print 'got here'
else
print 'false'

-- 결과

   error

-- 설명

   if 1 <> 0 에는 print 'true' 만 적용된다.  따라서 else 부분이 문제가 된다. 

   위 프로그램을 실행시키려면 Begin ... End 를 사용한다.

 

if 1 <> 0
begin
  print 'true'
  print 'got here'
end
else
  print 'false'
  
-- 결과
-- true
-- got here     
if 1 <> 0
begin
print 'true'
print 'got here'
end
else
print 'false'
print 'got here too'


/* ----------------------
결과
true
got here
got here too
---------------------- */

여기서 if 문은 begin ~ print 'false' 까지.

 

if 1 <> 0
begin
print 'true'
print 'got here'
end
else
begin
print 'false'
print 'got here too'
end

/* ----------------------
결과
true
got here
---------------------- */

 

 

4. Case ... When ... End
  - Then 사용 가능		 
declare @minute tinyint
set @minute = datepart(mi,getdate())
declare @OddEven bit
set @OddEven = @minute %2
select
case @OddEven
when 0 then 'Even'
when 1 then 'Odd'
else 'Unknown'
end as [Minute]

/* ----------------------
결과
Odd  (or Even)
---------------------- */

 

 

5. Select 문에서 변수 사용
nchar(1) 타입으로 maritalstatus 라는 변수를 선언하고
이 변수에 'M' 을 넣고
DimCustomer 테이블에서 MaritalStatus 가  변수 maritalstatus 의 값을 가지는 데이터 select
 
declare @maritalstatus nchar(1)
set @maritalstatus = 'M'
select * from DimCustomer
where MaritalStatus = @maritalstatus

 

 

Stored Procedure 로 넘어가기 위한 Base 쿼리

 

select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer

 

1. Create Proc 프로시저명 as
   Begin
     ...
   End		 
-- first stored procedure
create proc GetCustomers as
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer


-- better
create proc GetCustomers as
begin
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer
end

 

 

2. Alter Proc 프로시저명 as
   Begin
     ...
   End		 
alter proc GetCustomers as
begin
-- [Full Name] 을 Fullname 으로 변경
select FirstName + ' ' + LastName as [Fullname], MaritalStatus, Gender
from DimCustomer
end

 

3. 실행 : exec procedure명 
alter proc GetCustomers as
begin
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer
where MaritalStatus = 'S' and Gender = 'F'
end

--------------------------

-- 실행
exec GetCustomers

 

 

4. procedure 에 내부 변수 사용 
-- where 절에 변수 사용
alter proc GetCustomers as
begin

declare @maritalstatus nchar(1)  -- 내부 변수
declare @gender nvarchar(1)      -- 내부 변수
set @maritalstatus = 'S'
set @gender = 'F'

select FirstName + ' ' + LastName as [Full Name], 
       MaritalStatus, 
       Gender
from   DimCustomer
where  MaritalStatus = @maritalstatus 
       and Gender = @gender
end


-- 실행
exec GetCustomers

/* --------------------------------
결과 :
MaritalStatus = S 이고 Gender = F 인 row 출력
-------------------------------- */

 

 

5. procedure 에 input parameter 사용 
alter proc GetCustomers
@maritalstatus nchar(1),  -- 외부 변수
@gender nvarchar(1)       -- 외부 변수
as
begin

select FirstName + ' ' + LastName as [Full Name], 
       MaritalStatus, 
       Gender
from   DimCustomer
where  MaritalStatus = @maritalstatus 
       and Gender = @gender
	   
end


-- 실행 1
exec GetCustomers         -- error

-- 실행 2
exec GetCustomers 'S', 'F'

/* ------------------------
실행 2 의 결과 :
MaritalStatus = S  Gender = F 인 row select
/* ------------------------

 

 

6. procedure 에 input parameter 사용  +  변수에 default 값 주기 
alter proc GetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F'
as
begin

select FirstName + ' ' + LastName as [Full Name], 
       MaritalStatus, 
       Gender
from   DimCustomer
where  MaritalStatus = @maritalstatus 
       and Gender = @gender

end


-- 실행 1
exec GetCustomers


-- 실행 2
exec GetCustomers 'M', 'M'

/* ---------------------------
실행 1 결과 :
default 로 설정된 F / S 값이 들어간 row select

실행 2 결과 :
입력된 파라미터 M /M 값이 들어간 row select
--------------------------- */

 

 

7.  Output Parameter 사용  
alter proc GetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F',
@count int out                  -- out parameter 선언
as
begin

select FirstName + ' ' + LastName as [Full Name], 
       MaritalStatus, 
       Gender
from   DimCustomer
where  MaritalStatus = @maritalstatus 
       and Gender = @gender

set @count = @@ROWCOUNT

end


/*******************************

-- 실행 1
exec GetCustomers

-- 실행 2
exec GetCustomers 'M', 'F'

-- 실행 3
exec GetCustomers @count out

-- 실행 4
declare @count int
exec GetCustomers 'M', 'M', @count out
select @count as [Records]

-- 실행 5  p. 265
declare @count int
exec GetCustomers , , @count out
select @count as [Records]

-- 실행 6  p. 265
declare @count int
exec GetCustomers default, default, @count out
select @count as [Records]

-- 실행 7  p. 265
declare @count int
declare @return int
exec @return = GetCustomers default, default, @count out
select @count as [Records]
select @return as [Return]

*******************************/

 

실행 결과

-- 결과 1
프로시저 또는 함수 'GetCustomers'에 매개 변수 '@count'이(가) 필요하지만 제공되지 않았습니다.

-- 결과 2
프로시저 또는 함수 'GetCustomers'에 매개 변수 '@count'이(가) 필요하지만 제공되지 않았습니다.

-- 결과 3
스칼라 변수 "@count"을(를) 선언해야 합니다.

-- 결과 4
   Records
----------
1  5266

-- 결과 5
',' 근처의 구문이 잘못되었습니다.

-- 결과 6
   Records 
---------- 
1  4388 


-- 결과 7
   Records 
------------ 
1  4388 

   Return 
------------ 
1  0 

 

 

8.  Output Parameter + Return  in Procedure 
-- p. 267
alter proc GetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F',
@count int out
as
begin

declare @err int

select FirstName + ' ' + LastName as [Full Name], 
       MaritalStatus, 
       Gender
from   DimCustomer
where  MaritalStatus = @maritalstatus 
       and Gender = @gender

set    @count = @@ROWCOUNT

if @count = 0
  return -99
else
  return 0

end


/****************************************
declare @count int
declare @return int
exec @return = GetCustomers 'X', 'Y', @count out
-- X, Y 를 넣으면 row 는 0개 select
select @count as [Records]
select @return as [Return]
****************************************/

 

 

결과 :

   Records 
------------ 
1  0 

   Return 
------------ 
1  -99

 

 

9. Procedure 에서 다른 Procdure 호출 
-- p. 268
create proc CallGetCustomers as
begin

declare @count int
declare @return int

exec @return = GetCustomers 'S', 'F', @count out

select @count as [Records]
select @return as [Return]

end


/***********************************
실행 :
exec CallGetCustomers
***********************************/

 

결과 :

   Records 
------------ 
1  4388

   Return 
------------ 
1  0

 

 

10. Try ... Catch  

 

-- p. 269
alter proc CallGetCustomers as
begin

declare @count int
declare @return int

begin try
  exec @return = GetCustomers 'S', 'F', @count out
  // GetCustomers 'S', 'F', @count out 호출하면 리턴값 = 0
end try

begin catch
  set @count = 0
  set @return = -100

  print ERROR_MESSAGE()
end catch

select @count as [Records]
select @return as [Return]

end


/******************************
exec CallGetCustomers
******************************/

 

결과 :

   Records 
------------ 
1  4388

   Return 
------------ 
1  0

설명 : Try block 에서 문제가 발생하면 Catch block 이 trigger 된다

 

만일 GetCustomers 에서 from   DimCustomer 을  from   XXXX 라고 하고 실행하면

결과 :

   Return 
------------ 
1  -100

 

 

11. Procedure 호출부분에 input parameter 넣어주기

 

-- p. 271

alter proc CallGetCustomers
@maritalstatus nchar(1)    = 'S',
@gender        nvarchar(1) = 'F'
as
begin

declare @count int
declare @return int

begin try
  -- input parameter 에 상수가 아닌 변수를 넣어준다
  exec @return = GetCustomers @maritalstatus, @gender, @count out
end try

begin catch
  set @count = 0
  set @return = -100

  print ERROR_MESSAGE()
end catch

select @count as [Records]
select @return as [Return]

end


/*****************************************
-- 실행 1
exec CallGetCustomers 'X', 'Y'  -- 결과 row 개수 = 0

-- 실행 2
exec CallGetCustomers default, default

-- 실행 3
exec CallGetCustomers 'S', 'M'
*****************************************/

 

결과 1 :
   Records 
------------ 
1  0

   Return 
------------ 
1  -99
--------------------------------

결과 2 :
   Records 
------------ 
1  4388

   Return 
------------ 
1  0
--------------------------------

결과 3 :
   Records 
------------ 
1  4085

   Return 
------------ 
1  0

 

저작자표시

'[prog] SQL - MSSQL' 카테고리의 다른 글

MSSQL 데이터타입 / 변수사용  (0) 2020.12.18
SQL SERVER - SSMS tip  (0) 2020.10.15
MSSQL 날짜변환 / 날짜계산CONVERT / DATETIME  (0) 2020.10.11
자주 사용하는 쿼리문들 (Queries Frequently Used)  (0) 2020.09.17
SQL SERVER / 서버의 DB 를 로컬로 복사(가져오기)  (0) 2020.09.09
Posted by dawnawaker

티스토리툴바