SQL 정리 - 1

====================================================

# 연결 연산자 

SELECT ename ||' is a '|| job
FROM emp ;

 ====================================================

# 리터럴 값
컬럼, 테이블이 아닌 사용자 정의의 임의의 문자, 숫자, 날짜
- 문자, 날짜는 ' '로 묶어야 함

 ====================================================

# Column Heading (열 머리글)
 - 대문자 출력.

 - 첫 글자는 문자.

 - 최대 30byte.

 - 영자, 숫자, 3가지 특수문자($,#,_)만 됨.

 - AS로 " "하면 아무거나 사용 가능.

 ====================================================

# NULL 값
 - 비어있는 상태, 값이 존재하지 않음
 - 숫자 0, 문자 공백과 다름
 - 연산 시 계산 불가능, 비교 불가능

 ====================================================
# DISTINCT
 - 중복 행 제거

 ====================================================

# 테이블 목록 확인

SELECT * FROM tab ;

 ====================================================

# 컬럼 정의 확인

DESCRIBE table_name

 ====================================================

# 비교 연산자 종류

=, >, <, >=, <=, !=, <>
IN            : 값 목록 중에 하나의 값과 같으면 검색
BETWEEN       : 범위 검색
LIKE          : 문자의 패턴 비교
IS [NOT] NULL : NULL 값 비교

문자열 비교에서 크다는 오름차순으로 정렬 시 더 뒤에 있다. 작다는 더 아래에 있다.

 ====================================================

# 문자 비교 // SCOTT과 scott은 비교할 때 분명 다르다! -- 비교 시 대소문자 차이 있음

SELECT * FROM emp
WHERE ename = 'SCOTT' ;

SELECT * FROM emp
WHERE ename = 'scott' ;

 ==================================================== 

# 날짜 비교 -- 날짜 비교시 날짜 포맷과 동일해야 함

SELECT * FROM emp
WHERE hiredate > '09-JUN-81' ;  --error

=> 따라서 비교하는 09-JUN-81을 다음과 같이 현재 Date Format로 변환해주어야 한다.

SELECT * FROM EMP

WHERE HIREDATE > TO_DATE('09-JUN-81', 'DD-MON-RR');

 ==================================================== 
# IN 연산자 -- 필드명 IN ( ... )은 각각의 OR로 풀어질 수 있다.

SELECT * FROM emp
WHERE deptno = 10
   OR deptno = 20 ;

// 위 아래 문장은 같음.

SELECT * FROM emp
WHERE deptno IN (10,20) ;

 ==================================================== 

# BETWEEN 연산자

SELECT * FROM emp
WHERE sal BETWEEN 3000 AND 2000 ;     //이거 하면 무조건 아무것도 검색이 안됨.

 ==================================================== 

# LIKE 연산자
 - 문자의 부분 패턴을 비교 (숫자와 날짜는 암시적 형변환이 발생하여 허가됨)
 - %, _ 사용 (% : 0개 이상의 문자, _ : 1개의 문자)

문자필드 LIKE 'S%' ;  // S로 시작하는 모든 문자열

문자필드 LIKE '%S' ;         // S로 끝나는 모든 문자열

문자필드 LIKE '__e' ;  // 땡땡e로 끝나는 모든 문자열(즉 이름이 무조건 3글자여야 함)

문자필드 LIKE '%A_%' ;  // 중간에 A땡이 들어간 모든 문자열

문자필드 LIKE '%A\_%' ESCAPE '\' ;  // 중간에 A_이 들어간 모든 문자열

문자필드 NOT LIKE 'S%' ;  // S로 시작하는 모든 문자열을 제외한 문자열

문자필드 LIKE '77%' ; // 77로 시작하는 모든 직원 번호 (즉, 숫자도 가능하고 ' '로 묶어줘야 함)

문자필드 LIKE '1981%' ;  

 ==================================================== 

# IS NULL, IS NOT NULL 연산자

SELECT * FROM emp
WHERE comm = NULL ;    // NULL은 = 연산을 하지 않고 모두 NULL로 처리하여 검색된 값이 없게 된다.

 ==================================================== 

# ORDER BY 절
 - ORDER BY 절이 없는 경우 정렬 상태는 보장되지 않음

SELECT * FROM emp
ORDER BY deptno DESC ;     // 내림차순

SELECT * FROM emp
ORDER BY ename ASC ;       // 오름차순. 디폴트 설정이다

SELECT empno, ename, sal * 12 salary FROM emp
ORDER BY deptno, salary ;    // 부서별로 먼저 소팅하고, 그 다음

SELECT * FROM emp
ORDER BY 8, 6 DESC ;         // select된 컬럼 중에서 8번째 컬럼은 오름차순, 6번째 컬럼은 내림차순.

====================================================

# 명시적 형 변환
 - TO_CHAR, TO_NUMBER, TO_DATE
 - 규칙 : 문자와의 변환만 형식이 맞으면 됨. / 나머지는 안됨.
   - 날짜, 숫자는 문자형으로 변환 가능
   - 형식 맞는 문자는 날짜, 숫자로 변환 가능 

     (SELECT TO_DATE('1990/06/23') FROM DUAL;)
   - 날짜 -> 숫자, 숫자 -> 날짜의 변경 불가능
     ex) '2016/06/28' => NUMBER ?
          1234        => DATE ?
