shunmania

뇌를 자극하는 MSSQL SERVER2005 공식 사이트에서 다운받기

BETWEEN AND 와 OR,  IN()

- userTBL에서 height가 180이상이면서 183이하인 데이터 추출

기본 방식:
SELECT name,height FROM userTBL WHERE height >=180 AND height <=183

BETWEEN AND 사용:
SELECT name,height FROM userTBL WHERE height BETWEEN 180 AND 183

- userTBL에서 height가 180 이거나 183인 데이터 추출

OR 사용:
SELECT name,height FROM userTBL height=180 OR height=183

IN 사용
SELECT name,height FROM userTBL WHERE height IN(180,183)


서브쿼리에서 ANY, ALL, SOME

userTBL의 mobile1 컬럼에는 핸드폰 앞자리 번호가 저장되어 있다.
핸드폰 번호가 '019' 인 사람들의 키보다 크거나 같은 사람들을 추출해보자

SELECT name,height FROM userTBL WHERE height >= (SELECT height FROM userTBL WHERE mobile1 = '019')
위와 같은 쿼리를 실행하면 오류가 난다. 'SELECT height FROM userTBL WHERE mobile1 = '019' 쿼리가 데이터행을
2행 이상 반환하기 때문이다.

위의 쿼리를 아래와 같이 수정한다.

SELECT name,height FROM userTBL WHERE height >= ANY (SELECT height FROM userTBL WHERE mobile1 = '019')

ANY는 반환된 서브쿼리에서 반환된 여러개의 데이터행들 중 하나라도 만족하는 값이 있으면 해당 값을 추출한다.
height컬럼의 값이 179부터 183까지 있는데 하나라도  height >= 의 조건을 만족하면 출력하는 것이다.
결국 ANY는 IN() 함수와 같은 결과를 제공한다. Some은 ANY와 같은 의미이다.

위의 쿼리를 ALL로 바꿔서 출력해보자

SELECT name,height FROM userTBL WHERE height >= ALL (SELECT height FROM userTBL WHERE mobile1 = '019')

이번엔 하나의 데이터만 추출되었다.
ALL은 모든 데이터가 height>=을 만족해야 한다.179부터 183까지 있는데 height>=179도 만족해야 하고 height>=183도 만족해야 한다.


ORDER BY

오름차순 정렬
SELECT name,height FROM userTbl ORDER BY height;

ORDER BY절만 입력할 경우 기본적으로 오름차순 정렬이 적용된다. 명시적으로 전부 입력해주고 싶으면
SELECT name,height FROM userTbl ORDER BY height ASC; 라고 입력하면 된다.

내림차순 정렬
SELECT name,height FROM userTbl ORDER BY height DESC;

DISTINCT

userTbl에 있는 주소 정보를 우선 출력해보자

SELECT addr FROM userTbl

주소 정보가 어디어디가 있는지를 알고 싶은데 중복되는 데이터가 있어서 번거롭다.
중복되는 데이터는 모두 하나로 합쳐서 단일의 데이터만 출력하고 싶다면

SELECT DISTINCT addr FROM userTbl;

중복된 데이터는 모두 하나로 합쳐졌다.


TOP()

이번엔 adventureworks DB를 가지고 사용해보자.
sales.creditcard 테이블에 expyear 라는 신용카드 만료연도 와 expmonth라는 신용카드 유효월이 있다.
여기에서 만료일자가 가까운 10개의 데이터만 뽑아낸다고 해보자.

SELECT TOP(10) CreditCardID FROM Sales.CreditCard WHERE CardType = 'VISTA'  ORDER BY ExpYear,ExpMonth;

* TOP(N)은 이전 버전의 SQL server와의 호환성을 위해 TOP N 방식으로 써도 되지만 되도록 TOP(N) 형식을 권장한다.

TOP구문은 SQL SERVER2005에서는 변수 , 수식, 하위쿼리도 사용 할 수 있다.

SELECT TOP(SELECT COUNT(*) /100 FROM Sales.CreditCard)
       CreditCardID FROM Sales.CreditCard
       WHERE CardType='VISTA'
       ORDER BY ExpYear,ExpMonth

다음은 전체 상위 건수의 0.1%만 추출하는 쿼리문이다.

SELECT TOP(0.1) PERCENT

       CreditCardID FROM Sales.CreditCard
       WHERE CardType='VISTA'
       ORDER BY ExpYear,ExpMonth

아래의 쿼리는 상위 0.1% 갯수의 데이터만 추출하지만 갯수 범위과 초과하더라도 값이 같으면 추출하는 쿼리이다.


SELECT TOP(0.1) PERCENT WITH TIES CreditCardID,ExpYear,ExpMonth
    FROM Sales.CreditCard
    WHERE CardType='VISTA'
    ORDER BY ExpYear,ExpMonth;

GROUP BY와 HAVING

아래의 쿼리를 우선 실행해 보자. 아래의 쿼리는 userid별로 물품을 구매한 내역이 있는 테이블이다. 쿼리를 실행하면 아래 그림과 같은
데이터가 추출되어 나온다

SELECT userid,amount FROM buyTbl ORDER BY userid;

현재 userid가 중복되어 나타나고 있으며 중복되는 userid별로도 구매수량이 따로 입력되어 있다. 이것을 하나의 userid마다 전체 구매수량을
확인하고 싶다고 할 경우 GROUP BY를 이용한다. 아래의 쿼리를 실행시켜 보자.

SELECT userid,SUM(amount) FROM buyTbl GROUP BY userid ORDER BY userid;

GROUP BY 절을 사용해서 userid별로 그룹화 시키고 sum() 함수를 사용하여 각 userid의 구매수량을 더하였다.
GROUP BY 함수를 사용할 경우 위에서 설명한 sum()과 같은 집계함수를 이용하면 되며 GROUP BY 절에 명시된 컬럼은 SELECT 절 다음에
반드시 지정이 되어 있어야 한다.

*집계함수는 AVG(): 평균, MIN():최소값 구하기, MAX():최대값 구하기, COUNT()전체 행의 갯수  구하기 등의 함수를 말한다.
 GROUP BY 없이 별도의 컬럼을 집계 함수와 함께 사용 할 수 없다.

위의 SELECT userid,SUM(amount) FROM buyTbl GROUP BY userid ORDER BY userid; 쿼리를 실행한 결과에서 특정 조건을 추가하여
결과를 추출하고 싶다면 HAVING절을 이용한다. HAVING 절은 WHERE절과 비슷한 역할을 하며 GROUP BY절을 이용하여 그룹핑한 데이터에
대해서 조건을 거는 것이다.

SELECT userid ,SUM(price * amount) FROM buyTbl
    GROUP BY userid
    HAVING SUM(price*amount)>1000