:: 작업장

결과
DEPTNO   AVG(SAL)

---------- ----------

10         2916.66667

20         2175

* 오라클에서는 NULL 값을 가장 큰 값으로 취급한다.

* SQL Server에서는 NULL 값을 가장 작은 값으로 취급.

ENAME COMM

---------- ----------

TURNER     0

ALLEN   300

WARD   500

MARTIN 1400

SCOTT

KING

ADAMS

JAMES

FORD

MILLER

BLAKE

JONES

GOODBOY

CLARK

EX4)여러가지 컬럼을 기준으로 정렬

연봉이 높은 순서대로, 연봉이 같은경우 커미션이 높은 순서대로 정렬

SELECT ENAME, SAL, NVL(COMM,0) NVL_COMM

FROM EMP

ORDER BY SAL, NVL_COMM;

EX5) 칼럼 순서로 정렬.

SELECT ENAME, SAL, NVL(COMM,0) NVL_COMM

FROM EMP

ORDER BY 2,3,1;

예제. DEPT 테이블 정보를 부서명, 지역별, 부서번호 내림차순으로 정렬하여 출력. EX1) 칼럼명사용. SELECT DNAME, LOC, DEPTNO FROM DEPT ORDER BY DNAME, LOC, DEPTNO; EX2) 칼럼명 + ALIAS 사용  SELECT DNAME, LOC LOC_AS, DEPTNO FROM DEPT ORDER BY DNAME, LOC_AS, DEPTNO; EX3) 칼럼순서번호 + ALIAS 사용 SELECT DNAME, LOC LOC_AS, DEPTNO FROM DEPT ORDER BY 1, LOC_AS, 3; -결과는 모두 같다.

2. SELECT 문장 실행 순서.

- GROUP BY 절과 ORDER BY 절이 같이 사용될 때 SELECT 문장은 6개의 절로 구성된다.

=> 문장 실행 순서.(옵티마이저가 SYNTAX 및 SEMANTIC 에러를 점검하는 순서) 

1. 발췌대상 테이블 참조.( FROM )

2. 발췌대상 데이터가 아닌것은 제거 ( WHERE)

3. 행들을 소그룹화 (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만을 출력 (HAVING)

5. 데이터값을 출력 및 계산 (SELECT)

6. 데이터를 정렬(ORDER BY)

* ORDER BY절에는 SELECT 목록에 없는 문자형 항목이 포함될 수 있다.

단, SELET DISTINCT를 지정하거나 SQL 문장에 GROUP BY 절이 있거나 또는 SELECT문에 UNION 연산자가 있으면 열정의가 SELECT 목록에 표시되어야 한다.

(관계형 DB에서 데이터를 메모리로 올릴 때 행단위로 모든 컬럼을 가져오게되므로 SELECT절에서는 일부만 선택하더라도 ORDER BY 절에서 메모리에 올라온 다른 컬럼데이터를 사용할 수 있다.)

ex) SELECT 절에 없는 EMP 칼럼을 ORDER BY 절에 사용.

SELECT EMPNO, ENAME

FROM EMP

ORDER BY MGR ;

     EMPNO ENAME

---------- ----------

      7902 FORD

      7788 SCOTT

      7900 JAMES

      7499 ALLEN

      7521 WARD

      7844 TURNER

      7654 MARTIN

      7934 MILLER

      7876 ADAMS

      7698 BLAKE

      7566 JONES

      7782 CLARK

      7369 GOODBOY

      7839 KING

ex) 인라인 뷰에 정의된 SELECT 칼럼을 메인쿼리에서 사용.

SELECT EMPNO

FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);

ex) 인라인 뷰에 미정의된 SELECT 칼럼을 메인쿼리에서 사용.

SELECT MGR

FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;)

결과

ERROR at line 1:

ORA-00904: "MGR": invalid identifier

=> MGR이 인라인뷰를 통해 조회되지않음.

- 서브쿼리절에서 선택되지 않은 컬럼들은 계속 유지가 되는것이 아니라 서브쿼리 범위를 벗어나면 더이상 하용할 수 없다.

-GROUP BY 절에서 그룹핑 기준을 정의하게 되면 데이터베이스는 일반적인 SELECT 문장처럼 FROM 절에 정의된 테이블 구조를 그대로 가지고 가는 것이 아니라 GROUP BY 절의 그룹핑 기준에 사용된 칼럼과 집계 함수에

사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만든다.