====================================================

TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'),
TO_CHAR(HIREDATE,'YYYY'),
TO_CHAR(HIREDATE,'DD Month, YYYY'),       // 05 December, 1990
TO_CHAR(HIREDATE,'fmDD Month, YYYY'),    // 위랑 같은데, 여백이 없음.
TO_CHAR(HIREDATE,'Day'),          // 해당 일이 무슨 요일인지 출력
TO_CHAR(HIREDATE,'D')             // 해당 일이 무슨 요일인지 숫자로 출력(일요일부터 1)

====================================================

 # RR과 YY의 차이점 : RR은 해당 연도의 세기정보를 가지고 있다. 하지만 YY는 단순히 해당 Date의 연도를 뒤에서 두 개 자르는 것이므로, 위에서 YY로 자르면 2080으로 정보가 들어간다.

====================================================

 # NVL(expr1, expr2) : expr1이 NULL이면, expr2로 변환해준다.

 SELECT EMPNO, ENAME, MGR,
        NVL(MGR, 'NO MANAGER')
 FROM EMP ;     

// MGR의 Data Type은 Number이기 때문에, NVL로 MGR 컬럼이 Null인 row를
   Character인 'NO MANAGER'로 변환하게 되면 같은 컬럼에 두 가지 데이터 타입이 있게    되므로 에러가 발생한다.

 SELECT EMPNO, ENAME, MGR,
        NVL(TO_CHAR(MGR), 'NO MANAGER')
 FROM EMP ;

====================================================

# DECODE, CASE 표현식  - SQL 명령문에서 IF문을 구현

- 의미 : 한 row씩 읽으면서, 조건에 맞으면  해당 값을 출력하고, 없으면 디폴트 출력

          (디폴트도 없으면 null임)

DECODE(DEPTNO, 10, SAL * 1.1  
                       , 20, SAL * 1.2
                       , 30, SAL * 1.3
                            , SAL)

CASE DEPTNO WHEN 10 THEN SAL * 1.1
                    WHEN 20 THEN SAL * 1.2
                    WHEN 30 THEN SAL * 1.3
                                   ELSE SAL END
 
CASE WHEN DEPTNO IN (10,20) THEN SAL * 1.1
        WHEN DEPTNO = 30       THEN SAL * 1.2
                                            ELSE SAL END 

SELECT MAX(DECODE(JOB, 'MANAGER', NULL

                                                   , SAL))
FROM EMP => 맞는 결과인가?

// 이 결과로 첫 글자가 가장 큰 값이 나오는데, 그 이유는 DECODE는 RETURN하는 값('MANAGER')의 데이터 형식을 따르기 때문이다. 즉, SAL의 데이터 타입이 문자가 된다.

// 따라서 실제 최대 값을 출력하고 싶은 경우 TO_NUMBER로 숫자로 데이터 타입을 변환해주어야 한다.
SELECT MAX(DECODE(JOB, 'MANAGER',

                   TO_NUMBER(NULL), SAL))
FROM EMP 

SELECT MAX( CASE WHEN JOB != 'MANAGER' // 이건 THEN 뒤에 있는 Column의 형식을 따르는 것으로 보인다.
                 THEN SAL END )
FROM EMP
WHERE DEPTNO = 30 ;

====================================================

# 날짜값 조작 시 주의 사항
// SYSDATE은 현재 년,월,일,시,분,초를 알 수 있다. 다만 현재 Date Format에 따라서 특정 정보가 보이지 않는 것처럼( 다시 말해, 저장되지 않는 것처럼) 보일 수 있다.

 

TO_CHAR(TRUNC(SYSDATE),'YYYY/MM/DD HH24:MI:SS')   // 시분초가 0으로 되어 저장된다

TO_CHAR(TRUNC(SYSDATE,'MONTH'),'YYYY/MM/DD HH24:MI:SS')  // 연, 달까지만 현재 값으로 저장되고, 나머지 일은 1로, 시분초는 0으로 초기화된다.

TO_CHAR(TRUNC(SYSDATE,'YEAR'),'YYYY/MM/DD HH24:MI:SS')   // 연도까지만 현재 값으로 저장되고, 나머지 월, 일은 1로, 시분초는 0으로 초기화된다.

