트리거란?

• 오라클에서 트리거(trigger)는 데이터베이스 안의 특정 상황이나 동작, 즉 이벤트가 발생할 경우에 자동으로 실행되는 기능을 정의하는 PL/SQL 서브프로그램이다.

• 트리거를 통해 연관 데이터 작업을 잘 정의해 두면 좋은 장점

  1. 데이터와 연관된 여러 작업을 수행하기 위해 여러 PL/SQL문 또는 서브프로그램을 일일이 실행해야 하는 번거로움을 줄일 수 있습니다. 즉 데이터 관련 작업을 좀 더 간편하게 수행할 수 있습니다.

  2. 제약 조건(constraints)만으로 구현이 어렵거나 불가능한 좀 더 복잡한 데이터 규칙을 정할 수 있어 더 수준 높은 데이터 정의가 가능합니다.

  3. 데이터 변경과 관련된 일련의 정보를 기록해 둘 수 있으므로 여러 사용자가 공유하는 데이터 보안성과 안정성 그리고 문제가 발생했을 때 대처 능력을 높일 수 있습니다.

• 하지만 트리거는 특정 작업 또는 이벤트 발생으로 다른 데이터 작업을 추가로 실행하기 때문에 무분별하게 사용하면 데이터베이스 성능을 떨어뜨리는 원인이 되므로 주의가 필요하다.

• 트리거는 테이블∙뷰∙스키마∙데이터베이스 수준에서 이벤트 동작을 지정할 수 있다.

  1. 데이터 조작어(DML) : INSERT, UPDATE, DELETE
  2. 데이터 정의어(DDL) : CREATE, ALTER, DROP
  3. 데이터베이스 동작 : SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN

• 트리거가 발생할 수 있는 이벤트 종류에 따라 오라클은 다음과 같이 구분한다.

DML 트리거

• DML 트리거 형식
  • DML 트리거는 특정 테이블에 DML 명령어는 실행했을 때 작동하는 트리거이다.

// 기본 형식
CREATE [OR REPLACE] TRIGGER 트리거 이름 1.
BEFORE | AFTER 2.
INSERT | UPDATE | DELETE ON 테이블 이름 3.
REFERENCING OLD as old | New as new 4.
FOR EACH ROW WHEN 조건식 5.
FOLLOWS 트리거 이름2, 트리거 이름3 ... 6.
ENABLE | DISABLE 7.

DECLARE
  선언부
BEGIN
  실행부
EXCEPTION
  예외 처리부
END;

트리거 관리

트리거 정보 조회

• 트리거 정보를 확인하려면 USER_TRIGGERS 데이터 사전을 조회한다.

// USER_TRIGGERS로 트리거 정보 조회하기
SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, STATUS
  FROM USER_TRIGGERS;

트리거 변경

• ALTER TRIGGER 명령어로 트리거 상태를 변경할 수 있다.
• 특정 트리거를 활성화 또는 비활성화하려면 ALTER TRIGGER 명령어에 ENABLE 또는 DISABLE 옵션을 지정한다.

ALTER TRIGGER 트리거 이름 ENABLE | DISABLE;

• 특정 테이블과 관련된 모든 트리거의 상태를 활성화하거나 비활성화하기

특정 테이블과 관련된 모든 트리거의 상태 활성화
ALTER TABLE 테이블 이름 ENABLE ALL TRIGGERS;

특정 테이블과 관련된 모든 트리거의 상태 비활성화
ALTER TABLE 테이블 이름 DISABLE ALL TRIGGERS;

트리거 삭제

DROP TRIGGER 트리거 이름;

• 프로시저와 함수의 차이점

함수 생성하기

• 함수는 반환 값의 자료형과 실행부에서 반환할 값을 RETURN절 및 RETURN문으로 명시해야 한다.
• 실행부의 RETURN문이 실행되면 함수 실행은 즉시 종료된다.

// 기본 형식
CREATE [OR REPLACE] FUNCTION 함수 이름
[(파라미터 이름1 [IN] 자료형1, 1.
  파라미터 이름2 [IN] 자료형2,
  ...
  파라미터 이름N [IN] 자료형N
)]
RETURN 자료형 2.
IS | AS
  선언부
BEGIN
  실행부
 RETURN (반환 값); 3.
EXCEPTION
  예외 처리부
END [함수 이름];

// 함수 생성하기
CREATE OR REPLACE FUNCTION func_aftertax(
   sal IN NUMBER
)
RETURN NUMBER
IS
   tax NUMBER := 0.05;
BEGIN
   RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
/

함수 실행하기

• PL/SQL로 실행할 때는 함수 반환 값을 대입받을 변수가 필요하다.

PL/SQL로 함수 실행하기

SQL문에서 함수 실행하기

• 함수에 정의한 파라미터와 자료형이 일치한다면 내장 함수와 마찬가지로 특정 열 또는 열 데이터 간에 연산 가공된 데이터를 입력하는 것도 가능하다.

함수 삭제하기

19 - 4 패키지

