코맛

다중 컬럼 서브쿼리 

EX) Unpairwise 방법

SELECT ENAME
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
                 FROM DEPT
                 GROUP BY DEPTNO)
                 AND
      SAL IN (SELECT MAX(SAL)
                FROM EMP
                GROUP BY DEPTNO);   

인라인뷰

다중 컬럼 서브쿼리 중 하나에 인라인뷰가 있다. 

인라이뷰는 필요한 컬럼들만 모아 새로운 테이블을 만들고

거기서만 데이터를 이용하기 위해 사용한다.

EX) SELECT TOTAL_SUM, TOTAL_AVG
FROM 

(SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(SAL)TOTAL_AVG, COUNT(*) CNT
FROM EMP
GROUP BYDEPTNO) E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

Q. 포드와 입사일이 같은 직원은?

SELECT ENAME
FROM EMP
WHERE HIREDATE = (SELECT HIREDATE
FROM EMP
WHERE ENAME = 'FORD');

Q. 포드와 워드의 월급과 같은 월급을 받고 있는 직원은?

SELECT ENAME
FROM EMP
WHERE SAL IN (SELECT SAL
FROM EMP
WHERE ENAME IN ('WARD','FORD'));  

* IN 은 레코드가 두 개 또는 두 행 이상일 때 쓰인다.

ALL 연산자

SAL < ALL (100,200) 일 경우 SAL의 결과값은 ALL의 최소값 100미만을 찾음

SAL> ALL (100,200) 일 경우 SAL의 결과값은 ALL의 최대값 200 초과를 찾음

ANY 연산자

SAL > ANY(100, 200) 일 경우 SAL의 결과값은 ANY 안에 있는 100보다 큰 걸 찾음

SAL < ANY(100, 200) 일 경우 SAL의 결과값은 ANY 안에 있는 200보다 작은 걸 찾음.

EXISTS

서브 쿼리의 값 하나라도 만족하면 메인 쿼리를 실행한다.

EX)

SELECT EMPNO, SAL
FROM EMP
WHERE EXISTS(SELECT EMPNO
FROM EMP
WHERE SAL > 3000);

 여기서 서브쿼리의 값이 메인쿼리에 존재한다면 메인쿼리의 모든 정보를 출력한다.

서브쿼리

SELECT 문(MAINQUERY)에 포함되어 있는 또 하나의 다른 SELECT 문(SUBQUERY)를 말한다.

EX)

SELECT ename
FROM EMP
WHERE SAL >= (SELECT SAL
                FROM EMP
                WHERE ENAME = 'SMITH');

ㅁ MainQuery  

    SubQuery    

서브쿼리 특징

-단일 행 연산자 

: >, =, >=, <, !=

-복수 행 연산자

: IN, ANY, ALL, EXISTS

-서브쿼리는 반드시 괄호를 사용한다.

-서브쿼리에는 ORDER BY 절 사용불가.

서브쿼리가 사용가능한 곳

1. SELECT 절

2. FROM 절

3. WHERE 절

4. HAVING 절

5. ORDER BY 절

6. INSERT 문의 VALUES 절

7. UPDATE문의 SET절

ANSI

- JOIN의 형식이 FROM 절에서 지정된다.

- JOIN 조건이 WHERE절이 아닌 ON에 입력된다.

- NATURAL JOIN 과 USING절에서는 공통 컬럼명에 ALIAS 사용을 못함.

CROSS JOIN

SELECT ENAME, DNAME, E.DEPTNO

FROM EMP CROSS JOIN DEPT D;

CROSS JOIN 은 오라클 조인 CARTESIAN과 같다.

NATURAL JOIN

NATURAL 조인은 EQUI-JOIN과 동일하게 1개의 공통 컬럼명만 추출한다.

SELECT EMP EMPNO, ENAME, DNAME, LOC FROM EMP NATURAL JOIN DEPT;

USING 

동일 이름의 컬럼이 여러 개인 경우 조인 컬럼을 지정.

단, 공통 컬럼명에 TABLE ALIAS 명을 사용할 수 없다.

SELECT EMPNO, ENAME, DNAME, LOC FROM EMP INNER JOIN DEPT

USING(DEPTNO); *INNER은 생략가능

JOIN ~ ON