TO_CHAR(ROUND(SYSDATE+1,'DAY'),'YYYY/MM/DD HH24:MI:SS')  //  SYSDATE + 1를 DAY를 기준으로 반올림한다. 즉, 월화수이면 그주의 일요일로 버림되고, 목금토이면 차주 일요일로 올림된다.

==============================================
# 여러행 함수 (그룹 함수)
- 그룹당 하나의 결과 리턴
- 데이터 타입에 따라 사용 불가능한 함수 있음
- 컬럼의 NULL은 제외하고 연산 수행

AVG(COMM) // COMM에 NULL을 제외한 것들끼리 계산.

AVG(NVL(COMM,0 // COMM의 NULL도 0으로 변환하여 계산하고 싶으면 NVL 함수 사용.
SUM(ALL DEPTNO), SUM(DISTINCT DEPTNO) // 모든 row의 deptno를 더한다; 부서 번호인 10, 20, 30만을 더한다.
FROM EMP ;

COUNT(COMM), COUNT(*)   // NULL을 제외한 COMM 컬럼의 개수; NULL을 포함한 row의 개수

==============================================
# GROUP BY 사용 시 규칙
 - GROUP BY 절에 정의된 컬럼은 SELECT 절에 없어도 된다.
 - SELECT 절에 정의된 컬럼(그룹함수 제외)은 반드시 GROUP BY 절에 정의되어야 한다.

SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY deptno ;      -- error. SELECT절에 정의된 job 컬럼이 GROUP BY에 없다.

==============================================

# HAVING 절 사용
 - 그룹 함수의 조건식은 WHERE절에서 사용 불가능
 - 반드시 HAVING 절에 조건식 정의
 - 그룹함수 및 GROUP BY절에 정의된 컬럼의 조건식 정의 가능

 

SELECT deptno, SUM(sal)
FROM emp
WHERE SUM(sal) > 10000
GROUP BY deptno ;     -- error

 

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 10000 ;

 

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO) ;    // 맨 마지막 하위에 DEPT에 대한 총계 값이 나온다.
==============================================

Equi     Join    : 조인 비교시 '=' 사용
Non-Equi Join : '=' 이외의 비교 연산자를 사용

Inner Join       : 조인 조건식에 만족하는 결과만 검색
Outer Join      : Inner Join 결과와 함께 한쪽 집합에만 있는 행도 추가

                    LEFT, RIGHT, FULL Outer join

Self  Join        : 동일 테이블을 이용한 조인
Cross Join       : 조인 조건 없이 연결될 수 있는 모든 경우의 수 연결

 

# Outer Join

- DEPT 테이블에 40번 부서 번호 추가 검색
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO  ;

 

- 양쪽 집합에 추가 행이 있다면? ANSI JOIN만 가능
# Oracle Join으로 FULL Outer join 효과를 내기 위해서는
  두번의 Outer join 진행 후 합집합 결과 검색 (중복행 제거해서)

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO (+)
UNION
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO ;

====================================================

# Outer Join 활용

- 1981년에 월별 입사 인원 수 계산
- 입사 인원이 없는 달은 검색 불가능 !!
SELECT TO_CHAR(hiredate,'YYYY/MM') AS HIRE,
       COUNT(*) AS CNT
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') = '1981'
GROUP BY TO_CHAR(hiredate,'YYYY/MM')
ORDER BY 1 ;

- Outer Join을 활용하여 12개의 월을 생성하고
  조인을 통해 결과 완성 가능

SELECT B.HIRE, NVL(A.CNT,0) AS CNT
FROM (SELECT TO_CHAR(hiredate,'YYYY/MM') AS HIRE,
              COUNT(*) AS CNT
      FROM emp
      WHERE TO_CHAR(hiredate,'YYYY') = '1981'
      GROUP BY TO_CHAR(hiredate,'YYYY/MM')) A,
     (SELECT '1981/'||LPAD(LEVEL,2,'0') HIRE
      FROM dual
      CONNECT BY LEVEL <= 12) B
WHERE A.HIRE (+) = B.HIRE
ORDER BY B.HIRE ;

====================================================

# Natural Join, Using 절 사용

Natural Join : 두 집합에 동일한 이름의 모든 컬럼을
                  자동으로 Equi Join으로 처리

 

SELECT *
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
  AND E.MANAGER_ID    = D.MANAGER_ID  ;       -- 32 행

SELECT *
FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D  ;  -- 32 행

 

# USING 절 사용
- 두 집합에 조인 컬럼을 지정하여 잘못된 연산 수행 제거
- Euqi Join 결과만 가능

SELECT *
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (DEPARTMENT_ID) ;

# 주의사항
- Natural Join, Using 절을 이용한 조인 수행 시
  조인 컬럼에 테이블 이름, 별칭을 이용하여 수행하면 error

EX)
SELECT *
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (D.DEPARTMENT_ID)
WHERE D.DEPARTMENT_ID IN (10,20,30,40) ;