-- 동의어(SYNONYM)
-- 다른 데이터베이스가 가진 객체에 대한 별명 혹은 줄임말
-- 여러 사용자가 테이블을 공유할 경우
-- 다른 사용자가 테이블에 접근할 때 '사용자명.테이블명'으로 표현하는데
-- 동의어를 사용하면 간단하게 사용할 수 있다.
-- 생성 방법
-- CREATE SYNONYM 줄임말 FOR 사용자명.객체명;
CREATE SYNONYM EMP FOR EMPLOYEE;
SELECT
E.*
FROM EMP E;
-- <시스템 계정으로 실행>
GRANT CREATE SYNONYM TO C##EMPLOYEE;
-- 동의어의 구분
-- 1. 비공개 동의어
-- 객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어
-- 2. 공개 동의어
-- 모든 권한을 주는 사용자가 정의한 동의어
-- 모든 사용자가 사용할 수 있음(PUBLIC)
-- <시스템 계정으로 실행>
CREATE PUBLIC SYNONYM DEPT FOR C##EMPLOYEE.DEPARTMENT;
-- 공개 동의어로 설정되어 시스템 계정에서도 조회 가능
SELECT
D.*
FROM DEPT D;
-- 비공개 동의어로 설정되어 시스템 계정에서는 조회 불가
SELECT
E.*
FROM EMP E;
-- 권한과 ROLE
-- <사용자 관리>
-- : 사용자의 계정과 암호설정, 권한 부여
-- 보안을 위한 데이터베이스 관리자
-- : 사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대해
-- 특정 권한을 가질 수 있게 하는 권한이 있음
-- 다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안 설정
-- 데이터베이스에 접근하는 사용자마다 서로 다른 권한과 롤을 부여함
-- 내가 다른 사용자에게 부여한 객체 권한을 조회
-- <SYSTEM 계정으로 실행>
SELECT
UTPR.*
FROM USER_TAB_PRIVS_RECD UTPR;
-- 나에게 부여된 객체 권한, 객체 이름을 조회
SELECT
UTPM.*
FROM USER_TAB_PRIVS_MADE UTPM;
-- 1. 시스템 권한 : 데이터베이스 관리자가 가지고 있는 권한으로
-- 오라클 접속, 테이블, 뷰, 인덱스 등의 생성 권한
-- CREATE USER(사용자 계정 만들기)
-- DROP USER(사용자 계정 삭제)
-- DROP ANY TABLE(임의의 테이블 삭제)
-- QUERY REWRITE(함수 기반 인덱스 생성 권한)
-- BACKUP ANY TABLE(테이블 백업)
-- 시스템 관리자가 사용자에게 부여하는 권한
-- CREATE SESSION(데이터베이스에 접속)
-- CREATE TABLE(테이블 생성)
-- CREATE VIEW(뷰 생성)
-- CREATE SEQUENCE(시퀀스 생성)
-- <SYSTEM 계정으로 실행>
CREATE USER C##SAMPLE IDENTIFIED BY SAMPLE;
-- 생성한 SAMPLE 계정으로 접속 시도 시 접속 권한(CREATE SESSION)이 없어서 접속 불가
GRANT CREATE SESSION TO C##SAMPLE;
-- C##SAMPLE 계정으로 테이블 생성 구문 실행
-- 테이블 생성 권한(CREATE TABLE)이 없어서 생성 불가
GRANT CREATE TABLE TO C##SAMPLE;
CREATE TABLE TEST_TABLE(
COL1 VARCHAR2(20),
COL2 NUMBER
);
-- WITH ADMIN OPTION
-- : 사용자에게 시스템 권한을 부여할 때 사용함
-- 권한을 부여받은 사용자는 다른 사용자에게 권한을 지정할 수 있음
GRANT CREATE SESSION TO C##SAMPLE
WITH ADMIN OPTION;
-- C##SAMPLE2 계정 생성하기 (SYSTEM 계정으로 실행)
CREATE USER C##SAMPLE2 IDENTIFIED BY SAMPLE2;
-- C##SAMPLE 계정으로 C##SAMPLE2 계정에 접속 권한 부여하기
GRANT CREATE SESSION TO C##SAMPLE2;
-- 2. 객체 권한 : 사용자가 특정 객체(테이블, 뷰, 시퀀스, 함수)를 조작하거나 접근할 수 있는 권한
-- DML(SELECT/INSERT/UPDATE/DELETE)
-- GRANT 권한종류 [(컬럼명)] | ALL
-- ON 객체명 | ROLE 이름 | PUBLIC
-- TO 사용자 이름
-- WITH GRANT OPTION
-- : 사용자가 특정 객체를 조작하거나 접근 할 수 있는 권한을 부여받으면서
-- 그 권한을 다른 사용자에게 다시 부여할 수 있는 권한 옵션
GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE
WITH GRANT OPTION;
-- C##SAMPLE 계정에서 C##EMPLOYEE.EMPLOYEE 조회
SELECT
EE.*
FROM C##EMPLOYEE.EMPLOYEE EE;
-- C##SAMPLE 계정이 C##SAMPLE2 계정에게 권한 부여 가능
GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE2;
-- 권한 철회(REVOKE)
REVOKE SELECT ON C##EMPLOYEE.EMPLOYEE FROM C##SAMPLE;
-- 참고
-- WITH GRANT OPTION은 REVOKE시 다른 사용자에게도 부여한 권한을 같이 회수
-- WITH ADMIN OPTION은 특정 사용자의 권한만 회수가 되고 나머지 다른 사용자에게
-- 부여한 권한은 회수가 되지 않음
-- 데이터베이스 ROLE - 권한 제어
-- : 사용자마다 일일히 권한을 부여하는 것은 번거롭기 때문에
-- 간편하게 권한을 부여할 수 있는 방법으로 ROLE을 제공한다.
-- 롤(ROLE)
-- : 사용자에게 보다 간편하게 부여할 수 있도록 여러 개의 권한을 묶어놓는 것
-- 사용자에게 부여한 권한을 수정하고자 할 때도 롤만 수정하면
-- 그 롤에 대한 권한을 부여 받은 사용자들의 권한이 자동으로 수정된다.
-- 롤을 활성화 하거나 비활성화 해서 일시적으로 권한을 부여하고 철회할 수 있다.
SELECT
GRANTEE
, PRIVILEGE
FROM DBA_SYS_PRIVS
-- WHERE GRANTEE = 'RESOURCE';
WHERE GRANTEE = 'CONNECT';
-- 롤의 종류
-- 1. 사전 정의된 롤 : 오라클 설치 시 시스템에서 기본적으로 제공 됨
-- EX) CONNECT ROLE, RESOURCE ROLE
-- 2. 사용자가 정의하는 롤
-- : CREATE ROLE 명령으로 롤 생성
-- 롤 생성은 반드시 DBA 권한이 있는 사용자만 할 수 있음
-- CREATE ROLE 롤이름; -- 1. 롤 생성
-- GRANT 권한종류 TO 롤이름; -- 2. 생성 된 롤에 권한 추가
-- GRANT 롤이름 TO 사용자이름; -- 3. 사용자에게 롤 부여
CREATE ROLE C##MYROLE;
GRANT CREATE VIEW, CREATE SEQUENCE TO C##MYROLE;
GRANT C##MYROLE TO C##SAMPLE;
-- MYROLE 권한 확인
SELECT
GRANTEE
, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'C##MYROLE';
-- SAMPLE 계정의 ROLE 확인
SELECT
DRP.*
FROM DBA_ROLE_PRIVS DRP
WHERE GRANTEE = 'C##SAMPLE';
-- <SAMPLE 계정으로 확인하기>
CREATE SEQUENCE SEQ_TEST;
DROP SEQUENCE SEQ_TEST;
CREATE OR REPLACE FORCE VIEW VIEW_TEST
AS
SELECT
E.A
FROM EEE E;
테이블과는 다르게 실질적으로 데이터를 저장하고 있지 않지만, 사용자는 테이블을 사용하는것과 동일하게 사용할 수 있다.
CREATE OR REPLACE VIEW V_EMPLOYEE(사번,이름,부서,지역)
AS SELECT
EMP_ID
, EMP_NAME
, DEPT_TITLE
, NATIONAL_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT
ON (DEPT_ID = DEPT_CODE)
LEFT JOIN LOCATION
ON (LOCATION_ID = LOCAL_CODE)
LEFT JOIN NATIONAL
USING (NATIONAL_CODE);
SELECT
*
FROM V_EMPLOYEE;
서브쿼리의 SELECT절에 함수가 사용된 경우 반드시 별칭을 지정해 주어야 한다.
CREATE OR REPLACE VIEW V_EMP_JOB(사번,이름,직급,성별,근무년수)
AS SELECT
EMP_ID
, EMP_NAME
, JOB_NAME
, DECODE(SUBSTR(EMP_NO,8,1),1,’남‘
,2,’여’)
, EXTRACT(YEAR FROM SYSDATE) –
EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);
CREATE OR REPLACE VIEW V_JOB
AS SELECT
JOB_CODE
, JOB_NAME
FROM JOB;
-- VIEW(뷰)
-- SELECT 쿼리문을 저장한 객체이다.
-- 실질적인 데이터를 저장하고 있지 않은 논리적인 테이블
-- 테이블을 사용하는 것과 동일하게 사용할 수 있다.
-- 1) 복잡한 SELECT문을 다시 작성할 필요가 없음
-- 2) 민감한 데이터를 숨길 수 있음
-- CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리
-- 사번, 이름, 직급명, 부서명, 근무지역을 조회하고,
-- 그 결과를 V_RESULT_EMP 라는 뷰를 생성해서 저장하세요
CREATE OR REPLACE VIEW V_RESULT_EMP
AS
SELECT E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, D.DEPT_TITLE
, L.LOCAL_NAME
FROM EMPLOYEE E
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE);
-- VIEW 생성 권한 부여
-- <GRANT 작성 - 시스템 계정으로 전환 - 실행 - 다시 직원관리 계정전환>
GRANT CREATE VIEW TO C##EMPLOYEE;
SELECT
V.*
FROM V_RESULT_EMP V
WHERE V.EMP_ID = '205';
-- 데이터 딕셔너리(Data Dictionary)
-- 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블
-- 사용자가 테이블을 생성하거나, 사용자를 변경하는 등의 작업을 할 때
-- 데이터베이스 서버에 의해 자동으로 갱신되는 테이블
-- 사용자는 데이터 딕셔너리 내용을 직접 수정하거나 삭제할 수 없음
-- 원본 테이블을 커스터마이징 해서 보여주는 원본 테이블의
-- 가상 테이블 객체(VIEW)
-- 3개의 딕셔너리 뷰로 나뉨
-- 1. DBA_XXX : 데이터베이스 관리자만 접근이 가능한 객체 등의 정보 조회
-- 2. ALL_XXX : 자신의 계정 + 권한을 부여받은 객체의 정보 조회
-- 3. USER_XXX : 자신의 계정이 소유한 객체 등에 관한 정보 조회
-- 뷰에 대한 정보를 확인하는 데이터 딕셔너리
SELECT
UV.*
FROM USER_VIEWS UV;
-- 뷰에 별칭 부여
CREATE OR REPLACE VIEW V_EMP
(
사번
, 이름
, 부서
)
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
FROM EMPLOYEE E;
SELECT
V.*
FROM V_EMP V;
SELECT
V.*
FROM (SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
FROM EMPLOYEE E
) V
;
DROP VIEW V_EMP;
-- 베이스테이블의 정보가 변경 되면
-- VIEW도 같이 변경된다.
COMMIT;
UPDATE
EMPLOYEE E
SET E.EMP_NAME = '정중앙'
WHERE E.EMP_ID = '205';
SELECT
E.*
FROM EMPLOYEE E
WHERE E.EMP_ID = '205';
SELECT
V.*
FROM V_RESULT_EMP V
WHERE V.EMP_ID = '205';
ROLLBACK;
DROP VIEW V_RESULT_EMP;
-- 뷰 서브쿼리 안에 연산의 결과도 포함할 수 있다.
-- 이 때는 반드시 별칭을 부여해서 생성해야 함
CREATE OR REPLACE VIEW V_EMP_JOB
(
사번
, 이름
, 직급
, 성별
, 근무년수
)
AS
SELECT E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, DECODE(SUBSTR(E.EMP_NO, 8, 1), 1, '남', '여')
, EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.HIRE_DATE)
FROM EMPLOYEE E
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);
SELECT
V.*
FROM V_EMP_JOB V;
DMB 명령어로 조작이 불가능한 경우
1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우
3. 산술 표현식이 정의된 경우
4. JOIN을 이용해 여러 테이블을 연결한 경우
5. DISTINCT를 포함한 경우
6. 그룹함수나 GROUP BY 절을 포함한 경우
뷰 정의에 포함되지 않는 컬럼을 INSERT/UPDATE하는 경우 에러 발생
단, DELETE는 가능하다.
CREATE OR REPLACE VIEW V_JOB2
AS SELECT
JOB_CODE
FROM JOB;
INSERT
INTO V_JOB2
VALUES(‘J8’,’인턴’);
뷰에 포함되지 않은 NOT NULL 제약조건이 있는 컬럼이 존재하면 INSERT/UPDATE시 에러 발생
단, DELETE는 가능하다.
CREATE OR REPLACE VIEW V_JOB3
AS SELECT
JOB_NAME
FROM JOB;
INSERT
INTO V_JOB3
VALUES(’인턴’);
뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE시 에러 발생
단, DELETE는 가능하다.
CREATE OR REPLACE VIEW EMP_SAL
AS SELECT
EMP_ID
, EMP_NAME
, SALARY
,(SALARY + (SALARY * NVL(BONUS,0))) * 12 연봉
FROM EMPLOYEE;
-- VIEW를 통한 DML 구문 수행
CREATE OR REPLACE VIEW V_JOB
AS
SELECT J.JOB_CODE
, J.JOB_NAME
FROM JOB J;
SELECT
V.*
FROM V_JOB V;
INSERT
INTO V_JOB
(
JOB_CODE
, JOB_NAME
)
VALUES
(
'J8'
, '인턴'
);
SELECT
V.*
FROM V_JOB V;
SELECT
J.*
FROM JOB J;
UPDATE
V_JOB V
SET V.JOB_NAME = '알바'
WHERE V.JOB_CODE = 'J8';
SELECT
V.*
FROM V_JOB V;
SELECT
J.*
FROM JOB J;
DELETE
FROM V_JOB V
WHERE V.JOB_CODE = 'J8';
SELECT
V.*
FROM V_JOB V;
SELECT
J.*
FROM JOB J;
-- DML 명령어로 조작이 불가능한 경우
-- 1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
-- 2. 뷰에 포함되지 않은 컬럼 중에,
-- 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정 된 경우
-- 3. 산술표현식으로 정의된 경우
-- 4. JOIN을 이용해 여러 테이블을 연결한 경우
-- 5. DISTINCT 포함한 경우
-- 6. 그룹함수나 GROUP BY 절을 포함한 경우
-- 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
CREATE OR REPLACE VIEW V_JOB2
AS
SELECT J.JOB_CODE
FROM JOB J;
SELECT
V.*
FROM V_JOB2 V;
-- JOB_NAME 부적합한 식별자 오류
INSERT
INTO V_JOB2
(
JOB_CODE
, JOB_NAME
)
VALUES
(
'J8'
, '인턴'
);
-- JOB_NAME 부적합한 식별자 오류
UPDATE
V_JOB2 V
SET V.JOB_NAME = '인턴'
WHERE V.JOB_CODE = 'J7';
-- 뷰 정의에 사용 된 컬럼만 사용하므로 삽입 가능
INSERT
INTO V_JOB2
(
JOB_CODE
)
VALUES
(
'J8'
);
SELECT
J.*
FROM JOB J;
-- 뷰 정의에 사용 된 컬럼만 사용하여 DELETE 가능
DELETE
FROM V_JOB2
WHERE JOB_CODE = 'J8';
-- 뷰에 포함되지 않은 컬럼 중에
-- 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우
CREATE OR REPLACE VIEW V_JOB3
AS
SELECT J.JOB_NAME
FROM JOB J;
SELECT
V.*
FROM V_JOB3 V;
-- JOB_CODE 뷰 정의에 없어 부적합한 식별자 오류
INSERT
INTO V_JOB3
(
JOB_CODE
, JOB_NAME
)
VALUES
(
'J8'
, '인턴'
);
-- JOB_CODE에는 NULL이 삽입 될 수 없어 오류
INSERT
INTO V_JOB3
(
JOB_NAME
)
VALUES
(
'인턴'
);
-- 뷰에 정의 된 컬럼만을 사용한 UPDATE 수행 가능
UPDATE
V_JOB3 V
SET V.JOB_NAME = '인턴'
WHERE V.JOB_NAME = '사원';
-- 산술표현식으로 정의된 경우
CREATE OR REPLACE VIEW EMP_SAL
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.SALARY
, (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 연봉
FROM EMPLOYEE E;
SELECT
ES.*
FROM EMP_SAL ES;
-- 산술표현식으로 연산 된 가상 컬럼 연봉에는 INSERT 불가
INSERT
INTO EMP_SAL
(
EMP_ID
, EMP_NAME
, SALARY
, 연봉
)
VALUES
(
'800'
, '정진훈'
, 3000000
, 4000000
);
-- 산술표현식으로 연산 된 가상 컬럼 연봉은 UPDATE 불가
UPDATE
EMP_SAL ES
SET ES.연봉 = 80000000
WHERE ES.EMP_ID = '200';
-- DELETE의 조건으로는 사용 가능
DELETE
FROM EMP_SAL ES
WHERE ES.연봉 = 124800000;
ROLLBACK;
-- JOIN을 이용해 여러 테이블을 연결한 경우
CREATE OR REPLACE VIEW V_JOINEMP
AS
SELECT E.EMP_ID
, E.EMP_NAME
, D.DEPT_TITLE
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID);
SELECT
V.*
FROM V_JOINEMP V;
-- 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
INSERT
INTO V_JOINEMP
(
EMP_ID
, EMP_NAME
, DEPT_TITLE
)
VALUES
(
888
, '조세오'
, '인사관리부'
);
-- 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
UPDATE
V_JOINEMP V
SET V.DEPT_TITLE = '인사관리부';
DELETE
FROM V_JOINEMP V
WHERE V.EMP_ID = '219';
SELECT
V.*
FROM V_JOINEMP V
WHERE V.EMP_ID = '219';
ROLLBACK;
-- DISTINCT를 포함한 경우
CREATE OR REPLACE VIEW V_DT_EMP
AS
SELECT DISTINCT E.JOB_CODE
FROM EMPLOYEE E;
-- 뷰에 대한 데이터 조작이 부적합합니다
INSERT
INTO V_DT_EMP
(
JOB_CODE
)
VALUES
(
'J9'
);
-- 뷰에 대한 데이터 조작이 부적합합니다
UPDATE
V_DT_EMP V
SET V.JOB_CODE = 'J9'
WHERE V.JOB_CODE = 'J7';
-- 뷰에 대한 데이터 조작이 부적합합니다
DELETE
FROM V_DT_EMP V
WHERE V.JOB_CODE = 'J7';
-- 그룹 함수나 GROUP BY 절을 포함한 경우
CREATE OR REPLACE VIEW V_GROUPDEPT
AS
SELECT E.DEPT_CODE
, SUM(E.SALARY) 합계
, AVG(E.SALARY) 평균
FROM EMPLOYEE E
GROUP BY E.DEPT_CODE;
SELECT
V.*
FROM V_GROUPDEPT V;
-- 가상 열은 사용할 수 없습니다
INSERT
INTO V_GROUPDEPT
(
DEPT_CODE
, 합계
, 평균
)
VALUES
(
'D0'
, 60000000
, 4000000
);
-- 뷰에 대한 데이터 조작이 부적합합니다
UPDATE
V_GROUPDEPT V
SET V.DEPT_CODE = 'D10'
WHERE V.DEPT_CODE = 'D1';
-- 뷰에 대한 데이터 조작이 부적합합니다
DELETE
FROM V_GROUPDEPT V
WHERE V.DEPT_CODE = 'D1';
VIEW 옵션
1. OR REPLACE 옵션 - 생성한 뷰가 존재하면, 뷰를 갱신한다.
2. FORCE/NOFORCE 옵션 - FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰를 생성한다. - 기본값은 NOFORCE로 지정되어 있다.
3. FORCE/NOFORCE 옵션 - FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰를 생성한다. - 기본값은 NOFORCE로 지정되어 있다.
4. WITH READ ONLY 옵션 - 뷰에 대해 조회만 가능하고, 삽입, 수정, 삭제 등을 하지 못하게 한다.
-- VIEW 옵션
-- OR REPLACE : 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고
-- 존재하지 않으면 새로 생성하는 옵션
-- FORCE 옵션 : 서브쿼리에 사용 된 테이블이 존재하지 않아도 뷰 생성
CREATE OR REPLACE FORCE VIEW V_EMP
AS
SELECT TCODE
, TNAME
, TCONTENTS
FROM TT;
SELECT
V.*
FROM V_EMP V;
-- NOFORCE 옵션 : 서브쿼리에 테이블이 존재해야만 뷰 생성함(기본값)
CREATE OR REPLACE /*NOFORCE*/ VIEW V_EMP2
AS
SELECT TCODE
, TNAME
, TCONTENTS
FROM TT;
-- WITH CHECK OPTION : 조건절에 사용 된 컬럼의 값을 수정하지 못하게 한다.
CREATE OR REPLACE VIEW V_EMP3
AS
SELECT E.*
FROM EMPLOYEE E
WHERE MANAGER_ID = '200'
WITH CHECK OPTION;
-- 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
UPDATE
V_EMP3
SET MANAGER_ID = '900'
WHERE MANAGER_ID = '200';
-- WITH READ ONLY : DML 수행이 불가능하게 한다.
CREATE OR REPLACE VIEW V_DEPT
AS
SELECT D.*
FROM DEPARTMENT D
WITH READ ONLY;
-- 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
DELETE
FROM V_DEPT;
INLINE VIEW
일반적으로 FROM 절에 사용된 서브쿼리의 결과 화면에 별칭을 붙인 것을 말한다.
FROM절에 서브쿼리를 직접 사용해도 되고, 따로 뷰를 생성 후 FROM절에서 생성한 뷰를 사용해도 된다.
ORACLE OBJECT(SEQUENCE)
SEQUENCE
순차적으로 정수 값을 자동으로 생성하는 객체로, 자동 번호 발생기의 역할을 한다.
표현식
CREATE SEQUENCE 시퀀스명
[START WITH 숫자 ] - 처음 발생시킬 시작 값 지정, 기본값 1
[INCREMENT BY 숫자 ] - 다음 값에 대한 증가치, 기본값 1
[MAXVALUE 숫자 | NOMAXVALUE ] - 발생시킬 최대값 지정, 10의 27승 -1까지 가능
[MINVALUE 숫자 | NOMINVALUE ] – 발생시킬 최소값 지정, -10의 26승
[CYCLE | NOCYCLE ] – 시퀀스가 최대값까지 증가를 완료하면 CYCLE은 START WITH 설정값으로
돌아가고, NOCYCLE은 에러발생
[CACHE | NOCACHE] – CACHE는 메모리상에서 시퀀스값을 관리, 기본값 20
CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;
SELECT
SEQ_EMP_ID.CURRVAL
FROM DUAL;
NEXTVAL / CURRVAR 사용 가능 여부
사용가능
사용불가
서브쿼리가 아닌 SELECT문
VIEW의 SELECT절
INSERT문의 SELECT절
DISTINCT 키워드가 있는 SELECT문
INSERT문의 VALUE절
GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문
UPDATE문의 SET절
SELECT, DELETE, UPDATE의 서브쿼리
CREATE TABLE, ALTER TABLE 명령의 DEFAULT값
시퀀스 수정 시 CREAT에 사용한 옵션을 변경할 수 있다.
단, START WITH값 변경은 불가하기 때문에 변경하려면 삭제 후 다시 생성해야 한다.
ALTER SEQUENCE SEQ_EMPID
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;
-- 시퀀스(SEQUENCE)
-- 자동 번호 발생기 역할을 하는 객체
-- 순차적으로 정수값을 자동으로 생성해줌
/*
CREATE SEQUENCE 시퀀스이름
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1 기본
[START WITH 숫자] -- 처음 발생시킬 값 지정, 생략하면 자동 1 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정(10의 27승)
[MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정(-10의 26승)
[CYCLE | NOCYCLE] -- 값 순환 여부
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소는 2바이트
*/
CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;
-- 시퀀스.CURRVAL : 해당 시퀀스가 가지고 있는 CURRENT VALUE (현재 값)
-- 시퀀스.NEXTVAL : 해당 시퀀스가 가질 다음 값 리턴
-- NEXTVAL를 1회 수행해야 CURRVAL를 알아올 수 있다
SELECT SEQ_EMPID.CURRVAL FROM DUAL;
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --300
SELECT SEQ_EMPID.CURRVAL FROM DUAL; --300
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --305
SELECT SEQ_EMPID.CURRVAL FROM DUAL; --305
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --310
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --MAXVALUE를 넘어서면 에러 발생
-- 시퀀스 변경
ALTER SEQUENCE SEQ_EMPID
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;
-- START WITH 값은 변경이 불가능하므로
-- START WITH 값을 변경하려면 DROP으로 삭제 후 다시 생성해야 함
-- START WITH 값은 변경이 불가능하므로
-- START WITH 값을 변경하려면 DROP으로 삭제 후 다시 생성해야 함
-- SELECT문에서 사용 가능
-- INSERT문에서 SELECT 구문 사용 가능
-- INSERT문에서 VALUES 절에서 사용 가능
-- UPDATE문에서 SET절에서 사용 가능
-- 단, 서브쿼리의 SELECT문에서 사용 불가
-- VIEW의 SELECT절에서 사용 불가
-- DISTINCT 키워드가 있는 SELECT문에서 사용 불가
-- GROUP BY, HAVING절이 있는 SELECT문에서 사용 불가
-- ORDER BY절에서 사용 불가
-- CREATE TABLE, ALTER TABLE의 DEFAULT값으로 사용 불가
-- 인덱스(INDEX)
-- : SQL명령문의 검색 처리 속도를 향상시키기 위해
-- 컬럼에 대해서 생성하는 오라클 객체
-- 하드디스크의 어느 위치인지에 대한 정보를 가진 주소록
-- DATA - ROWID로 구성
-- ROWID 구조 : 오브젝트 번호, 상대파일 번호, 블록 번호, 데이터 번호
SELECT
ROWID
, E.EMP_ID
, E.EMP_NAME
FROM EMPLOYEE E;
-- 인덱스의 내부 구조는 이진트리 형식으로 구성되어 있고
-- 인덱스를 생성하기 위해서는 시간이 필요함
-- 또한 인덱스를 위한 추가 저장 공간이 필요하기 때문에 반드시 좋은 것은 아니다.
-- => 인덱스가 생성 된 컬럼에서 DML 작업이 빈번한 경우 처리 속도가 느려진다.
-- 따라서 일반적으로 테이블 전체 로우의 15% 이하의 데이터를 조회할 때 인덱스를 생성한다.
-- 장점
-- 검색 속도가 빨라짐
-- 시스템에 걸리는 부하를 줄여서 시스템 전체의 성능을 향상시킴
-- 단점
-- 인덱스를 위한 추가 저장 공간이 필요함
-- 인덱스를 생성하는데 시간이 걸림
-- 데이터의 변경작업(INSERT/UPDATE/DELETE)이 자주 일어나는 경우
-- REBUILD 작업을 주기적으로 해주어야 하고, REBUILD를 자주 해주지 않으면
-- 성능이 오히려 저하된다.
-- 인덱스를 관리하는 데이터 딕셔너리
-- PK, UNIQUE 제약 조건이 있으면 자동으로 INDEX 객체가 생성 된다.
SELECT
UIC.*
FROM USER_IND_COLUMNS UIC;
-- 인덱스 종류
-- 1. 고유 인덱스(UNIQUE INDEX)
-- 2. 비고유 인덱스(NONUNIQUE INDEX)
-- 3. 단일 인덱스(SINGLE INDEX)
-- 4. 결합 인덱스(CONPOSITE INDEX)
-- 5. 함수기반 인덱스(FUNCTION BASED INDEX)
-- UNIQUE INDEX
-- UNIQUE INDEX로 생성 된 컬럼에는 중복 값이 포함될 수 없음
-- 오라클 PRIMARY KEY, UNIQUE 제약조건을 생성하면
-- 자동으로 해당 컬럼에 UNIQUE INDEX가 생성 됨
-- 해당 컬럼으로 ACCESS 하는 경우 성능 향상의 효과가 있음
-- 인덱스 힌트
-- 일반적으로는 옵티마이저가 적절한 인덱스를 타거나 풀 스캐닝을 해서 비용이 적게 드는 효율적인 방식으로 검색함
-- 하지만 우리가 원하는 테이블에 있는 인덱스를 사용할 수 있도록 해주는 구문(힌트)를 통해 선택 가능
-- SELECT절 첫 줄에 힌트 주석(/*+ 내용 */)을 작성하여 적절한 인덱스를 부여할 수 있다.
SELECT /*+ INDEX(E 엔터티1_PK)*/
E.*
FROM EMPLOYEE E;
-- 인덱스가 내림차순으로 생성 되어서 인덱스 영역에서 역방향으로 스캔하라는 뜻
-- 예전에 넣었던 데이터부터 순서대로 나오도록 정렬
SELECT /*+ INDEX_DESC(E 엔터티1_PK)*/
E.*
FROM EMPLOYEE E;
-- UNIQUE 제약 조건에 의해 이미 인덱스가 존재하여 생성할 수 없음
CREATE UNIQUE INDEX IDX_EMPNO
ON EMPLOYEE(EMP_NO);
SELECT
UIC.*
FROM USER_IND_COLUMNS UIC;
-- PK나 UNIQUE 제약 조건으로 인해 생성 된 인덱스는 DROP 할 수 없음
DROP INDEX SYS_C007415;
-- 중복 값이 있는 컬럼은 UNIQUE 인덱스 생성하지 못함
CREATE UNIQUE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);
-- NONUNIQUE INDEX
-- WHERE절에서 빈번하게 사용 되는 일반 컬럼을 대상으로 생성
-- 주로 성능 향상을 위한 목적으로 생성함
CREATE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);
-- 결합 인덱스(COMPOSITE INDEX)
-- 결합 인덱스는 중복 값이 낮은 값이 먼저 오는 것이 검색 속도를 향상시킨다.
CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE);
SELECT /*+ INDEX_DESC(D IDX_DEPT)*/
D.DEPT_ID
FROM DEPARTMENT D
WHERE D.DEPT_TITLE > '0'
AND D.DEPT_ID > '0';
-- 0보다 크다는 것은 '모두'라는 뜻
-- 함수 기반 인덱스
-- SELECT절이나 WHERE절에서 산술 계산식이나 함수가 사용된 경우
-- 계산에 포함 된 컬럼은 인덱스의 적용을 받지 않는다.
-- 계산식으로 검색하는 경우가 많다면, 수식이나 함수식으로 이루어진 컬럼을
-- 인덱스로 만들 수도 있다.
CREATE INDEX IDX_EMP_SALCALC
ON EMPLOYEE((SALARY + (SALARY * NVL(BONUS, 0))) * 12);
SELECT /*+ INDEX_DESC(E IDX_EMP_SALCALC)*/
E.EMP_ID
, E.EMP_NAME
, ((E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12) 연봉
FROM EMPLOYEE E
WHERE ((E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12) > 10000000;
-- DML (Data Manupulation Language)
-- INSERT, UPDATE, DELETE, SELECT
-- : 데이터 조작 언어, 테이블에 값을 삽입하거나, 수정하거나
-- 삭제하거나, 조회하는 언어
-- INSERT : 새로운 행을 추가하는 구문이다.
-- 테이블의 행 갯수가 증가한다.
-- [표현식]
-- 테이블의 일부 컬럼에 INSERT할 때
-- INSERT INTO 테이블명(컬럼명, 컬럼명, ..) VALUES (데이터, 데이터, ...);
-- 테이블의 모든 컬럼에 INSERT할 때
-- INSERT INTO 테이블명 VALUES (데이터, 데이터, ..);
-- 하지만 모든 컬럼에 INSERT 할 때에도 컬럼명을 기술하는 것이 의미파악에 더 좋다.
SELECT
E.*
FROM EMPLOYEE E
WHERE EMP_NAME = '장채현';
-- INSERT 대신에 서브쿼리를 이용할 수 있다.
CREATE TABLE EMP_01 (
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);
SELECT
E.*
FROM EMP_01 E;
INSERT
INTO EMP_01 A
(
A.EMP_ID
, A.EMP_NAME
, A.DEPT_TITLE
)
(
SELECT
E.EMP_ID
, EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
);
SELECT
E.*
FROM EMP_01 E;
INSERT ALL
INSERT시 사용하는 서브쿼리가 사용하는 테이블이 같은 경우, 두 개 이상의 테이블에 INSERT ALL을 이용하여 한번에 삽입을 할 수 있다.
단, 각 서브쿼리의 조건절이 같아야 한다.
CREATE TABLE EMP_DEPT_D1
AS SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
WHERE 1=0;
SELECT
*
FROM EMP_DEPT_D1;
CREATE TABLE EMP_MANAGER
AS SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, MANAGER_ID
FROM EMPLOYEE
WHERE 1=0;
SELECT
*
FROM EMP_MANAGER;
INSERT ALL
INTO EMP_DEPT_D1
VALUES(EMP_ID,EMP_NAME,DEPT_CODE,HIRE_DATE)
INTO EMP_MANAGER
VALUES(EMP_ID,EMP_NAME,DEPT_CODE,HIRE_DATE,MANAGER_ID)
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
, MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE = ‘D1’;
EMPLOYEE 테이블의 구조를 복사하여 사번, 이름, 입사일, 급여를 기록할 수 있는 테이블 EMP_OLD와 EMP_NEW를 생성하세요.
CREATE TABLE EMP_OLD
AS SELECT
EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
FROM EMPLOYEE
WHERE 1 = 0;
CREATE TABLE EMP_NEW
AS SELECT
EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
FROM EMPLOYEE
WHERE 1 = 0;
EMPLOYEE 테이블의 입사일 기준으로 2000년 1월1일 이전에 입사한 사원의 사번, 이름, 입사일, 급여를 조회해서 EMP_OLD 테이블에 삽입하고, 그전에 입사한 사원의 정보는 EMP_NEW 테이블에 삽입하세요.
INSERT ALL
WHEN HIRE_DATE < ‘2000/01/01’ THEN
INTO EMP_OLD
VALUES (EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= ‘2000/01/01’ THEN
INTO EMP_NEW
VALUES (EMP_ID,EMP_NAME,HIRE_DATE,SALARY)
SELECT
EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
FROM EMPLOYEE;
-- INSERT ALL : INSERT 시에 사용 하는 서브쿼리가 같은 경우
-- 두개 이상의 테이블에 INSERT ALL을 이용하여
-- 한번에 데이터를 삽입할 수 있다.
-- 단, 각 서브쿼리의 조건절이 같아야 한다.
-- 테이블을 생성하자
CREATE TABLE EMP_DEPT_D1
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE 1 = 0;
-- 생성된 테이블 조회하기
SELECT
ED.*
FROM EMP_DEPT_D1 ED;
-- 테이블을 하나 더 만든다.
CREATE TABLE EMP_MANAGER
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE 1 = 0;
-- 테이블을 조회하기
SELECT
EM.*
FROM EMP_MANAGER EM;
-- EMP_DEPT_D1 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
-- 조회해서 사번, 이름, 소속부서, 입사일을 삽입하고,
-- EMP_MANAGER 테이블에 EMPLOYEE 테이블에 있는 부서 코드가 D1인 직원을
-- 조회해서 사번, 이름, 관리자 사번을 삽입하세요
INSERT
INTO EMP_DEPT_D1 A
(
A.EMP_ID
, A.EMP_NAME
, A.DEPT_CODE
, A.HIRE_DATE
)
(
SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1'
);
INSERT
INTO EMP_MANAGER A
(
A.EMP_ID
, A.EMP_NAME
, A.MANAGER_ID
)
(
SELECT E.EMP_ID
, E.EMP_NAME
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1'
);
SELECT
ED.*
FROM EMP_DEPT_D1 ED;
SELECT
EM.*
FROM EMP_MANAGER EM;
DELETE
FROM EMP_DEPT_D1;
DELETE
FROM EMP_MANAGER;
INSERT ALL
INTO EMP_DEPT_D1
VALUES
(
EMP_ID
, EMP_NAME
, DEPT_CODE
, HIRE_DATE
)
INTO EMP_MANAGER
VALUES
(
EMP_ID
, EMP_NAME
, MANAGER_ID
)
SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.HIRE_dATE
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE E.DEPT_CODE = 'D1';
SELECT
ED.*
FROM EMP_DEPT_D1 ED;
SELECT
EM.*
FROM EMP_MANAGER EM;
-- EMPLOYEE 테이블에서 입사일 기준으로 2000년 1월 1일 이전에 입사한
-- 사원의 사번, 이름, 입사일, 급여를 조회하여
-- EMP_OLD 테이블에 삽입하고
-- 그 이후에 입사한 사원은 EMP_NEW 테이블에 삽입하세요
-- 문제 의도 : INSERT ALL을 사용하되 입사일 기준으로 나누어라
CREATE TABLE EMP_OLD
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.HIRE_DATE
, E.SALARY
FROM EMPLOYEE E
WHERE 1 = 0;
CREATE TABLE EMP_NEW
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.HIRE_DATE
, E.SALARY
FROM EMPLOYEE E
WHERE 1 = 0;
-- INSERT ALL
INSERT ALL
WHEN HIRE_DATE < '2000/01/01'
THEN
INTO EMP_OLD
VALUES
(
EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
)
WHEN HIRE_DATE >= '2000/01/01'
THEN
INTO EMP_NEW
VALUES
(
EMP_ID
, EMP_NAME
, HIRE_DATE
, SALARY
)
SELECT E.EMP_ID
, E.EMP_NAME
, E.HIRE_DATE
, E.SALARY
FROM EMPLOYEE E;
-- 24개 행 이(가) 삽입되었습니다.
-- 잘 들어갔는지 조회
SELECT
EO.*
FROM EMP_OLD EO;
SELECT
EN.*
FROM EMP_NEW EN;
MERGE
구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능을 한다.
두 테이블에서 지정하는 조건의 값이 존재하면 UPDATE되고, 조건의 값이 없으면 INSERT 되도록 한다.
CREATE TABLE EMP_M01
AS SELECT
*
FROM EMPLOYEE;
CREATE TABLE EMP_M02
AS SELECT
*
FROM EMPLOYEE
WHERE JOB_CODE = ‘J4’;
-- MERGE :
-- 구조가 같은 두 개의 테이블을 하나로 합치는 기능을한다.
-- 테이블에서 지정하는 조건의 값이 존재하면 UPDATE
-- 조건의 값이 없으면 INSERT 됨
CREATE TABLE EMP_M01
AS
SELECT E.*
FROM EMPLOYEE E;
-- Table EMP_M01이(가) 생성되었습니다.
CREATE TABLE EMP_M02
AS
SELECT E.*
FROM EMPLOYEE E
WHERE E.JOB_CODE = 'J4';
-- Table EMP_M02이(가) 생성되었습니다.
INSERT
INTO EMP_M02 A
(
A.EMP_ID, A.EMP_NAME, A.EMP_NO, A.EMAIL, A.PHONE
, A.DEPT_CODE, A.JOB_CODE, A.SAL_LEVEL, A.SALARY, A.BONUS
, A.MANAGER_ID, A.HIRE_DATE, A.ENT_DATE, A.ENT_YN
)
VALUES
(
999, '임수연', '000101-4567890', 'limsoo@greedy.com', '01011112222'
, 'D9', 'J4', 'S1', 9000000, 0.5
, NULL, SYSDATE, NULL, DEFAULT
);
-- 1 행 이(가) 삽입되었습니다.
SELECT
EM1.*
FROM EMP_M01 EM1;
SELECT
EM2.*
FROM EMP_M02 EM2;
UPDATE
EMP_M02
SET SALARY = 0;
-- 5개 행 이(가) 업데이트되었습니다.
MERGE
INTO EMP_M01 M1
USING EMP_M02 M2
ON (M1.EMP_ID = M2.EMP_ID)
WHEN MATCHED THEN
UPDATE
SET M1.EMP_NAME = M2.EMP_NAME
, M1.EMP_NO = M2.EMP_NO
, M1.EMAIL = M2.EMAIL
, M1.PHONE = M2.PHONE
, M1.DEPT_CODE = M2.DEPT_CODE
, M1.JOB_CODE = M2.JOB_CODE
, M1.SAL_LEVEL = M2.SAL_LEVEL
, M1.SALARY = M2.SALARY
, M1.BONUS = M2.BONUS
, M1.MANAGER_ID = M2.MANAGER_ID
, M1.HIRE_DATE = M2.HIRE_DATE
, M1.ENT_DATE = M2.ENT_DATE
, M1.ENT_YN = M2.ENT_YN
WHEN NOT MATCHED THEN
INSERT
(
M1.EMP_ID, M1.EMP_NAME, M1.EMP_NO, M1.EMAIL, M1.PHONE
, M1.DEPT_CODE, M1.JOB_CODE, M1.SAL_LEVEL, M1.SALARY, M1.BONUS
, M1.MANAGER_ID, M1.HIRE_DATE, M1.ENT_DATE, M1.ENT_YN
)
VALUES
(
M2.EMP_ID, M2.EMP_NAME, M2.EMP_NO, M2.EMAIL, M2.PHONE
, M2.DEPT_CODE, M2.JOB_CODE, M2.SAL_LEVEL, M2.SALARY, M2.BONUS
, M2.MANAGER_ID, M2.HIRE_DATE, M2.ENT_DATE, M2.ENT_YN
);
--5개 행 이(가) 병합되었습니다.
SELECT
EM.*
FROM EMP_M01 EM;
UPDATE
테이블에 기록된 컬럼의 값을 수정하는 구문이다. 테이블의 전체 행 개수에는 변화가 없다.
WHERE 조건을 설정하지 않으면 모든 행의 컬럼 값이 변경된다.
CREATE TABLE DEPT_COPY
AS SELECT
*
FROM DEPARTMENT;
UPDATE
DEPT_COPY
SET DEPT_TITLE = ‘전략기획팀’
WHERE DEPT_ID = 'D9';
UPDATE시에도 서브쿼리를 이용할 수 있다.
평상시 유재식 사원을 부러워하던 방명수 사원의 급여와 보너스율을 유재식 사원과 동일하게 변경해 주기로 했다. 이를 반영하는 UPDATE문을 작성하세요.
CREATE TABLE EMP_SALARY
AS SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
, BONUS
FROM EMPLOYEE;
UPDATE
EMP_SALARY
SET SALARY = (SELECT
SALARY
FROM EMP_SALARY
WHERE EMP_NAME= '유재식’),
BONUS = (SELECT
BONUS
FROM EMP_SALARY
WHERE EMP_NAME=‘유재식’)
WHERE EMP_NAME = '방명수';
SELECT
*
FROM EMP_SALARY
WHERE EMP_NAME IN (‘유재식’,’방명수’);
[ UPDATE 적용 전 ]
[ UPDATE 적용 후 ]
각각 쿼리문 작성한 것을 다중행 다중열 서브쿼리로 변경하세요.
UPDATE
EMP_SALARY
SET (SALARY, BONUS) = (SELECT
SALARY
, BONUS
FROM EMP_SALARY
WHERE EMP_NAME - '유재식’)
WHERE EMP_NAME = '방명수';
SELECT
*
FROM EMP_SALARY
WHERE EMP_NAME IN (‘유재식’,’방명수’);
EMP_SALARY 테이블에서 아시아 지역에 근무하는 직원의 보너스 포인트를 0.3으로 변경하세요.
UPDATE
EMP_SALARY
SET BONUS = 0.3
WHERE EMP_ID IN (SELECT
EMP_ID
FROM EMPLOYEE
JOIN DEPARTMENT
ON (DEPT_ID = DEPT_CODE)
JOIN LOCATION
ON (LOCATION_ID = LOCAL_CODE)
WHERE LOCAL_NAME LIKE 'ASIA%’);
-- UPDATE : 테이블에 기록 된 컬럼의 값을 수정하는 구문이다.
-- 테이블의 전체 행 갯수는 변화가 없다.
-- 테스트할 테이블을 만들기
CREATE TABLE DEPT_COPY
AS
SELECT D.*
FROM DEPARTMENT D;
-- DEPT_COPY이(가) 생성되었습니다.
-- UPDATE의 문법 :
-- UPDATE 테이블명 SET 컬럼명 = 바꿀값, 컬럼명 = 바꿀값, ...
-- WHERE 절이 없으면 모든 행이 대상이 된다.
-- [WHERE 컬럼명 비교연산자 비교값];
SELECT
DC.*
FROM DEPT_COPY DC;
UPDATE
DEPT_COPY DC
SET DC.DEPT_TITLE = '전략기획팀'
WHERE DC.DEPT_ID = 'D9';
SELECT
DC.*
FROM DEPT_COPY DC;
-- UPDATE에서도 서브쿼리를 사용할 수 있다.
-- UPDATE 테이블명
-- SET 컬럼명 = (서브쿼리)
CREATE TABLE EMP_SALARY
AS
SELECT E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.SALARY
, E.BONUS
FROM EMPLOYEE E;
SELECT
ES.*
FROM EMP_SALARY ES
WHERE ES.EMP_NAME IN ('유재식', '방명수');
-- 평상시 유재식 사원을 부러워 하던 방명수 사원의
-- 급여와 보너스율을 유재식 사원과 동일하게 변경해 주기로 했다.
-- 이를 반영하는 UPDATE 문을 작성해보세요.
UPDATE
EMP_SALARY ES
SET ES.SALARY = (SELECT E1.SALARY
FROM EMPLOYEE E1
WHERE E1.EMP_NAME = '유재식'
)
, ES.BONUS = (SELECT E2.BONUS
FROM EMPLOYEE E2
WHERE E2.EMP_NAME = '유재식'
)
WHERE ES.EMP_NAME = '방명수';
SELECT
ES.*
FROM EMP_SALARY ES
WHERE ES.EMP_NAME IN ('유재식', '방명수');
-- 다중열 서브쿼리를 이용한 UPDATE문
-- 방명수 사원의 급여인상 소식을 전해들은 다른 직원들이
-- 단체로 파업을 진행했다.
-- 노옹철, 전형돈, 정중하, 하동운 사원의 급여와 보너스를
-- 유재식 사원의 급여와 보너스와 같게 변경하는 UPDATE문 작성
UPDATE
EMP_SALARY ES
SET (ES.SALARY, ES.BONUS) = (SELECT E1.SALARY, E1.BONUS
FROM EMPLOYEE E1
WHERE E1.EMP_NAME = '유재식'
)
WHERE ES.EMP_NAME IN ('노옹철', '전형돈', '정중하', '하동운') ;
SELECT
ES.*
FROM EMP_SALARY ES
WHERE ES.EMP_NAME IN ('유재식', '방명수', '노옹철', '전형돈', '정중하', '하동운');
-- 다중행 서브쿼리를 이용한 UPDATE
-- EMP_SALARY 테이블에서 아시아 근무 지역에 근무하는 직원의
-- 보너스를 0.5로 변경하세요.
UPDATE
EMP_SALARY ES
SET ES.BONUS = 0.5
WHERE ES.EMP_ID IN (SELECT E1.EMP_ID
FROM EMPLOYEE E1
JOIN DEPARTMENT D1 ON(E1.DEPT_CODE = D1.DEPT_ID)
JOIN LOCATION L1 ON (D1.LOCATION_ID = L1.LOCAL_CODE)
WHERE L1.LOCAL_NAME LIKE 'ASIA%'
);
SELECT
ES.*
FROM EMP_SALARY ES
WHERE ES.EMP_ID IN (SELECT E1.EMP_ID
FROM EMPLOYEE E1
JOIN DEPARTMENT D1 ON(E1.DEPT_CODE = D1.DEPT_ID)
JOIN LOCATION L1 ON (D1.LOCATION_ID = L1.LOCAL_CODE)
WHERE L1.LOCAL_NAME LIKE 'ASIA%'
);
-- UPDATE 시 변경 값은 해당 컬럼에 대한 제약 조건에 위배되지 않아야 함
UPDATE
EMPLOYEE E
-- FOREIGN KEY 제약 조건에 위배됨 (부모키가 없습니다.)
SET E.DEPT_CODE = '65'
WHERE E.DEPT_CODE = 'D6';
UPDATE
EMPLOYEE E
-- NOT NULL 제약 조건에 위배됨
SET E.EMP_NAME = NULL
WHERE E.EMP_ID = '200';
UPDATE
EMPLOYEE E
-- UNIQUE 제약 조건에 위배됨
SET E.EMP_NO = '800808-2123341'
WHERE E.EMP_ID = '200';
DELETE
테이블의 행을 삭제하는 구문이다. 테이블의 행 개수가 줄어든다.
WHERE 조건을 설정하지 않으면 모든 행이 삭제된다.
DELETE
FROM EMPLOYEE
WHERE EMP_NAME = '장채현';
FOREIGN KEY 제약조건이 설정되어 있는 경우 참조되고 있는 값에 대해서는 삭제 할 수 없다.
DELETE
FROM DEPARTMENT
WHERE DEPT_ID = 'D1';
FOREIGN KEY 제약조건이 설정되어 있는 경우 참조되고 있지 않은 값에 대해서는 삭제할 수 있다.
DELETE
FROM DEPARTMENT
WHERE DEPT_ID = ‘D3';
비활성화 된 제약조건을 다시 활성화 시킬 수 있다.
ALTER TABLE EMPLOYEE
ENABLE CONSTRAINT FK_DEPT_ID CASCADE;
-- DELETE : 테이블의 행을 삭제하는 구문이다.
-- 테이블의 행의 갯수가 줄어든다.
-- DELETE FROM 테이블명 WHERE 조건설정
-- 만약 WHERE 조건을 설정하지 않으면 모든 행이 다 삭제된다.
COMMIT;
--커밋 완료.
DELETE
FROM EMPLOYEE E;
-- 24개 행 이(가) 삭제되었습니다.
SELECT
E.*
FROM EMPLOYEE E;
-- COMMIT 시점을 만들었기에 되돌리기 가능
ROLLBACK;
-- 롤백 완료.
DELETE
FROM EMPLOYEE E
WHERE E.EMP_NAME = '장채현';
-- 1 행 이(가) 삭제되었습니다.
SELECT
E.*
FROM EMPLOYEE E;
ROLLBACK;
-- 롤백 완료.
-- FOEIGN KEY 제약조건이 설정 되어 있는 경우
-- 참조되고 있는 값에 대해서는 삭제할 수 없다.
DELETE
FROM DEPARTMENT D
WHERE D.DEPT_ID = 'D1';
-- FOEIGN KEY 제약조건이 설정되어있어도
-- 참조되고 있지 않는 값에 대해서는 삭제 가능
DELETE
FROM DEPARTMENT D
WHERE D.DEPT_ID = 'D3';
-- 1 행 이(가) 삭제되었습니다.
-- 삭제 된 결과 조회하기
SELECT
D.*
FROM DEPARTMENT D;
ROLLBACK;
-- 롤백 완료.
TRUNCATE
테이블의 전체 행을 삭제 시 사용한다. DELETE문 보다 수행 속도가 빠르지만, ROLLBACK을 통해 복구를 할 수 없다.
또한, DELETE와 마찬가지로 FOREIGN KEY 제약조건일 때는 적용 불가능하기 때문에 제약조건을 비활성화 해야 삭제를 할 수 있다.
--TRUNCATE : 테이블의 전체행을 삭제할 시 사용한다.
-- DELETE 보다 수행속도가 더 빠르다.
-- ROLLBACK을 통해 복수할 수 없다.
-- DDL의 종류에 해당한다.
SELECT
ES.*
FROM EMP_SALARY ES;
COMMIT;
DELETE
FROM EMP_SALARY ES;
SELECT
ES.*
FROM EMP_SALARY ES;
-- 롤백완료
ROLLBACK;
-- 복구된결과 조회
SELECT
ES.*
FROM EMP_SALARY ES;
-- 테이블 지우기
TRUNCATE TABLE EMP_SALARY;
-- Table EMP_SALARY이(가) 잘렸습니다.
-- 잘린 테이블 확인
SELECT
ES.*
FROM EMP_SALARY ES;
-- 롤백완료
ROLLBACK;
-- 롤백을 해도 행들이 돌아오지않음을 확인
SELECT
ES.*
FROM EMP_SALARY ES;
TCL (Transaction Control Language)
트랜잭션이란한꺼번에 수행되어야 할 최소의 작업 단위를 말한다.
COMMIT과 ROLLBACK이 있다.
COMMIT : 트랜잭션 작업이 정상 완료되면 변경 내용을 영구히 저장
ROLLBACK : 트랜잭션 작업을 취소하고 최근 COMMIT한 시점으로 이동
SAVEPOINT 세이브포인트명 : 현재 트랜잭션 작업 시점에 이름을 정해줌, 하나의 트랜잭션 안에 구역을 나눔
ROLLBACK TO 세이브 포인트명 : 트랜잭션 작업을 취소하고 SAVEPOINT 지점으로 이동
-- TCL (Transaction Control Language)
-- 트랜잭션 제어 언어
-- commit과 rollback이 있다.
-- 트랜잭션이란?
-- 한꺼번에 수행되어야 할 최소의 작업 단위를 말한다.
-- 논리적 작업 단위 (Logical Unit of Work : LUW)
-- 하나의 트랜잭션으로 이루어진 작업은 반드시 한꺼번에 완료 (COMMIT)
-- 되어야 하며, 그렇지 않은 경우에는 한번에 취소 (ROLLBACK) 되어야 함
-- COMMIT : 트랜잭션 작업이 정상 완료되면 변경 내용을 영구히 저장
-- ROLLBACK : 트랜잭션 작업을 취소하고 최근 COMMIT한 시점으로 이동
-- SAVEPOINT 세이브포인트명 : 현재 트랜잭션 작업 시점에 이름을 정해줌
-- 하나의 트랜잭션 안에 구역을 나눔
-- ROLLBACK TO 세이브 포인트명 : 트랜잭션 작업을 취소하고 SAVEPOINT 지점으로 이동
CREATE TABLE TBL_USER(
USENO NUMBER UNIQUE,
ID VARCHAR2(20) PRIMARY KEY,
PASSWORD CHAR(20) NOT NULL
);
-- Table TBL_USER이(가) 생성되었습니다.
INSERT
INTO TBL_USER A
(
A.USENO
, A.ID
, A.PASSWORD
)
VALUES
(
1
, 'test1'
, 'pass1'
);
-- 1 행 이(가) 삽입되었습니다.
INSERT
INTO TBL_USER A
(
A.USENO
, A.ID
, A.PASSWORD
)
VALUES
(
2
, 'test2'
, 'pass2'
);
-- 1 행 이(가) 삽입되었습니다.
INSERT
INTO TBL_USER A
(
A.USENO
, A.ID
, A.PASSWORD
)
VALUES
(
3
, 'test3'
, 'pass3'
);
-- 1 행 이(가) 삽입되었습니다.
COMMIT;
SELECT
UT.*
FROM TBL_USER UT;
INSERT
INTO TBL_USER A
(
A.USENO
, A.ID
, A.PASSWORD
)
VALUES
(
4
, 'test4'
, 'pass4'
);
-- 1 행 이(가) 삽입되었습니다.
SELECT
UT.*
FROM TBL_USER UT;
ROLLBACK;
-- 롤백 완료.
SELECT
UT.*
FROM TBL_USER UT;
SELECT
UT.*
FROM TBL_USER UT;
INSERT
INTO TBL_USER A
(
A.USERNO
, A.ID
, A.PASSWORD
)
VALUES
(
4
, 'test4'
, 'pass4'
);
SAVEPOINT SP1;
INSERT
INTO TBL_USER A
(
A.USERNO
, A.ID
, A.PASSWORD
)
VALUES
(
5
, 'test5'
, 'pass5'
);
SELECT
UT.*
FROM TBL_USER UT;
ROLLBACK TO SP1;
SELECT
UT.*
FROM TBL_USER UT;
ROLLBACK;
SELECT
UT.*
FROM TBL_USER UT;
-- DDL (DATA DEFINITION LANGUAGE)
-- ALTER : 객체를 수정하는 구문
-- 테이블 객체 수정 : ALTER TABLE 테이블명 수정할 내용
-- 컬럼 추가/삭제/변경, 제약조건 추가/삭제/변경
-- 테이블명 변경, 제약조건 이름 변경
-- 컬럼추가
-- 테이블 조회
SELECT
DC.*
FROM DEPT_COPY DC;
-- 내용 변경
ALTER TABLE DEPT_COPY
ADD (LNAME VARCHAR2(20));
-- Table DEPT_COPY이(가) 변경되었습니다.
-- 변경된 테이블 조회
SELECT
DC.*
FROM DEPT_COPY DC;
-- 컬럼 삭제
ALTER TABLE DEPT_COPY
DROP COLUMN LNAME;
-- Table DEPT_COPY이(가) 변경되었습니다.
-- 삭제된 테이블 조회
SELECT
DC.*
FROM DEPT_COPY DC;
-- 컬럼 생성시 DEFAULT 값 지정
-- 20 바이트 컬럼 추가 ADD
ALTER TABLE DEPT_COPY
ADD (CNAME VARCHAR2(20) DEFAULT '한국');
-- Table DEPT_COPY이(가) 변경되었습니다.
-- 추가되어 변경된 테이블 조회
SELECT
DC.*
FROM DEPT_COPY DC;
-- 컬럼에 제약조건 추가
CREATE TABLE DEPT_COPY2
AS
SELECT D.*
FROM DEPARTMENT D;
-- Table DEPT_COPY2이(가) 생성되었습니다.
-- 테이블 조회
SELECT
DC.*
FROM DEPT_COPY2 DC;
-- 테이블 변경
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT PK_DEPT_ID2 PRIMARY KEY(DEPT_ID);
-- Table DEPT_COPY2이(가) 변경되었습니다.
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT UN_DEPT_TITLE2 UNIQUE(DEPT_TITLE);
-- Table DEPT_COPY2이(가) 변경되었습니다.
ALTER TABLE DEPT_COPY2
MODIFY DEPT_TITLE CONSTRAINT NN_LID NOT NULL;
-- Table DEPT_COPY2이(가) 변경되었습니다.
-- 컬럼 자료형 수정
ALTER TABLE DEPT_COPY2
MODIFY DEPT_ID CHAR(3)
MODIFY DEPT_TITLE VARCHAR(30)
MODIFY LOCATION_ID VARCHAR(2);
-- 컬럼의 크기를 줄이는 경우에는
-- 변경하려는 크기를 초과하는 값이 없을 때만 변경할 수 있다.
-- 수정
ALTER TABLE DEPT_COPY2
MODIFY DEPT_TITLE VARCHAR2(10);
-- 오류 보고 -
-- ORA-01441: 일부 값이 너무 커서 열 길이를 줄일 수 없음
-- DEFAULT 값 변경
ALTER TABLE DEPT_COPY
MODIFY CNAME DEFAULT '미국';
INSERT
INTO DEPT_COPY
VALUES
(
'D0'
, '생산부'
, 'L2'
, DEFAULT
);
-- 1 행 이(가) 삽입되었습니다.
-- 테이블 조회
SELECT
DC.*
FROM DEPT_COPY DC;
-- 컬럼 삭제
ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_TITLE;
-- Table DEPT_COPY2이(가) 변경되었습니다.
-- 사라진 테이블 조회
SELECT
DC.*
FROM DEPT_COPY2 DC;
-- 추가 삭제
ALTER TABLE DEPT_COPY2
DROP COLUMN LOCATION_ID;
-- Table DEPT_COPY2이(가) 변경되었습니다.
-- 삭제된 테이블 재조회
SELECT
DC.*
FROM DEPT_COPY2 DC;
-- DEPT_ID 삭제를 시도해보자
ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID;
-- 오류 보고 -
-- ORA-12983: 테이블에 모든 열들을 삭제할 수 없습니다
-- 테이블에 최소 한 개 이상의 컬럼이 남아있어야 하므로
-- 모든 열을 삭제할 수 없음
-- 참조관련 테스트
-- 테이블 생성
CREATE TABLE TB1 (
PK NUMBER PRIMARY KEY,
FK NUMBER REFERENCES TB1,
COL1 NUMBER,
CHECK (PK > 0 AND COL1 >0 )
);
-- Table TB1이(가) 생성되었습니다.
-- 컬럼 삭제 시 참조하고 있는 컬럼이 있다면 삭제를 못한다.
ALTER TABLE TB1
DROP COLUMN PK;
-- 제약조건도 함께 삭제한다면 삭제할 수 있다.
ALTER TABLE TB1
DROP COLUMN PK CASCADE CONSTRAINTS;
--Table TB1이(가) 변경되었습니다.
SELECT
T.*
FROM TB1 T;
-- 컬럼 삭제 : DROP COLUMN 삭제할 컬럼명 또는 DROP (삭제할 컬럼명)
-- 데이터가 기록 되어 있어도 삭제 됨
-- 삭제된 컬럼은 복구가 불가능
-- 테이블에는 최소 한개 이상의 컬럼이 존재해야 함 : 모든 컬럼 삭제 불가능
SELECT
DC.*
FROM DEPT_COPY DC;
ALTER TABLE DEP_COPY
DROP (CNAME);
-- 테이블 조회
SELECT
DC.*
FROM DEPT_COPY DC;
ALTER TABLE DEPT_COPY
DROP (DEPT_TITLE, LOCATION_ID);
-- Table DEPT_COPY이(가) 변경되었습니다.
SELECT
DC.*
FROM DEPT_COPY DC;
ROLLBACK;
SELECT
DC.*
FROM DEPT_COPY DC;
-- 제약조건 삭제
CREATE TABLE CONST_EMP(
ENAME VARCHAR2(20) NOT NULL,
ENO VARCHAR2(15) NOT NULL,
MARRIAGE CHAR(1) DEFAULT 'N',
EID CHAR(3),
EMAIL VARCHAR2(30),
JID CHAR(2),
MID CHAR(3),
DID CHAR(2),
-- 테이블 레벨로 제약조건 설정
CONSTRAINT CK_MARRIAGE CHECK(MARRIAGE IN('Y', 'N')),
CONSTRAINT PK_EID PRIMARY KEY(EID),
CONSTRAINT UN_ENO UNIQUE(ENO),
CONSTRAINT UN_EAMIL UNIQUE(EMAIL),
CONSTRAINT FK_JID FOREIGN KEY(JID) REFERENCES JOB(JOB_CODE) ON DELETE SET NULL,
CONSTRAINT FK_MID FOREIGN KEY(MID) REFERENCES CONST_EMP ON DELETE SET NULL,
CONSTRAINT FK_DID FOREIGN KEY(DID) REFERENCES DEPARTMENT ON DELETE CASCADE
);
-- 제약조건 1개 삭제시
ALTER TABLE CONST_EMP
DROP CONSTRAINT CK_MARRIAGE;
-- 제약조건 여러개 삭제시
ALTER TABLE CONST_EMP
DROP CONSTRAINT FK_DID
DROP CONSTRAINT FK_JID
DROP CONSTRAINT FK_MID;
-- NOT NULL 제약 조건 삭제 시 MODIFY 사용
ALTER TABLE CONST_EMP
MODIFY (ENAME NULL, ENO NULL);
-- 컬럼 이름 변경
CREATE TABLE DEPT_COPY3
AS SELECT * FROM DEPARTMENT;
ALTER TABLE DEPT_COPY3
RENAME COLUMN DEPT_ID TO DEPT_CODE;
SELECT
DC.*
FROM DEPT_COPY3 DC;
-- 제약조건 이름 변경
ALTER TABLE DEPT_COPY3
ADD CONSTRAINT PK_DEPT_CODE3 PRIMARY KEY(DEPT_CODE);
ALTER TABLE DEPT_COPY3
RENAME CONSTRAINT PK_DEPT_CODE3 TO PK_DCODE;
-- 테이블 이름 변경
ALTER TABLE DEPT_COPY3
RENAME TO DEPT_TEST;
SELECT
DC.*
FROM DEPT_COPY3 DC;
SELECT
DT.*
FROM DEPT_TEST DT;
-- 테이블 삭제
DROP TABLE DEPT_TEST CASCADE CONSTRAINTS;
-- DDL (CREAT TABLE) 및 제약 조건
-- DDL (DATA DEFINITION LANGUAGE) 데이터 정의 언어
-- 객체(OBJECT)를 만들고(CREAT), 수정(ALTER)하고, 삭제(DROP)하는 구문
-- 테이블 만들기
-- [표현식] :
-- CREATE TABLE 테이블명 (컬럼명 자료형(크기), 컬럼명 자료형(크기), ...);
CREATE TABLE MEMBER(
MEMBER_ID VARCHAR2(20),
MEMBER_PWD VARCHAR2(20),
MEMBER_NAME VARCHAR2(20)
);
SELECT
M.*
FROM MEMBER M;
-- 컬럼에 주석 달기
-- [표현식]
-- COMMENTS ON CLOUME 테이블명.컬럼명 IS '주석내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
-- 데이터 딕셔너리
-- 현재 이 계정이 가지고 있는 테이블들에 대하여 조회 한다는 뜻
SELECT
UT.*
FROM USER_TABLES UT;
-- 컬럼들에 대하여 조회
SELECT
UTC.*
FROM USER_TAB_COLUMNS UTC
WHERE UTC.TABLE_NAME = 'MEMBER';
오라클의 데이터형
데이터형
설명
CHAR(크기)
고정길이 문자 데이터
VARCHAR2(크기)
가변길이 문자 데이터(최대 2,000 Byte)
NUMBER
숫자 데이터(최대 40자리)
NUMBER(길이)
숫자 데이터로, 길이 지정 가능하다 (최대 38자리)
DATE
날짜 데이터(BC 4712년 1월 1일 ~ AD 4712년 12월 31일)
LONG
가변 길이 문자형 데이터(최대 2GB)
LOB
2GB까지의 가변길이 바이너리 데이터 저장 가능(이미지, 실행파일 등을 저장할 수 있음)
ROWID
DB에 저장되지 않는 행을 식별할 수 있는 고유 값
BFILE
대용량의 바이너리 데이터 저장 가능(최대 4GB)
TIMESTAMP
DATE형의 확장된 형태이다.
INTERVAL YEAR TO MONTH
년과 월을 이용하여 기간을 저장한다.
INTERVAL DAY TO SECONT
일, 시, 분, 초를 이용하여 기간을 저장한다.
컬럼 주석
[ 표현식 ] COMMENT ON CULUMN 테이블명.컬럼명 IS ‘주석내용’ ;
COMMENT ON COLUMN MEMBER.MEMBER_ID IS ‘회원아이디’;
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS ‘비밀번호’;
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS ‘회원이름’;
제약 조건(CONSTRAINTS)
테이블 작성 시 각 컬럼에 대한 기록에 대해 제약조건을 설정할 수 있다.
데이터 무결성 보장이 주 목적이다.
입력 데이터에 문제가 없는지 검사와 데이터의 수정/삭제 가능 여부 검사 등에 사용한다.
제약 조건
설명
NOT NULL
데이터에 NULL을 허용하지 않는다.
UNIQUE
중복된 값을 허용하지 않는다.
PRIMARY KEY
NULL을 허용하지 않고, 중복 값을 허용하지 않는다. 컬럼의 고유 식별자로 사용하기 위함이다.
FOREIGN KEY
참조되는 테이블의 컬럼의 값이 존재하면 허용한다.
CHECK
저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용한다.
-- 제약조건
-- 테이블 작성 시 각 컬럼에 대해 값 기록에 대한 제약조건을 설정할 수 있다.
-- '이 테이블에는 어떠한 조건만 들어와야 됩니다.'
-- 데이터 무결성 보장을 목적으로 함
-- 입력/수정하는 데이터에 문제가 없는지 자동으로 검사하는 목적
-- 5가지 제약 조건 :
-- PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY
SELECT
UC.*
FROM USER_CONSTRAINTS UC;
SELECT
UCC.*
FROM USER_CONS_COLUMNS UCC;
제약 조건(CONSTRAINTS) 확인
NOT NULL
해당 컬럼에 반드시 값이 기록되어야 하는 경우, 특정 컬럼에 값을 저장하거나 수정할 때 NULL값을 허용하지 않도록 컬럼 레벨에서 제한한다.
NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며, 한 테이블 당 한 개만 설정할 수 있다.
컬럼 레벨과 테이블 레벨에서 둘 다 지정 가능하다.
--PRIMARY KEY(기본키) 제약조건
-- : 테이블에서 한 행의 정보를 찾기위해 사용할 컬럼을 의미한다.
-- 테이블에 대한 식별자 역할을 한다.
-- NOT NULL + UNIQUE 제약조건 의미
-- 한 테이블당 한개만 설정할 수 있음
-- 컬럼 레벨, 테이블 레벨 둘 다에서 설정 가능함
-- 한 개 컬럼에 설정할 수도 있고, 여러개의 컬럼을 묶어서 설정할 수 있음
CREATE TABLE USER_PRIMARYKEY(
USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
INSERT
INTO USER_PRIMARYKEY
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL
)
VALUES
(
1, 'user01', 'pass01'
, '홍길동', '남', '010-1234-5678'
, 'hong123@greedy.com'
);
-- 1 행 이(가) 삽입되었습니다.
-- PK값은 UNIQUE 해야 함, 오류 발생
INSERT
INTO USER_PRIMARYKEY
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL
)
VALUES
(
1, 'user02', 'pass03'
, '유관순', '여', '010-7777-5678'
, 'yoo123@greedy.com'
);
-- 오류 보고 ORA-00001: 무결성 제약 조건(C##EMPLOYEE.PK_USER_NO)에 위배됩니다
-- PK는 NULL값이 존재할 수 없음, 중복된 값을 가지는 것도 안됨
INSERT
INTO USER_PRIMARYKEY
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL
)
VALUES
(
NULL, 'user02', 'pass03'
, '유관순', '여', '010-7777-5678'
, 'yoo123@greedy.com'
);
-- 오류 보고 -
-- ORA-01400: NULL을 ("C##EMPLOYEE"."USER_PRIMARYKEY"."USER_NO") 안에 삽입할 수 없습니다
-- 제약조건 관련 테이블에서 조회하기
SELECT
UC.TABLE_NAME
, UCC.COLUMN_NAME
, UC.CONSTRAINT_NAME
, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.CONSTRAINT_NAME = 'PK_USER_NO';
** FOREIGN KEY 제약조건으로 USER_GRADE TABLE의 GRADE_CODE 컬럼을 참조한다. ** USER_GRADE 테이블을 USER_FOREIGNKEY 테이블에서 참조하는 관계이기 때문에 USER_GRADE 테이블의 데이터 삭제 시 참조무결성에 위배되기 때문에 삭제가 불가능하다.
-- FOREIGN KEY (외부키 / 외래키) 제약 조건
-- 참조(REFERENCE) 된 다른 테이블에서 제공하는 값만 사용할 수 있음
-- 참조 무결성을 위배하지 않게 하기 위해서 사용
-- FOREIGN KEY 제약 조건에 의해서 테이블 간의 관계가 형성 됨
-- 제공되는 값 외에는 NULL을 사용할 수 없음
-- 컬럼 레벨일 경우
-- 컬럼명 자료형(크기) [CONSTRAINT 이름] REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]
-- 테이블 레벨일 경우
-- [CONSTRAINT 이름] FOREIGN KEY (적용할 컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]
-- 참조할 컬럼을 생략시 프라이머리 키를 참조한다.
-- 참조할 테이블의 참조할 컬럼명이 생략 되면
-- PRIMARY KEY 로 설정 된 컬럼이 자동 참조할 컬럼이 됨
-- 참조될 수 있는 컬럼은 PRIMARY KEY 컬럼과,
-- UNIQUE 로 지정된 컬럼만 외래키로 참조할 수 있음
CREATE TABLE USER_GRADE(
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT
INTO USER_GRADE
(
GRADE_CODE
, GRADE_NAME
)
VALUES
(
10
, '일반회원'
);
INSERT
INTO USER_GRADE
(
GRADE_CODE
, GRADE_NAME
)
VALUES
(
20
, '우수회원'
);
INSERT
INTO USER_GRADE
(
GRADE_CODE
, GRADE_NAME
)
VALUES
(
30
, '특별회원'
);
SELECT
UG.*
FROM USER_GRADE UG;
-- 테이블이 생성되었음을 확인할 수 있다.
-- USER_FOREIGNKEY 테이블에서
-- 회원 아이디, 이름, 성별, 연락처, 회원 등급명 조회
SELECT
UF.USER_ID
, UF.USER_NAME
, UF. GENDER
, UF.PHONE
, UG. GRADE_NAME
FROM USER_FOREIGNKEY UF
LEFT JOIN USER_GRADE UG ON (UF.GRADE_CODE = UG.GRADE_CODE);
삭제 옵션
부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떠한 방식으로 처리할 지에 대한 내용을 제약조건 설정 시 옵션으로 지정할 수 있다.
기본 삭제 옵션은 ON DELETE RESTRICTED로 지정되어 있다.
부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 NULL로 변경된다.
CREATE TABLE USER_FOREIGNKEY(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER REFERENCES USER_GRADE(GRADE_CODE) ON DELETE SET NULL
);
DELETE
FROM USER_GRADE
WHERE GRADE_CODE = 10;
부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 존재하던 행 전체를 삭제한다.
CREATE TABLE USER_FOREIGNKEY(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER REFERENCES USER_GRADE(GRADE_CODE) ON DELETE CASCADE
);
DELETE
FROM USER_GRADE
WHERE GRADE_CODE = 10;
-- 만약 참조하고있는 테이블의 값이 삭제되는 일이 발생하면 어떻게 될까?
-- 삭제옵션
-- : 부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를
-- 어떤 식으로 처리할 것인지에 대한 내용을 설정할 수 있다.
-- 특정행을 선택하기 위해서 WHERE 사용
DELETE
FROM USER_GRADE
WHERE GRADE_CODE = 10;
-- ON DELETE RESTRICT 로 기본 지정되어있음
-- 삭제 룰이 별도로 지정되어있지않다면 삭제할 수 없다.
-- 오류 보고 -
-- ORA-02292: 무결성 제약조건(C##EMPLOYEE.FK_GRADE_CODE)이 위배되었습니다- 자식 레코드가 발견되었습니다
-- 제공하는 컬럼의 값은 삭제하지 못한다.
COMMIT;
-- 커밋 완료. 이 지점을 기억하기 위함이다.
DELETE
FROM USER_GRADE
WHERE GRADE_CODE = 20;
-- 출력 : 1 행 이(가) 삭제되었습니다.
SELECT
UG.*
FROM USER_GRADE UG;
-- 20은 아무것도 참조하고 있지않으므로 자식레코드가 발견되지않는다. 곧 삭제 가능하다.
ROLLBACK;
-- COMMIT한 순간으로 돌아간다. 즉, 20이 삭제되기 이전으로 돌아간다.
롤백 이후 재 실행시 나타나는 결과
-- 두번째 삭제옵션 확인
-- ON DELETE SET NULL : 부모키를 삭제 시 자식키를 NULL로 변경하는 옵션
CREATE TABLE USER_GRADE2(
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT
INTO USER_GRADE2
(
GRADE_CODE
, GRADE_NAME
)
VALUES
(
10
, '일반회원'
);
INSERT
INTO USER_GRADE2
(
GRADE_CODE
, GRADE_NAME
)
VALUES
(
20
, '우수회원'
);
INSERT
INTO USER_GRADE2
(
GRADE_CODE
, GRADE_NAME
)
VALUES
(
30
, '특별회원'
);
SELECT
UG.*
FROM USER_GRADE UG;
-- 테이블이 생성되었음을 확인할 수 있다.
-- 위 테이블을 대상으로 참조할 테이블을 만들어 보자
-- 삭제룰을 넣어보자.
CREATE TABLE USER_FOREIGNKEY2(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE2 FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE2(GRADE_CODE) ON DELETE SET NULL
);
-- Table USER_FOREIGNKEY2이(가) 생성되었습니다.
-- 데이터1
INSERT
INTO USER_FOREIGNKEY2
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
1, 'user01', 'pass01'
, '홍길동', '남', '010-1234-5678'
, 'hong123@greedy.com', 10
);
-- 데이터2
INSERT
INTO USER_FOREIGNKEY2
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
2, 'user02', 'pass02'
, '유관순', '여', '010-7777-5678'
, 'yoo123@greedy.com', 10
);
-- 데이터 3
INSERT
INTO USER_FOREIGNKEY2
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
3, 'user03', 'pass03'
, '이순신', '남', '010-9999-5678'
, 'lee123@greedy.com', 30
);
-- 데이터4
INSERT
INTO USER_FOREIGNKEY2
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
4, 'user04', 'pass04'
, '신사임당', '여', '010-8888-5678'
, 'shin123@greedy.com', NULL
);
-- 데이터5
INSERT
INTO USER_FOREIGNKEY2
(
USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
5, 'user05', 'pass05'
, '윤봉길', '남', '010-1111-5678'
, 'yoon123@greedy.com', 50
);
-- 위의 4개는 잘 삽입되었으나 5번 데이터의 50은 오류 보고 발생 -
-- ORA-02291: 무결성 제약조건(C##EMPLOYEE.FK_GRADE_CODE)이 위배되었습니다- 부모 키가 없습니다
COMMIT;
-- 커밋 완료.
DELETE
FROM USER_GRADE2
WHERE GRADE_CODE = 10;
-- 1 행 이(가) 삭제되었습니다.
SELECT
UG.*
FROM USER_GRADE2 UG;
SELECT
UF.*
FROM USER_FOREIGNKEY2 UF;
-- 30을 제외하고 모두 NULL로 변환된 모습이다.
-- 회원가입용 테이블 생성 (USER_TEST)
-- 컬럼명 : USER_NO(회원번호)
-- USER_ID(회원 아이디) -- 중복금지, NULL값 허용안함
-- USER_PWD(회원 비밀번호) --NULL값 허용안함
-- PNO (주민등록번호) -- 중복급지, NULL값 허용안함
-- GENDER(성별) -- 남 또는 여로 입력
-- PHONE(연락처)
-- ADDRESS(주소)
-- STATUS (탈퇴여부) -- NOT NULL, 'Y' 혹은 'N' 입력
-- 각 제약조건 이름부여
-- 5명 이상 회원 정보 INSERT
-- 각 컬럼별로 코멘트 생성
CREATE TABLE USER_TEST(
USER_NO NUMBER,
USER_ID VARCHAR2(20) CONSTRAINT NN_USER_ID NOT NULL,
USER_PWD VARCHAR2(20) CONSTRAINT NN_USER_PWD NOT NULL,
PNO VARCHAR2(20) CONSTRAINT NN_PNO NOT NULL,
GENDER VARCHAR2(3),
PHONE VARCHAR2(20),
ADDRESS VARCHAR2(100),
STATUS VARCHAR2(3) CONSTRAINT NN_STATUS NOT NULL,
CONSTRAINT UK_USER_ID UNIQUE(USER_ID),
CONSTRAINT UK_PNO UNIQUE (PNO),
CONSTRAINT CK_GENDER CHECK(GENDER IN ('남', '여')),
CONSTRAINT CK_STATUS CHECK(STATUS IN ('Y', 'N'))
);
COMMENT ON COLUMN USER_TEST.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_TEST.USER_ID IS '회원아이디';
COMMENT ON COLUMN USER_TEST.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_TEST.PNO IS '주민등록번호';
COMMENT ON COLUMN USER_TEST.GENDER IS '성별';
COMMENT ON COLUMN USER_TEST.PHONE IS '연락처';
COMMENT ON COLUMN USER_TEST.ADDRESS IS '주소';
COMMENT ON COLUMN USER_TEST.STATUS IS '탈퇴여부';
INSERT
INTO USER_TEST
(
USER_NO, USER_ID, USER_PWD
, PNO, GENDER, PHONE
, ADDRESS, STATUS
)
VALUES
(
1, 'user01', 'pass01'
, '881122-1234567', '여', '010-1234-5678'
, '서울시 강남구 역삼동', 'N'
);
INSERT
INTO USER_TEST
(
USER_NO, USER_ID, USER_PWD
, PNO, GENDER, PHONE
, ADDRESS, STATUS
)
VALUES
(
2, 'user02', 'pass02'
, '891122-1234567', '남', '010-1234-5679'
, '서울시 강남구 역삼동', 'N'
);
INSERT
INTO USER_TEST
(
USER_NO, USER_ID, USER_PWD
, PNO, GENDER, PHONE
, ADDRESS, STATUS
)
VALUES
(
3, 'user03', 'pass03'
, '901122-1234567', '여', '010-1234-5670'
, '서울시 강남구 역삼동', 'Y'
);
INSERT
INTO USER_TEST
(
USER_NO, USER_ID, USER_PWD
, PNO, GENDER, PHONE
, ADDRESS, STATUS
)
VALUES
(
4, 'user04', 'pass04'
, '911122-1234567', '남', '010-1234-5671'
, '서울시 강남구 역삼동', 'N'
);
INSERT
INTO USER_TEST
(
USER_NO, USER_ID, USER_PWD
, PNO, GENDER, PHONE
, ADDRESS, STATUS
)
VALUES
(
5, 'user05', 'pass05'
, '921122-1234567', '여', '010-1234-5672'
, '서울시 강남구 역삼동', 'N'
);
SELECT
UT.*
FROM USER_TEST UT;
++ 추가적으로 알고있으면 좋은 삭제
-- 서브쿼리를 이용한 테이블 생성
-- 컬럼명, 데이터 타입, 값이 복사되고, 제약 조건은 NOT NULL만 복사됨
-- EMPLOYEE_COPY 복사본
CREATE TABLE EMPLOYEE_COPY
AS
SELECT
E.*
FROM EMPLOYEE E;
-- EMPLOYEE 테이블의 전체행을 조회
SELECT
EC.*
FROM EMPLOYEE_COPY EC;
-- 새로 생성
CREATE TABLE EMPLOYEE_COPY2
AS
SELECT
E.EMP_ID
, E.EMP_NAME
, E.SALARY
, D.DEPT_TITLE
, J.JOB_NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);
-- Table EMPLOYEE_COPY2이(가) 생성되었습니다.
SELECT
EC.*
FROM EMPLOYEE_COPY2 EC;
-- 제약 조건 추가
-- ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명);
-- ALTER TABLE 테이블명 ADD FOREIGN KEY (컬럼명) REFERENCES 테이블명 (컬럼명);
-- ALTER TABLE 테이블명 ADD UNIQUE (컬럼명);
-- ALTER TABLE 테이블명 ADD CHECK (컬럼명 비교연산자 비교값);
-- ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;
ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY (EMP_ID);
-- 실습
-- EMPLOYEE 테이블의 DEPT_CODE에 외래키 제약조건 추가
-- 참조 테이블은 DEPARTMENT, 참조컬럼은 DEPARTMENT의 기본키
-- DEPARTMENT 테이블의 LOCATION_ID에 외래키 제약조건 추가
-- 참조 테이블은 LOCATION, 참조 컬럼은 LOCATION의 기본키
-- EMPLOYEE 테이블의 JOB_CODE에 외래키 제약조건 추가
-- 참조 테이블은 JOB 테이블, 참조 컬럼은 JOB테이블의 기본키
-- EMPLOYEE 테이블의 SAL_LEVEL에 외래키 제약조건 추가
-- 참조테이블은 SAL_GRADE테이블, 참조 컬럼은 SAL_GRADE테이블 기본키
-- EMPLOYEE테이블의 ENT_YN컬럼에 CHECK제약조건 추가('Y','N')
-- 단, 대 소문자를 구분하기 때문에 대문자로 설정
-- EMPLOYEE테이블의 SALARY 컬럼에 CHECK제약조건 추가(양수)
-- EMPLOYEE테이블의 EMP_NO컬럼에 UNIQUE 제약조건 추가
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT (DEPT_ID);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION (LOCAL_CODE);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (SAL_LEVEL) REFERENCES SAL_GRADE (SAL_LEVEL);
ALTER TABLE EMPLOYEE ADD CHECK (ENT_YN IN ('Y', 'N'));
ALTER TABLE EMPLOYEE ADD CHECK (SALARY > 0);
ALTER TABLE EMPLOYEE ADD UNIQUE (EMP_NO);
서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행되며 비교연산자의 오른쪽에 기술해야 하고, 반드시 괄호로 묶어야 한다.
또한 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 한다.
SUBQUERY 예제
전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의 사번, 이름, 직급코드, 급여를 조회하세요.
-- subquery
-- 부서 코드가 노옹철 사원과 같은 소속의 직원 명단 조회
-- 사원명이 노옹철인 사람의 부서 조회
SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철';
-- 부서코드가 D9인 직원을 조회
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';
-- 위의 두 쿼리를 하나로
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철');
-- 전 직원의 평균급여보다 많은 급여를 받고있는 직원의
-- 사번, 이름, 직급코드, 급여를 조회하세요.
SELECT
AVG(SALARY)
FROM EMPLOYEE;
-- 서브쿼리 작성
SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY >= (SELECT
AVG(SALARY)
FROM EMPLOYEE);
SUBQUERY의 유형
1. 단일행 서브쿼리 - 서브쿼리의 조회 결과 값의 개수가 1개 일 때
2. 다중행 서브쿼리 - 서브쿼리의 조회 결과 값의 행이 여러 개 일 때
3. 다중열 서브쿼리 - 서브쿼리의 조회 결과 컬럼의 개수가 여러 개 일 때
4. 다중행 다중열 서브쿼리 - 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 때
5. 상(호연)관 서브쿼리 - 서브쿼리가 만든 결과값을 메인 쿼리가 비교 연산할 때, 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀐다.
6. 스칼라 서브쿼리 - 상관쿼리 이면서 결과값이 한 개인 서브쿼리
-- 서브쿼리의 유형
-- 단일행 서브쿼리 : 서브쿼리의 조회 결과 값이 1개 행일때
-- 다중행 서브쿼리 : 서브쿼리의 조회 결과 값의 행이 여러개일때
-- 다중열 서브쿼리 : 서브쿼리의 조회 결과 값의 컬럼이 여러개일때
-- 다중행 다중열 서브쿼리 : 조회 결과 행 수 와 열 수가 여러개일때
-- 서브쿼리의 유형에 따라 서브쿼리 앞에 붙는 연산자가 다름
-- 단일행 서브쿼리 앞에는 일반 비교 연산자 사용
-- >, <, >=, <=, =, != / ^= / <> (셋다 같지않다의 뜻)
-- (서브쿼리)
-- 노옹철 사원의 급여보다 많이 받는 직원의
-- 사번, 이름, 부서, 직급, 급여를 조회하세요
-- (메인쿼리)
-- 가장 적은 급여를 받는 직원의
-- 사번, 이름, 부서, 직급, 급여를 조회하세요
-- 서브쿼리는 SELECT, FROM, WHERE, HAVING, ORDER BY 절에서
-- 사용할 수 있다.
-- 부서별 급여의 합계 중 가장 큰 부서의 부서명, 급여 합계를 구하세요 (HAVING 사용)
SELECT
D.DEPT_TITLE
, SUM(E.SALARY)
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
GROUP BY D.DEPT_TITLE
HAVING SUM (E.SALARY) = (SELECT MAX (SUM(E2.SALARY))
FROM EMPLOYEE E2
GROUP BY DEPT_CODE
);
전 직원의 급여 평균보다 급여를 많이 받는 직원의 이름, 직급, 부서, 급여 조회
다중행(MULTIPLE ROW) 서브쿼리
부서별 최고 급여를 받는 직원의 이름, 직급 , 부서, 급여 조회
-- 다중행 서브쿼리
-- 다중행 서브쿼리 앞에서는 일반 비교 연산자를 사용할 수 없다.
-- 왜냐면 비교대상은 하나이기 때문에 1:1비교해야하기 때문
-- IN / NOT IN : 여러개의 결과 값 중에서 한 개라도 일치하는 값이 있다면
-- OR 없다면의 의미
-- > ANY / < ANY : 여러개의 결과 값 중에서 한개라도 큰 / 작은 경우
-- 가장 작은 값보다 크냐? / 가장 큰 값보다 작냐?
-- > ALL < ALL : 모든 값보다 큰 / 작은 경우
-- 가장 큰 값보다 크냐? / 가장 작은 값보다 작냐?
-- EXIST / NOT EXIST : 서브 쿼리에만 사용하는 연산자로
-- 값이 존재하냐? / 존재하지 않냐?
-- 부서'별' 최고급여를 받는 직원의 이름, 직급, 부서, 급여를 조회
SELECT
E.DEPT_CODE
, MAX(E.SALARY)
FROM EMPLOYEE E
GROUP BY E.DEPT_CODE;
SELECT
E.EMP_NAME
, E.JOB_CODE
, E.DEPT_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE E.SALARY IN (SELECT MAX(E2.SALARY)
FROM EMPLOYEE E2
GROUP BY E2.DEPT_CODE
);
-- 관리자에 해당하는 직원에 대한 정보와 관리자가 아닌 직원의
-- 정보를 추출하여 조회
-- 사번, 이름, 부서명, 직급, '관리자' AS 구분 / '직원' AS 구분
SELECT
DISTINCT E.MANAGER_ID -- ( DISTINCT : 중복 제거 )
FROM EMPLOYEE E
WHERE E.MANAGER_ID IS NOT NULL; -- NULL 제거
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, D.DEPT_TITLE 부서명
, J.JOB_NAME 직급
, '관리자' AS 구분
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE E.EMP_ID IN (SELECT
DISTINCT E2.MANAGER_ID
FROM EMPLOYEE E2
WHERE E2.MANAGER_ID IS NOT NULL
)
UNION -- 위와 아래 결과를 합쳐준다.
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, D.DEPT_TITLE 부서명
, J.JOB_NAME 직급
, '직원' AS 구분
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE E.EMP_ID NOT IN (SELECT
DISTINCT E2.MANAGER_ID
FROM EMPLOYEE E2
WHERE E2.MANAGER_ID IS NOT NULL
);
-- 이와 같은 결과를 SELECT 절의 SUBQUERY를 이용해서도 만들 수 있다.
-- SELECT 절에서도 서브쿼리를 사용할 수 있다.
SELECT
E.EMP_ID
, E.EMP_NAME
, D.DEPT_TITLE
, CASE
WHEN E.EMP_ID IN (SELECT
DISTINCT E2.MANAGER_ID
FROM EMPLOYEE E2
WHERE E2.MANAGER_ID IS NOT NULL
)
THEN '관리자'
ELSE '직원'
END AS 구분
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);
-- 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는
-- 직원의 사번, 이름, 직급명, 급여를 조회하세요.
-- 서브이자 조건
SELECT
E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE J.JOB_NAME = '과장';
-- 메인
SELECT
E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE J.JOB_NAME = '대리';
-- 합쳐보자
SELECT
E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE J.JOB_NAME = '대리'
AND E.SALARY > ANY (SELECT
E2.SALARY
FROM EMPLOYEE E2
JOIN JOB J2 ON (E2.JOB_CODE = J2.JOB_CODE)
WHERE J2.JOB_NAME = '과장'
);
-- 차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의
-- 사번, 이름, 직급, 급여를 조회하세요
-- 단, > ALL 혹은 < ALL 연산자를 사용
SELECT
E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE J.JOB_NAME = '과장'
AND E.SALARY > ALL (SELECT
E2.SALARY
FROM EMPLOYEE E2
JOIN JOB J2 ON (E2.JOB_CODE = J2.JOB_CODE)
WHERE J2.JOB_NAME = '차장'
);
-- 자기 직급의 평균 급여를 받고있는 직원의
-- 사번, 이름, 직급코드, 급여를 조회하세요
-- 단, 급여와 급여 평균은 만원 단위로 계산 하세요 TRUNC(컬럼명, -5)
SELECT
E.JOB_CODE
, TRUNC(AVG(E.SALARY), -5)
FROM EMPLOYEE E
GROUP BY E.JOB_CODE;
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE E.SALARY IN (SELECT TRUNC (AVG(E2.SALARY), -5)
FROM EMPLOYEE E2
GROUP BY E2.JOB_CODE
);
-- 이 상황에서 SALARY만 비교했기에 논리적으로 오류가 발생한다.
-- 다중 행 다중열 서브쿼리를 이용한 해결
-- 비교하려는 행과 조회되는 행의 개수가 일치해야 한다.
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE (E.JOB_CODE, E.SALARY) IN (SELECT
E2.JOB_CODE
, TRUNC (AVG(E2.SALARY), -5)
FROM EMPLOYEE E2
GROUP BY E2.JOB_CODE
);
다중열 서브쿼리
퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일을 조회하세요.
-- 다중열 서브쿼리
-- 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는
-- 사원의 이름, 직급, 부서, 입사일을 조회
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.JOB_CODE
FROM EMPLOYEE E
WHERE SUBSTR(E.EMP_NO, 8, 1) = 2
AND E.ENT_YN = 'Y';
SELECT
E.EMP_ID
, E.JOB_CODE
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE E.DEPT_CODE = (SELECT E2.DEPT_CODE
FROM EMPLOYEE E2
WHERE SUBSTR(E2.EMP_NO, 8, 1) = 2
AND E2.ENT_YN = 'Y'
)
AND E.JOB_CODE = (SELECT E3.JOB_CODE
FROM EMPLOYEE E3
WHERE SUBSTR(E3.EMP_NO, 8, 1) = 2
AND E3.ENT_YN = 'Y'
)
AND E.EMP_ID != (SELECT E4.EMP_ID
FROM EMPLOYEE E4
WHERE SUBSTR(E4.EMP_NO, 8, 1) = 2
AND E4.ENT_YN = 'Y'
);
-- 다중열 서브쿼리로 변경
SELECT
E.EMP_ID
, E.JOB_CODE
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE (E.DEPT_CODE, E.JOB_CODE) = (SELECT E2.DEPT_CODE
, E2.JOB_CODE
FROM EMPLOYEE E2
WHERE SUBSTR(E2.EMP_NO, 8, 1) = 2
AND E2.ENT_YN = 'Y'
)
AND E.EMP_ID != (SELECT E3.EMP_ID
FROM EMPLOYEE E3
WHERE SUBSTR(E3.EMP_NO, 8, 1) = 2
AND E3.ENT_YN = 'Y'
);
다중행 다중열 서브쿼리
직급별 최소 급여를 받는 직원의 사번, 이름, 직급, 급여 조회
-- FROM 절에서 서브쿼리를 사용할 수 있다 => 테이블 대신 사용
-- 인라인 뷰(INLINE VIEW)라고도 함
-- : 서브쿼리가 만든 결과 집합(RESULT SET)에 대한 출력 화면
SELECT
E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM (SELECT E2.JOB_CODE
, TRUNC(AVG(E2.SALARY), -5) AS JOBAVG
FROM EMPLOYEE E2
GROUP BY E2.JOB_CODE
) V
JOIN EMPLOYEE E ON (V.JOBAVG = E.SALARY AND E.JOB_CODE = V.JOB_CODE)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
ORDER BY J.JOB_NAME;
SELECT
V.EMP_NAME
, V.부서명
, V.직급이름
FROM (SELECT EMP_NAME
, DEPT_TITLE AS 부서명
, JOB_NAME AS 직급이름
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
) V
WHERE V.부서명 = '인사관리부';
SELECT
V.EMP_NAME
, V.부서명
, V.직급이름 -- 별칭을 줬으면 별칭으로 검색해야함
FROM (SELECT EMP_NAME
, DEPT_TITLE AS 부서명
, JOB_NAME AS 직급이름
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
) V
WHERE V.부서명 = '인사관리부';
-- 인라인뷰를 활용한 TOP-N 분석
-- ORDER BY 한 결과에 ROWNUM을 붙임
-- ROWNUM 은 행 번호를 의미함
-- WHERE 절에서 붙여지므로 ORDER BY 한 다음에
-- ROWNUM이 붙게 하려면 서브쿼리 (인라인뷰) 를 사용해야함
SELECT
ROWNUM
, E.EMP_NAME
, E.SALARY
FROM EMPLOYEE E
ORDER BY E.SALARY DESC;
SELECT
ROWNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
WHERE ROWNUM <= 5;
-- 6위부터 10위까지 조회
-- 결과가 나오지 않음. 왜일까?
-- ROWNUM은 WHERE절에 붙는다.
-- WHERE ROWNUM BETWEEN 6 AND 10; 범위를 1 AND 10으로 바꿔주면 동작된다.
-- 오류가 난 행은 지속해서 1번으로 리셋이 되기 때문
SELECT
V2.RNUM
,V2.EMP_NAME
,V2.SALARY
FROM(SELECT
ROWNUM RNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
) V2
WHERE RNUM BETWEEN 6 AND 10;
-- STOPKEY 활용
SELECT
V2.RNUM
,V2.EMP_NAME
,V2.SALARY
FROM(SELECT
ROWNUM RNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
WHERE ROWNUM < 11
) V2
WHERE RNUM BETWEEN 6 AND 10;
-- 급여 평균 3위 안에 드는 부서의
-- 부서 코드와 부서명, 평균 급여를 조회하세요
SELECT
V.DEPT_CODE
, V.DEPT_TITLE
, V.평균급여
FROM (SELECT
E.DEPT_CODE
, D.DEPT_TITLE
, AVG(E.SALARY) 평균급여
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
GROUP BY E.DEPT_CODE, D.DEPT_TITLE
ORDER BY AVG(E.SALARY) DESC
) V
WHERE ROWNUM <= 3;
-- 직원 정보에서 급여를 가장 많이 받는 순으로 이름, 급여, 순위 조회
-- RANK() 함수는 동일한 순위 이후의 등수를 동일한 인원수 만큼 건너뛰고
-- 다음 순위를 계산하는 방식
-- DENSE_RANK() 함수는 중복되는 순위 이후의 등수를 이후 등수로 처리
SELECT
E.EMP_NAME
, E.SALARY
, RANK() OVER(ORDER BY E.SALARY DESC) 순위
FROM EMPLOYEE E;
SELECT
E.EMP_NAME
, E.SALARY
, DENSE_RANK() OVER(ORDER BY E.SALARY DESC) 순위
FROM EMPLOYEE E;
-- TOP 5 조회
SELECT
V.*
FROM ( SELECT
E.EMP_NAME
, E.SALARY
, RANK() OVER(ORDER BY E.SALARY DESC) 순위
FROM EMPLOYEE E
) V
WHERE V.순위 <= 5;
-- 직원 테이블에서 보너스를 포함한 연봉이 높은 5명의
-- 사번, 이름, 부서명, 직급명, 입사일을 조회하세요.
SELECT
V.EMP_ID
, V.EMP_NAME
, V.DEPT_TITLE
, V.JOB_NAME
, V.HIRE_DATE
FROM ( SELECT
E.EMP_ID
, E.EMP_NAME
, D.DEPT_TITLE
, J.JOB_NAME
, E.HIRE_DATE
, E.SALARY
, (E.SALARY + (E.SALARY * NVL(E.BONUS,0))) * 12
, RANK() OVER(ORDER BY((E.SALARY * NVL(E.BONUS,0))) * 12 DESC) 순위
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
) V
WHERE V.순위 < 6;
-- WITH 이름 AS (쿼리문)
-- 서브쿼리에 이름을 붙여주고 사용시 이름을 사용하게 됨
-- 인라인 뷰로 사용 될 서브쿼리에서 이용됨
-- 같은 서브쿼리가 여러번 사용될 경우 중복 작성을 줄일 수 있으며
-- 실행속도도 빨리진다는 장점이 있다.
WITH
TOPN_SAL
AS (SELECT E.EMP_ID
, E.EMP_NAME
, E.SALARY
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
)
SELECT ROWNUM
, T.EMP_NAME
, T.SALARY
FROM TOPN_SAL T;
-- 부서별 급여 합계가 전체 급여의 총 합의 20%보다 많은
-- 부서의 부서명과, 부서별 급여 합계 조회
SELECT
D.DEPT_TITLE
, SUM(E.SALARY)
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
GROUP BY D.DEPT_TITLE
HAVING SUM(E.SALARY) > (SELECT SUM(E2.SALARY) * 0.2
FROM EMPLOYEE E2
);
-- WITH 사용
WITH
TOTAL_SAL
AS (SELECT D.DEPT_TITLE
, SUM(E.SALARY) SSAL
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
GROUP BY D.DEPT_TITLE
)
SELECT DEPT_TITLE
, SSAL
FROM TOTAL_SAL
WHERE SSAL > (SELECT SUM (E2.SALARY) * 0.2
FROM EMPLOYEE E2
);
-- WITH로 서브쿼리 여러개 사용
WITH
SUM_SAL
AS (SELECT SUM(E.SALARY)
FROM EMPLOYEE E
)
, AVG_SAL
AS (SELECT AVG(E2.SALARY)
FROM EMPLOYEE E2
)
SELECT
S.*
FROM SUM_SAL S
UNION
SELECT
A.*
FROM AVG_SAL A;
상(호연)관 서브쿼리
관리자가 있는 사원들 중 관리자의 사번이 EMPLOYEE테이블에 존재하는 직원의 사번, 이름, 소속부서, 관리자사번을 조회하세요.
-- 상[호연]관 서브쿼리
-- 일반적으로 서브쿼리가 만든 결과 값을 메인 쿼리가 비교연산
-- 메인쿼리가 사용하는 테이블의 값을 서브쿼리가 이용해서 결과를 만듦
-- 메인쿼리의 테이블 값이 변경되면, 서브쿼리의 결과값도 바뀌게 됨
-- 관리자 사번이 EMPLOYEE 테이블에 존재하는 직원에 대한 조회
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE EXISTS (SELECT E2.EMP_ID
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
);
스칼라 서브쿼리 - SELECT절
모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회하세요.
-- SELECT 절에서 스칼라 서브쿼리 이용
-- SELECT 절에서 서브쿼리는 무조건 결과값이 하나여야 한다.
-- 모든 사원의 사번, 이름, 관리자 사번, 관리자명을 조회하세요.
SELECT
E.EMP_ID
, E.EMP_NAME
, E.MANAGER_ID
, NVL((SELECT E2.EMP_NAME
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
), '없음')
FROM EMPLOYEE E
ORDER BY 1;
스칼라 서브쿼리 - WHERE절
자신이 속한 직급의 평균 급여보다 많이 받는 직원의 이름, 직급, 급여를 조회하세요.
-- 스칼라 서브쿼리
-- 단일행 서브쿼리 + 상관 쿼리
-- 동일 직급의 급여 평균보다 급여를 많이 받고있는 직원의
-- 사번, 직급 코드, 급여를 조회하세요.
-- WHERE절에서 스칼라 서브쿼리 이용
SELECT
E.EMP_NAME
, E.JOB_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE E.SALARY > (SELECT TRUNC (AVG(E2.SALARY), -5)
FROM EMPLOYEE E2
WHERE E.JOB_CODE = E2.JOB_CODE
);
스칼라 서브쿼리 – ORDER BY절
모든 직원의 사번, 이름, 소속부서를 조회 후 부서명 내림차순으로 정렬하세요.
-- ORDER BY 절에서 스칼라 서브쿼리 이용
-- 모든 직원의 사번, 이름, 소속부서 조회
-- 단, 부서명 내림차순 정렬
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
FROM EMPLOYEE E
ORDER BY (SELECT D.DEPT_TITLE
FROM DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
) DESC NULLS LAST;
-- JOIN
-- : 두 개 이상의 테이블을 하나로 합쳐서 결과를 조회한다.
-- 오라클 전용 구문
-- FROM 절에 ','로 구분하여 합치게 될 테이블 명을 기술하고
-- WHERE절엔 합치기에 사용할 컬럼명을 명시한다.
-- 1. 연결에 사용할 두 컬럼명이 다른 경우
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID; -- 연결기준을 주어야한다.
-- 2. 연결에 사용할 두 컬럼명이 같은 경우
/* EMPLOYEE, JOB 에서 사용하는 컬럼명이 JOB_CODE로 같다.\
해결방법 : 어떤 테이블의 코드인지 분명히 명시 해 주어야 한다. */
SELECT
EMP_ID
, EMP_NAME
, EMPLOYEE.JOB_CODE
, JOB.JOB_NAME
FROM EMPLOYEE
, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE; -- 연결기준을 주어야한다.
-- 테이블명에 별칭 사용
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, J.JOB_NAME
FROM EMPLOYEE E
, JOB J
WHERE E.JOB_CODE = J.JOB_CODE;
2. ANSI 표준 구문
연결에 사용하려는 컬럼명이 같은 경우 USING()을 사용하고, 다른 경우 ON()을 사용한다.
다른 DBMS에서도 통용된다.
두 가지 버전을 다 활용 및 차이점을 숙지 할 줄 알아야 한다.
연결에 사용하려는 컬럼명이 같은 경우 USING( )을 사용하고, 다른 경우 ON( )을 사용한다.
-- ANSI 표준 구문
-- 1. 연결에 사용할 컬럼명이 같은 경우 : USING(컬럼명)을 사용함
SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE); -- JOB코드를 이용해서 JOIN한다
-- 2. 연결에 사용할 컬럼명이 다른 경우 : ON()을 사용함
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID); -- ON( )코드를 이용해서 JOIN한다
-- 컬럼명이 같은 경우에도 ON()을 사용할 수 있다.
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, J.JOB_NAME
FROM EMPLOYEE E
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);
-- 부서 테이블과 지역 테이블을 조인하여 테이블에 있는 모든 데이터를 조회하세요.
-- ANSI 표준
SELECT
*
FROM DEPARTMENT
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE);
-- 오라클 전용
SELECT
*
FROM DEPARTMENT D
, LOCATION L
WHERE D.LOCATION_ID = L.LOCAL_CODE;
INNER JOIN 과 OUTER JOIN
두 개 이상의 테이블을 조인할 때, 일치하는 값이 없는 행은 조인에서 제외된다.
이것을 INNER JOIN이라고 하며, 명시적으로 사용하지 않을 시에는 기본적으로 INNER JOIN 이다.
하지만 일치하지 않은 값 또한 JOIN에 포함시킬 수도 있다.
이것을 OUTER JOIN이라고 하며, 반드시 OUTER JOIN임을 명시해야 한다.
-- 조인은 기본이 EQUAL JOIN 이다. (EQU JOIN이라고도 함)
-- 연결되는 컬럼의 값이 일치하는 행들만 조인된다.
-- 일치하는 값이 없는 행은 조인에서 제외되는 것을 INNER JOIN이라고 한다.
-- JOIN의 기본은 INNER JOIN & EQU JOIN 이다.
INNER JOIN
-- [기본] : INNER JOIN
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
OUTER JOIN의 종류
1. LEFT OUTER JOIN
합치기에 사용한 두 테이블 중에 왼쪽 편에 기술된 테이블의 컬럼 수를 기준으로 JOIN을 할 때 사용한다.
2. RIGHT OUTER JOIN
합치기에 사용한 두 테이블 중에 오른쪽 편에 기술된 테이블의 컬럼 수를 기준으로 JOIN을 할 때 사용한다.
3. FULL OUTER JOIN
합치기에 사용한 두 테이블이 가진 모든 행을 결과에 포함 시킬 경우 사용한다.
-- OUTER JOIN : 두 테이블의 지정하는 컬럼 값이 일치하지 않는 행도
-- 조인에 포함 시킨다. 세가지 종류가 있다.
-- 기본은 INNER조인이기 때문에 반드시 OUTER JOIN은 따로 명시해야 한다.
-- 1. LEFT OUTER JOIN
-- : 합치기에 사용한 두 테이블 중 왼편에 기술 된 테이블의 행의 수를 기준으로 JOIN
-- 2. RIGHT OUTER JOIN
-- : 합치기에 사용한 두 테이블 중 오른편에 기술 된 테이블의 행의 수를 기준으로 JOIN
-- 3. FULL OUTER JOIN
-- : 합치기에 사용한 두 테이블이 가진 모든행을 결과에 포함하여 JOIN
OUTER JOIN
-- 1. LEFT OUTER JOIN
-- 1-1. ANSI 표준
/* EMPLOYEE 기준의 값을 얻을 수 있다. */
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
/* OUTER을 생략한 상태로 동작해도 무방하다. */
-- LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
LEFT OUTER JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
-- 2. RIGHT OUTER JOIN
-- 2-1. ANSI 표준
/* DEPARTMENT 기준의 값을 얻을 수 있다. */
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
/* OUTER을 생략한 상태로 동작해도 무방하다. */
-- LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
RIGHT OUTER JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
-- 3. FULL OUTER JOIN
-- 3-1. ANSI 표준
/* LEFT RIGHT 모든 행이 전부 포함된다. */
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
/* OUTER을 생략한 상태로 동작해도 무방하다. */
-- FULL JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
FULL OUTER JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
오라클 전용 OUTER JOIN
-- 1. LEFT OUTER JOIN
-- 1-2. 오라클 전용 OUTER 구문
/* EMPLOYEE 기준의 값을 얻을 수 있다. */
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);
-- 2. RIGHT OUTER JOIN
-- 2-2. 오라클 전용 OUTER 구문
/* DEPARTMENT 기준의 값을 얻을 수 있다. */
/* EMPLOYEE 기준이었던 (+)을 반대쪽으로 옮겨준다. */
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;
-- 3. FULL OUTER JOIN
-- 3-2. 오라클 전용 OUTER 구문
/* 오라클 전용 구문으로는 FULL OUTER JOIN을 하지 못한다. */
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
/* 오라클 전용에서는 불가능하다. */
-- WHERE DEPT_CODE(+) = DEPT_ID(+);
CROSS JOIN
카테이션곱(Cartensian projuct)라고도 한다.
조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 조인 방법
검색되는데이터 수는 행의 컬럼수 x 행의 컬럼수로 나오게 된다.
-- CROSS JOIN : 카네이션 곱이라고도 한다.
-- 조인 되는 테이블의 각 행들이 모두 매핑된
-- 데이터가 검색되는방법이다.
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;
NON_EQU JOIN
지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식이다.
-- NON EQUAL JOIN(NON EQU JOIN)
-- : 지정한 컬럼의 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행등을
-- 연결하는 방식
-- ANSI 표준
SELECT
EMP_NAME
, SALARY
, E.SAL_LEVEL "EMPLOYEE의 SAL_LEVEL"
, S.SAL_LEVEL "SAL_GRADE의 SAL_LEVEL"
FROM EMPLOYEE E
JOIN SAL_GRADE S ON(SALARY BETWEEN MIN_SAL AND MAX_SAL);
-- 오라클 전용
SELECT
EMP_NAME
, SALARY
, E.SAL_LEVEL "EMPLOYEE의 SAL_LEVEL"
, S.SAL_LEVEL "SAL_GRADE의 SAL_LEVEL"
FROM EMPLOYEE E
, SAL_GRADE S
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;
SELF JOIN
조인은 두 개 이상의 서로 다른 테이블을 연결하기도 하지만, 같은 테이블을 조인하는 경우도 있다. 이러한 경우 자기 자신과 조인을 맺는 것이라 하여 SELF JOIN이라 한다.
-- SELF JOIN : 같은 테이블을 조인하는 경우이다.
-- 즉, 자기 자신과 조인을 맺는 것이다.
-- 오라클 전용
SELECT
E1.EMP_ID
, E1.EMP_NAME 사원이름
, E1.DEPT_CODE
, E1.MANAGER_ID 관리자이름
, E2.EMP_NAME
FROM EMPLOYEE E1
, EMPLOYEE E2
WHERE E1.MANAGER_ID = E2.EMP_ID;
-- 다중 JOIN : N개의 테이블을 조회할 때 사용
-- ANSI 표준
/* 조인 순서가 중요하다. 다중조인, 안시표준일때는 순서를 고려해야한다. */
SELECT
EMP_ID --EMPLOYEE
, EMP_NAME --EMPLOYEE
, DEPT_CODE --EMPLOYEE
, DEPT_TITLE -- DEPARTMENT
, LOCAL_NAME --LOCATION
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE);
-- 오라클 전용
-- 조인 순서는 상관없다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, DEPT_TITLE
, LOCAL_NAME
FROM EMPLOYEE
, DEPARTMENT
, LOCATION
WHERE DEPT_CODE = DEPT_ID
AND LOCATION_ID = LOCAL_CODE;
/* 조건이 3개일경우 연결구문은 2개 작성, 즉 조건과 연결을 잘 고려해야한다. */
-- 직급이 대리이면서 아시아 지역에 근무하는 직원 조회
-- 사번, 이름, 직급명, 부서명, 근무지역명, 급여 조회
-- (조회세에는 모든 컬럼에 테이블 별칭을 사용하는 것이 좋다.)
-- ANSI 표준
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, J.JOB_NAME 직급명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 근무지역명
, E.SALARY 급여조회
FROM EMPLOYEE E
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
WHERE J.JOB_NAME = '대리'
AND L.LOCAL_NAME LIKE 'ASIA%';
-- 오라클 전용
SELECT
E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, D.DEPT_TITLE
, L.LOCAL_NAME
, E.SALARY
FROM EMPLOYEE E
, JOB J
, DEPARTMENT D
, LOCATION L
WHERE E.JOB_CODE = J.JOB_CODE
AND E.DEPT_CODE = D.DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND J.JOB_NAME = '대리'
AND L.LOCAL_NAME LIKE 'ASIA%';
EMPLOYEE 테이블에서 부서코드, 보너스를 지급받는 사원 수를 조회하고 부서코드 순으로 정렬하세요.
-- GROUP BY와 HAVING
-- 숫자대로 실행 순서 눈여겨보기
/*
5 : SELECT 컬럼명 AS 별칭, 계산식, 함수식
1 : FROM 참조할 테이블명
2 : WHERE 컬럼명 | 함수식 비교연산자 비교값
3 : GROUP BY 그룹을 묶을 컬럼명
4 : HAVING 그룹함수식 비교연산자 비교값
6 : ORDER BY 컬럼명 | 별칭 | 컬럼순번 정렬방식 [NULLS FIRST | LAST]
* 진행순서를 생각해봐야 내가 원하는 데이터를 가져오는 부분이 있다.
*/
-- DEPT_CODE 기준으로 그루핑
/* GROUP BY 절 없이는 오류가 발생하는 것을 확인할 수 있다. */
SELECT
COUNT(*)
, DEPT_CODE
FROM EMPLOYEE
GROUP BY DEPT_CODE;
-- DEPT_CODE, JOB_CODE 기준으로 그루핑
/* 부서와 직급 두 가지 기준 */
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE
, JOB_CODE
ORDER BY 1;
-- 직원 테이블에서 부서 코드별 그룹을 지정하여
-- 부서코드, 그룹별 급여의 합계, 그룹별 급여의 평균(정수처리), 인원수
-- 조회하고 부서코드 순으로 오름차순 정렬하세요.
SELECT
DEPT_CODE
, SUM(SALARY) 합계
, FLOOR(AVG(SALARY)) 평균
, COUNT(*) 인원수
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;
-- 직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여
-- 직급 코드 순으로 오름차순 정렬하세요.
SELECT
JOB_CODE
, COUNT(BONUS)
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY JOB_CODE;
-- ( 조건 추가 + )
-- 직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여
-- 직급 코드 순으로 오름차순 정렬하세요.
-- 단, 보너스를 받는 사람이 없는 직급 코드의 경우 RESULT SET 에서 제외한다.
SELECT
JOB_CODE
, COUNT(BONUS)
FROM EMPLOYEE
WHERE BONUS IS NOT NULL -- 보너스가 NULL이 아니어야한다는 조건 추가
GROUP BY JOB_CODE
ORDER BY JOB_CODE;
EMPLOYEE 테이블에서 EMP_NO의 8번째 자리가 1이면 '남’, 2이면 ‘여’로 결과를 조회하고, 성별별 급여의 평균(정수처리), 급여의 합계, 인원수를 조회한 뒤, 인원수로 내림차순 정렬하세요.
-- 직원 테이블에서 주민번호 8번째 자리를 조회하여
-- 1이면 남, 2면 여로 결과 조회하고
-- 성별별 급여 평균 (정수처리), 급여합계, 인원수를 조회한 뒤
-- 인원수로 내림차순 정렬하세요.
SELECT
DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') AS 성별
, FLOOR(AVG(SALARY)) 평균
, SUM(SALARY) 합계
, COUNT(*) 인원수
FROM EMPLOYEE
/* 성별기준정렬 단, 오더바이와는 달리 그룹바이에서는 별칭을 쓸 수 없다.
실행 순서상 아직 실행 되지 않았기 때문이다. 오버바이는 가장 마지막에 실행되므로
별칭 만으로 사용할 수 있다.*/
/* GROUP BY 절에서는 SELECT 절의 별칭 사용 불가 */
GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')
ORDER BY 인원수 DESC;
-- GROUP BY 절 : 같은 값들이 여러 개 기록 된 컬럼을 가지고
-- 같은 값들을 하나의 그룹으로 묶음
-- GROUP BY 컬럼명 | 함수식, ... (여러개를 나열 해 그루핑 할 수 있다.)
-- 그룹으로 묶은 값에 대해서 SELECT 절에서 그룹 함수를 사용한다.
HAVING 절
그룹함수로 값을 구해올 그룹에 대해 조건을 설정할 때는 HAVING절에 기술한다. (WHERE절은 SELECT에 대한 조건이다.)
급여 3000000원 이상인 직원의 그룹별 평균
급여 평균이 3000000원 이상인 그룹에 대한 평균
-- HAVING 절 : 그룹함수로 구해올 그룹에 대해 조건을 설정할 때 사용
-- HAVING 컬럼명 | 함수식 비교연산자 비교값
-- HAVING 과 WHERE의 차이를 알아보자
/* 300만원 이상의 월급을 받는 사원들을 대상으로
부서별 그룹 월급 평균 계산*/
SELECT
DEPT_CODE
, FLOOR(AVG(SALARY)) 평균
FROM EMPLOYEE
WHERE SALARY > 3000000
GROUP BY DEPT_CODE
ORDER BY 1;
/* 모든 직원을 대상으로 부서별 월급 평균을 구한 뒤
평균이 300만원 이상인 부서 조회*/
SELECT
DEPT_CODE
, FLOOR(AVG(SALARY)) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) > 3000000
ORDER BY 1;
-- 급여 합계가 가장 많은 부서의 부서코드와 급여 합계를 구하세요.
/* HAVING은 GROUP BY와 함께간다. DEPT_CODE 내에서 '가장 많은'
이라는 조건을 찾기위해 HAVING 절에서 서브쿼리를 이용한다. */
SELECT
DEPT_CODE
, SUM (SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
);
ROLLUP과 CUBE
그룹별 산출한 결과값의 집계를 계산하는 함수이다.
인자로 전달받은 그룹 중에 가장 먼저 지정한 그룹별 합계와 총 합계를 구한다.
-- 집계 함수
-- ROLLUP 함수 : 그룹별로 중간 집계 처리를 하는 함수
-- GROUP BY 절에서만 사용하는 함수
-- 그룹별로 묶여진 값에 대한 중간 집계와 총 집계를 구할 때 사용한다.
-- 그룹별로 계산 된 결과값들에 대한 총 집계가 자동으로 추가 됨
/* ROLLUP 과 CUBE 의 차이점
: 전달하는 값이 여러개가 될 때 차이점을 확인할 수 있다. */
SELECT
JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(JOB_CODE)
ORDER BY 1;
/* 8행 NULL : 1~7에 묶여진 그룹에 대한 총 집계값 */
-- CUBE : 그룹별 산출 결과를 집계하는 함수
SELECT
JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(JOB_CODE)
ORDER BY 1;
CUBE
그룹으로 지정된 모든 그룹에 대한 합계와 총 합계를 구한다.
ROLLUP과 CUBE
/* ROLLUP 과 CUBE 의 차이 확인 */
-- ROLLUP : 인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별
-- 합계와 총 합계를 구하는 함수
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE, JOB_CODE)
ORDER BY 1;
-- CUBE : 인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별
-- 그룹으로 지정 된 모든 그룹에 대한 집계와 총 합계를 구하는 함수
-- 즉, 전달받은 모든 인자에 대해 중간집계가 들어간다.
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
/* 8행 NULL : 1~7에 묶여진 그룹에 대한 총 집계값 */
GROUPING
ROLLUP이나 CUBE에 의한 집계 산출물이 인자이다.
전달받은 컬럼 집합의 산출물이면 0을 반환하고, 아니면 1을 반환하는 함수이다.
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
, CASE
WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN ‘부서별합계’
WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN ‘직급별합계’
WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 1 THEN ‘총합계’
ELSE ‘그룹별합계’
END AS 구분
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
-- GOUPING 함수 : ROLLUP 이나 CUBE 에 의한 산출물이
-- 인자로 전달받은 컬럼 집합의 산출물이면 0을 반환하고,
-- 아니면 1을 반환하는 함수
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
, GROUPING(DEPT_CODE) "부서별그룹묶인상태"
, GROUPING(JOB_CODE) "직급별그룹묶인상태"
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
/* 위와같이 작성하면 무엇이 그룹별이고 무엇이 부서별인지 알아보기 힘들다.
아래와같이 선택함수를 이용해 고쳐주면 좀 더 쉽게 알아볼 수 있다.*/
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
, CASE
WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별합계'
WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별합계'
WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 0 THEN '그룹별합계'
ELSE '총합계'
END 구분
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
SET OPERATION
여러 개의 SELECT 결과물을 하나의 쿼리로 만드는 연산자이다.
UNION : 합쳐지되 중복영역은 한번만 나옴 UNION ALL : 중복이 제거되지않고 중복 영역이 두번 나옴 INTERSECT : 공통되는 영역만 조회 MINUS : 첫번째 실험 결과에서 두번째 실험결과는 제외
UNION
UNION과 UNION ALL은 여러 개의 쿼리 결과를 하나로 합치는 연산자이다.
그 중 UNION은 중복된 영역을 제외하여 하나로 합치는 연산자이다.
-- SET OPERATION(집합연산)
-- UNION : 여러개의 쿼리 결과를 하나로 합치는 연산자이다.
-- 중복 된 영역을 제외하여 하나로 합친다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION -- 중간에 집합 연산자를 넣는다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
-- 14개 행에서 중복값 2개가 없어져 12개가 산출되는 것을 확인할 수 있다.
UNION ALL
UNION ALL은 UNION과 같은 여러 쿼리 결과물에 대한 합집합을 의미한다.
UNION과의 차이점은 중복된 영역을 모두 포함시키는 연산자라는 점이다.
-- UNION ALL : 여러개의 쿼리를 하나로 합치는 연산자
-- UNION과 차이점은 중복 영역을 모두 포함시킨다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION ALL -- 중간에 집합 연산자를 넣는다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
-- 14개 행에서 중복값 2개를 포함, 두번 조회 되어 14개가 산출되는 것을 확인할 수 있다.
INTERSECT
여러 개의 SELECT 결과에서 공통된 부분만 결과로 추출한다.
즉, 수행결과에 대한 교집합이라고 볼 수 있다.
-- INTERSECT : 여러개의 SELECT 한 결과에서 공통 부분만 결과로 추출
-- 수학에서의 교집합과 비슷하다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT -- 중간에 집합 연산자를 넣는다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
MINUS
선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출한다. 즉, 두 쿼리 결과물의 차집합이라고 볼 수 있다.
-- MINUS : 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을
-- 제외한 나머지 부분만 추출, 수학에서의 차집합과 비슷하다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
MINUS -- 중간에 집합 연산자를 넣는다.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
/* 앞의결과 - 뒤의 결과
앞의결과에서 뒤의 결과가 빠진 것을 알 수 있다. */
GROUPING SETS
그룹별로 처리된 여러 개의 SELECT문을 하나로 합친 결과를 원할 때 사용한다.
SET OPERATOR(집합연산자) 사용한 결과와 동일한 결과를 얻을 수 있다.
-- GOUPING SETS : 그룹별로 처리 된 여러개의 SELECT 문을 하나로
-- 합칠때 사용한다. SET OPERATION과 결과 동일함
SELECT
DEPT_CODE
, JOB_CODE
, MANAGER_ID -- 본인을 관리하는 사원의 사번
, FLOOR(AVG(SALARY))
FROM EMPLOYEE
/* 다양한 그루핑 조합을 넣는다. */
GROUP BY GROUPING SETS ((DEPT_CODE, JOB_CODE, MANAGER_ID)
, (DEPT_CODE, MANAGER_ID)
, (JOB_CODE, MANAGER_ID)
);
하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램이다.
호출하며 값을 전달하면 수행 결과를 리턴하는 방식으로 사용된다.
함수의 유형
-- 그룹함수와 단일행함수
-- 함수(Function) : 컬럼값을 읽어서 계산하 결과를 리턴함
-- 단일행 함수 : 컬럼에 기록된 N개의 값을 읽어서 N개의 결과를 리턴
-- 그룹함수 :
-- SELECT 절에서 단일행 함수와 그룹 함수를 함께 사용 못한다.
--: 결과 행의 갯수가 다르기 때문에
-- 그룹함수 : SUN, AVG, MAX, MIN, COUNT
-- SUM(숫자가 기록된 컬럼명) : 합계를 구하여 리턴
SELECT
SUM(SALARY)
FROM EMPLOYEE;
--MIN(컬럼명) : 컬럼에서 가장 작은 값 리턴
-- 취급하는 자료형은 ANY TYPE
SELECT
MIN(EMAIL)
, MIN (HIRE_DATE)
, MIN (SALARY)
FROM EMPLOYEE;
-- MAX(컬럼명) : 컬럼에서 가장 큰 값 리턴
-- 취급하는 자료형은 ANY TYPE
SELECT
MAX(EMAIL)
, MAX(HIRE_DATE)
, MAX(SALARY)
FROM EMPLOYEE;
문자 처리 함수
구분
입력 값 타입
리턴 값 타입
설명
LENGTH
CHARACTER
NUMBER
문자열의 길이를 반환한다.
LENGTHB
문자열의 바이트 크기를 반환한다.
INSTR
특정 문자의 위치를 반환한다.
INSTRB
특정 문자의 위치 바이트 크기를 반환한다.
LPAD
CHARACTER
문자열을 지정된 숫자만큼의 크기로 설정하고, 지정한 문자를 왼쪽부터 채워서 생성된 문자열을 리턴 한다.
RPAD
문자열을 지정된 숫자만큼의 크기로 설정하고, 지정한 문자를 오른쪽부터 채워서 생성된 문자열을 리턴 한다.
RTRIM
왼쪽부터 지정한 문자를 잘라내고 남은 문자를 리턴한다.
LTRIM
오른쪽부터 지정한 문자를 잘라내고 남은 문자를 리턴한다.
TRIM
왼쪽/오른쪽/양쪽부터 지정한 문자를 잘라내고 남은 문자를 리턴한다.
SUBSTR
지정한 위치에서 지정한 길이만큼 문자를 잘라내어 리턴한다.
SUBSTRB
지정한 위치에서 지정한 바이트만큼 문자를 잘라내어 리턴한다.
LOWER
전달받은 문자/문자열을 소문자로 변환하여 리턴한다.
UPPER
전달받은 문자/문자열을 대문자로 변환하여 리턴한다.
INITCAP
전달받은 문자/문자열의 첫 글자를 대문자로, 나머지 글자는 소문자로 변환하여 리턴한다.
CONCAT
인자로 전달받은 두 개의 문자/문자열을 합쳐서 리턴한다.
REPLACE
전달받은 문자열중에 지정한 문자를 인자로 전달받은 문자로 변환하여 리턴한다.
문자 처리 함수 - LENGTH
주어진 컬럼 값/문자열의 길이(문자 개수)를 반환하는 함수
작성법
리턴 값 타입
파라미터
LENGTH (CHAR | STRING)
NUMBER
CHARACTER 타입의 컬럼 또는 임의의 문자열
문자 처리 함수 - LENGTHB
주어진 컬럼 값 / 문자열의 길이(BYTE)를 반환하는 함수
작성법
리턴 값 타입
파라미터
LENGTHB(CHAR | STRING)
NUMBER
CHARACTER 타입의 컬럼 또는 임의의 문자열
-- 단일행 함수
-- 문자 관련 함수
-- : LENGTH, LENGTHB, SUBSTR, UPPER, LOWER, INSTR...
SELECT
LENGTH('오라클')
, LENGTHB('오라클')
FROM DUAL;
/* LENGTH는 정확히 세단어로 세지만
LENGTHB는 바이트 단위로 센다. 한글자당 3BYTE이기 때문
그렇다면 한글값이 아닌것은 어떨까?
*/
SELECT
LENGTH(EMAIL)
, LENGTHB(EMAIL)
FROM EMPLOYEE;
문자 처리 함수 - INSTR
찾는 문자(열)이 지정한 위치부터 지정한 회수만큼 나타난 시작 위치를 반환
작성법
리턴 값 타입
INSTR(STRING, STR, [POSITION,[OCCURRENCE]]
NUMBER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
STR
찾으려는 문자(열)
POSITION
찾을 위치 시작 값(기본값 1) POSITION > 0 : STRING의 시작부터 끝 방향으로 찾음 POSITION < 0 : STRING의 끝부터 시작 방향으로 찾음
OCCURRENCE
SUBSTRING이 반복될 때의 지정하는 빈도(기본값 1), 음수 사용 불가
ex: EMAIL 컬럼의 문자열 중 “@”의 위치를 구하시오
--INSTR ('문자열' | 컬럼명, '문자', 찾을 위치의 시작값, [빈도])
SELECT
EMAIL
, INSTR(EMAIL, '@', -1) 위치
FROM EMPLOYEE;
/* 이메일속 @ 문자를 뒤에서부터 위치를 찾으려는 함수 */
SELECT INSTR ('AABAACAABBAA', 'B') FROM DUAL;
/* 아무값을 전달하지않으면 가장 처음부터 찾는다. */
SELECT INSTR ('AABAACAABBAA', 'B', 1) FROM DUAL;
/* 1이라는 값을 넣으면 처음부터 시작해서 찾는다. */
SELECT INSTR ('AABAACAABBAA', 'B', -1) FROM DUAL;
/* -1은 뒤에서부터 가장 첫번째 B를 찾았다는 의미 */
SELECT INSTR ('AABAACAABBAA', 'B', -1, 2) FROM DUAL;
/* 시작은 뒤에서 하되 두번째 (빈도) B를 찾으라는 의미 */
-- COUNT(* | 컬럼명) : 행의 갯수를 헤아려서 리턴
-- COUNT ([DISTINCT] 컬럼명) : 중복을 제거한 행 갯수 리턴
-- COUNT(*) : NULL을 포함한 전체 행 갯수 리턴, * = 모든을 의미
-- COUNT(컬럼명) : NULL을 제외한 실제값이 기록된 행 갯수 리턴
SELECT
COUNT(*)
, COUNT (DEPT_CODE)
, COUNT (DISTINCT DEPT_CODE)
FROM EMPLOYEE;
문자 처리 함수 – LPAD/ RPAD
주어진 컬럼 문자열에 임의의 문자열을 왼쪽/ 오른쪽에 덧붙여 길이 N의 문자열을 반환하는 함수
작성법
리턴 값 타입
LPAD (STRING, N, [STR]) RPAD (STRING, N, [STR])
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
N
반환할 문자(열)의 길이(바이트) 원래 STRING 길이보다 작다면 N만큼 잘라서 표시한다.
STR
덧붙이려는 문자(열), 생략 시 공백문자임
--LPAD / RPAD : 주어진 컬럼 문자열에 임의의 문자열을 덧붙여
-- 길이 N의 문자열을 반환하는 함수
/* 오른쪽 정렬, 빈자리는 #으로 채움 */
SELECT
LPAD (EMAIL, 20, '#')
FROM EMPLOYEE;
/* 왼쪽정렬, 빈자리는 #으로 채움 */
SELECT
RPAD (EMAIL, 20, '#')
FROM EMPLOYEE;
/* 자리수가 모자라면 그냥 잘려져서 나온다. */
SELECT
RPAD (EMAIL, 10)
FROM EMPLOYEE;
문자 처리 함수 – LTRIM / RTRIM
주어진 컬럼이나 문자열의 왼쪽 혹은 오른쪽에서 지정한 STR에 포함된모든 문자를 제거한 나머지를 반환한다.
작성법
리턴 값 타입
LTRIM(STRING, STR) RTRIM(STRING, STR)
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
STR
제거하려는 문자(열), 생략하면 공백문자
문자 처리 함수 - LTRIM
문자 처리 함수 - RTRIM
문자 처리 함수 - TRIM
주어진 컬럼이나 문자열의 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지를 반환한다.
작성법
리턴 값 타입
TRIM ( STRING ) TRIM ( CHAR FROM STRING) TRIM ( LEADING | TRAILING | BOTH [CHAR] FROM STRING)
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
CHAR
제거하려는 문자, 생략하면 공백문자
LEADING
TRIM할 CHAR의 위치를 지정한다. 앞(LEADING) / 뒤(TRAILING) / 양쪽(BOTH) 지정 가능 (기본값 양쪽)
문자 처리 함수 - TRIM
-- LTRIM / RTRIM : 주어진 컬럼이나 문자열 왼쪽/오른쪽에서
-- 지정한 문자 혹은 문자열을 제거한 나머지를 반환하는 함수이다.
/* 오른쪽의 문자, 숫자는 제거하지 못한다. */
SELECT LTRIM (' GREEDY') FROM DUAL;
SELECT LTRIM (' GREEDY', ' ') FROM DUAL;
SELECT LTRIM ('000123456', '123') FROM DUAL;
SELECT LTRIM ('123123GREEDY123', '123') FROM DUAL;
SELECT LTRIM ('ACABACGREEDY', 'ABC') FROM DUAL;
/* ABC는 덩어리가 아니고 보이는 즉시 다 제거해준다. */
SELECT LTRIM ('2782GREEDY', '0123456789') FROM DUAL;
/* 문자열중 숫자만 골라서 지우고 싶을떄 다음과같이 작성한다. */
/* 왼쪽의 문자, 숫자는 제거하지 못한다. 오른쪽만 가능 */
SELECT RTRIM ('GREEDY ') FROM DUAL;
SELECT RTRIM ('GREEDY ', ' ') FROM DUAL;
SELECT RTRIM ('123456000', '0') FROM DUAL;
SELECT RTRIM ('GREEDY123123', '123') FROM DUAL;
SELECT RTRIM ('123123GREEDY123', '123') FROM DUAL;
SELECT RTRIM ('GREEDYACABAC', 'ABC') FROM DUAL;
SELECT RTRIM ('GREEDY5782', '0123456789') FROM DUAL;
-- TRIM : 주어진 컬럼이나 문자열의 앞/뒤에 지정한 문자를 제거
SELECT TRIM (' GREEDY ') FROM DUAL;
SELECT TRIM ('Z' FROM 'ZZZGREEDYZZZ') FROM DUAL;
/* LTRIM 처럼 기능 */
SELECT TRIM (LEADING 'Z' FROM 'ZZZGREEDYZZZ') FROM DUAL;
/* RTRIM 처럼 기능 */
SELECT TRIM (TRAILING 'Z' FROM 'ZZZGREEDYZZZ') FROM DUAL;
SELECT TRIM (BOTH 'Z' FROM 'ZZZGREEDYZZZ') FROM DUAL;
문자 처리 함수 – SUBSTR
컬럼이나 문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 리턴하는 함수이다.
작성법
리턴 값 타입
SUBSTR ( STRING, POSITION, [LENGTH] )
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
POSITION
문자열을 잘라낼 위치이다. 양수이면 시작방향에서 지정한 수 만큼 위치를 의미하고, 음수이면 끝 방향에서 지정한 수 만큼의 의치를 의미한다.
LENGTH
반환할 문자의 개수를 의미한다. (생략시 문자열의 끝까지를 의미하고, 음수이면 NULL을 리턴함)
문자 처리 함수 – SUBSTR
-- SUBSTR : 컬럼이나 문자열에서 지정한 위치로부터 지정한 갯수의 문자열을
-- 잘라서 리턴하는 함수이다.
/* 5번째 위치에서 2개의 길이 리턴 */
SELECT SUBSTR('SHOWMETHEMONEY', 5, 2) FROM DUAL;
/* 7번째 위치에서 해당 문자열 끝까지 리턴 */
SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL;
/* THE, 뒤에서 부터 센 8번째 자리에서 3가지 길이를 리턴 */
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3) FROM DUAL;
/* 두번째 위치에서 공백포함한 5가지 길이 리턴 */
SELECT SUBSTR('쇼우 미 더 머니', 2, 5) FROM DUAL;
-- EMPLOYEE 테이블에서 직원들의 주민번호를 조회하여
-- 사원명, 생년, 생월, 생일을 각각 분리하여 조회
-- 단, 컬럼의 별칭은 사원명, 생년, 생월, 생일로 한다.
SELECT
EMP_NAME 사원명
, SUBSTR(EMP_NO, 1, 2) 생년
, SUBSTR(EMP_NO, 3, 2) 생월
, SUBSTR(EMP_NO, 5, 2) 생일
FROM EMPLOYEE;
-- 날짜 데이터에서 사용할 수 있다.
-- 직원들의 입사일에도 입사년도, 입사월, 입사 날짜를 분리하여 조회
SELECT
HIRE_DATE
, SUBSTR(HIRE_DATE, 1, 2) 입사년도
, SUBSTR(HIRE_DATE, 4, 2) 입사월
, SUBSTR(HIRE_DATE, 7, 2) 입사날짜
FROM EMPLOYEE;
-- WHERE절에서도 함수 사용이 가능하다.
-- EMP_NO를 통해 성별을 판단하여 여직원들의 모든 컬럼 정보를 조회하세요
SELECT
*
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';
-- WHERE절에서는 단일행 함수만 사용 가능하다.
/* 그룹함수는 허가되지않습니다라는 오류 발생 */
SELECT
*
FROM EMPLOYEE
WHERE SALARY > AVG(SALARY);
-- 함수 중첩 사용 가능 : 함수 안에서 함수를 사용할 수 있음
-- EMPLOYEE 테이블에서 사원명, 주민번호 조회
-- 단, 주민번호는 생년월일만 보이게 하고, '-' 다음의 값은 '*'로 바꿔서 출력
SELECT
EMP_NAME
, RPAD(SUBSTR(EMP_NO, 1, 7), 14, '*')
FROM EMPLOYEE;
--EMPLOYEE 테이블에서 사원명, 이메일과
-- @ 이후를 제외한 아이디 조회
/* @ 앞까지만 추출하기 위해서 갯수를 @-1 처리함 */
SELECT
EMP_NAME
, EMAIL
, SUBSTR(EMAIL, 1,INSTR(EMAIL,'@')-1)
FROM EMPLOYEE;
-- SUBSTRB : 바이트 단위로 추출하는 함수
SELECT
SUBSTR('ORACLE', 3, 2)
, SUBSTRB('ORACLE', 3, 2)
FROM DUAL;
SELECT
SUBSTR('오라클', 3, 2)
, SUBSTRB('오라클', 4, 6)
FROM DUAL;
문자 처리 함수 – LOWER/UPPER/INITCAP
컬럼의 문자 혹은 문자열을 소문자/대문자/첫 글자만 대문자로 변환하여 리턴하는 함수이다.
작성법
리턴 값 타입
LOWER(STRING) / UPPER(STRING) / INITCAP(STRING)
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
-- LOWER / UPPER / INITCAP : 대소문자 변경해주는 함수
-- LOWER(문자열 | 컬럼) : 소문자로 변경해주는 함수
SELECT
LOWER('WELCOM TO MY WORLD')
FROM DUAL;
-- UPPER(문자열 | 컬럼) : 대문자로 변경해주는 함수
SELECT
UPPER('welcome to my world')
FROM DUAL;
-- INITCAP(문자열 | 컬럼) : 앞 글자만 대문자로 변경해주는 함수
SELECT
INITCAP('welcome to my world')
FROM DUAL;
문자 처리 함수 – CONCAT
컬럼의 문자 혹은 문자열을 두 개 전달 받아 하나로 합친 후 리턴하는 함수이다.
작성법
리턴 값 타입
CONCAT(STRING, STRING)
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
-- CONCAT : 문자열 혹은 컬럼 두 개를 입력 받아
-- 하나로 합친후 리턴
SELECT
CONCAT('가나다라', 'ABCD')
FROM DUAL;
SELECT
'가나다라' || 'ABCD'
FROM DUAL;
문자 처리 함수 – REPLACE
첫 번째 인자의 문자열에서 두번째 인자의 문자열을 세 번째 인자의 문자열로 대체 후 리턴하는 함수이다.
작성법
리턴 값 타입
REPLACE(STRING, STR1, STR2)
CHARACTER
파라미터
설명
STRING
문자 타입 컬럼 또는 문자열
STR1
변경하려고 하는 문자 혹은 문자열
STR2
변경하려고 하는 문자 혹은 문자열
-- REPLACE : 컬럼 혹은 문자열을 입력 받아 변경하고자 하는 문자열을
-- 새롭게 전달하려는 값으로 문자열을 바꾼 후 리턴
SELECT
REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동')
FROM DUAL;
숫자 처리 함수
숫자 처리 함수 - ABS
인자로 전달받은 숫자의 절대값을 구하는 함수이다.
작성법
리턴 값 타입
ABS(NUMBER)
NUMBER
파라미터
설명
NUMBER
숫자 혹은 숫자 데이터 컬럼
-- 숫자 처리 함수 : ABS, MOD, ROUND, FLOOR, TRUNC, CEIL
-- ABS(숫자|숫자로 된 컬럼명) : 절대값을 구하는 함수
SELECT
ABS(-10)
, ABS(10)
FROM DUAL;
숫자 처리 함수 - MOD
인자로 전달받은 숫자를 나누어 나머지를 구하는 함수이다.
작성법
리턴 값 타입
MOD (NUMBER, DIVISION)
NUMBER
파라미터
설명
NUMBER
숫자 혹은 숫자 데이터 컬럼
DIVISION
나눌 수 혹은 나눌 숫자 데이터 컬럼
-- MOD(숫자 | 숫자로 된 컬럼명, 숫자 | 숫자로 된 컬럼명)
-- : 두 수를 나누어서 나머지를 구하는 함수
-- 처음 인자는 나누어지는 수, 두 번째 인자는 나눌 수
SELECT
MOD (10, 5)
, MOD (10, 3)
FROM DUAL;
숫자 처리 함수 - ROUND
인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치로부터 반올림하여 값을 리턴하는 함수이다.
작성법
리턴 값 타입
ROUND(NUMBER) ROUND(NUMBER, POSITION)
NUMBER
파라미터
설명
NUMBER
숫자 혹은 숫자 데이터 컬럼
POSITION
반올림 할 위치 자리
-- ROUND (숫자 | 숫자로 된 컬럼명, [위치])
-- : 반올림해서 리턴하는 함수
SELECT ROUND (123.456) FROM DUAL;
/* 그냥 정수인 123 리턴 */
SELECT ROUND (123.456, 0) FROM DUAL;
SELECT ROUND (123.456, 1) FROM DUAL;
SELECT ROUND (123.456, 2) FROM DUAL;
SELECT ROUND (123.456, -2) FROM DUAL;
숫자 처리 함수 - FLOOR
인자로 전달받은 숫자 혹은 컬럼의 소수점 자리의 수를 버리는 함수이다.
작성법
리턴 값 타입
FLOOR(NUMBER)
NUMBER
파라미터
설명
NUMBER
숫자 혹은 숫자 데이터 컬럼
-- FLOOR (숫자 | 숫자로 된 컬럼명) : 내림처리 하는 함수
/* 위치지정은 없고 그냥 내림으로 버려서 정수를 반환 */
SELECT FLOOR (123.456) FROM DUAL;
SELECT FLOOR (123.678) FROM DUAL;
숫자 처리 함수 - TRUNC
인자로 전달받은 숫자 혹은 컬럼의 지정한 위치부터 소수점 자리의 수를 버리는 함수이다.
작성법
리턴 값 타입
TRUNC(NUMBER, POSITION)
NUMBER
파라미터
설명
NUMBER
숫자 혹은 숫자 데이터 컬럼
POSITION
버림 할 위치 자리
-- TRUNC(숫자 | 숫자로 된 컬럼명, [위치]) : 내림처리(절삭) 함수
SELECT TRUNC(123.456) FROM DUAL;
SELECT TRUNC(123.678) FROM DUAL;
/* 지정된 자릿수 아래는 절삭됨 */
SELECT TRUNC(123.456, 1) FROM DUAL;
SELECT TRUNC(123.456, 2) FROM DUAL;
/* 1의 자리에 해당하는 3이 절삭됨 */
SELECT TRUNC(123.456, -1) FROM DUAL;
숫자 처리 함수 - CEIL
인자로 전달받은 숫자 혹은 컬럼을 올림 계산하여 나온 값을 리턴하는 함수이다.
작성법
리턴 값 타입
CEIL(NUMBER)
NUMBER
파라미터
설명
NUMBER
숫자 혹은 숫자 데이터 컬럼
--CEIL (숫자 | 숫자로 된 컬럼명) : 올림 처리 함수
SELECT CEIL(123.456) FROM DUAL;
SELECT CEIL(123.678) FROM DUAL;
SELECT
ROUND(123.456)
, FLOOR(123.456)
, TRUNC(123.456)
, CEIL(123.456)
FROM DUAL;
숫자 처리 함수 – ROUND/FLOOR/TRUNC/CEIL
날짜 처리 함수
날짜 처리 함수 - SYSDATE
작성법
리턴 값 타입
SYSDATE
DATE
날짜 처리 함수 – MONTHS_BETWEEN
인자로 날짜 두 개를 전달받아, 개월 수의 차이를 숫자 데이터형으로 리턴하는 함수이다.
작성법
리턴 값 타입
MONTHS_BETWEEN(DATE1, DATE2)
NUMBER
파라미터
설명
DATE1
기준이 되는 날짜를 입력한다.
DATE2
개월 수를 구하려는 날짜를 입력한다.
EMPLOYEE테이블에서 사원의 이름, 입사입,근무 개월수를 조회하라.
날짜 처리 함수 – ADD_MONTHS
인자로 전달받은 날짜에 인자로 전달받은 숫자만큼 개월 수를 더하여 특정 날짜를 리턴하는 함수이다.
작성법
리턴 값 타입
ADD_MONTHS(DATE, NUMBER)
DATE
파라미터
설명
DATE
기준이 되는 날짜를 입력한다.
DATE2
더하려는 개월 수를 입력한다.
EMPLOYEE테이블에서 사원의 이름, 입사일, 입사 후 6개월이 된 날짜를 조회하라
-- 날짜 처리 함수 : SYSDATE, MONTHS_BETWEEN, ADD_MONTH, LAST_DAT, EXTRACT
-- SYSDATE : 시스템에 저장 되어 있는 날짜를 반환하는 함수
SELECT SYSDATE FROM DUAL;
-- MONTHS_BETWEEN(날짜, 날짜)
-- : 두 날짜의 개월 수 차이를 숫자로 리턴하는 함수
SELECT
EMP_NAME
, HIRE_DATE
, CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))
FROM EMPLOYEE;
-- ADD_MONTH(날짜, 숫자)
-- 날짜에 숫자만큼 개월 수 더해서 리턴
SELECT
ADD_MONTHS(SYSDATE, 5)
FROM DUAL;
-- EMPLOYEE 테이블에서 사원의 이름, 입사일, 엽시 후 6개월이 되는 날짜 조회
SELECT
EMP_NAME
, HIRE_DATE
, ADD_MONTHS(HIRE_DATE, 6)
FROM EMPLOYEE;
-- EMPLOYEE 테이블에서 근무년수가 20년 이상인 직원의 모든 컬럼 조회
/* 두가지 방법 */
SELECT
*
FROM EMPLOYEE
-- WHERE ADD_MONTHS(HIRE_DATE, 240) <= SYSDATE;
-- WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240;
날짜 처리 함수 – NEXT_DAY
-- NEXT_DAY(기준날짜, 요일(문자|숫자))
-- : 기준날짜에서 구하려는 요일에 가장 가까운 날짜 리턴
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목') FROM DUAL;
/* 일요일 기준으로 목요일은 5번째이다. */
SELECT SYSDATE, NEXT_DAY(SYSDATE, 5) FROM DUAL;
/* 영어로 입력시 지정한 요일이 부적합하다는 오류가 뜬다. */
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL;
/* 영어로 세션 변경후에는 THURSDAY가 잘 작동된다.
변경 구절은 반드시 실행을 해 준후 다음 줄을 테스트 해야 한다.*/
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL;
/* 한국어로 다시 세션을 변경 해 준다. */
ALTER SESSION SET NLS_LANGUAGE = KOREAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
날짜 처리 함수 – LAST_DAY
인자로 전달받은 날짜가 속한 달의 마지막 날짜를 구하여 리턴한다.
작성법
리턴 값 타입
LAST_DAY (DATE)
DATE
파라미터
설명
DATE
기준이 되는 날짜를 입력한다.
EMPLOYEE 테이블에서 사원의 이름,입사일, 입사일의 마지막날을 조회하세요
-- LAST_DAY(날짜) : '해당 월의 마지막 날짜'를 구하여 리턴
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;
EXTRACT(YEAR FROM DATE) EXTRACT(MONTH FROM DATE) EXTRACT(DAY FROM DATE)
DATE
파라미터
설명
DATE
기준이 되는 날짜를 입력한다.
EMPLOYEE테이블에서 사원 이름, 입사 년, 입사 월, 입사 일을 조회하세요
-- EXTRACT : 년, 월, 일 정보를 추출하여 리턴하는 함수
-- EXTRACT (YEAR FROM 날짜) : 년도만 추출
-- EXTRACT (MONTH FROM 날짜) : 월만 추출
-- EXTRACT (DAY FROM 날짜) : 날짜만 추출
SELECT
EXTRACT (YEAR FROM SYSDATE) 년도
, EXTRACT (MONTH FROM SYSDATE) 월
, EXTRACT (DAY FROM SYSDATE) 일
FROM DUAL;
-- EMPLOYEE 테이블에서 사원 이름, 입사년, 입사월, 입사일 조회
SELECT
EMP_NAME 사원이름
, EXTRACT (YEAR FROM HIRE_DATE) 입사년
, EXTRACT (MONTH FROM HIRE_DATE) 입사월
, EXTRACT (DAY FROM HIRE_DATE) 입사일
FROM EMPLOYEE
/* ORDER BY : 오름차순, 기본적으로 오름차순으로 정렬 */
/* ASC : 오름차순 */
/* DESC : 내림차순*/
-- ORDER BY EMP_NAME ASC;
-- ORDER BY EMP_NAME DESC;
-- ORDER BY 사원이름;
/* 1번인 사원이름으로 정렬하고자 할 때 */
-- ORDER BY 1;
/* 같은 입사년이라는 정렬 기준을 가졌어도
그 안에서 번째 기준인 사원이름으로 한번 더 정렬 된다.*/
ORDER BY 입사년 ASC, 사원이름;
-- EMPLOYEE 테이블에서 직원의 이름, 입사일, 근무년수를 조회
-- 단, 근무년수는 [현재년도 - 입사년도]로 조회
/* 단순히 2020-1986 같은 계산이 나온다. */
SELECT
EMP_NAME
, HIRE_DATE
, EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE;
-- MONTH_BETWEEN으로 근무년수 조회
SELECT
EMP_NAME
, HIRE_DATE
, FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) 근무년수
FROM EMPLOYEE;
형변환 함수
형변환 함수 – TO_CHAR
날짜형 데이터를 문자형 데이터로 변환하거나, 숫자데이터를 문자형데이터로 변환하여 리턴한다.
작성법
리턴 값 타입
TO_CHAR(DATE, [FORMAT]) TO_CHAR(NUMBER, [FORMAT])
CHARACTER
파라미터
설명
DATE
문자형으로 변환하려는 날짜형 데이터
NUMBER
문자형으로 변환하려는 숫자형 데이터
FORMAT
문자형으로 변환시 지정할 출력 형식
형식
의미
의미
YYYY
년도 표현(4자리)
YY
년도 표현(2자리)
MM
월을 숫자로 표현
MON
월을 알파벳으로 표현
DAY
요일 표현
DY
요일을 약어로 표현
-- 형변환 함수
-- TO_CHAR(날짜, [포맷]) : 날짜형 데이터를 문자형 데이터로 변경
-- TO_CHAR(숫자, [포맷]) : 숫자형 데이터를 문자형 데이터로 변경
SELECT TO_CHAR(1234) FROM DUAL;
/* 9는 빈자리를 채우지는 않는다. */
SELECT TO_CHAR(1234, '99999') FROM DUAL;
/* 0은 빈자리를 0으로 채워준다. */
SELECT TO_CHAR(1234, '00000') FROM DUAL;
SELECT TO_CHAR(1234, 'L99999') FROM DUAL;
SELECT TO_CHAR(1234, '$99999') FROM DUAL;
SELECT TO_CHAR(1234, '00,000') FROM DUAL;
/* 원래 표현할 수 있는 숫자보다 작다면 잘못된 상황으로 출력된다. */
SELECT TO_CHAR(1234, '999') FROM DUAL;
-- 직원 테이블에서 사원명, 급여 조회
-- 급여는 '\9,000,000' 형식으로 표시하세요
SELECT
EMP_NAME
, TO_CHAR(SALARY, 'L99,999,999')
FROM EMPLOYEE;
-- 날짜 데이터 포맷 적용시에도 TO_CHAR 함수 사용
/* 24시간 체제 */
SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;
/* 12시간 체제, 오전 오후로 구분 */
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;
/* 1-18 */
SELECT TO_CHAR(SYSDATE, 'YYYY-fmMM-DD DAY') FROM DUAL;
/* 01-18 */
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;
/* 영어로 출력 TWENTY TWENTY-TWO, 1 출력
Q는 분기를 의미한다. */
SELECT TO_CHAR(SYSDATE, 'YEAR,Q') FROM DUAL;
SELECT
EMP_NAME
, TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') 입사일
FROM EMPLOYEE;
/* 다음형식은 날짜 형식이 부적합합니다 라는 오류가 출력 */
SELECT
EMP_NAME
, TO_CHAR(HIRE_DATE, 'YYYY년 MM월 DD일') 입사일
FROM EMPLOYEE;
/* 해당값을 쌍따옴표로 감싸면 문자열 처리된다. */
SELECT
EMP_NAME
, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') 입사일
FROM EMPLOYEE;
/* 시간까지 표기하여 상세입사일을 출력한다. */
SELECT
EMP_NAME
, TO_CHAR(HIRE_DATE, 'YYYY/MM/DD HH24:MI:SS') 상세입사일
FROM EMPLOYEE;
-- 오늘 날짜에 대해 년도 4자리, 년도 2자리,
-- 년도 이름으로 출력
/* RR과 YY로 년도를 표현할 수 있다. */
SELECT
TO_CHAR(SYSDATE, 'YYYY')
, TO_CHAR(SYSDATE, 'RRRR')
, TO_CHAR(SYSDATE, 'YY')
, TO_CHAR(SYSDATE, 'RR')
, TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL;
-- RR과 YY의 차이
-- RR은 두자리 년도를 네자리로 바꿀 때
-- 바꿀 년도가 50년 미만이면 2000년을 적용하고
-- 50년 이상히면 1900년을 적용한다.
-- YY는 년도를 바꿀 때 현재 세기(2000년)를 적용한다.
-- 99/01/01이면 YY는 2099년으로, RR은 1999로 해석한다.
/* 2098 */
SELECT
TO_CHAR(TO_DATE('980630', 'YYMMDD'), 'YYYY-MM-DD')
FROM DUAL;
/* 1998 */
SELECT
TO_CHAR(TO_DATE('980630', 'RRMMDD'), 'YYYY-MM-DD')
FROM DUAL;
-- 오늘 날짜에서 월만 출력
SELECT
TO_CHAR(SYSDATE, 'MM')
, TO_CHAR(SYSDATE, 'MONTH')
, TO_CHAR(SYSDATE, 'MON')
/* 로마자로 표기되는 월 RM */
, TO_CHAR(SYSDATE, 'RM')
FROM DUAL;
-- 오늘 날짜에서 일(DAY)만 출력
SELECT
TO_CHAR(SYSDATE, '"1년기준 " DDD "일 째"')
, TO_CHAR(SYSDATE, '"달 기준 " DD "일 째"') -- 1월
, TO_CHAR(SYSDATE, '"주 기준 " D "일 째"')
FROM DUAL;
-- 오늘 날짜에서 분기와 요일 출력 처리
SELECT
TO_CHAR(SYSDATE, 'Q "분기"')
, TO_CHAR(SYSDATE, 'DAY') -- 화요일
, TO_CHAR(SYSDATE, 'DY') -- 화
FROM DUAL;
-- EMPLOYEE 테이블에서 이름, 입사일 조회
-- 입사일 포맷은 '2018년 6월 15일 (수)' 형식으로 출력처리 하세요
SELECT
EMP_NAME
, TO_CHAR(HIRE_DATE, 'RRRR"년" fmMM"월" DD"일" "("DY")"')
FROM EMPLOYEE;
-- 자동 형변환
/* 자동으로 숫자로 형변환 되어 579로 계산 후 출력된다. */
SELECT '123' + '456' FROM DUAL;
-- 숫자로 된 문자열만 가능하다.
/* 숫자 외의 문자를 넣으면 수치가 부적합하다는 에러메세지가 뜬다. */
SELECT '123' + '456A' FROM DUAL;
SELECT
EMP_NAME
, HIRE_DATE
FROM EMPLOYEE
WHERE HIRE_DATE = '90/02/06'; -- 자동형변환
--EMPLOYEE 테이블에서 사번이 홀수인 직원들의 정보 모두 조회
SELECT
*
FROM EMPLOYEE
WHERE MOD(EMP_ID, 2) = 1;
-- 자동형변환이 되지만 그럼에도 TO_DATE가 필요한 경우 - 계산
SELECT
TO_NUMBER('1,000,000', '99,999,999') + TO_NUMBER('500,000', '999,999')
FROM DUAL;
-- 직원 테이블에서 사원 번호가 201인 사원의
-- 이름, 주민번호 앞자리, 주민번호 뒷자리,
-- 주민번호 앞자리와 뒷자리의 합을 조회하세요
-- 단, 자동 형변환 사용하지 않고 조회
SELECT
EMP_NAME
, EMP_NO
, SUBSTR(EMP_NO, 1, 6) --앞부분
, SUBSTR(EMP_NO, 8) -- 뒷부분
, TO_NUMBER(SUBSTR(EMP_NO, 1, 6)) + TO_NUMBER(SUBSTR(EMP_NO, 8)) 결과
FROM EMPLOYEE
WHERE EMP_ID = TO_CHAR(201);
NULL 처리 함수 - NVL
NULL로 되어 있는 컬럼의 값을 지정한 숫자 혹은 문자로 변경하여 리턴한다.
작성법
리턴 값 타입
NVL(P1, P2)
NUMBER CHARACTER
파라미터
설명
P1
NULL데이터를 처리할 컬럼명 혹은 값
P2
NULL값을 변경하고자 하는 값
-- NULL 처리 함수
-- NVL (컬럼명, 컬럼값이 NULL 일때 바꿀 값)
SELECT
EMP_NAME
, BONUS
, NVL (BONUS, 0)
FROM EMPLOYEE;
-- NVL2 (컬럼명, 바꿀값1, 바꿀값2)
-- 해당 컬럼이 값이 있으면 바꿀값1로 변경,
-- 해당 컬럼이 NULL 이면 바꿀값2로 변경
-- 직원 정보에서 보너스 포인트가 NULL인 직원은 0.5로
-- 보너스 포인트가 NULL 이 아닌 경우 0.7로 변경하여 조회
SELECT
EMP_NAME
, BONUS
, NVL2(BONUS, 0.7, 0.5)
FROM EMPLOYEE;
선택 함수 - DECODE
여러 가지 경우에 선택을 할 수 있는 기능을 제공한다.(일치하는 값)
작성법
리턴 값 타입
DECODE(표현식, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, …)
결과
파라미터
설명
표현식
값에 따라 선택을 다르게 할 컬럼 혹은 값 입력
조건
해당 값이 참인지 거짓인지 여부를 판단한다.
결과
해당 조건과 일치하는 경우 결과를 리턴한다.
DEFAULT
모든 조건이 불일치 시 리턴할 값
-- 선택함수
-- 여러가지 경우에 선택할 수 있는 기능을 제공한다.
-- DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2 ...)
SELECT
EMP_ID
, EMP_NAME
, EMP_NO
, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여')
FROM EMPLOYEE;
-- 마지막 인자로 조건값 없이 선택값을 작성하면
-- 아무것도 해당하지 않을 때 마지막에 작성한 선택값을 무조건 선택한다.
SELECT
EMP_ID
, EMP_NAME
, EMP_NO
, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '여')
FROM EMPLOYEE;
CASE WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2 WHEN 조건3 THEN 결과3 ELSE 결과N END
결과
파라미터
설명
조건
해당 값이 참인지 거짓인지 여부를 판단한다.
결과
해당 조건과 일치하는 경우 결과를 리턴한다.
DEFAULT
모든 조건이 불일치 시 리턴할 값
-- CASE
-- WHEN 조건식 THEN 결과값
-- WHEN 조건식 THEN 결과값
-- ELSE 결과값
-- END
SELECT
EMP_NAME
, JOB_CODE
, SALARY
, CASE
WHEN JOB_CODE = 'J7' THEN SALARY * 1.1
WHEN JOB_CODE = 'J6' THEN SALARY * 1.15
WHEN JOB_CODE = 'J5' THEN SALARY * 1.2
ELSE SALARY * 1.05
END AS 인상급여
FROM EMPLOYEE;
-- 사번, 사원명, 급여를 EMPLOYEE 테이블에서 조회하고
-- 급여가 500만원 초과이면 '고급'
-- 300~500 만원 사이이면 '중급'
-- 그 이하는 '초급'으로 출력처리하고 별칭은 '구분'으로 한다.
SELECT
EMP_ID
, EMP_NAME
, SALARY
, CASE
WHEN SALARY > 5000000 THEN '고급'
WHEN SALARY BETWEEN 3000000 AND 5000000 THEN '중급'
ELSE '초급'
END 구분
FROM EMPLOYEE;
그룹 함수 - SUM
해당 컬럼 값들의 총 합을 구하는 함수이다.
EMPLOYEE 테이블에서 남자 사원의 급여 총 합을 계산하세요.
EMPLOYEE 테이블에서 부서코드가 D5인 직원의 보너스 포함 연봉을 계산하세요.
그룹 함수 – AVG
해당 컬럼 값들의 평균을 구하는 함수이다.
EMPLOYEE 테이블에서 전 사원의 보너스 평균을 소수 둘째자리에서 반올림하여 구하세요.
EMPLOYEE 테이블에서 부서코드가 D5인 직원의 보너스 포함 연봉을 계산하세요.
-- AVG (숫자가 기록된 컬럼명) : 평균을 구하여 리턴
SELECT
AVG(SALARY)
FROM EMPLOYEE;
▶ 도구 - 환경 설정 - 환경 - 인코딩(E): UTF-8 설정 ▶ 워크 시트 만드는 방법 : 시스템 계정 우클릭 - SQL 워크시트 열기 클릭 ▶ 주석 처리 : -- (슬래쉬 두번), /**/ 도 가능하다. ▶ SQL은 대소문자를 구분하지않는다. 일반적으로 대문자 작성 ▶ 수업 내용 보존을 위해 파일로 저장해야 한다. ▶ GRANT CONNECT, RESOURCE TO C##EMPLOYEE; → 방금만든 파일에 연결할 수 있는, 리소스를 다양하게 다룰 수 있는 권한을 준다 → 권한을 주는 것은 시스템 계정이 하는 일, 일반계정은 권한 부며 능력이 없다. ▶ 저장 : CTRL + S ▶ 실행 : CTRL + ENTER ▶ DROP USER : 지운다는 의미 ▶ 워크시트에 행번호 표시 : 워크 시트 -> 우클릭 -> 행 번호 토글
▶시스템 계정에 다음과 같이 적는다.
-- 계정 생성 (CREAT USER)
CREAT USER C##EMPLOYEE IDENTIFIED BY EMPLOYEE;
-- 권한부여 (GRANT)
관계형 데이터베이스에서 데이터를 조회하거나 조작하기 위해 사용하는 표준 검색언어이다. 원하는 데이터를 찾는 방법이나 절차를 기술하는 것이 아닌 조건을 기술하여 작성한다.
분류
용도
명령어
DQL (Data Query Language)
데이터 검색
SELECT
DML (Data Manipulation Language)
데이터 조작
INSERT UPDATE DELETE
DDL (Data Definition Language)
데이터 정의
CREATE DROP ALTER
TCL (Transaction Control Language)
트랜젝션 제어
COMMIT ROLLBACK
주요 데이터 타입 (1)
데이터 타입
하위 데이터 타입
설명
NUMBER
숫자
CHARACTER
CHAR
고정길이 문자 (최대 2000 바이트)
VARCHAR2
가변길이 문자 (최대 4000 바이트)
LONG
가변길이 문자 (최대 2 기가 바이트)
DATE
날짜
LOB
CLOB
가변길이 문자 (최대 4 기가 바이트)
BLOB
Binary Data
주요 데이터 타입 (2)
NUMBER [ ( P [, S] ) ]
P : 표현할 수 있는 전체 숫자 자리수(1~38)
S : 소수점 이하 자리수 (-84 ~ -127)
주요 데이터 타입 (3)
CHAR ( SIZE [ Byte | char ] )
SIZE : 포함될 문자(열) 크기
지정한 크기보다 작은 문자(열)이 입력되고 남는 공간은 공백으로 채운다.
데이터는 ‘ ‘를 사용하여 표기하고, 대/소문자를 구분한다.
주요 데이터 타입 (4)
VARCHAR (SIZE [Byte | char ] )
SIZE : 포함될 문자(열) 크기
지정한 크기보다 작은 문자(열)이 입력되고 남는 공간은 없앤다.
데이터는 ‘’를 사용하여 표기하고, 대/소문자를 구분한다.
주요 데이터 타입 (5)
DATE
일자(세기/년/월/일) 및 시간(시/분/초) 정보를 관리
기본적으로 화면에 년/월/일 정보만 표기된다.
날짜의 연산 및 비교가 가능하다.
SELECT
데이터를 조회한 결과를 Result Set이라고 한다.
SELECT 구문에 의해 반환된 행들의 집합을 의미한다.
Result Set은 0개 이상의 행이 포함될 수 있다.
Result Set은 특정한 기준에 의해 정렬될 수 있다.
특정 컬럼이나 특정 행 혹은 특정 행/특정 컬럼을 조회할 수 있으며, 여러 테이블의 특정 행/컬럼을 조회할 수도 있다.
SELECT 기본 작성법
[ 표현식 ]
SELECT
컬럼명
[, 컬럼명, … ]
, 컬럼명
FROM 테이블명
WHERE 조건식;
[ 구문 설명 ]
· SELECT : 조회하고자 하는 컬럼명을 기술한다.
여러 컬럼을 조회하는 경우 컬럼은 쉼표로 구분하고,
마지막 컬럼 다음은 쉼표를 사용하지 않는다.
모든 컬럼을 조회 시 컬럼명 대신 ‘*’기호로 사용가능하다.
조회 결과는 기술한 컬럼명 순으로 표시된다.
· FROM : 조회 대상 컬럼이 포함된 테이블명을 기술한다.
· WHERE : 행을 선택하는 조건을 기술한다.
여러 개의 제한조건을 포함할 수 있으며,
각각의 제한 조건은 논리 연산자로 연결한다.
제한조건을 만족시키는 행들만 Result Set에 포함한다.
SELECT 사용 예시 -기본
직원들의 사번과 이름을 조회하는 SELECT 구문
SELECT * FROM EMPLOYEE; 이렇게 한 줄로 적어도 문제가 없으나
SELECT * FROM EMPLOYEE; 이와 같이 적기로 약속한다.
* : 모든 컬럼이라는 의미
SELECT 사용 예시 -기본
방법 1) 직원들의 모든 정보를 조회하는 SELECT 구문
-- SELECT 기본 문법 및 연산자
-- 모든 행 모든 컬럼 조회
-- EMPLOYEE 테이블에서 모든 정보 조회
SELECT
*
FROM EMPLOYEE;
-- 원하는 컬럼 조회
-- EMPLOYEE테이블의 사번, 이름 조회
SELECT
EMP_ID
, EMP_NAME
FROM EMPLOYEE;
-- 원하는 행 조회
-- EMPLOYEE 테이블에서 부서코드가 D9인 사원 조회
SELECT
*
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';
-- EMPLOYEE 테이블에서 직급 코드가 J1인 사원 조회
SELECT
*
FROM EMPLOYEE
WHERE JOB_CODE = 'J1';
-- 원하는 행과 컬럼 조회
-- EMPLOYEE 테이블에서 급여가 300만원 이상인 사원의
-- 사번, 이름, 부서코드, 급여를 조회하시오
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
방법 2) 직원들의 모든 정보를 조회하는 SELECT 구문
SELECT 사용 예시 – 컬럼 값 산술 연산
컬럼 값에 대해 산술 연산한 결과를 조회할 수 있다.
SELECT 사용 예시 – 컬럼 별칭
‘AS + 원하는 별칭‘ 을 기술하여 컬럼 별칭을 부여할 수 있다.
-- 컬럼에 별칭 짓기
-- AS + 별칭을 기술하여 별칭을 지을 수 있다.
SELECT
EMP_NAME AS 이름
, SALARY * 12 "1년 급여(원)"
-- 숫자와 숫자가 붙어서 생기는 문제는 쌍따옴표로 감싸기
, (SALARY + (SALARY * BONUS)) * 12
, (SALARY + (SALARY * NVL(BONUS, 0))) * 12 AS "총 소득(원)"
FROM EMPLOYEE;
SELECT 사용 예시 – 리터럴
임의로 지정한 문자열을 SELECT 절에 사용하면, 테이블에 존재하는 데이터 처럼 사용할 수 있다.
-- 임의로 지정한 문자열을 SELECT 절에서 사용할 수 있다.
SELECT
EMP_ID
, EMP_NAME
, SALARY
, '원' AS 단위
FROM EMPLOYEE;
SELECT 사용 예시 – DISTICT
컬럼에 포함된 중복 값을 한번씩만 표시하고자 할 때 사용한다
-- DISTINCT 키워드는 중복 된 컬럼 값을 제거하여 조회한다.
SELECT
DISTINCT JOB_CODE
FROM EMPLOYEE;
-- DISTINCT 키워드는 SELECT절에 딱 한번만 쓸 수 있다.
-- 여러개의 컬럼을 묶어서 중복을 제외시킨다.
SELECT
DISTINCT JOB_CODE
, /* DISTINCT */ DEPT_CODE
FROM EMPLOYEE;
SELECT 사용 예시 – WHERE절
검색할 컬럼의 조건을 설정하여 행을 결정한다.
여러 개의 조건 작성 시 AND/OR를 사용할 수 있다.
-- WHERE 절
-- 테이블에서 조건을 만족하는 값을 가진 행을 골라낸다.
-- 여러개의 조건을 만족하는 행을 골라 낼 때 AND 혹은 OR를 사용할 수 있다.
-- 부서코드가 D6이고 급여가 200만원을 초과하여 받은 직원의
-- 이름, 부서코드, 급여 조회
SELECT
EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6'
AND SALARY > 2000000;
-- NULL 값 조회
-- 보너스를 지급받지 않는 사원의
-- 사번, 이름, 급여, 보너스를 조회하세요
SELECT
EMP_ID
, EMP_NAME
, SALARY
, BONUS
FROM EMPLOYEE
WHERE BONUS IS NULL;
-- NULL이 아닌 값 조회
-- 보너스를 지급받는 사원의
-- 사번, 이름, 급여, 보너스를 조회하세요
SELECT
EMP_ID
, EMP_NAME
, SALARY
, BONUS
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
연결 연산자
연결 연산자인 ‘||’를 사용하여 여러 컬럼을 하나의 컬럼인 것처럼 연결하거나, 컬럼과 리터럴을 연결할 수 있다.
컬럼과 컬럼을 연결한 경우
컬럼과 리터럴을 연결한 경우
-- 연결 연산자를 이용해여 여러 컬럼을 하나의 컬럼인 것처럼
-- 연결할 수 있다. (||)
-- 컬럼과 컬럼 연결
SELECT
EMP_ID || EMP_NAME || SALARY
FROM EMPLOYEE;
-- 컬럼과 리터럴 연결
SELECT
EMP_NAME || '의 월급은 ' || SALARY || '원 입니다.'
FROM EMPLOYEE;
논리 연산자
여러 개의 제한 조건 결과를 하나의 논리결과로 만들어준다.
비교 연산자
표현시 사이의 관계를 비교하기 위해 사용하고,
비교 결과는 논리 결과 중에 하나(TRUE/FALSE/NULL)가 된다.
단, 비교하는 두 컬럼 값/표현식은 서로 동일한 데이터 타입이어야 한다.
-- 비교 연산자
-- = 같다, > 크다, < 작다, >= 크거나 같다, <= 작거나 같다.
-- !=, ^=, <> 같지않다. (세가지 버전)
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
FROM EMPLOYEE
-- WHERE DEPT_CODE != 'D9';
-- WHERE DEPT_CODE ^= 'D9';
WHERE DEPT_CODE <> 'D9';
-- EMPLOYEE 테이블에서 퇴사 여부가 N인 직원을 조회하고
-- 근무 여부를 별칭으로 하여 재직중이라는 문자열을 결과집합에 포함하여 조회한다.
-- 사번, 이름, 입사일, 근무여부 조회
SELECT
EMP_ID
, EMP_NAME
, HIRE_DATE
, '재직중' 근무여부
FROM EMPLOYEE
WHERE ENT_YN = 'N';
-- EMPLOYEE 테이블에서 급여를 350만원 이상
-- 550만원 이하를 받는 직원의 사번, 이름, 부서코드, 직급코드를 조회하세요
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, JOB_CODE
FROM EMPLOYEE
WHERE SALARY >= 3500000
AND SALARY <= 5500000;
비교 연산자 – BETWEEN AND
비교하려는 값이 지정한 범위(상한 값과 하한 값의 경계도 포함)에 포함되면 TRUE를 리턴하는 연산자이다.
[ 급여를 350만원보다 많이 받고 600만원보다 적게 받는 직원 이름과 급여 조회 ]
1,2 번은 표현식만 다를 뿐 동일한 결과를 출력한다
-- BETWEEN AND 사용
-- 컬럼명 BETWEEN 하한값 AND 상한값
-- : 하한값 이상, 상한값 이하의 값
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, JOB_CODE
FROM EMPLOYEE
WHERE SALARY BETWEEN 3500000 AND 5500000;
-- 반대로 급여를 350만원 미만, 또는 550만원을 초과하는
-- 직원의 사번, 이름, 부서코드, 급여를 조회하세요.
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, JOB_CODE
FROM EMPLOYEE
-- WHERE /*NOT*/ SALARY NOT BETWEEN 3500000 AND 5500000;
WHERE SALARY /*NOT*/ BETWEEN 3500000 AND 5500000;
-- NOT (1) 컬럼명 앞 (2) 컬럼명 뒤(=BETWEEN 앞)
-- LIKE 연산자 : 문자 패턴이 일치하는 값을 조회할 때 사용
-- 컬럼명 LIKE '문자패턴'
-- 문자 패턴 : '글자%' (글자로 시작하는 값),
-- '%글자%' (글자가 포함된 값),
-- '%글자' (글자로 끝나는 값)
-- EMPLOYEE 테이블에서 성이 김씨인 직원의
-- 사번, 이름, 입사일 조회
SELECT
EMP_ID
, EMP_NAME
, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '김%';
-- EMPLOYEE 테이블에서 성이 김씨가 아닌 직원의
-- 사번, 이름, 입사일 조회
SELECT
EMP_ID
, EMP_NAME
, HIRE_DATE
FROM EMPLOYEE
-- WHERE NOT EMP_NAME LIKE '김%';
WHERE EMP_NAME NOT LIKE '김%';
-- EMPLOYEE 테이블에서 '하'가 이름에 포함된
-- 직원의 이름, 주민번호, 부서코드 조회
SELECT
EMP_NAME
, EMP_NO
, DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';
-- EMPLOYEE 테이블에서 전화번호 국번이 9로 시작하는
-- 직원의 사번, 이름, 전화번호를 조회하세요
-- 와일드 카드 사용 : _(글자 한 자리), %(0개 이상의 글자)
SELECT
EMP_ID
, EMP_NAME
, PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '___9%';
-- EMPLOYEE 테이블에서 전화번호 국번이 4자리이면서
-- 9로 시작하는 직원의 사번, 이름, 전화번호를 조회하세요
SELECT
EMP_ID
, EMP_NAME
, PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '___9_______';
-- EMPLOYEE 테이블에서 _앞글자가 3자리인 이메일 주소를 가진
-- 사원의 사번, 이름, 이메일 주소 조회
SELECT
EMP_ID
, EMP_NAME
, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___#_%' ESCAPE '#';
-- 이씨성이 아닌 직원의 사번, 이름, 이메일 주소 조회
SELECT
EMP_ID
, EMP_NAME
, EMAIL
FROM EMPLOYEE
WHERE EMP_NAME NOT LIKE '이%';
-- 부서코드가 'D6'이거나 'D8'인 직원의
-- 이름, 부서, 급여를 조회하세요
-- IN 연산자 : 비교하는 값 목록에 일치하는 값이 있는지 확인
SELECT
EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE IN('D6', 'D8');
-- 부서코드가 'D6'이거나 'D8'인 직원을 제외한 나머지 직원의
-- 이름, 부서, 급여를 조회하세요
SELECT
EMP_NAME
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE NOT IN('D6', 'D8');
-- 연산자 우선순위
/*
1. 산술연산자
2. 연결연산자
3. 비교연산자
4. IS NULL/IS NOT NULL, LIKE/NOT LIKE, IN/NOT IN
5. BETWEEN AND/NOT BETWEEN AND
6. NOT(논리연산자)
7. AND
8. OR
*/
-- J2 직급의 급여 200만원 이상 받는 직원이거나
-- J7 직급인 직원의 이름, 급여, 직급코드 조회
SELECT
EMP_NAME
, SALARY
, JOB_CODE
FROM EMPLOYEE
WHERE JOB_CODE = 'J7'
OR JOB_CODE = 'J2'
AND SALARY >= 2000000;
-- J7 직급이거나 J2 직급인 직원들 중
-- 급여가 200만원 이상인 직원의
-- 이름, 급여, 직급코드 조회
SELECT
EMP_NAME
, SALARY
, JOB_CODE
FROM EMPLOYEE
WHERE (JOB_CODE = 'J7'
OR JOB_CODE = 'J2')
AND SALARY >= 2000000;
한 조직에 필요한 정보를 여러 응용 시스템에서 공용할 수 있도록 논리적으로 연관된 데이터를 모으고, 중복되는 데이터를 최소화하여 구조적으로 통합/저장해 놓은 것이다.
Database의 정의 1. 운영 데이터(Operational Data) : 조직의 목적을 위해 사용되는 데이터를 의미 2. 공용 데이터(Shared Data) : 공동으로 사용되는 데이터를 의미 3. 통합 데이터(Integrated Data) : 중복을 최소화하여 중복으로 인한 데이터 불일치 현상 제거 4. 저장 데이터(Stored Data) : 컴퓨터 저장 장치에 저장된 데이터를 의미
Database의 특징
1. 실시간 접근성(Real Time Accessibility) - 사용자가 데이터를 요청하면 실시간으로 결과를 서비스한다. 2. 계속적인 변화(Continuous Change) - 데이터 값은 시간에 따라 항상 바뀐다. 3. 동시 공유(Concurrent Sharin) - 데이터베이스는 서로 다른 업무 또는 여러 사용자에게 동시 공유된다. 4. 내용에 따른 참조(Reference By Content) - 데이터베이스에 저장된 데이터는 데이터의 물리적 위치가 아니라 데이터 값에 따라 참조된다.
DBMS(DataBase Management System)란?
- 데이터베이스에서 데이터를 추출,조작,정의,제어 등을 할 수 있게 해주는 데이터베이스 전용 관리 프로그램이다.
DBMS의 기능
데이터 추출 (Retrieval)
사용자가 조회하는 데이터 혹은 응용 프로그램의 데이터를 추출함
데이터 조작 (Manipulation)
데이터를 조작하는 소프트웨어(응용 프로그램)가 요청하는 데이터의 삽입, 수정, 삭제 작업을 지원함
데이터 정의 (Definition)
데이터의 구조를 정의하고 데이터 구조에 대한 삭제 및 변경 기능을 수행함
데이터 제어 (Control)
데이터베이스 사용자를 생성하고 모니터링하며 접근을 제어함. 백업과 회복, 동시성 제어 등의 기능을 지원함
DBMS의 사용 이점
1 데이터 독립화
데이터와 응용 프로그램을 분리시킴으로써 상호 영향 정도를 줄일 수 있다.
2데이터 중복 최소화 데이터 무결성 보장
-중복되는 데이터를 최소화 시키면 데이터 무결성이 손상될 가능성이 줄어든다. -중복되는 데이터를 최소화시키면 필요한 저장공간의 낭비를 줄일 수 있다.
3 데이터 보안 향상
-응용프로그램은 DBMS를 통해 DBMS가 허용하는 데이터에만 접근할 수 있다. -권한에 맞게 데이터 접근을 제한하거나 데이터를 암호화시켜 저장할 수 있다.
4 관리 편의성 향상
-다양한 방법으로 데이터를 백업할 수 있다. -장애 발생 시 데이터를 복구할 수 있다.
Database의 유형
계층형 데이터베이스
트리 형태의 계층적 구조를 가진 데이터베이스 최상위 계층의 데이터부터 검색하는 구조
네트워크형 데이터베이스
하위 데이터끼리의 관계까지 정의할 수 있는 구조를 가진 데이터베이스 설계/구현이 너무 복잡하고 어려움
Database의 유형
관계형 데이터베이스
- 모든 데이터를 2차원 테이블 형태로 표현하고, 테이블 사이의 비즈니스적 관계를 도출하는 구조를 가진 데이터베이스 유형 - 데이터의 중복을 최소화 할 수 있으며, 업무 변화에 대한 적응력이 우수함
Database의 유형 : 객체-관계형 데이터베이스
1. 사용자 정의 타입을 지원한다. - 사용자가 임의로 정한 데이터 유형을 말하며, 기본형 데이터 타입을 뛰어 넘어 다양한 형태의 데이터를 다룰 수 있다. 2. 참조(reference)타입을 지원한다. - 객체들로 이루어진 객체 테이블의 경우, 하나의 레코드가 다른 레코드를 참조할 수 있는 것을 말한다. 3. 중첩 테이블을 지원한다. - 테이블을 구성하는 로우(Row)자체가 또 다른 테이블로 구성되는 테이블을 지원하여 조금 더 복잡하고 복합적인 정보 표현이 가능하다. 4. 대단위 객체의 저장, 추출이 가능하다. - 이미지, 오디오, 비디오 등 을 저장하기 위한 대단위 객체(LOB)를 지원한다. 5. 객체간의 상속관계를 지원한다. - 오라클의 경우 OBJECT 타입을 지원함으로써 상속기능을 구현하고 있다.