NON-EQUI 조인 또는 임의의 조건으로 조인 시 사용.

복잡한 조건의 조인 가능하다.

(서브쿼리, AND/OR 연산자, EXSIT, IN 연산자)

ON 조건 절에 JOIN 조건 외에도 데이터 검색조건을 추가할 수 있지만,

검색 조건이 목적인 경우에는 WHERE절을 사용하는 게 낫다.

EX)

SELECT EMPNO
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
AND DEPT.DEPTNO = 10;

EX)

SELECT EMPNO 
FROM EMP 
JOIN DEPT 
ON EMP.DEPTNO = DEPT.DEPTNO 
WHERE DEPT.DEPTNO = 10; 

JOIN

여러 테이블을 묶어서 필요한 데이터를 찾을 수 있는 방법을 말함.

예를 들어 회사 내에 구매팀에서 일하는 사람이 누군인 지 알아보고 싶다고 하자.

A테이블에 사원들의 이름과 부서번호가 있고 B테이블은 부서번호와 부서의 이름이 있다.

이 경우 A,B테이블에 모두 접근해야 구매팀에서 일하는 사람을 찾을 수 있다.

B테이블에서 구매팀의 부서번호를 알아 낸뒤 

그 번호를 이용하여 A테이블에서 찾아야한다.

이 경우 SELECT를 두 번 입력해서 검색해야하는 번거로운 문제가 발생한다.

하지만, A와 B테이블을 묶어 C테이블을 만든다면 내가 찾고자 하는 데이터가 모두 포함되어 있어

SELECT를 두 번 입력해야되는 불편함을 피할 수 있다.

 JOIN 의 종류

** 조인 종류는 이것보다 더 많지만 금일 배운 내용만 기재하였습니다.

CARTESIAN PRODUCT

다음과 같은 경우 CARTESIAN PRODUCT 가 발생한다.

- 조인 조건이 생략된 경우

- 조인 조건이 잘못된 경우

- 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행이 조인되는 경우

만일 CARTESIAN PRODUCT가 실행된다면 양쪽 ROW의 개수를 곱한 결과를 반환한다. 

EX) SELECT EMPNO, ENAME, DEPT.DNAME, LOC
     FROM DEPT, EMP; 

EQUI JOIN

EQUI JOIN 방법

- SELECT 절은 검색할 열 이름을 명시

- FROM절은 데이터베이스가 ACCESS해야 하는 두 개의 테이블을 입력

- WHERE절은 테이블의 조인 조건을 명시

- 양쪽 테이블에 공통으로 존재하는 열 이름을 열 이름앞에 테이블명을 기술함.

EX) DEPT.DEPTNO

1)

SELECT * 
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

2) 

SELECT S.GRADE, COUNT(*)
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL 
AND S.GRADE = 2
GROUP BY S.GRADE;

1.

SELECT DEPTNO, SUM(SAL) FROM EMP

WHERE SAL >= 800

GROUP BY DEPTNO

HAVING SUM(SAL) >7000;

주의! WHERE절 뒤에는 그룹함수 EX) SUM(SAL), AVG(SAL) 등이 올 수 없음.

2.

