▲ Select 컬럼필터링(ColumnFiltering) From 로우필터링(RowFiltering)
테이블은 데이터집합(Data Set)이다. From 테이블의 개념을 조금 더 확장하면 From '어떤 데이터 셋' 이 된다. 데이터베이스 활용에서는 이 개념이 기초가 되므로 잘 익혀둔다. 연습을 위해 Nrothwind, Pubs 예제 데이터베이스가 있어야 한다.
Select * From ::fn_helpCollations()
Select * From dbo.titleView
Select * From ( Select * From titles ) as A
Select * From ( Select GetDate() as 현재 ) as A
▲ 파생컬럼 : 임의의 컬럼 하나를 만드는 법
Select title_id, price, royalty, Round() as [로열티 포함 가격], GetDate() as 현재
From Pubs..titles
▲ 플러스 효과 ( + ) : 2개 컬럼의 문자열을 붙이거나 산술연산에 사용
Select type + ' : ' + title as [묶음 타이틀]
From Pubs..titles
Select GetDate() + 1
Select GetDate() - 1
Select Cast( GetDate() - ( GetDate() - 1 ) as int )
▲ Where 절에 사용되는 조건문 유형
(Where 이하 생략 ) 컬럼이름 = '어떤 값'
컬럼이름 <= 10, 컬럼이름 < 10, 컬럼이름 >= 10, 컬럼이름 > 10
컬럼이름 != '문법', 컬럼이름 <> '문법'
컬럼이름 Between 45 and 50
컬럼이름 Like '%huey%' 또는 Not Like '%huey' /* 검색에 많이 활용되므로 Like 사용법을 잘 알아둔다. */
▲ 다양한 Select 문제 해결의 방법
- 각 출판사 별로 몇 권의 책들을 출판했는가?
Select pub_name, (
Select Count(*) From Pubs..titles Where pub_id = A.pub_id ) [발간물 수]
From pubs..publishers A
- 프랑스에 있는 출판사 목록을 뽑아내라.
Select * From Pubs..Publishers
Where country = 'France'
- Northwind 주문데이터에서 모든 경유지를 뽑는데 운송지(ShipCity)가 독일(Germany)의 Cunewalde인 경우에는 경유지(Shipvia)가 2 이상 거쳐가는 곳만 포함하라.
select OrderID, CustomerID, ShipVia, Shipcity, ShipCountry from Northwind..Orders
where shipvia >= ( case ShipCity when 'Cunewalde' then 2 else 0 end )
또는
select * from (
select *, ( case shipcity when 'Cunewalde' then 2 else 0 end ) as returnX
from northwind..orders ) as X
where X.shipvia >= X.returnX
- Northwind..Orders 테이블에서 운송료(Freight)가 상위 10%인 것을 제외한 나머지 주문 데이터를 뽑아라.
/* 먼저 운송료를 내림차순으로 정렬하여 상위 10%만 출력하였다. */
select top 10 percent OrderID, Freight from Northwind..Orders
Order by Freight desc
/* 결과적으로 위 구문에서 얻어진 내용을 제외한 나머지를 뽑으면 되는 것이다. 이 때 not in () 을 where 조건절에 사용하였다. */
select * from Northwind..Orders
where OrderID not in ( select top 10 percent OrderID
from Northwind..Orders
Order by Freight desc )
Order by Freight desc
- Northwind..Orders 테이블에서 운송료(Freight)가 상위 10%, 하위 10%인 것을 제외한 나머지 주문 데이터를 뽑아라.
select * from Northwind..Orders
where OrderID not in (
select top 10 percent OrderID from Northwind..Orders Order by Freight desc)
and OrderID not in (
select top 10 percent OrderID from Northwind..Orders Order by Freight )
/* 위 예문의 결과에서 이 한 줄만 추가하였다. */
/* 다른 방법은? */
select * from Northwind..Orders
where OrderID not in
(
select orderID from
(
select OrderID, Freight from ( select top 10 percent orderID, freight from Northwind..Orders Order by Freight desc ) A
Union All
select OrderID, Freight from ( select top 10 percent orderID, freight from Northwind..Orders Order by Freight ) B
) C
)
▲ Having : 결과 집합을 만들어낸 후 맨 마지막에 조건을 처리한다.
- Northwind 주문 테이블에서 선박명(ShipName)이 C로 시작하지 않는 선박들의 평균운임(Freight)은 얼마인가?
먼저 C로 시작하지 않는 선박명 목록을 구해본다.
Select ShipName, Freight From Northwind..Orders
Where ShipName Not Like 'C%'
Order by ShipName
필자는 이 구문을 처음에 이렇게 작성하였다.
Select ShipName, Freight From Northwind..Orders
Where ShipName Not In (
Select ShipName From Northwind..Orders
Where ShipName Like 'C%' )
Order by ShipName
어떤가? 결과를 내기 위해 똑같은 생각을 했지만 초보 때 작성한 구문과 어느 정도 훈련된 상태에서 작성한 구문이 이렇게 다르다. 그래서 꾸준하게 훈련해야 하고 기본기의 중요성은 두 말 할 필요가 없다.
이제 목록을 출력해봤으니 Freight 평균을 내야할 단계다. 운임평균은 AVG(Freight) 하면 되는데 이 때 무엇을 기준으로 Group by 해야할까?
ShipName 별로 평균을 내야 하기 때문에 Group by ShipName 이다.
Select ShipName, AVG(Freight) 평균운임 From Northwind..Orders
Where ShipName Not Like 'C%'
Group by ShipName
Order by ShipName
같은 내용을 Having을 사용하면 다음과 같다.
Select ShipName, AVG(Freight) 평균운임 From Northwind..Orders
Group by ShipName
Having ShipName Not Like 'C%'
Order by ShipName /* Having 앞에 Order by를 쓰면 에러 발생 !! */
Where 조건절의 내용을 Having 절에 그대로 사용하였다. 즉 Having절에 Where 조건절의 내용과 똑같은 내용을 포함할 수 있다. Where 절은 원하는 결과 집합을 찾는 경로를 지시하는 것이라면 Having은 결과집합을 먼저 만들어 둔 후 이 결과 집합을 다시 한번 필터링 한다.
좀 더 자세히 말하면 Where 절을 사용한 구문을 보면 Where ShipName Not Like 'C%' 를 먼저 처리한 결과 집합에서 AVG(Freight)를 계산하여 보여준다. 그러나 Having 구문에서는 ShipName으로 그룹핑되어 정렬(Sorting)된 결과집합을 먼저 만든 후 AVG(Freight) 계산을 한다. 그리고 나서 Having 절을 처리한다. 따라서 Having 절은 Select 연산의 맨 마지막 과정에서 처리되는 것이 Where 절 사용하는 구문과의 차이점이다. 다음 예문으로 조금 더 고민해보면 Having 절의 장점을 알게 될 것이다.
- Northwind..Orders 테이블에서 ShipName이 C로 시작하지 않는 ShipName의 평균 Freight를 구하는데 평균 Freight는 30달러 이상이어야 한다. (충분한 시간을 가지고 각자 해결해본 후 다음을 참고한다.)
필자는 이렇게 해결하였다.
select ShipName, AVG(Freight) 평균운임 From Northwind..Orders
Where ShipName Not Like 'C%'
Group by ShipName
Having AVG(Freight) >= 30
Order by 평균운임
Having을 쓰지 않으면 다음과 같이 해야 할 것이다.
Select * From (
Select ShipName, AVG(Freight) 평균운임 From Northwind..Orders
Where ShipName Not Like 'C%'
Group by ShipName ) A
Where A.평균운임 >= 30
Order by 평균운임
헬기조종사 휴이(huey) 당부 사항 :
위 예제는 손호성님이 쓰신 SQL server 2005 가이드와 MSDN의 SQL Documentation을 공부하면서 필자가 요약 정리한 내용들 입니다. 플밍을 제대로 하려는 뜻이 있고 SQL 2005를 깊이 있게 공부할 생각이 있다면 반드시 저자 손호성님의 책을 구해서 줄 긋고 형광펜 칠 해가면서 공부하시기 바랍니다.
책 구매 링크 : http://www.yes24.com/24/goods/2708644?scode=032&OzSrank=1
'Mobile & IT Life > MS-SQL' 카테고리의 다른 글
| [sql 2005] Transact SQL 기본 (12) : Select Advanced (5) 집계함수 다루기 (0) | 2011/04/29 |
|---|---|
| [sql 2005] Transact SQL 기본 (11) : Select Advanced (4) 집계함수 다루기 (0) | 2011/04/29 |
| [sql 2005] Transact SQL 기본 (10) : Select Advanced (3) 집계 (0) | 2011/04/29 |
| [sql 2005] Transact SQL 기본 (9) : Select Advanced (2) Order by (0) | 2011/04/29 |
| [sql 2005] Transact SQL 기본 (8) : Select Advanced (1) (0) | 2011/04/29 |
| [sql 2005] Transact SQL 기본 (7) : Null 찜쪄먹기 (0) | 2011/04/28 |
| [sql 2005] Transact SQL 기본 (6) : Delete ~ From ~ (0) | 2011/04/28 |
| [sql 2005] Transact SQL 기본 (5) : Update ~ From ~ (0) | 2011/04/28 |
| [sql 2005] Transact SQL 기본 (4) : INSERT (0) | 2011/04/28 |
| [sql 2005] Transact SQL 기본 (3) (0) | 2011/04/27 |
| [sql 2005] Transact SQL 기본 (2) (0) | 2011/04/27 |
댓글을 달아 주세요