• 패키지(package는 업무나 기능 면에서 연관성이 높은 프로시저, 함수 등 여러 개의 PL/SQL 서브프로그램을 하나의 논리 그룹으로 묶어 통합∙관리하는 데 사용하는 객체를 뜻한다.

▶︎ PL/SQL 서브프로그램의 제작∙사용∙관리∙보안∙서능 등에 좋은 영향을 끼친다.

패키지 구조와 생성

• 패키지는 프로시저, 함수와 달리 보통 두 부분으로 나누어 제작한다. 하나는 명세(specification) 또 하나는 본문(body)이라고 부른다.

패키지 명세

• 패키지 명세는 패키지에 포함할 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램을 선언하는 용도로 작성한다.
• 패키지 명세에 선언한 여러 객체는 패키지 내부뿐만 아니라 외부에서도 참조할 수 있다.

// 기본 형식
CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS
  서브프로그램을 포함한 다양한 객체 선언
END [패키지 이름];

패키지 생성하기

패키지 명세 확인하기(USER_SOURCE 데이터 사전으로 조회)

패키지 명세 확인하기(DESC 명령어로 조회)

패키지 본문

• 패키지 본문에는 패키지 명세에서 선언한 서브프로그램 코드를 작성한다.
• 패키지 명세에 선언하지 않은 객체나 서브프로그램을 정의하는 것도 가능하다.
• 패키지 본문에만 존재하는 프로그램은 패키지 내부에서만 사용할 수 있다.
• 패키지 본문 이름은 패키지 명세 이름과 같게 지정해야 한다.

// 기본 형식
CREATE [OR REPLACE] PACKAGE BODY 패키지 이름
IS | AS
  패키지 명세에서 선언한 서브프로그램을 포함한 여러 객체를 정의
  경우에 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능
END [패키지 이름];

패키지 본문 생성하기

서브프로그램 오버로드

• 서브프로그램 이름은 중복될 수 없다. 하지만 같은 패키지에서 사용하는 파라미터의 개수, 자료형, 순서가 다를 경우에 한해서만 이름이 같은 서브프로그램을 정의할 수 있다. 이를 서브프로그램 오버로드(subprogram overload)라고 한다.
• 서브프로그램 오버로드는 보통 같은 기능을 수행하는 여러 서브프로그램이 입력 데이터를 각각 다르게 정의할 때 사용한다. 또한 서브프로그램 종류가 같아야 오버로드가 가능하다.
• 프로시저와 이름이 같은 함수를 정의할 수는 없다.

// 기본 형식
CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS
  서브프로그램 종류 서브프로그램 이름(파라미터 정의);
  서브프로그램 종류 서브프로그램 이름(개수나 자료형, 순서가 다른 파라미터 정의);
END [패키지 이름];

프로시저 오버로드하기

패키지 본문에서 오버로드된 프로시저 작성하기

패키지 사용하기

• 패키지를 통해 그룹화된 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램은 패키지 이름과 마침표(.)와 사용할 객체 이름으로 사용할 수 있다.

패키지 이름.객체 이름;

패키지에 포함된 서브프로그램 실행하기

패키지 삭제하기

• 패키지 명세와 본문을 한 번에 삭제하거나 패키지 본문만 삭제할 수도 있다. 하지만 패키지에 포함된 서브프로그램을 따로 삭제하는 것은 불가능하다.

// 패키지 명세와 본문을 한 번에 삭제하기
DROP PACKAGE 패지키 이름;

// 패키지의 본문만을 삭제
DROP PACKAGE BODY 패키지 이름;

• 여러번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램을 저장 서브프로그램(stored subprogram)이라고 한다.

• 익명 블록과 달리 저장 서브프로그램은 오라클에 저장하여 공유할 수 있으므로 메모리∙성능∙재사용성 등 여러 면에서 장점이 있다.

• 오라클에서 저장 서브프로그램을 구현할 수 있는데 대표적인 구현 방식은 프로시저∙함수∙패키지∙트리거이다.

19 - 2 프로시저

• 저장 프로시저(stored procedure)는 특정 처리 작업을 수행하는 데 사용하는 저장 서브프로그램으로 용도에 따라 파라미터를 사용할 수 있고 사용하지 않을 수도 있다.

파라미터를 사용하지 않는 프로시저

프로시저 생성하기

• 작업 수행에 별다른 입력 데이터가 필요하지 않을 경우에 파라미터를 사용하지 않는 프로시저를 사용한다.

// 기본 형식
CREATE 1.[OR REPLACE] PROCEDURE 2.프로시저 이름
IS | AS 3.
  선언부
BEGIN
  실행부
EXCEPTION 4.
  예외 처리부
END [프로시저 이름]; 5.

• 프로시저 생성하기

• 프로시저 실행하기

  EXECUTE 프로시저 이름;

프로시저 내용 확인하기

• 이미 저장되어 있는 프로시저를 포함하여 서브프로그램의 소스 코드 내용을 확인하려면 USER_SOURCE 데이터 사전에서 조회한다.

// USER_SOURCE를 통해 프로시저 확인하기
SELECT *
FROM USER_SOURCE
WHERE NAME = 'PRO_NOPARAM';

프로시저 삭제하기

• DROP PROCEDURE 명령어로 프로시저를 삭제할 수 있다.

파라미터를 사용하는 프로시저

• 프로시저를 실행하기 위해 입력 데이터가 필요한 경우에 파라미터를 정의할 수 있다. 파라미터는 여러 개 작성할 수 있으며 다음과 같은 형식으로 사용한다.

// 기본 형식
CREATE 1.[OR REPLACE] PROCEDURE 2.프로시저 이름
[(파라미터 이름1 [modes] 자료형 [ := | DEFAULT 기본값], 3.
  파라미터 이름2 [modes] 자료형 [ := | DEFAULT 기본값],
  ...
  파라미터 이름N [modes] 자료형 [ := | DEFAULT 기본값]
)]
IS | AS 4.
   선언부
BEGIN
   실행부
EXCEPTION 5.
   예외 처리부
END [프로시저 이름]; 6.

IN 모드 파라미터

• 프로시저 실행에 필요한 값을 직접 입력받는 형식의 파라미터를 지정할 때 IN을 사용한다.
• IN은 기본값이기 때문에 생략 가능하다.

프로시저에 파라미터 지정하기

파라미터를 입력하여 프로시저 사용하기

기본값이 지정된 파라미터 입력을 제외하고 프로시저 사용하기

실행에 필요한 개수보다 적은 파라미터를 입력하여 프로시저 실행하기

파라미터 이름을 활용하여 프로시저에 값 입력하기

OUT 모드 파라미터

• OUT 모드를 사용한 파라미터는 프로시저 실행 후 호출한 프로그램으로 값을 반환한다.

OUT 모드 파라미터 정의하기

OUT 모드 파라미터 사용하기

IN OUT 모드 파라미터

• IN OUT 모드로 선언한 파라미터는 IN, OUT으로 선언한 파라미터 기능을 동시에 수행한다.

IN OUT 모드 파라미터 정의하기

IN OUT 모드 파라미터 사용하기

프로시저 오류 정보 확인하기

생성할 때 오류가 발생하는 프로시저 알아보기

▶︎ 서브프로그램을 만들 때 발생한 오류는 SHOW ERRORS 명령어와 USER_ERRORS 데이터 사전을 조회하여 확인할 수 있다.

SHOW ERRORS로 오류 확인

• SHOW ERRORS 명령어는 가장 최근에 생성되거나 변경된 서브프로그램의 오류 정보를 출력한다.

SHOW ERRORS 명령어로 오류 확인하기

▶︎ SHOW ERRORS 명령어는 줄여서 SHOW ERR로 사용할 수도 있다.

// 최근에 발생한 프로그램 오류가 아니라 특정 프로그램의 오류 정보를 확인하기
SHOW ERR 프로그램 종류 프로그램 이름;
SHOW ERR PROCEDURE pro_err;

USER_ERRORS로 오류 확인

// USER_ERRORS로 오류 확인하기
SELECT *
  FROM USER_ERRORS
 WHERE NAME = 'PRO_ERR';

오류란?

• 오라클에서 SQL 또는 PL/SQL이 정상 수행되지 못하는 상황을 오류(error)라고 한다. 이 오류는 크게 두 가지로 구분된다.

  1. 문법이 잘못되었거나 오타로 인한 오류로 컴파일 오류(compile Error), 문법 오류(syntax error)라고 한다.

  2. 명령문의 실행 중 발생한 오류를 런타임 오류(runtime error) 또는 실행 오류(execute error)라고 부른다.

• 오라클에서는 이 두 가지 오류 중 후자, 즉 프로그램이 실행되는 도중 발생하는 오류를 예외(exception)라고 한다.

예외 종류

• 오라클에서 예외는 크게 내부 예외(internal exceptions)와 사용자 정의 예외(user-defined exceptions)로 나뉜다.

  1. 내부예외 : 오라클에서 미리 정의한 예외를 뜻하며 내부 예외는 이름이 정의되어 있는 예외인 사전 정의된 예외(predefined name exceptions)와 이름이 정해지지 않은 예외로 다시 나뉜다.

  2. 사용자 정의 예외 : 사용자가 필요에 따라 추가로 정의한 예외를 의미한다.

| 예외 종류 | 설명 | |:-----:|:--------:|:------------| | 내부 예외
(internal
exceptions) | 사전 정의된 예외
(predefined name exceptions) | 내부 예외 중 예외 번호에 해당하는 이름이 존재하는 예외 | | | 이름이 없는 예외
(unnames exceptions) | 내부 예외 중 이름이 존재하지 않는 예외(사용자가 필요에 따라 이름을 지정할 수 있음) | | 사용자 정의 예외(user-defined exceptions) | | 사용자가 필요에 따라 직접 정의한 예외 |

예외 처리부 작성

• 예외 처리부는 EXCEPTION절에 필요한 코드를 사용하여 작성한다.
• 여러 예외를 명시하여 작성할 수 있다.
• WHEN으로 시작하는 절을 예외 핸들러(exception handler)라고 하며, 발생한 예외 이름과 일치하는 WHEN절의 명령어를 수행한다.
• 수행할 명령어는 PL/SQL 실행부와 마찬가지로 여러 문법을 사용할 수 있다.
• OTHERS는 먼저 작성한 어느 예외와도 일치하는 예외가 없을 경우에 처리할 내용을 작성한다.

// 기본 형식
EXCEPTION
  WHEN 예외 이름1 [OR 예외 이름2 - ] THEN
    예외 처리에 사용할 명령어;
  WHEN 예외 이름3 [OR 예외 이름4 - ] THEN
    예외 처리에 사용할 명령어;
  ...
  WHEN OTHERS THEN
    예외 처리에 사용할 명렁어;

이름 없는 예외 사용

• 이름이 없는 내부 예외를 사용해야 한다면 이름을 직접 지정해 주어야 예외 처리부에서 사용할 수 있다.

// 기본 형식
DECLARE
  예외 이름1 EXCEPTION;
  PRAGMA EXCEPTION_INIT(예외 이름1, 예외 번호);
.
.
.
EXCEPTION
  WHEN 예외 이름1 THEN
    예외 처리에 사용할 명령어;
  ...
END;

사용자 정의 예외 사용

• 사용자 정의 예외는 오라클에 정의되어 있지 않은 특정 상황을 직접 오류로 정의하는 방식이다.

// 기본 형식
DECLARE
  사용자 예외 이름 EXCEPTION;
  ...
BEGIN
  IF 사용자 예외를 발생시킬 조건 THEN
    RAISE 사용자 예외 이름
  ...
  END IF;
EXCEPTION
  WHEN 사용자 예외 이름 THEN
    예외 처리에 사용할 명령어;
  ...
END;

오류 코드와 오류 메시지 사용

• 오류 처리부가 잘 작성되어 있다면 오류가 발생해도 PL/SQL은 정상 종료된다. PL/SQL문의 정상 종료 여부와 상관없이 발생한 오류 내역을 알고 싶을 때 SQLCODE, SQLERRM 함수를 사용한다.

커서란?

• 커서(cursor)는 SELECT문 또는 데이터 조작어 같은 SQL문을 실행했을 때 해당 SQL문을 처리하는 정보를 저장한 메모리 공간을 뜻한다.
  

SELECT INTO 방식

• SELECT INTO문은 조회되는 데이터가 단 하나의 행일 때 사용 가능한 방식이다.
• 커서는 결과 행이 하나이든 여러 개이든 상관없이 사용할 수 있다.
• SELECT INTO문은 SELECT절에 명시한 각 열의 결과 값을 다음과 같이 변수에 대입해 준다.

// 기본 형식
SELECT 열1, 열2, ..., 열n INTO 변수1, 변수2, ..., 변수n
FROM ...
///

```sql
// SELECT INTO를 사용한 단일행 데이터 저장하기
DECLARE
V_DEPT_ROW DEPT%ROWTYPE;
BEGIN
SELECT DEPTNO, DNAME, LOC INTO V_DEPT_ROW
FROM DEPT
WHERE DEPTNO = 40;
DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO);
DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME);
DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC);
END;
/

명시적 커서

• 명시적 커서는 사용자가 직접 커서를 선언하고 사용하는 커서를 뜻한다.

// 기본 형식
DECLARE
    CURSOR 커서 이름 IS SQL문; -- 커서 선언(Declaration)
BEGIN
    OPEN 커서 이름;              -- 커서 열기(Open)
    FETCH 커서이름 INTO 변수     -- 커서로부터 읽어온 데이터 사용(Fetch)
    CLOSE 커서이름;              -- 커서 닫기(Close)
END;

하나의 행만 조회되는 경우

• 하나의 행만 조회되는 SELECT문을 커서로 지정하여 사용할 경우 SELECT INTO문을 사용할 때보다 복잡한 여러 단계를 작성해여 하므로 다소 번거롭다. 커서의 효용성은 조회되는 행이 여러 개일 때 극대화된다.

여러 행이 조회되는 경우 사용하는 LOOP문

• 커서에 지정한 SELECT문이 여러 행을 결과 값을 가질 경우에 여러 방식의 LOOP문을 사용할 수 있다.

• %NOTFOUND는 실행된 FETCH문에서 행을 추출했으면 false, 추출하지 않았으면 true를 반환한다.

• FETCH문을 통해 더 이상 추출한 데이터가 없을 경우에 LOOP 반복이 끝난다.

여러 개의 행이 조회되는 경우(FOR LOOP문)

• 커서에 FOR LOOP문을 사용하면 좀 더 간편하게 여러 행을 다룰 수 있다.

// 기본 형식
FOR 루프 인덱스 이름 IN 커서 이름 LOOP
   결과 행별로 반복 수행할 작업;
END LOOP;

커서에 파라미터 사용하기

• 고정 값이 아닌 직접 입력한 값 또는 상황에 따라 여러 값을 번갈아 사용하려면 다음과 같이 커서에 파라미터를 지정할 수 있다.

// 기본 형식
CURSOR 커서 이름(파라미터 이름 자료형, ...) IS
SELECT ...

묵시적 커서

• 묵시적 커서는 별다른 선언 없이 SQL문을 사용했을 때 오라클에서 자동으로 선언되는 커서를 뜻한다.
• 자동으로 생성되어 실행되는 묵시적 커서는 별다른 PL/SQL문을 작성하지 않아도 되지만, 다음 묵시적 커서의 속성을 사용하면 현재 커서의 정보를 확인할 수 있다.

레코드란?

• 레코드(record)는 자료형이 각기 다른 데이터를 하나의 변수에 저장하는 데 사용한다.

// 기본 형식
TYPE 레코드 이름 IS RECORD(
변수 이름 자료형 NOT NULL := (또는 DEFAULT) 값 또는 값이 도출되는 여러 표현식
)
// 1. 레코드 이름
// 2. 변수 이름
// 3. 자료형
// 4. NOT NULL
// 5. := (또는 DEFAULT) 값 또는 값이 도출되는 여러 표현식

• 정의한 레코드는 지금까지 다룬 변수와 마찬가지로 기존 자료형처럼 사용할 수 있다. 레코드에 포함된 변수는 레코드 이름과 마침표(.)로 사용할 수 있다.

레코드를 사용한 INSERT

• PL/SQL문에서는 테이블에 데이터를 삽입하거나 수정하는 INSERT, UPDATE문에도 레코드를 사용할 수 있다.

레코드를 사용한 UPDATE

• 레코드는 UPDATE문에도 사용할 수 있다. 이 경우에 SET절은 ROW 키워드와 함께 레코드 이름을 명시한다. 기존 UPDATE문에서는 SET절을 통해 변경할 열을 하나하나 지정한 것과 달리 레코드에 저장된 데이터를 사용하여 행 전체의 데이터를 바꿔 준다.

17 - 2 자료형이 같은 여러 데이터를 저장하는 컬렉션

• 컬렉션은 특정 자료형의 데이터를 여러 개 저장하는 복합 자료형이다. 여러 종류의 데이터를 하나로 묶어 사용하는 레코드를 테이블의 한 행처럼 사용한다면, 컬렉션은 열 또는 테이블과 같은 형태로 사용할 수 있다.

• PL/SQL에서 사용할 수 있는 컬렉션은 다음과 같이 세 가지 종류가 있다.

  1. 연관 배열(associative array (or index by table)

  2. 중첩 테이블(nested table)

  3. VARRAY(variable-size array)

  
  

연관 배열

• 연관 배열은 인덱스라고도 불리는 키(key), 값(value)으로 구성되는 컬렉션이다.

• 중복되지 않은 유일한 키를 통해 값을 저장하고 불러오는 방식을 사용한다.

• 연관 배열을 정의할 때 자료형이 TABLE인 변수를 다음과 같이 작성한다.

// 기본 형식
TYPE 연관 배열 이름 IS TABLE OF 자료형[NOT NULL]
INDEX BY 인덱스형;
// 1. 연관 배열 이름
// 2. 자료형 [NOT NULL]
// 3. 인덱스형

컬렉션 메서드

• 오라클에서는 켈렉션 사용상의 편의를 위해 몇 가지 서브프로그램을 제공하고 있다. 이를 컬렉션 메서드라고 한다.

• 컬렉션 메서드는 컬렉션과 관련된 다양한 정보 조회 기능을 제공한다. 이와 더불어 컬렉션 내의 데이터 삭제나 컬렉션 크기 조절을 위한 특정 조작도 가능하다.

• 컬렉션 메서드는 컬렉션형으로 선언한 변수에 마침표(.)와 함께 작성하여 사용할 수 있다.

• 서브쿼리는 SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미한다.
• 서브퀴리의 결과 값을 사용하여 기능을 수행하는 영역은메인쿼리라고 부른다.

// 메인쿼리
SELECT 조회할 열
FROM 조회할 테이블
WHERE 조건식 ( SELECT 조회할 열 // 서브쿼리
                               FROM 조회할 테이블
                                WHERE 조건식 )

• 서브쿼리 작성의 핵심은 주어진 문제를 어떻게 SELECT문으로 나누어 처리할지를 결정하는 데에 있다.

SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
                             FROM EMP
                            WHERE ENAME = 'JONES');

서브쿼리의 특징

1. 서브쿼리는 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이면 괄호 ()로 묶어서 사용한다.
2. 특수한 몇몇 경우를 제외한 대부분의 서브쿼리에서는 ORDER BY절을 사용할 수 없다.
3. 서브쿼리의 SELECT절에 명시한 열은 메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정해야 한다. 즉 메인쿼리의 비교 대상 데이터가 하나라면 서브쿼리의 SELECT절 역시 같은 자료형인 열을 하나 지정해야 한다.
4. 서브쿼리에 있는 SELECT문의 결과 행 수는 함께 사용하는 메인쿼리의 연산자 종류와 호환 가능해야 한다. 예를 들어 메인쿼리에 사용한 연산자가 단 하나의 데이터로만 연산이 가능한 연산자라면 서브쿼리의 결과 행 수는 반드시 하나여야 한다. 이 내용은 ‘단일행 서브쿼리 및 다중행 서브쿼리’에서 자세히 살펴보겠다.

09 - 2 실행 결과가 하나인 단일행 서브쿼리

• 단일행 서브쿼리는 실행 결과가 단 하나의 행으로 나오는 서브쿼리를 뜻한다. 서브쿼리에서 출력되는 결과가 하나이므로 메인쿼리와 서브쿼리 결과는 다음과 같이 단일행 연산자를 사용하여 비교한다.

단일행 서브쿼리와 날짜형 데이터

• 단일행 서브쿼리는 서브쿼리 결과 값이 날짜(DATE) 자료형일 때도 사용할 수 있다.

SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE
                                        FROM EMP
                                     WHERE ENAME = 'SCOTT');

단일행 서브쿼리와 함수

• 서브쿼리에서 특정 함수를 사용한 결과 값이 하나일 때 역시 단일행 서브쿼리로서 사용 가능하다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 20
AND E.SAL > (SELECT AVG(SAL)
                            FROM EMP);

09 - 3 실행 결과가 여러 개인 다중행 서브쿼리

• 다중행 서브쿼리는 실행 결과 행이 여러 개로 나오는 서브쿼리를 가리킨다.

IN

• 부서별 최고 급여 데이터를 먼저 구하고, 이 데이터와 일치하는 메인쿼리 데이터를 IN 연산자를 통해 선별해 낼 수 있다.

SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
                FROM EMP
              GROUP BY DEPTNO);

• 다중행 연산자 중 IN 연산자는 가장 자주 사용하는 연산자이므로 사용법을 반드시 기억해 두자.

ANY, SOME 연산자

• ANY, SOME 연산자는 서브쿼리가 반환한 여러 결과 값 중 메인쿼리와 조건식을 사용한 결과가 하나라도 true라면 메인쿼리 조건식을 true로 반환해 주는 연산자이다.
• 메인쿼리와 값을 비교할 때 ANY 및 SOME 연산자를 등가 비교 연산자(=)와 함께 사용하면 IN 연산자와 정확히 같은 기능을 수행한다.

SELECT *
FROM EMP
WHERE SAL = ANY (SELECT MAX(SAL)
                                    FROM EMP
                                    GROUP BY DEPTNO);

• 등가 비교 연산자가 아닌 대소 비교 연산자를 ANY 연산자와 함께 사용하는 경우는 조금 생각을 해야 한다.

SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL
                                    FROM EMP
                                    WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;

// < ANY 연산자를 사용한 경우
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL
                                FROM EMP
                                WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;

// 서브쿼리에 MAX 함수를 사용한 경우
SELECT *
FROM EMP
WHERE SAL < (SELECT MAX(SAL)
                            FROM EMP
                            WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;

ALL 연산자

ANY 및 SOME과 달리 ALL 연산자는 서브쿼리의 모든 결과가 조건식에 맞아떨어져야만 메인쿼리의 조건식이 true가 되는 연산자이다.

// 부서 번호가 30번인 사원들의 최소 급여보다 더 적은 급여를 받는 사원 출력하기
SELECT *
FROM EMP
WHERE SAL < ALL (SELECT SAL
                                    FROM EMP
                                WHERE DEPTNO = 30);

EXISTS 연산자

EXISTS 연산자는 조금 특이한 연산자인데 서브쿼리에 값이 하나 이상 존재하면 조건식이 모두 true, 존재하지 않으면 false가 되는 연산자이다.

// 서브쿼리 결과 값이 존재하는 경우
SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME
                FROM DEPT
                WHERE DEPTNO = 10);

// 서브쿼리 결과 값이 존재하지 않는 경우
SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME
                FROM DEPT
                WHERE DEPTNO = 50);

• EXISTS 연산자는 다른 다중행 연산자에 비해 그리 자주 사용하는 편은 아니지만, 특정 서브쿼리 결과 값의 존재 유무를 통해 메인쿼리의 데이터 노출 여부를 결정해야 할 때 간혹 사용한다.

09 - 4 비교할 열이 여러 개인 다중열 서브쿼리

• 다중열 서브쿼리는 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식이다.
• 메인쿼리에 비교할 열을 묶어 명시하고, 서브쿼리에서는 괄호로 묶은 데이터와 같은 자료형 데이터를 SELECT절에 명시하여 사용할 수 있다.

// 다중열 서브쿼리 사용하기
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                                FROM EMP
                                GROUP BY DEPTNO);

09 - 5 FROM절에 사용하는 서브쿼리와 WITH절

• FROM절에 사용하는 서브쿼리는 인라인 뷰라고도 부른다.
• 인라인 뷰는 특정 테이블 전체가 아닌 SELECT문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주어 사용할 수 있다.

// 인라인 뷰 사용하기
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
    (SELECT * FROM DEPT) D
    WHERE E10.DEPTNO = D.DEPTNO;

하지만 FROM절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어질 수 있기 때문에 경우에 따라 WITH절을 사용하기도 한다.

WITH절은 메인쿼리가 될 SELECT문 안에서 사용할 서브쿼리와 별칭을 먼저 지정한 후 메인쿼리에서 사용한다.

// 기본 형식
WITH
[별칭1] AS (SELECT문 1),
[별칭2] AS (SELECT문 2),
...
[별칭n] AS (SELECT문 n)
SELECT
FROM 별칭1, 별칭2, 별칭3
...

// WITH절 사용하기
WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D   AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10, D
WHERE E10.DEPTNO = D.DEPTNO;

09 - 6 SELECT절에 사용하는 서브쿼리

• 서브쿼리는 SELECT절에도 사용할 수 있다. 흔히 스칼라 서브쿼리라고 부르는 이 서브쿼리는 SELECT절에 하나의 열 영역으로서 결과를 출력할 수 있다.

// SELECT절에 서브쿼리 사용하기
SELECT EMPNO, ENAME, JOB, SAL,
        (SELECT GRADE
            FROM SALGRADE
            WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
            DEPTNO,
            (SELECT DNAME
            FROM DEPT
            WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
            FROM EMP E;

SELECT절에 명시하는 서브쿼리는 반드시 하나의 결과만 반환하도록 작성해 주어야 한다는 것도 꼭 기억하자.

잊기 전에 한 번 더!

• Q1. 전체 사원 중 ALLEN과 같은 직책(JOB)인 사원들의 사원 정보, 부서 정보를 다음과 같이 출력하는 SQL문을 작성하세요.

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND JOB = (SELECT JOB
                FROM EMP
               WHERE ENAME = 'ALLEN');

• Q2. 전체 사원의 평균 급여(SAL)보다 높은 급여를 받는 사원들의 사원 정보, 부서 정보, 급여 등급 정보를 출력하는 SQL문을 작성하세요(단 출력할 때 급여가 많은 순으로 정렬하되 급여가 같을 경우에는 사원 번호를 기준으로 오름차순으로 정렬하세요).

SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC, E.SAL, S.GRADE
  FROM EMP E, DEPT D, SALGRADE S
 WHERE E.DEPTNO = D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > (SELECT AVG(SAL)
                FROM EMP)
ORDER BY E.SAL DESC, E.EMPNO;

• Q3. 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 사원 정보, 부서 정보를 다음과 같이 출력하는 SQL문을 작성하세요.

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 10
   AND JOB NOT IN (SELECT DISTINCT JOB
                     FROM EMP
                    WHERE DEPTNO = 30);

• Q4. 직책인 SALESMAN인 사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원 정보, 급여 등급 정보를 다음과 같이 출력하는 SQL문을 작성하세요(단 서브쿼리를 활욜할 때 다중행 함수를 사용하는 방법과 사용하지 않는 방법을 통해 사원 번호를 기준으로 오름차순으로 정렬하세요).

-- 다중행 함수 사용하지 않는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > (SELECT MAX(SAL)
                FROM EMP
               WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO; 

--다중행 함수 사용하는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > ALL (SELECT DISTINCT SAL
                    FROM EMP
                   WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO; 

08 - 1 조인

집합 연산자와 조인의 차이점

• 조인은 두 개 이상의 테이블 데이터를 가로로 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식이다.
• 집합 연산자를 사용한 결과는 두 개 이상의 SELECT문의 결과 값을 세로로 연결한 것이다.

여러 테이블을 사용할 때의 FROM절

• 꼭 테이블이 아니더라도 테이블 형태, 즉 열과 행으로 구성된 데이터 집합이면 모두 FROM절에 지정 가능하다. SELECT절의 여러 열을 구분할 때와 마찬가지로 FROM절에 여러 테이블을 명시할 때 쉼표(,)를 구분자로 사용하여 지정한다. 그리고 WHERE, GROUP BY, ORDER BY절 등 다른 절도 그대로 사용할 수 있다.

SELECT
FROM 테이블1, 테이블2, ..., 테이블N

• FROM절에 여러 테이블 선언하기

SELECT *
FROM EMP, DEPT
ORDER BY EMPNO;

이렇게 EMP, DEPT 테이블을 FROM절에 함께 명시하여 출력하면 생각보다 많은 양의 데이터가 FROM절에 명시학 각 테이블을 구성하는 행이 모든 경우의 수로 조합되어 출력되기 때문이다.

위 이미지와 같이 각 집합을 이루는 모든 원소의 순서쌍을 데카르트 곱이라고 한다. 조인 이름으로는 크로스 조인 또는 교차 조인이라고도 한다.

조인 조건이 없을 때의 문제점

• EMP 테이블 SMITH 사원이 있는 행을 보면 DEPTNO가 20이고 DEPT 테이블의 DEPTNO 열 값이 20인 행이 된다. 하지만 조건 없이 결과를 출력하면 사원 데이터와 부서 데이터가 정확히 맞아떨어지지 않는 데이터도 함께 출력된다.

• 그러므로 출력 행을 선정하는 조건식을 명시하는 WHERE절이 중요한 역할을 한다. 따라서 서로 다른 테이블인 EMP 테이블과 DEPT 테이블에 같은 이름의 DEPTNO 열을 구별하는 방법이 필요하다.

• 이 때 사용하는 것이 바로 열 앞에 테이블 이름을 명시하여 특정 열이 어느 테이블에 속한 열인지를 구별하는 방식이다.

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

FROM절에 WHERE절을 사용해 출력하려는 조건을 걸어 두 테이블의 DEPTNO 열 값이 같은 14개 행만 출력된다. 그리고 각 행별 부서 정보도 정확하게 연결되었음을 확인할 수 있다.

테이블 별칭 설정

• FROM절에 지정한 테이블에는 SELECT절의 열에 사용한 것처럼 별칭을 지정할 수 있다.

FROM 테이블 이름1 별칭1, 테이블 이름2 별칭2 ...

지정한 별칭은 테이블의 열을 지칭하는 데 사용할 수 있다.

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

08 - 2 조인 종류

• 두 개 이상의 테이블을 하나의 테이블처럼 가로로 늘어뜨려 출력하기 위해 사용하는 조인은 대상 데이터를 어떻게 연결하느냐에 따라 등가 조인, 비등가 조인, 자체 조인, 외부 조인 등으로 구분한다.

등가 조인

• 등가 조인은 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식이다. 등가 조인은 내부 조인 또는 단순 조인으로 부르기도 한다.

• 등가 조인은 일반적으로 가장 많이 사용되는 조인 방식이다.

• 여러 테이블의 열 이름이 같을 때 유의점

SELECT EMPNO, ENAME, DEPTNO, DNAME, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

DEPTNO처럼 등가 조인을 사용할 때 조인 조건이 되는 각 테이블의 열 이름이 같을 경우에 해당 열 이름을 테이블 구분 없이 명시하면 오류가 발생하여 실행되지 못합니다.

• 열 이름에 각각의 테이블 이름도 함께 명시할 때

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC // D.DEPTNO -> E.DEPTNO 가능
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO, E.EMPNO;

• 조인 테이블 개수와 조건식 개수의 관계

기본적으로 데카르트 곱 현상이 일어나지 않게 하는 데 필요한 조건식의 최소 개수는 조인 테이블 개수에서 하나를 뺀 값이다.

비등가 조인

사원 정보와 더불어 사원의 급여 등급 정보를 함께 출력하고자 한다면 EMP 테이블과 SALGRADE 테이블을 조인해야 한다. 하지만 사용하는 열의 일치 여부를 기준으로 테이블을 조인하는 등가 조인 방식은 이 두 테이블의 연결에 적합하지 않다. 왜냐하면 급여 등급을 맞춰 주려면 사원의 급여 금액이 일치하는 것이 아니라 최소 급여(LOSAL)와 최대 급여(HISAL) 사이에 있어야 하기 때문이다.

SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

• 비등가 조인 방식은 등가 조인 방식에 비해 그리 자주 사용하는 방식은 아니다.

자체 조인

• 자체 조인은 하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식이고 FROM절에 같은 테이블을 여러 번 명시하되 테이블의 별칭만 다르게 지정하는 방식으로 사용한다.

• EMP테이블을 자체 조인을 사용하여 E1.MGR = E2.EMPNO를 나란히 출력한다. 하나의 테이블 이지만 SELECT문 내부에서 별칭을 각각 달리주어 논리적으로 다른 테이블인 것처럼 명시하여 두 테이블을 조인하는 방식이다.

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

외부 조인 설명좀

• 조인 조건 데이터 중 어느 한쪽이 NULL임에도 결과를 출력할 때 포함시켜야 하는 경우가 종종 있다. 두 테이블ㄱ간 조인 수행에서 조인 기준 열의 어느 한쪽이 NULL이어도 강제로 출력하는 방식을 외부 조인이라고 한다.
• 외부 조인은 좌우를 따로 나누어 지정하는데 WHERE절에 조인 기준 열 중 한쪽에(+) 기호를 붙여 준다.

• 왼쪽 외부 조인 사용하기

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;

• 오른쪽 외부 조인 사용하기

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;

• 왼쪽 외부 조인은 간단히 말해서 왼쪽 열을 기준으로 오른쪽 열의 데이터 존재 여부에 상관없이 출력하라는 뜻이다.
• 오른쪽 외부 조인은 오른쪽 열을 기준으로 왼쪽 열 데이터의 존재와 상관없이 데이터를 출력하라는 뜻이다.

08 - 3 SQL-99 표준 문법으로 배우는 조인

• 오라클은 9i 버전부터 SQL-99 방식의 문법을 지원한다.
• 앞에서 배운 조인 방식과 기능은 같지만 조인을 사용하는 문법에서 다소 차이가 난다. 그리고 다른 DBMS 제품에서도 사용할 수 있다.

NATURAL JOIN

• 등가 조인을 대신해 사용할 수 있는 조인 방식으로 조인 대상이 되는 두 테이블에 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가 조인을 해 주는 방식이다.
• EMP 테이블과 DEPT 테이블은 공통 열 DEPTNO를 가지고 있으므로 NATURAL JOIN을 사용할 때 자동으로 DEPTNO 열을 기준으로 등가 조인된다.
• 기존 등가 조인과 다르게 조인 기준 열인 DEPTNO를 SELECT절에 명시할 때 테이블 이름을 붙이면 안 되는 특성이 있다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
DEPTNO, D.DNAME, D.LOC
FROM EMP E NATURAL JOIN DEPT D
ORDER BY DEPTNO, E.EMPNO;

JOIN ~ USING

• 이 조인 역시 등가 조인을 대신하는 방식이다. USING 키워드에 조인 기준으로 사용할 열을 명시하여 사용한다.

FROM TABLE1 JOIN TABLE2 USING (조인에 사용한 기준열)

• 다른 조인 방식과 마찬가지로 조인된 결과 행을 추가로 제한할 때 WHERE절에 조건식을 추가하여 함께 사용할 수 있다.
• 조인 기준 열로 명시된 열은 SELECT절에서 테이블 이름을 붙이지 않고 작성한다.

JOIN ~ ON

• 가장 범용성 있는 조인 방식이다.
• 기존 WHERE절에 있는 조인 조건식을 ON 키워드 옆에 작성한다.
• 조인 기준 조건식은 ON에 명시하고 그 밖의 출력행을 걸러 내기 위해 WHERE 조건식을 따로 사용하는 방식이다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
E.DEPTNO, 
D.DNAME, D.LOC
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL <= 3000
ORDER BY E.DEPTNO, EMPNO;

OUTER JOIN

• OUTER JOIN 키워드는 외부 조인에 사용한다.

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

• 전체 외부 조인은 왼쪽 · 오른쪽 외부 조인을 모두 적용한, 즉 왼쪽 열이 NULL인 경우와 오른쪽 열이 NULL인 경우를 모두 출력하는 방식이다.
• SQL-99 방식의 외부 조인은 FULL OUTER JOIN ~ ON 키워드로 양쪽 모두 외부 조인된 결과 값을 출력할 수 있다.

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

• SQL-99 조인은 FROM절에 특정 키워드를 사용함으로써 기존 WHERE절에 조건식으로 조인하는 조인 방식보다 더 간략하고 명시적으로 어떤 방식의 조인을 사용하고 있는지 알 수 있다.
• 조인 조건식과 출력 행을 선정하는 조건식을 구별할 수 있으므로 여러 테이블을 조인해야 하는 복잡한 SELECT문에서 SQL-99 조인의 장점이 드러난다.

SQL-99 조인 방식에서 세 개 이상의 테이블을 조인할 때

기존 조인 방식은 FROM절에 조인 테이블을 명시하고 조인 관련 조건식을 WHERE절에 명시하기 때문에 테이블 수가 두 개를 넘더라도 다음과 같이 작성하면 아무 문제가 없다

FROM TABLE1, TABLE2 TABLE3
WHERE TABLE1.COL = TABLE2.COL
AND TABLE2.COL = TABLE3.COM

하지만 FROM절에 조인 관련 내용을 작성해야 하는 SQL-99 방식에서는 FROM절에 두 개 테이블을 키워드로 조인한 바로 옆에 SQL-99 방식의 조인 내용을 추가로 작성하면 세 개 이상의 테이블도 조인할 수 있다.

FROM TABLE1 JOIN TABLE2 ON (조인 조건식)
JOIN TABLE3 ON (조건식)

잊기 전에 한 번 더!

• Q1. 급여(SAL)가 2000 초과인 사원들의 부서 정보, 사원 정보를 오른쪽과 같이 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND SAL > 2000;

// SQL-99 방식
SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL > 2000;

• Q2. 오른쪽과 같이 각 부서별 평균 급여, 최대 급여, 최소 급여, 사원수를 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
TRUNC(AVG(E.SAL)) AS AVG_SAL,
MAX(E.SAL) AS MAX_SAL,
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

// SQL-99 방식
SELECT DEPTNO, D.DNAME, TRUNC(AVG(E.SAL)) AS AVG_SAL,
MAX(E.SAL) AS MAX_SAL,
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP E JOIN DEPT D USING (DEPTNO)
GROUP BY DEPTNO, D.DNAME;

• Q3. 모든 부서 정보와 사원 정보를 오른쪽과 같이 부서 번호, 사원 이름순으로 정렬하여 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DEPTNO, E.ENAME; 

// SQL-99 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D ON (D.DEPTNO = E.DEPTNO)
ORDER BY DEPTNO, E.ENAME;

// DEPTNO, D.DEPTNO ㅠㅠ ?

• Q4. 다음과 같이 모든 부서 정보, 사원 정보, 급여 등급 정보, 각 사원의 직속 상관의 정보를 부서 번호, 사원 번호 순서로 정렬하여 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
       S.LOSAL, S.HISAL, S.GRADE,
       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
  FROM EMP E, DEPT D, SALGRADE S, EMP E2
 WHERE E.DEPTNO(+) = D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
   AND E.MGR = E2.EMPNO(+)
ORDER BY D.DEPTNO, E.EMPNO;

// SQL-99 방식
SELECT D.DEPTNO, D.DNAME,
       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
       S.LOSAL, S.HISAL, S.GRADE,
       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
  FROM EMP E RIGHT OUTER JOIN DEPT D
                ON (E.DEPTNO = D.DEPTNO)
              LEFT OUTER JOIN SALGRADE S
                ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
              LEFT OUTER JOIN EMP E2
                ON (E.MGR = E2.EMPNO)
ORDER BY D.DEPTNO, E.EMPNO;

NATURAL JOIN

• NATURAL JOIN은 등가 조인을 대신해 사용할 수 있는 조인 방식으로 조인대상이 되는 두 테이블에 이름과 자료형이 같은 열을 찾은 후 그열을 기준으로 등가 조인을 해 주는 방식이다.

JOIN ~ USING

JOIN ~ USING 키워드를 사용한 조인 역시 기존 등가 조인을 대신하는 조인 방식이다.

FROM TABLE1 JOIN TABLE2 USING (조인에 사용한 기준열)

다른 조인 방식과 마찬가지로 조인된 결과 행을 추가로 제한할 때 WHERE절에 조건식을 추가하여 함께 사용할 수 있다.

JOIN ~ ON

가장 범용성 있는 JOIN ~ ON 키워드를 사용한 조인 방식에서는 기존 WHERE절에 있는 조인 조건식을 ON 키워드 옆에 작성한다. 조인 기준 조건식은 ON에 명시하고 그 밖의 출력 행을 걸러 내기 위해 WHERE 조건식을 따로 사용하는 방식이다.

FROM TABLE JOIN TABLE2 ON (조인 조건식)

OUTER JOIN

OUTER JOIN 키워드는 외부 조인에 사용한다. WHERE절이 아닌 FROM절에서 외부 조인을 선언한다.

SQL-99 조인 방식에서 세 개 이상의 테이블을 조인할 때

기존 조인 방식은 FROM절에 조인 테이블을 명시하고 조인 관련 조건식을 WHERE절에 명시하기 때문에 테이블 수가 두 개를 넘더라도 다음과 같이 작성하면 아무 문제가 없다

FROM TABLE1, TABLE2 TABLE3
WHERE TABLE1.COL = TABLE2.COL
ABD TABLE2.COL = TABLE3.COM

하지만 FROM절에 조인 관련 내용을 작성해야 하는 SQL-99 방식에서는 FROM절에 두 개 테이블을 키워드로 조인한 바로 옆에 SQL-99 방식의 조인 내용을 추가로 작성하면 세 개 이상의 테이블도 조인할 수 있다.

FROM TABLE1 JOIN TABLE2 ON (조인 조건식)
JOIN TABLE3 ON (조건식)

잊기 전에 한 번 더!

• Q1. 급여(SAL)가 2000 초과인 사원들의 부서 정보, 사원 정보를 오른쪽과 같이 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND SAL > 2000;

// SQL-99 방식
SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL > 2000;

• Q2. 오른쪽과 같이 각 부서별 평균 급여, 최대 급여, 최소 급여, 사원수를 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
TRUNC(AVG(E.SAL)) AS AVG_SAL,
MAX(E.SAL) AS MAX_SAL,
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

// SQL-99 방식
SELECT DEPTNO, D.DNAME, TRUNC(AVG(E.SAL)) AS AVG_SAL,
MAX(E.SAL) AS MAX_SAL,
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP E JOIN DEPT D USING (DEPTNO)
GROUP BY DEPTNO, D.DNAME;

• Q3. 모든 부서 정보와 사원 정보를 오른쪽과 같이 부서 번호, 사원 이름순으로 정렬하여 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DEPTNO, E.ENAME;

// SQL-99 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D ON (D.DEPTNO = E.DEPTNO)
ORDER BY DEPTNO, E.ENAME;

// DEPTNO, D.DEPTNO ㅠㅠ ?

• Q4. 다음과 같이 모든 부서 정보, 사원 정보, 급여 등급 정보, 각 사원의 직속 상관의 정보를 부서 번호, 사원 번호 순서로 정렬하여 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
       S.LOSAL, S.HISAL, S.GRADE,
       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
  FROM EMP E, DEPT D, SALGRADE S, EMP E2
 WHERE E.DEPTNO(+) = D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
   AND E.MGR = E2.EMPNO(+)
ORDER BY D.DEPTNO, E.EMPNO;

// SQL-99 방식
SELECT D.DEPTNO, D.DNAME,
       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
       S.LOSAL, S.HISAL, S.GRADE,
       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
  FROM EMP E RIGHT OUTER JOIN DEPT D
                ON (E.DEPTNO = D.DEPTNO)
              LEFT OUTER JOIN SALGRADE S
                ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
              LEFT OUTER JOIN EMP E2
                ON (E.MGR = E2.EMPNO)
ORDER BY D.DEPTNO, E.EMPNO;

집합 연산자와 조인의 차이점

• 조인은 두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식이다.
• 차이점은 집합 연산자를 사용한 결과는 두 개 이상 SELECT문의 결과 값을 세로로 연결한 것이고, 조인을 사용한 결과는 두 개 이상의 테이블 데이터를 가로로 연결한 것이라고 볼 수 있다.

여러 테이블을 사용할 때의 FROM절

• 지금까지 사용한 SELECT문은 다음과 같이 FROM절에 EMP 테이블을 하나만 명시했다. 하지만 FROM절에는 여러 개 테이블을 지정하는 것이 가능하다. 조금 더 정확하게는 꼭 테이블이 아니더라도 테이블 형태, 즉 열과 행으로 구성된 데이터 집합이면 모두 FROM절에 지정 가능하다.

SELECT 열1, 열2, ..., 열N
FROM EMP
WHERE 조건식
GROUP BY 그룹식
HAVING 그룹조건식
ORDER BY 정렬식

• SELECT절의 여러 열을 구분할 때와 마찬가지로 FROM절에 여러 테이블을 명시할 때 쉼표를 구분자로 사용하여 지정한다. 그리고 WHERE, GROUP BY, ORDER BY절 등 다른 절도 그대로 사용할 수 있다.

SELECT
FROM 테이블1, 테이블2, ..., 테이블N

조인 조건이 없을 때의 문제점

• 조인을 통한 출력은 결과로 나올 수 있는 모든 행을 조합하기 때문에 정확히 맞아떨어지지 않는 데이터도 함께 출력된다. 명시한 테이블의 데이터를 가로로 연결하기 위해 조인을 사용하지만, 어떤 데이터를 가로로 정확히 연결해야 하는지의 기준은 데이터베이스가 아닌 SQL문을 작성하는 프로그래머가 정해 주어야한다. 이때 출력 행을 선정하는 조건식을 명시하는 WHERE절이 중요한 역을을 한다. 따라서 서로 다른 테이블에서 같은 이름의 열을 구별하는 방법은 열 앞에 테이블 이름을 명시하여 특정 열이 어느 테이블에 속한 열인지를 구별하는 방식이다.

테이블의 별칭 설정

• FROM절에 지정한 테이블에는 SELECT절의 열에 사용한 것처럼 별칭을 지정할 수 있다. 테이블에 병칠을 지정할 때는 명시한 테이블 이름에서 한 칸 띄운 후에 지정한다.

FROM 테이블 이름1 별칭1, 테이블 이름2 별칭 2 ...

08 - 2 조인 종류

• 두 개 이상의 테이블을 하나의 테이블처럼 가로로 늘어뜨려 출력하기 위해 사용하는 조인은 대상 데이터를 어떻게 연결하느냐에 따라 등가 조인, 비등가 조인, 자체 조인, 외부 조인 등으로 구분한다.

등가 조인

• 등가 조인(equi join)은 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식이다. 등가 조인은 내부 조인(inner join) 또는 단순 조인(simple join)으로 부르기도 한다.

  등가 조인은 일반적으로 가장 많이 사용되는 조인 방식이다. 따라서 외부 조인(outer join)과 같이 이름을 특별히 명시하지 않으면 ‘조인을 사용한다’는 것은 대부분 등가 조인, 즉 특정 열 값이 일치한 출력 결과를 사용하는 방식이라고 보면 된다.

비등가 조인

• 비등가 조인(non-equi join)은 등가 조인 방식 외의 방식을 의미한다. 비등가 조인 방식은 등가 조인 방식에 비해 그리 자주 사용하는 방식은 아니다. 하지만 조인 조건이 특정 열의 일치 여부를 검사하는 방식 외에 다른 방식도 사용할 수 있음을 기억해라.

자체 조인

• 자체 조인(self join)은 하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방시긍로 앞에서 물리적으로 동일한 테이블 여러 개를 사용할 때 발생할 수 있는 문제점을 해결한다. 자체 조인은 FROM절에 같은 테이블을 여러 번 명시하되 테이블의 별칭만 다르게 지정하는 방식으로 사용한다.

외부 조인

• 두 테이블간 조인 수행에서 조인 기준 열의 어느 한쪽이 NULL이어도 강제로 출력하는 방식을 외부 조인(outer join)이라고 한다.

  외부 조인은 좌우를 따로 나누어 지정하는데 WHERE절에 조인 기준 열 중 한쪽에(+) 기호를 붙여 준다.

• 외부 조인은 조인 기준 열의 NULL을 처리하는 것을 목적으로 자주 사용하는 조인 방식이다. 하지만 (+) 기호를 붙이는 외부 조인 방식으로는 양쪽 모든 열이 외부 조인되는 ‘전체 외부 조인(full outer join)’ 사용은 불가능하다.

ROLLUP, CUBE, GROUPING SETS 함수

ROLLUP, CUBE, GROUPING SETS 함수는 GROUP BY절에 지정할 수 있는 특수 함수이다. ROLLUP함수와 CUBE함수는 그룹화 데이터의 합계를 출력할 때 유용하게 사용할 수 있다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP [그룹화 열 지정(여러 개 지정 가능)]; // 1.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY CUBE [그룹화 열 지정(여러 개 지정 가능)]; // 2.

• GROUPING SETS 함수

GROUPING SETS 함수는 같은 수준의 그룹화 열이 여러 개일 때 각 열별 그룹화를 통해 결과 값을 출력하는 데 사용한다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY GROUPING SETS [그룹화 열 지정(여러 개 지정 가능)]; // 1.

그룹화 함수

그룹화 함수는 데이터 자체의 가공이나 특별한 연산 기능을 수행하지는 않지만 그룹화 데이터의 식별이 쉽고 가독성을 높이기 위한 목적으로 사용한다.

• GROUPING 함수

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
GROUPING [GROUP BY절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE [그룹화 열]; // 1.

• GROUPING_ID 함수

GROUPING_ID 함수는 GROUPING 함수와 마찬가지로 ROLLUP 또는 CUBE 함수로 연살할 때 특정 열이 그룹화되었는지를 출력하는 함수이다.

그룹화 여부를 검사할 열을 하나씩 지정하는 GROUPING 함수와 달리 GROUPING_ID 함수는 한 번에 여러 열을 지정할 수 있다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
GROUPING_ID [그룹화 여부를 확인할 열(여러 개 지정 가능)]
FROM [조회할 테이블 이름] WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE [그룹화 할 열]; // 1.

LISTAGG 함수

LISTAGG 함수는 오라클 11g 버전부터 사용할 수 있는 함수이다. 그룹에 속해 있는 데이터를 가로로 나열할 때 사용한다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
LISTAGG([나열할 열(필수)], [각 데이터를 구분하는 구분자(선택)])
WITHIN GROUP(ORDER BY 나열할 열의 정렬 기준 열 (선택)) // 1.
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식];

PIVOT, UNPIVOT 함수

PIVOT, UNPIVOT 함수는 오라클 11g부터 제공하면 PIVOT함수는 기존 테이블 행을 열로 바꾸고 UNPIVOT 함수는 기존 테이블 열을 행으로 바꿔서 출력한다.

SELECT *
FROM(SELECT DEPTNO, JOB, SAL
FROM EMP)
PIVOT(MAX(SAL)
FOR DEPTNO IN (10, 20, 30)
)
ORDER BY JOB;

SELECT *
FROM(SELECT DEPTNO,
MAX(DECPDE(JOB, 'CLERK' , SAL)) AS "CLERK",
MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
MAX(DECODE(JOB, 'MNAGER', SAL)) AS "MANAGER",
MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEEPTNO)
UNPIVOT(
SAL FOR JOB IN (CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST))
ORDER BY DEPTNO, JOB;

• 다중행 함수는 여러 행을 바탕으로 하나의 결과 값을 도출해내기 위해 사용하는 함수이다.

SELECT SAL
FROM EMP;

SELECT SUM(SAL)
FROM EMP;

SUM 함수는 SELECT문으로 조회된 행에 지정한 열 값을 모두 더한 값을 반환해 주는 함수이다.

합계를 구하는 SUM함수

SUM([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
    [합계를 구할 열이나 연산자, 함수를 사용한 데이터(필수)]) // 1.

SUM함수를 분석하는 용도로 사용한다면 다음과 같이 함수를 작성한 후 OVER절을 사용할 수도 있다.

SUM([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
    [합계를 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법을 지정)(선택)

데이터 개수를 구해 주는 COUNT 함수

• COUNT 함수는 데이터 개수를 출력하는 데 사용한다.

COUNT([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
      [개수를 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법 지정)(선택) // 1.

SELECT *
FROM EMP;

SELECT COUNT(*)
FROM EMP;

최댓값과 최솟값을 구하는 MAX, MIN 함수

MAX 함수와 MIN 함수는 단어 의미 그대로 입력 데이터 중 최댓값과 최솟값을 반환하는 함수이다.

MAX([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
    [최댓값을 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법 지정)(선택) // 1.

MIN([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
        [최솟값을 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법을 지정)(선택) // 1.

평균 값을 구하는 AVG 함수

AVG 함수는 입력 데이터의 평균 값을 구하는 함수이다.

AVG([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
        [평균 값을 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법을 지정)(선택) // 1.

07 - 2 결과 값을 원하는 열로 묶어 출력하는 GROUP BY 절

GROUP BY절의 기본 사용법

SELECT문에서는 GROUP BY절을 작성하여 데이터를 그룹화할 수 있다.

SELECT  [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM    [조회할 테이블 이름]
WHERE   [조회할 행을 선별하는 조건식]
GROUP BY[그룹화할 열을 지정(여러 개 지정 가능)] // 1.
ORDER BY[정렬하려는 열 지정]

GROUP BY절을 사용할 때 유의점

GROUP BY절을 사용하여 출력 데이터를 그룹화할 경우 유의해야 할 점이 있는데 바로 다중행 함수를 사용하지 않은 일반 열은 GROUP BY절에 명시하지 않으면 SELECT절에서 사용할 수 없다는 것이다.

SELECT ENAME, DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

07 - 3 GROUP BY절에 조건을 줄 때 사용하는 HAVING절

• HAVING절은 SELECT문에 GROUP BY절이 존재할 때만 사용할 수 있다. 그리고 GROUP BY절을 통해 그룹화된 결과 값의 범위를 제한하는 데 사용한다.

HAVING절의 기본 사용법

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY [그룹화할 열 지정(여러 개 지정 가능)]
HAVING[출력 그룹을 제한하는 조건식] // 1.
ORDER BY [정렬하려는 열 지정];

HAVING절을 사용할 때 유의점

조건식을 지정한다는 점에서 HVING절이 WHERE절과 비슷하다고 생각할 수도 있다. 하지만 WHERE절은 출력 대상 행을 제한하고, HAVING절은 그룹화된 대상을 출력에서 제한하므로 쓰임새는 전혀 다르다.

WHERE절과 HAVING절의 차이점

• WHERE절을 사용하지 않고 HAVING절만 사용한 경우
• WHERE절과 HAVING절을 모두 사용한 경우

SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;

SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
WHERE SAL <= 3000
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;

• 오라클에서 날짜 데이터, 즉 DATE형 데이터는 다음과 같이 간단한 연산이 가능하다.

SELECT SYSDATE AS NOW,
SYSDATE-1 AS YESTERDAY,
SYSDATE+1 AS TOMORROW
FROM DUAL;

1을 빼거나 더했을 때 결과 날짜가 하루 이전이나 이후 날짜로 출력된다.

몇 개월 이후 날짜를 구하는 AD_MONTHS 함수

• 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수이다.

ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(정수)(필수)]) // 1.

SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 3)
FROM DUAL;

• ADD_MONTHS 함수를 WHERE절에 사용하는 것도 가능하다.

SELECT EMPNO,
ENAME, HIREDATE, SYSDATE
FROM EMP
WHERE ADD_MONTHS(HIREDATE, 486) > SYSDATE;

두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수

• 두 개의 날짜 데이터를 입력하고 두 날짜 간의 개월 수 차이를 구하는 데 사용한다.

MONTHS_BETWEEN([날짜 데이터1(필수)], [날짜 데이터2(필수)]) // 1.

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS2,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS3
FROM EMP;

MONTHS1, MONTHS2에서 알 수 있듯이 비교 날짜의 입력 위치에 따라 음수 또는 양수가 나올 수 있다. 개월 수 차이는 소수점 단위까지 결과가 나오므로 MONTHS3과 같이 TRUNC 함수를 조합하면 개월 수 차이를 정수로 출력할 수도 있다.

돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

• NEXT_DAY 함수는 날짜 데이터와 요일 문자열을 입력한다. 입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환한다.

NEXT_DAY([날짜 데이터(필수)], [요일 문자(필수)]) // 1.

LAST_DAY([날짜 데이터(필수)]) // 1.

날짜의 반올림, 버림을 하는 ROUND, TRUNC 함수

• 숫자 데이터를 반올림, 버림 처리에 사용한 ROUND, TRUNC 함수는 날짜 데이터를 입력 데이터로 사용할 수도 있다. 이때는 소수점 위치 정보를 입력하지 않고 반올림, 버림의 기준이 될 포맷(format) 값을 지정해 준다.

⭐ 날짜 데이터 기준에 대해 조금 더 자세한 내용을 알고 싶다면 ISO 공식 웹사이트(iso.org/iso/home/standards/iso8601.htm)를 참조하세요.

06 - 5 자료형을 변환하는 형 변환 함수

• ‘자동 형 변환’ 이라고도 불리는 암시적 형 변환

SELECT EMPNO, ENAME, EMPNO + '500'
FROM EMP
WHERE ENAME = 'SCOTT';

• 자료형을 직접 지정해 주는 방식인 명시적 형 변환

문자를 중심으로 숫자 또는 날짜 데이터의 변환이 가능하다.

날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수

• TO_CHAR 함수는 날짜, 숫자 데이터를 문자 데이터로 변환해 주는 함수이다.

TO_CHAR([날짜 데이터(필수)], '[출력되길 원하는 문자 형태(필수)]') // 1.

문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자형태(필수)]') // 1.

문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜형태(필수)]') // 1.

06 - 6 NULL 처리 함수

• 04장과 05장에서 데이터가 NULL이면 산술 연산자나 비교 연산자가 예상한 대로 동작하지 않는 것을 확인할 수 있었다. 하지만 특정 열의 데이터가 NULL일 경우에 연산 수행을 위해 데이터를 NULL이 아닌 다른 값으로 대체해 주어야 할 때가 종종 발생한다. 이때 여기에서 배울 NVL 함수와 NVL2 함수를 유용하게 사용할 수 있다.

NVL 함수의 기본 사용법

NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)],
    [앞의 데이터가 NULL일 경우 반환할 데이터](필수)) // 1.

SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM,
NVL(COMM, 0),
SAL+NVL(COMM, 0)
FROM EMP;

EMP 테이블의 급여 외 추가 수당을 의미하는 COMM 열 값이 NULL인 데이터를 0으로 대체하여 연산이 가능하다는 것을 확인할 수 있다. 이렇게 NVL 함수는 NULL 처리를 위해 자주 사용한다.

NVL2 함수의 기본 사용법

• NVL2 함수는 NVL 함수와 비슷하지만 데이터가 NULL이 아닐 때 반환할 데이터를 추가로 지정해 줄 수 있다.

NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)],
     [앞 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)],
     [앞 데이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)]) // 1.

SELECT EMPNO, ENAME, COMM,
NVL2(COMM, 'O', 'X'),
NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL
FROM EMP;

NVL2 함수는 NVL함수와는 달리 NULL이 아닌 경우에 반환 데이터까지 지정할 수 있으므로 좀 더 다양한 용도로 활용 가능하다.

06 - 7 상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문

• 특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할지 정할 때는 DECODE 함수 또는 CASE문을 사용한다.

DECODE 함수

• DECODE 함수는 기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 따라 다른 결과 값을 내보내는 함수이다.

DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
       [조건1], [데이터가 조건1과 일치할 때 반환할 결과],
       [조건2], [데이터가 조건2와 일치할 때 반환할 결과],
       ...
       [조건n], [데이터가 조건n과 일치할 때 반환할 결과],
       [위 조건1~조건n과 일치한 경우가 없을 때 반환할 결과])

CASE문

• 기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE 함수와 달리 CASE문은 각 조건에 사용하는 데이터가 서로 상관없어도 된다. 또 기준 데이터 값이 같은(=) 데이터 외에 다양한 조건을 사용할 수 있다.

CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
WHEN [조건1] THEN [조건1의 결과 값이 true일 때, 반환할 결과]
WHEN [조건2] THEN [조건2의 결과 값이 true일 때, 반환할 결과]
...
WHEN [조건n] THEN [조건n의 결과 값이 true일 때, 반환할 결과]
ELSE [위 조건1~조건n과 일치하는 경우가 없을 때 반환할 결과]
END

잊기 전에 한 번 더!

• Q1. EMPNO 열에는 EMP 테이블에서 사원 이름(ENAME)이 다섯 글자 이상이며 여섯 글자 미만인 사원 정보를 출력합니다. MASKING_EMPNO 열에는 사원 번호(EMPNO) 앞 두 자리 외 뒷자리를 * 기호로 출력합니다. 그리고 MASKING_ENAME 열에는 사원 이름의 첫 글자만 보여 주고 나머지 글자 수만큼 * 기호로 출력하세요.

SELECT EMPNO, RPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO,
ENAME, RPAD(SUBSTR(ENAME, 1, 1), 5, '*') AS MASKING_ENAME
FROM EMP
WHERE LENGTH(ENAME) >= 5
AND LENGTH(ENAME) < 6;

• Q2. EMP 테이블에서 사원들의 월 평균 근무일 수는 21.5일입니다. 하루 근무 시간을 8시간으로 보았을 때 사원들의 하루 급여(DAY_PAY)와 시급(TIME_PAY)을 계산하여 결과를 출렵합니다. 단 하루 급여는 소수점 세 번째 자리에서 버리고, 시급은 두 번째 소수점에서 반올림하세요.

SELECT EMPNO, ENAME, SAL,
TRUNC(SAL/21.5, 2) AS DAY_PAY,
ROUND(SAL/21.5/8, 1) AS TIME_PAY //~~ROUND(DAY_PAY/8, 1) AS TIME_PAY~~
FROM EMP;

• Q3. EMP 테이블에서 사원들은 입사일(HIREDATE)을 기준으로 3개월이 지난 후 첫 월요일에 정직원이 됩니다. 사원들이 정직원이 되는 날짜(R_JOB)를 YYYY-MM-DD 형식으로 오른쪽과 같이 출력해 주세요. 단 추가 수당(COMM)이 없는 사원의 추가 수당은 N/A로 출력하세요.

SELECT EMPNO, ENAME, HIREDATE,
       TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3), '월요일'), 'YYYY-MM-DD') AS R_JOB,
       NVL(TO_CHAR(COMM), 'N/A') AS COMM
  FROM EMP;
//nvl 데이터 타입 똑같아야 

• Q4. EMP 테이블의 모든 사원을 대상으로 직속 상관의 사원 번호(MGR)를 다음과 같은 조건을 기준으로 변환해서 CHG_MGR 열에 출력하세요.

1. 직속 상관의 사원 번호가 존재하지 않을 경우 : 0000
2. 직속 상관의 사원 번호 앞 두자리가 75일 경우 : 5555
3. 직속 상관의 사원 번호 앞 두자리가 76일 경우 : 6666
4. 직속 상관의 사원 번호 앞 두자리가 77일 경우 : 7777
5. 직속 상관의 사원 번호 앞 두자리가 78일 경우 : 8888
6. 그 외 직속 상관 사원 번호의 경우 : 본래 직속 상관의 사원 번호 그대로 출력

SELECT EMPNO, ENAME, MGR,
       CASE
          WHEN MGR IS NULL THEN '0000'
          WHEN SUBSTR(MGR, 1, 2) = '78' THEN '8888'
          WHEN SUBSTR(MGR, 1, 2) = '77' THEN '7777'
          WHEN SUBSTR(MGR, 1, 2) = '76' THEN '6666'
          WHEN SUBSTR(MGR, 1, 2) = '75' THEN '5555'
          ELSE TO_CHAR(MGR)
       END AS CHG_MGR
  FROM EMP;

• 내장 함수의 종류

1. 데이터가 한 행씩 입력되고 입력된 한 핸당 결과가 하나씩 나오는 함수를 단일행 함수라고 한다.
2. 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수라고 한다.

06 - 2 문자 데이터를 가공하는 문자 함수

• 문자 함수는 문자 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할 때 사용하는 함수이다. 실무에서 자주 사용하는 데이터는 문자, 숫자, 날짜 데이터이다.

대 · 소문자를 바꿔 주는 UPPER, LOWER, INITCAP 함수

SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;

• 문자열 길이를 구하는 LENGHT 함수

특정 문자열의 길이를 구할 때 LENGTH 함수를 사용한다.

SELECT ENAME, LENGTH(ENAME)
FROM EMP;

SELECT ENAME, LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) >= 5;

SELECT LENGTH('한글'), LENGTHB('한글')
FROM DUAL;
// LENGTG 함수는 길이 반환이라 2, LENGTHB 함수는 문자열의 바이트 수를 반환하기 때문에 4
// *한글은 한 문자당 2byte로 처리

문자열 일부를 출력하는 SUBSTR 함수

SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
FROM EMP;

문자열 데이터 안에서 특정 문자 위치를 차즌 INSTR 함수

문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 INSTR 함수를 사용한다. INSTR 함수는 총 네 개의 입력 값을 지정할 수 있으며 최소 두 개의 입력 값, 즉 원본 문자열 데이터와 원본 문자열 데이터에서 찾으려는 문자 이렇게 두 가지는 반드시 시정해 주어야 한다.

INSTR([대상 문자열 데이터(필수)],
[위치를 찾으려는 부분 문자(필수)],
[위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)],
[시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)]

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,
INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
FROM DUAL;

특정 문자를 다른 문자로 바꾸는 REPLACE 함수

REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)]

⭐ 만약 대체할 문자를 입력하지 않는다면 찾는 문자로 지정한 문자는 문자열 데이터에서 삭제된다.

데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

LPAD([문자열 데이터 또는 열이름(이름)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)]
RPAD([문자열 데이터 또는 열이름(이름)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)]

두 문자열 데이터를 합치는 CONCAT 함수

CONCAT 함수는 두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할을 한다.

SELECT CONCAT(EMPNO, ENAME),
CONCAT(EMPNO, CONCAT( ' : ', ENAME))
FROM EMP
WHERE ENAME = 'SCOTT';

특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수

TRIM([삭제 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)]

TRIM 함수의 삭제할 문자는 필수가 아니므로 지정하지 않아도 된다. 삭제할 문자가 없으면 공백이 제거된다.

LTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)]) // 1.
RTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)]) // 2.

06 - 3 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

• 숫자 데이터를 다루는 함수

특정 위치에서 반올림하는 ROUND 함수

ROUND 함수는 TRUNC 함수와 함께 가장 많이 사용하는 숫자 함수 중 하나이다.

ROUND([숫자(필수)], [반올림 위치(선택)]) // 1.

특정 위치에서 버리는 TRUNC 함수

TRUNC 함수는 지정된 자리에서 숫자를 버림 처리하는 함수이다.

TRUNC([숫자(필수)], [버림 위치(선택)]) // 1.

지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

CEIL ([숫자(필수)])
FLOOR([숫자(필수)])

숫자를 나눈 나머지 값을 구하는 MOD 함수

MOD([나눗셈 될 숫자(필수)], [나눌 숫자(필수)]) // 1.

• 산술 연산자

SELECT *
FROM EMP
WHERE SAL * 12 = 36000;

// SAL열에 12를 곱한 값이 36000인 행을 출력하는 SQL문

• 비교 연산자

SELECT *
FROM EMP
WHERE SAL >= 3000;
// SAL이 3000이상인 사원을 조회하는 SQL문

이 외에도 아래 표처럼 다른 대소 비교 연산자가 있다.

⭐ 대소 비교 연산자는 비교 대상인 데이터가 숫자가 아닌 문자열일 때도 사용할 수 있습니다. 하지만 문자열의 대소 비교는 숫자 데이터 비교보다는 자주 사용되는 내용은 아니기 때문에 가볍게 이해하는 정도로만 기억해도 된다.

• 등가 비교 연산자

연산자의 양쪽 항목 값이 같으면 true가 반환되고, 반대로 연산자의 양쪽 값이 다를 경우 true를 반환하는 연산자도 있다.

비교 연산자는 아니지만 ! =, <>, ^=과 똑같은 결과를 출력하기 위해 사용할 수 있는 연산자가 하나 더 있다. 바로 논리 부정 연산자인 NOT 연산자이다.

SELECT *
FROM EMP
WHERE NOT SAL = 3000;
// WHERE SAL != 3000; WHERE SAL <> 3000; WHERE SAL ^= 3000;
// 과 똑같이 출력된다.

⭐ NOT 연산자를 IN, BETWEEN, IS NULL 연산자와 함꼐 복합적으로 사용하는 경우가 많고, 대소 · 등가 비교 연산자에 직접 사용하는 경우는 별로 없다.

• IN 연산자

SELECT *
FROM EMP
WHERE JOB = 'MANAGER'
OR JOB = 'SALESMAN'
OR JOB = 'CLERK';
// OR 연산자를 사용해서 출력해도 되지만, 조건이 늘어날수록

OR 연산자를 사용해서 출력해도 되지만, 조건이 늘어날수록 조건식을 많이 작성해야 하기 때문에 아래와 같이 IN 연산자를 사용하면 특정 열에 해당하는 조건을 여러 개 지정할 수 있다.

SELECT *
FROM EMP
WHERE JOB IN ('MANAGER', 'SALESMAN', 'CLERK');

• BETWEEN A AND B 연산자

SELECT *
FROM EMP
WHERE SAL >= 2000
AND SAL <= 3000;

AND 연산자를 사용해서도 가능하지만, 특정 열 값의 최소 · 최고 범위를 지정하여 해당 범위 내의 데이터만 조회할 경우에 대소 비교 연산자 대신 BETWEEN A AND B 연산자를 사용하면 더 간단하게 표현할 수 있다.

SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;

• LIKE 연산자와 와일드 카드

LIKE 연산자는 이메일이나 게시판 제목 또는 내용 검색 기능처럼 일부 문자열이 포함된 데이터를 조회할 때 사용한다.

SELECT *
FROM EMP
WHERE ENAME LIKE 'S%';

ENAME LIKE ‘S%’ 조건식은 ENAME 열 값이 대문자 S로 시작하는 데이터를 조회하라는 뜻이다.

위 조건식에서 사용한 %기호를 와일드 카드라고 한다. 와일드 카드는 특정 문자 또는 문자열을 대체하거나 문자열 데이터의 패턴을 표기하는 특수 문자이다.

LIKE 연산자와 함께 사용할 수 있는 와일드 카드는 _와 %이다.

ENAME의 두 번째 글자가 L인 사원 데이터를 조회하고 싶다면 아래와 같이 LIKE 연산자에 와일드 카드를 활용할 수 있다.

SELECT *
FROM EMP
WHERE ENAME LIKE '_L%'

ENAME에 AM이라는 단어를 포함하는 사원을 조회할 때 아래와 같이 작성하면 된다.

SELECT *
FROM EMP
WHERE ENAME LIKE '%AM%';

• IS NULL

WHERE절은 조건식의 결과 값이 true인 행만 출력하는데 연산 결과 값이 NULL이 되어 버리면 조건식의 결과 값이 false도 true도 아니게 되므로 출력 대상에서 제외된다.

그러므로 특정 열 또는 연산의 결과 값이 NULL인지 여부를 확인하려면 IS NULL 연산자를 아래와 같이 사용해야 한다.

SELECT *
FROM EMP
WHERE COMM IS NULL;

IS NULL 연산자를 사용하면 추가 수당 열 값이 존재하지 않는 데이터만 출력한다.

⭐데이터가 NULL인지 아닌지를 확인하는 용도로만 사용하는 IS NULL과 IS NOT NULL 연산자는 매우 자주 사용되므로 사용법을 꼭 기억하자❗

• 집합연산자 UNION

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20;

집합 연산자를 사용하면서 주의할 점을 두 개의 SELECT문의 결과 값을 연결할 때 각 SELECT문이 출력하려는 열 개수와 각 열의 자료형이 순서별로 일치해야 한다.

⭐ 집합 연산자에는 아래와 같이 4가지 종류가 있다.

• 연산자 우선순위

WHERE절 조건식에 사용한 여러 연산자는 아래와 같은 우선순위(priority)를 가지고 있다.

⭐ 수학식에서와 마찬가지로 먼저 수행해야 하는 연산식을 소괄호()로 묶어 주면 연산자의 기본 우선순위와는 별개로 괄호 안의 연산식을 먼저 수행한다.

5장 잊기 전에 한 번 더!

• Q1. EMP 테이블을 사용하여 다음과 같이 사원 이름(ENAME)이 S로 끝나는 사원 데이터를 모두 출력하는 SQL문을 작성해 보세요.

SELECT *
FROM EMP
WHERE ENAME LIKE '%S';

• Q2. EMP 테이블을 사용하여 30번 부서(DEPTNO)에서 근무하고 있는 사원 중에 직책(JOB)이 SALESMAN인 사원의 사원 번호, 이름, 직책, 급여, 부서 번호를 출력하는 SQL문을 작성해 보세요.

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30
AND JOB = 'SALESMAN';

• Q3. EMP 테이블을 사용하여 20번, 30번 부서에 근무하고 있는 사원 중 급여(SAL)가 2000 초과인 사원을 다음 두 가지 방식의 SELECT문을 사용하여 사원 번호, 이름, 급여, 부서 번호를 출력하는 SQL문을 작성해 보세요.

1. 집합 연산자를 사용하지 않은 방식

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (20, 30)
AND SAL > 2000;

1. 집합 연산자를 사용한 방식

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20
AND SAL > 2000
UNION
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30
AND SAL > 2000;

• Q4. 이번에는 NOT BETWEEN A AND B 연산자를 쓰지 않고, 급여(SAL) 열 값이 2000 이상 3000 이하 범위 이외의 값을 가진 데이터만 출력하도록 SQL문을 작성해 보세요.

SELECT *
FROM EMP
~~WHERE SAL >= 2000
AND SAL <= 3000;~~
WHERE SAL < 2000
OR SAL > 3000;

• Q5. 사원 이름에 E가 포함되어 있는 30번 부서의 사원 중 급여가 1000~2000 사이가 아닌 사원 이름, 사원 번호, 급여, 부서 번호를 출력하는 SQL문을 작성해 보세요.

SELECT ENAME, EMPNO, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30
AND ENAME LIKE '%E%'
AND SAL NOT BETWEEN 1000 AND 2000;

• Q6. 추가 수당이 존재하지 않고 상급자가 있고 직책이 MANAGER, CLERK인 사원 중에서 사원 이름의 두 번째 글자가 L이 아닌 사원의 정보를 출력하는 SQL문을 작성해 보세요.

SELECT *
FROM EMP
WHERE COMM IS NULL
AND MGR IS NULL
AND JOB IN ('MANAGER', 'CLERK')
AND ENAME NOT LIKE '_L%';

• WHERE절은 SELECT문으로 데이터를 조화할 때 특정 조건을 기준으로 원하는 행을 출력하는 데 사용한다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하기 위한 조건식];

• 모든 열 출력

• DEPTNO가 30인 데이터만 출력

05 - 2 여러 개 조건식을 사용하는 AND, OR 연산자

AND

SELECT *
FROM EMP
WHERE DEPTNO = 30
AND JOB = 'SALESMAN';

• EMP 테이블에서 JOB과 DEPTNO 조건식의 결과 값이 모둔 true인 행만 출력한다.

❗테이블 안에 들어 있는 문자 또는 문자열 데이터는 대 · 소문자를 구별하니 주의.

OR

SELECT *
FROM EMP
WHERE DEPTNO = 30
OR JOB = 'CLERK';

• AND 연산자를 사용했을 때와 달리 조건식이 하나만 일치하는 DEPTNO = 30인 행과 JOB = ‘CLERK’인 행 모두 출력된다.

• 오른쪽과 같이 WHERE절에 사용할 수 있는 조건식의 개수는 사실상 제한이 없다고 보아도 무방하다.

SELECT *
FROM EMP
WHERE [조건식 1]
  AND [조건식 2]
   OR [조건식 3]
...
  AND [조건식 N]

사원 정보가 들어 있는 EMP 테이블

DESC EMP;

문자열 데이터가 10byte보다 작은 공간을 차지할 경우에는 데이터가 차지하는 공간만큼만 저장 공간을 할당한다.

기억장소가 절약되는 장점이 있다.

• 고정형:

CHAR(7)은 3byte만 사용해도 7byte 만큼의 데이터가 사용된다.

사이즈가 고정인 만큼 추후에 연산 필요성이 없어 검색 및 읽기 속도가 다른 타입보다 빠르다.

회사 부서 정보가 들어 있는 DEPT 테이블

DESC DEPT;

좌 : EMP, 우 : DEPT

• EMP 테이블의 DEPTNO 열이 DEPT 테이블의 DEPTNO 열을 참조하여 부서 관련 데이터를 확인할 수 있다. 즉 DEPT 테이블의 DEPTNO 열은 EMP 테이블의 외래키로 사용할 수 있다.

데이터를 조회하는 3가지 방법

• 셀렉션(selection) : 행 단위로 원하는 데이터를 조회
• 프로젝션(projection) : 열 단위로 원하는 데이터를 조회
• 조인(join) : 두 개 이상의 테이블을 연결하여 하나의 테이블인 것처럼 데이터를 조회

SELECT절과 FROM절

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름] 또는 애스터리스크(*)로 전체 열
FROM [조회할 테이블 이름];

• EMP 테이블 전체 열 조회하기

SELECT *
FROM EMP;

❗문장 끝에 세미콜론(;)을 꼭 넣자.

• EMP 테이블 부분 열 출력

SELECT EMPNO, ENAM
FROM EMP;

중복 데이터 삭제 DISTINCT

• 열 중복 제거하기

SELECT DISTINCT JOB, DEPTNO                        //DISTINCT DEPTNO, JOB
FROM EMP;

• SALESMAN의 경우 JOB(직책)과 DEPTNO(부서 번호)가 모두 일치하므로 중복 데이터로 걸러져 한 번만 출력되지만 MANAGER의 경우 DEPTNO(부서 번호)가 다르기 때문에 중복 데이터가 아닙니다.

⭐ DISTINCT [열1 이름], [열2 이름] 순서를 바꾸면 열 출력 순서만 바뀌고 결과는 똑같다~

• ALL로 중복되는 열 제거 없이 그대로 출력하기

SELECT JOB, DEPTNO          // SELECT ~~ALL~~ JOB, DEPTNO     
FROM EMP;                   // FROM EMP;

별칭 설정하기

• SQL문에서는 최종 출력되는 열 이름을 임의로 지정할 수 있다.

• 별칭 지정하는 방식

좌 : 별칭 지정 ❌, 우 : 별칭 지정 ⭕벼

별칭은 가독성을 위해 사용하기도 하지만 보안이나 데이터 노출 문제 때문에 사용하는 경우도 있다.

예로 별칭을 사용하지 않으면 위와 같이 계산식 자체가 열 이름에 노출되는 경우이다.

원하는 순서로 출력 데이터를 정렬하는 ORDER BY

• ORDER BY절은 SELECT문을 작성할 때 사용할 수 있는 여러 절 중 가장 마지막 부분에 쓴다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM   [조회할 테이블 이름]
.
. (그 밖의 절)
.
ORDER BY [정렬하려는 열 이름(여러 열 지정 가능)] [정렬 옵션];

• 정렬 옵션

1. ASC(오름차순) : 기본값이라 정렬 옵션을 지정하지 않을 경우 ASC로 정렬
2. DESC(내림차순)

• 각각의 열에 ASC와 DESC 동시에 사용하기

SELECT *
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC;

SELECT *
FROM EMP
ORDER BY SAL DESC, DEPTNO; //DEPTNO ASC <-- ASC는 기본값이라 생략가능

⭐ ORDER BY절은 먼저 기입하는 것에 따라 우선순위가 부여된다.

❗정렬을 하지 않으면 더 빨리 데이터 출력이 가능하고 여러 이유로 꼭 필요한 경우가 아니면 사용하지 않는 것이 좋다.

5. 더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자

필요한 데이터만 출력하는 WHERE절

• 특정 조건을 기준으로 원하는 행을 결과가 참(true)인 경우 출력하는데 사용한다.

SELECT *
FROM EMP
WHERE ~~DEPTNO = 30;~~

AND, OR 연산자

• VARCHAR2(10)는 가변형 문자열이라 10byte보다 작은 공간을 차지할 경우에 데이터가 차지하는 공간만큼만 저장 공간을 할당한다.

• EMP테이블의 DEPTNO 열이 DEPT테이블의 DEPTNO 열을 참조하여 부서 관련 데이터를 확인할 수 있어 데이터의 중복을 최소화할 수 있다. 즉 DEPT테이블의 DEPTNO 열은 EMP테이블의 외래키로 사용할 수 있따.

04 - 2 데이터를 조회하는 3가지 방법 - 셀렉션, 프로젝션, 조인

• 셀렉션 : 행 단위로 원하는 데이터를 조회하는 방식이다.
• 프로젝션 : 열 단위로 원하는 데이터를 조회하는 방식이다.
• 조인 : 두 개 이상의 테이블을 양옆에 연결하여 마치 하나의 테이블인 것처럼 데이터를 조회하는 방식이다.

04 - 3 SQL의 기본 뼈대, SELECT절과 FROM절

• SELECT문은 DB에 보관되어 있는 데이터를 조회하는 데 사용한다.
• FROM절은 조회할 데이터가 저장된 테이블 이름을 명시한다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름] 또는 *로 전체 열
FROM [조회할 테이블 이름];

04 - 4 중복 데이터를 삭제하는 DISTINCT

• SELECT문으로 데이터를 조회한 후 DISTINCT를 사용하여 중복을 제거한다.

// 중복제거X
SELECT DEPTNO
FROM EMP;

// 중복제거O
SELECT DISTINCT DEPTNO
FROM EMP;

• ALL은 중복되는 열 제거 없이 그대로 출력하기지만 ALL을 빼고 열만 조회해도 결과는 같습니다.

SELECT ALL JOB, DEPTNO
FROM EMP;

04 - 5 한눈에 보기 좋게 별칭 설정하기

• 별칭 사용 없이 출력하기

SELECT SAL*12+COMM
FROM EMP;

• 별칭 지정해서 출력하기

SELECT SAL*12+COMM AS ANNSAL
FROM EMP;

• 별칭은 가독성을 위해서만 사용되는 게 아닌 위 이미지처럼 계산식 자체가 열 이름에 노출되면 안 되는 경우 보안이나 데이터 노출 문제를 위해서도 사용된다.

⭐SAL*12+COMM AS ANNSAL 방식을 선호한다.

04 - 6 원하는 순서로 출력 데이터를 정렬하는 ORDER BY

• SELECT문을 사용하여 데이터를 조회할 때 시간이나 이름 순서 또는 어떤 다른 기준으로 데이터를 정렬해서 출력해야 하는 경우에 데이터를 정렬된 상태로 출력하기 위해 ORDER BY절을 사용한다.
• ORDER BY절 정렬옵션

1. ASC : 오름차순이며 정렬옵션을 지정하지 않을 경우 기본값으로 ASC가 설정된다.
2. DESC : 내림차순이다.

❗ORDER BY절은 SELECT문을 작성할 때 사용 할 수 있는 여러 절 중 가장 마지막 부분에 쓴다.

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
.
.
ORDER BY [정렬하려는 열 이름(여러 열 지정 가능)] [정렬옵션];

테이블

• 관계형 데이터베이스는 기본적으로 데이터를 2차원 표 형태로 저장하고 관리하며 이 형태의 저장 공간을 테이블(table)이라고 한다.
• 가로줄을 행(row), 세로줄을 열(column)이라고 부른다.

1. 행은 저장하려는 하나의 개체를 구성하는 여러 값을 가로로 늘어뜨린 형태이다.
2. 열은 저장하려는 데이터를 대표하는 이름과 공통 특성을 정의한다.

특별한 의미를 지닌 열, 키

• 종류별로 데이터를 구별하거나 테이블 간의 연관 관계를 표현할 때 키로 지정한 열을 사용한다.
• 키는 기본키(primary key), 후보키(candidate key), 외래키(foreign key), 복합키(composite key) 등으로 구분된다.

1. 기본키

키 중에서 가장 중요한 키로서 한 테이블 내에서 중복되지 않는 값만 가질 수 있는 키이다.

1. 보조키

대체키(alternate key)라고도 부르며 후보키(candidate key)에 속해 있는 키이다.

후보키는 기본키가 될 수 있는 모든 키를 의미하며, 기본키 역시 후보키에 속한다.

1. 외래키

특정 테이블에 포함되어 있으면서 다른 테이블의 기본키로 지정된 키를 의미한다.

외래키를 사용하면 병합과 유사한 효과를 얻을 수 있어 데이터 중복을 최소화할 수 있으며, 저장 공간 크기와 관리는 비용과 밀접한 관련이 있기 때문에 데이터 중복은 최대한 피하느 것이 좋다.

1. 복합키

여러 열을 조합하여 기본키 역할을 할 수 있게 만든 키이다.

02 - 2 오라클 데이터베이스

자료형

• 테이블을 구성하는 열에 지정한다.
• 가장 많이 사용하는 자료형은 VARCHAR2, NUMBER, DATE이다.

• 위 자료형 외에도 더 있다.

객체

• 객체는 오라클DB 내에서 데이터를 저장하고 관리하기 위한 논리 구조를 가진 구성 요소이다.