SELECT DEPTNO, AVG(SAL), SUM(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING DEPTNO = 10; 

ㄴ HAVING 절은 GROUP BY에 의해 분류된 그룹들을 제한하기 위한 방법이며 WHERE 처럼 컬럼의 조건을 걸 수 있음. 

3. 

SELECT JOB, SUM(SAL) PAYROLL

FROM EMP

WHERE JOB NOT LIKE 'SALE%' --1

GROUP BY JOB --2

HAVING SUM(SAL) > 5000 --3

ORDER BY SUM(SAL) DESC;

ㄴ 여러 조건이 있을 시 우선순위 잘 정해서 입력하기.

4.

SELECT

  SUM(CASE JOB WHEN 'CLERK' THEN 1 ELSE 0 END)"CLERK",

  SUM(CASE JOB WHEN 'SALESMAN' THEN 1 ELSE 0 END)"SALESMAN",

  SUM(CASE JOB WHEN 'MANAGER' THEN 1 ELSE 0 END)"MANAGER",

  SUM(CASE JOB WHEN 'ANALYST' THEN 1 ELSE 0 END ) "ANALYST",

  SUM(CASE JOB WHEN 'PRESIDENT' THEN 1 ELSE 0 END ) "PRESIDENT",

  COUNT(*)

FROM EMP

GROUP BY JOB;

SELECT COUNT(*) 총인원수,

      SUM(CASE TO_CHAR(HIREDATE,'YY') WHEN '80' THEN 1 ELSE 0 END) "80년도",

      SUM(CASE TO_CHAR(HIREDATE,'YY') WHEN '81' THEN 1 ELSE 0 END) "81년도",

      SUM(CASE TO_CHAR(HIREDATE,'YY') WHEN '82' THEN 1 ELSE 0 END) "82년도",

      SUM(CASE TO_CHAR(HIREDATE,'YY') WHEN '83' THEN 1 ELSE 0 END) "83년도",

      SUM(CASE TO_CHAR(HIREDATE,'YY') WHEN '84' THEN 1 ELSE 0 END) "84년도"

FROM EMP;

GROUP BY HIREDATE;

그룹함수

여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수를 말한다.

그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY절을 이용하여 

그룹화 할 수 있고 HAVING을 이용하여 그룹에 대한 조건을 제한할 수 있다.

SELECT, HAVING, GROUP BY 절에 사용 가능.

주의!

- SELECT 절 뒤에 사용할 수 있는 컬럼은 GROUP BY 뒤에 기술된 컬럼 또는 그룹함수가 적용된 컬럼이어야한다.

  단, 상수인 리터럴은 제외

-WHERE 절을 사용하여 행을 그룹으로 분류하기 전에 제외시킬 수 있다.

-HAVING절을 이용하여 GROUP BY 소그룹을 제외시킬 수 있다.

-GORUP BY 절 뒤에는 컬럼 별칭 및 컬럼순서 정수값을 사용할 수 없다.

-WHERE 절에 그룹함수를 사용할 수 없다. (그룹함수를 사용할 수 있는 GROUP BY절보다 WHERE 절이 먼저 수행된다.)

DECODE

반드시 일치하는 경우 사용.

DECODE (COL | EXPRESSION, SEARCH1, RESULT1.....,SEARCH n, result n, default)

데이터를 COL(EXPRESSION)에서 찾을 것이며

만약 search1의 데이터가 존재한다면 result1 의 결과를 출력.

여기서 default의 자리는 else와 같은 역할을 한다고 보면된다.

CASE...END

조건이 반드시 일치하지 않아도 범위 및 비교도 가능한 경우 사용.

SELECT SAL ,
CASE WHEN SAL>=4001 THEN 'A' 
WHEN  SAL>=3001 THEN 'B' 
WHEN  SAL>=2001 THEN 'C' 
WHEN  SAL>=1001 THEN 'D' 
ELSE 'E' END 등급 
FROM EMP ORDER BY 2;

WHEN 은 조건1, THEN 조건1이 참일 경우의 결과값 1

ELSE 는 앞의 조건들이 FALSE인 경우의 결과값

 TO_CHAR

SELECT ENAME, HIREDATE, SAL FROM EMP WHERE TO_CHAR(HIREDATE, 'MM') = 09 ;

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월"DD"일"') FROM DUAL; 

주의! '' 작은따옴표 안에 또 다른 작은 따옴표를 못쓰게 되어 있음.

TO_DATE

1. SELECT TO_DATE ('20170802', 'YYYYMMDD') FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; 

주의! 오라클에서는 날짜 형식의 Default 는 RR/MM/DD 이기 때문에 

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; 으로 바꿔준 뒤 1번과 같이 출력 가능.

변환함수

숫자, 문자, 날짜 데이터들의 데이터 형을 변환시킬 수 있는 함수를 말한다.

TO_CHAR 함수

숫자 및 날짜를 문자로 변환하기 위해서 사용되며 데이터 형 변환 시 추가로 출력형식을 지정할 수 있다.

EX) SELECT TO_CHAR(HIREDATE, 'YYYY'), TO_CHAR(123456,'999,999') FROM EMP;

출력 시 반드시 단일 따옴표(' ')내부에 기술해야된다.

EX) SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, (PM) DY HH:MI:SS') FROM DUAL;