:: 작업장

 가. 선수 - 팀 EQUI JOIN 사례

- 선수 테이블과 팀 테이블에서 K리그 소속 선수들의 이름,백넘버와 그 선수가 소속되어 있는 팀명 및 연고지를 알고싶은경우?

- 선수테이블의 소속코드를 기준으로 팀 테이블에 들어있는 데이터를 재배치 하여 결과를 얻게된다.

- 테이블 명을 앞에 쓸 경우 가독성이 떨어질 수 있으므로 ALIAS 를 주로 사용한다.

A. 

SELECT PLAYER.PLAYER_NAME, PLAYER.BACK_NO, TEAM.TEAM_NAME, TEAM.REGION_NAME

FROM PLAYER, TEAM

WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID

A. ANSI 

SELECT PLAYER.PLAYER_NAME, PLAYER.BACK_NO, TEAM.TEAM_NAME, TEAM.REGION_NAME

FROM PLAYER INNER JOIN TEAM 

ON PLAYER.TEAM_ID = TEAM.TEAM_ID

A. ALIAS 적용

SELECT P.PLAYER_NAME, P.BACK_NO, T.TEAM_NAME, T.REGION_NAME

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

SELECT P.PLAYER_NAME, P.BACK_NO, T.TEAM_NAME, T.REGION_NAME

FROM PLAYER P INNER JOIN TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

나. 선수 - 팀 WHERE 절 검색 조건 사례

- WHERE절에 JOIN 조건 이외이 검색 조건에 대한 제한 조건을 덧붙여 사용할 수 있다.

- EQUI JOIN의 최소한의 연관 관계를 위해서 테이블 개수 - 1 개의 JOIN 조건을 WHERE 절에 명시하고, 부수적인 제한조건을 논리 연산자를 통하여 추가로 입력하는것이 가능하다.

- JOIN 조건을 기술 할 때 만약 테이블에 대한 ALIAS를 적용해서 SQL문장을 작성했을 경우, WHERE절과 SELECT 절에는 테이블명이 아닌 테이블에 대한 ALIAS를 사용해야한다.  => 에러발생

SELECT PLAYER.PLAYER_NAME, P.BACK_NO, T.TEAM_NAME, T.REGION_NAME

FROM PLAYER P , TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

Q. 위에 수행한 쿼리에 포지션이 골키퍼인 선수들에 대한 데이터반 백넘버순으로 출력.

A.

SELECT PLAYER.PLAYER_NAME, PLAYER.BACK_NO, TEAM.TEAM_NAME, TEAM.REGION_NAME

FROM PLAYER, TEAM

WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID

다. 팀 - 구장 EQUI 조인 사례

Q. 팀 테이블과 구장 테이블의 관계를 이용하여 소속팀이 가지고있는 전용구장의 정보를 팀 정보와 함께 출력

SELECT T.REGEION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME, S.SEATCOUNT

FROM TEAM T, STADIUM S

WHERE T.STADIUM_ID = S.STADIUM_ID;

* 중복이 되지 않는 컬럼에 대해서는 테이블 명과 ALIAS를 쓰지 않아도 되며, 같은 이름을 가진 중복 칼럼의 경우 테이블명이나 ALIAS 가 필수이다.

SELECT REGEION_NAME, TEAM_NAME, T.STADIUM_ID, STADIUM_NAME, SEATCOUNT

FROM TEAM T, STADIUM S

WHERE T.STADIUM_ID = S.STADIUM_ID;

결과
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