40. Select문 기타키워드(Distinct / Case 등등)

--[1] 테이블생성: 고객, 나중에여러분들이무엇인가를만든다면???

Create Table dbo.Customers

(

       Num Int Identity(1, 1) Primary Key, -- 일련번호

       Name VarChar(25) Not Null, -- 이름

       Age TinyInt Null, -- 나이

       Address VarChar(100) -- 주소

)

Go

 

 

--[2] 샘플데이터입력

Insert Customers Values('홍길동', 21, '서울')

Insert Customers Values('백두산', 31, '서울')

Insert Customers Values('한라산', 41, '부산')

Insert Customers Values('지리산', 21, '부산')

Insert Customers Values('설악산', 31, '대전')

 

 

-- Select문주요기능확인

 

-- 전체고객리스트

Select * From Customers

 

--[1] 중복제거(Distinct) : 고객들이사는지역을중복없이출력하시오.

Select Distinct Address From Customers

 

 

--[2] Group By : 집계함수그룹화: 같은지역고객의나이의평균

Select Address, Avg(Age) As [지역별평균나이] From Customers

Group By Address

 

 

--[3] 조건이있는집계함수그룹화:

-- 같은지역고객중나이가40이상인고객의나이의평균

Select Address, Avg(Age) From Customers

Where Age >= 40

Group By Address

 

-- Group By All : 위쿼리문중조건에맞지않아도지역리스트출력

Select Address, Avg(Age) From Customers

Where Age >= 40

Group By All Address

 

 

--[4] Having : 집계함수에대한조건처리

-- 같은지역고객의나이의평균이30 이상인데이터만출력

Select Address, Avg(Age) As 나이평균 From Customers

Group By Address

Having Avg(Age) >= 30

 

 

--[5] RollUp : 소계: 지역별나이를출력후나이소계(중간합계)

Select Address, Avg(Age) From Customers

Group By Address With RollUp

 

--[6] Cube : 소계: 지역별나이를출력후나이소계(중간합계)

Select Address, Avg(Age) From Customers

Group By Address With Cube

 

--[7] Grouping() 함수:

-- RollUp/Cube 사용시그룹화된항목인지표시(예:1, 아니요:0)

Select Address, Avg(Age), Grouping(Address) As 그룹화여부

From Customers

Group By Address With Cube

 

--[8] Compute : 출력결과에대한집계

Select Address, Age From Customers

Compute Sum(Age), Avg(Age)

 

--[9] Compute By : 출력결과에대한집계에대한정렬

Select Address, Age From Customers Order By Address

Compute Sum(Age), Avg(Age) By Address

 

 

 

--[10] Case : 문장대체

Select Name, Age, Address From Customers

Select Name, Age,

       Address =

       Case Address

             When '서울' Then 'Seoul'

              When '부산' Then 'Busan'

             Else '다른지역'

       End

From Customers

 

 

--[!] 테이블삭제

Drop Table dbo.Customers

Go

 

--[1]번부터[10]번까지다시연습해보세요...