41.MS_SQL 2008 - Select 기타 키워드

--[1]테이블생성

 

-- 삭제하기위해다음과같이명령어사용

-- Drop Table Customers

Create Table dbo.Customers

(

       Num Int Identity(1,1) Primary Key,

       Name VarChar(25) Not Null,

       Age Int 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

 

 

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

Select Distinct Address From Customers

 

 

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

Select Address, AVG(Age) 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 사용시그룹화된항목인지표시

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

---

41SelectKeyWord.sql