- GROUP BY 절을 사용하게 되면 그룹핑 기준에 사용된 칼럼과 집계함수에 사용될 수 있는 숫자형 데이터 컬럼들의 집합을 새로 마드는데 개별 데이터는 필요 없으므로 저장되지 않는다. GROUP BY 이후 수행 절인 SELECT 절이나 ORDER BY 절에서 개별 데이터를 사용할 경우 에러가 발생한다.

* SELECT 절에서는 그룹핑 기준과 숫자 형식 칼럼의 집계함수를 사용할 수 있지만 그룹핑 기준 외의 문자 형식 칼럼은 정할 수 없다.

- GROUP BY 절 사용 시 SELECT 절에 일반 칼럼을 사용

SELECT JOB, SAL

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY SAL;

ERROR at line 1:

ORA-00979: not a GROUP BY expression

=> 에러발생.

- GROUP BY 절 사용시 ORDER BY 절에 일반 칼럼을 사용해본다.

SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY SAL;

ORA-00979: not a GROUP BY expression

=> 에러발생

- GROUP BY 절 사용시 ORDER BY 절에 집계 칼럼 사용.

SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

JOB

---------

singer

people

actor

MANAGER

PRESIDENT

SALESMAN

ANALYST

CLERK

3. TOP N 쿼리

- ROWNUM

오라클에서 우선순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM조건을 같이 사용하는 경우가 있는데 이 두조건으로는 원하는 값을 얻을 수 없다.

오라클의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라 데이터의 일부가 추출된 후 데이터에 대한 정렬작업을 수행한다.

예제. 사원테이블에서 급여가 높은 3명만 내림차순으로 출력

SELECT ENAME, SAL

FROM EMP

WHERE ROWNUM < = 3

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

ALLEN 1600

WARD 1250

GOODBOY   800

=> 해당 결과는 급여에 대한 오름차순 정렬이 아니라 무작위로 추출된 3명에 대해 급여를 내림차순으로 정렬한 결과이므로 원하는 결과가 출력된것이아니다.

( WHERE 절이 ORDER BY 절보다 먼저 수행 되기 때문이다.)

A. 정답. 인라인 뷰를 통해 데이터 정렬을 먼저 수행 후 메인쿼리에서 ROWNUM 사용.

SELECT  ENAME, SAL

FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)

WHERE ROWNUM < = 3 ;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

FORD 3000

* 인라인뷰를 사용하여 추출하고자 하는 집합을 정렬한 후 ROWNUM을 적용시킴으로써 결과에 참여하는 순서와 추출된는 로우 순서를 일치 시킴으로써 TOP N 쿼리의 결과를 만들었다.

- TOP

반면에, SQL server는 조건을 사용하게 되면 별도의 처리 없이 관련 ORDER BY 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력할 수 있다.

* TOP 절을 사용해서 결과 집합으로 반환되는 행의 수를 제한할 수 있다. 

- WITH TIES 옵션은 ORDER BY 절의 조건 기준으로 TOP N 의 마지막 행으로 표시되는 추가 행의 데이터가 같을경우 N+ 동일정렬 순서 데이터를 추가 반환하도록 지정하는 옵션이다.

EX) 사원테이블에서 급여가 높은 2명을 내림차순으로 출력.

SELECT TOP(2) ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

EX2) 사원테이블에서 급여가 높은 2명을 내림차순으로 출력하는데, 같은 급여를 받는 사원이 있으면 같이 출력

SELECT TOP(2) WITH TIES ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

FORD 3000

합성연산자

- 문자와 문자를 연결하는 합성 연산자를 이용하면 별도의 프로그램 도움 없이 SQL 만으로 유용한 리포트를 출력할 수 있다.

- 문자와 문자를 연결하는 경우 2개의 수직바 || 를 통해 이루어진다.(ORACLE)

- 문자와 문자를 연결하는 경우 + 표시에 의해 이루어진다 (SQL SERVER)

- 두 벤더는 모두 공통적으로 CONCAT(STRING1, STRING2) 함수를 사용할 수 있다.

- 칼럼과 문자ㄴ 또는 다른 칼럼과 연결시킨다

- 문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.

ex) 출력형태 선수명 선수, 키 cm 몸무게 kg

- 오라클

SELECT PLAYER_NAME || '선수,' || HEIGHT || 'CM ,' || WEIGHT || 'KG' 체격정보

FROM PLAYER;

- SQL server

SELECT PLAYER_NAME + '선수 ,' + HEIGHT + 'CM ,' + WEIGHT + 'KG ' 체격정보