--[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
'DataBase > MS SQL' 카테고리의 다른 글
43.MS_SQL 2008 - Output과 Return 키워드 (0) | 2009.09.18 |
---|---|
42.MS_SQL 2008 - 입력,출력,상세,수정,삭제,검색,뷰,인덱스 복습 (0) | 2009.09.17 |
40.MS_SQL 2008 - Index ( 인덱스효과 ) 복습 (0) | 2009.09.16 |
39.MS_SQL 2008 - Index ( 인덱스효과 ) (0) | 2009.09.16 |
38.MS_SQL 2008 - DTS - 우편번호 데이터 가져오기 (0) | 2009.09.16 |
Comments