<DDL 명령어>
SQL> CREATE TABLE BUSEO
2 (DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13));
SQL> DESC BUSEO
SQL> ALTER TABLE BUSEO ADD (PHONE VARCHAR2(20)); -- 컬럼추가
SQL> ALTER TABLE BUSEO
2 MODIFY (PHONE VARCHAR2(30)); -- 컬럼 타입 변경
SQL> ALTER TABLE BUSEO
2 RENAME COLUMN PHONE TO TEL; -- 컬럼 이름 변경
SQL> ALTER TABLE BUSEO DROP (TEL); -- 컬럼 삭제
SQL> ALTER TABLE BUSEO SET UNUSED (LOC); -- 컬럼 숨기기
SQL> ALTER TABLE BUSEO
3 DROP UNUSED COLUMNS; -- 숨긴 컬럼 삭제
SQL> DROP TABLE BUSEO; -- 테이블 삭제
SQL> CREATE TABLE BUSEO
2 (DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14));
SQL> INSERT INTO BUSEO VALUES(100, ‘ACCOUNTING’);
SQL> RENAME BUSEO TO DEPARTMENT; -- 테이블명 바꾸기
SQL> TRUNCATE TABLE DEPARTMENT; -- 테이블 잘라내기
SQL> DROP TABLE DEPARTMENT;
<연습문제>
1. 다음과 같은 구조의 테이블을 생성하시오
- 테이블명 : JUSO
- 컬럼 : NO, 타입 : NUMBER(3)
- 컬럼 : NAME, 타입 : VARCHAR2(10)
- 컬럼 : ADDR, 타입 : VARCHAR2(20)
- 컬럼 : EMAIL, 타입 : VARCHAR2(5)
2. 전화번호(PHONE) 컬럼을 VARCHAR2(10) 타입으로 추가하시오.
3. 이메일(EMAIL) 컬럼을 VARCHAR2(20) 타입으로 변경하시오.
4. 주소(ADDR) 컬럼을 삭제하시오.
5. 테이블을 삭제하시오.
<제약조건 – NOT NULL>
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4),
3 S_NAME VARCHAR2(10) NOT NULL,
4 S_HIREDATE DATE
5 CONSTRAINT SAWON_S_HIREDATE_NN NOT NULL);
SQL> INSERT INTO SAWON VALUES(1, '길동', NULL); -- 성공 OR 실패?
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE – 제약조건 확인
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'SAWON';
<제약조건 – UNIQUE>
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4),
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(10),
5 S_EMAIL VARCHAR2(20)
6 CONSTRAINT SAWON_S_EMAIL_UK UNIQUE);
SQL> INSERT INTO SAWON VALUES (1, '길동', 1000, 'GDH@XYZ.COM');
SQL> INSERT INTO SAWON
2 VALUES (2, '콩쥐', 2000, 'GDH@XYZ.COM'); -- ?
SQL> INSERT INTO SAWON VALUES (3, '팥쥐', 1500, NULL);
참고 – 테이블수준에서 제약조건 정의하기
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4),
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(10),
5 S_EMAIL VARCHAR2(20),
6 CONSTRAINT SAWON_S_EMAIL_UK UNIQUE(S_EMAIL));
<제약조건 – PRIMARY KEY>
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4) CONSTRAINT SAWON_S_NO_PK PRIMARY KEY,
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(10));
SQL> INSERT INTO SAWON VALUES (1, '길동', 1000);
SQL> INSERT INTO SAWON VALUES (1, '콩쥐', 2000); -- ?
SQL> INSERT INTO SAWON VALUES (NULL, '팥쥐', 3000); -- ?
참고 – 테이블수준에서 제약조건 정의하기
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4),
3 CONSTRAINT SAWON_S_NO_PK PRIMARY KEY(S_NO),
4 S_NAME VARCHAR2(10),
5 S_SAL NUMBER(10));
<제약조건 – FOREIGN KEY>
SQL> CREATE TABLE BUSEO(
2 B_NO NUMBER(4) CONSTRAINT BUSEO_B_NO_PK PRIMARY KEY,
3 B_NAME VARCHAR2(10),
4 B_LOC VARCHAR2(10));
SQL> INSERT INTO BUSEO VALUES (100, '인사과', '서울');
SQL> INSERT INTO BUSEO VALUES (200, '총무과', '대전');
SQL> INSERT INTO BUSEO VALUES (300, '경리과', '부산');
SQL> CREATE TABLE SAWON(
2 S_NO NUMBER(4) CONSTRAINT SAWON_S_NO_PK PRIMARY KEY,
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(5),
5 B_NO NUMBER(4)
6 CONSTRAINT SAWON_B_NO_FK REFERENCES BUSEO(B_NO));
SQL> INSERT INTO SAWON VALUES (1, '길동', 1000, 100);
SQL> INSERT INTO SAWON VALUES (2, '콩쥐', 2000, 150); -- ?
SQL> INSERT INTO SAWON VALUES (3, '팥쥐', 3000, NULL);
참고 – 테이블수준에서 제약조건 정의하기
SQL> CREATE TABLE SAWON(
2 S_NO NUMBER(4) CONSTRAINT SAWON_S_NO_PK PRIMARY KEY,
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(5),
5 B_NO NUMBER(4),
6 CONSTRAINT SAWON_B_NO_FK FOREIGN KEY (B_NO)
7 REFERENCES BUSEO(B_NO));
<제약조건 – CHECK>
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4),
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(10)
5 CONSTRAINT SAWON_S_SAL_CK CHECK (S_SAL > 0));
SQL> INSERT INTO SAWON VALUES (1, '길동', 1000);
SQL> INSERT INTO SAWON VALUES (2, '콩쥐', -1000); -- ?
참고 – 테이블수준에서 제약조건 정의하기
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4),
3 S_NAME VARCHAR2(10),
4 S_SAL NUMBER(10),
5 CONSTRAINT SAWON_S_SAL_CK CHECK (S_SAL > 0));
<제약조건 추가>
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4) CONSTRAINT SAWON_S_NO_PK PRIMARY KEY,
3 S_NAME VARCHAR2(10),
4 S_MGR NUMBER(4));
SQL> ALTER TABLE SAWON
2 ADD CONSTRAINT SAWON_S_MGR_FK FOREIGN KEY (S_MGR)
3 REFERENCES SAWON(S_NO);
<제약조건 삭제>
SQL> ALTER TABLE SAWON
2 DROP CONSTRAINT SAWON_S_MGR_FK;
<제약조건 비활성화>
SQL> CREATE TABLE SAWON (
2 S_NO NUMBER(4) CONSTRAINT SAWON_S_NO_PK PRIMARY KEY,
3 S_NAME VARCHAR2(10),
4 S_MGR NUMBER(4));
SQL> ALTER TABLE SAWON
2 DISABLE CONSTRAINT SAWON_S_NO_PK;
<제약조건 활성화>
SQL> ALTER TABLE SAWON
2 ENABLE CONSTRAINT SAWON_S_NO_PK;
<연습문제>
다음 ERD를 보고 테이블을 작성하시오. 단, 제약조건은 PRIMARY KEY, FOREIGN KEY만 사용하고, 제약조건에 대하여 이름을 지정하시오.
<DML - INSERT>
SQL> INSERT INTO DEPT(DEPTNO, DNAME, LOC)
2 VALUES(90, '인사과', '서울');
SQL> INSERT INTO DEPT VALUES(91, '홍보과', '대전');
// NULL을 입력하는 두가지 방법
SQL> INSERT INTO DEPT(DEPTNO, DNAME) VALUES(92, '총무과');
SQL> INSERT INTO DEPT VALUES(93, '경리과', NULL);
// 한번에 여러 행 입력
SQL> CREATE TABLE EMP10
2 (EMPNO NUMBER(4),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(10),
5 SAL NUMBER(7,2));
SQL> INSERT INTO EMP10(EMPNO, ENAME, JOB, SAL)
2 SELECT EMPNO, ENAME, JOB, SAL
3 FROM EMP
4 WHERE DEPTNO = 10;
<DML – UPDATE>
SQL> UPDATE DEPT SET LOC = '부산'
2 WHERE DEPTNO = 90;
<DML – DELETE>
SQL> DELETE FROM DEPT
2 WHERE DEPTNO = 93;
<DML – MERGE>
SQL> CREATE TABLE T1
2 (ID NUMBER(5),
3 NAME VARCHAR2(10));
SQL> INSERT INTO T1 VALUES(1, 'A');
SQL> INSERT INTO T1 VALUES(2, 'B');
SQL> INSERT INTO T1 VALUES(3, 'C');
SQL> CREATE TABLE T2
2 (NO NUMBER(6),
3 TNAME CHAR(10));
SQL> INSERT INTO T2 VALUES(1, 'Z');
SQL> INSERT INTO T2 VALUES(4, 'Y');
SQL> MERGE INTO T1
2 USING T2
3 ON (T1.ID=T2.NO)
4 WHEN MATCHED THEN
5 UPDATE SET T1.NAME=T2.TNAME
6 WHEN NOT MATCHED THEN
7 INSERT VALUES(T2.NO, T2.TNAME);
<COMMIT>
SQL> ALTER SESSION SET NLS_DATE_FORMAT=’RR/MM/DD’;
SQL> INSERT INTO EMP
2 VALUES (9003, '팥쥐', '상담원', 7698, '04/03/01', 3100, NULL, 30);
SQL> DELETE FROM DEPT
2 WHERE DEPTNO = 92;
SQL> COMMIT;
<ROLLBACK>
SQL> DELETE FROM EMP;
SQL> ROLLBACK;
<연습문제>
1. 부서 테이블에 부서코드가 99, 부서명은 ‘관리과’, 위치는 ‘대구’인 행을 입력하시오.
2. 부서 테이블에 99번 부서의 부서명을 ‘회계과’로 변경하시오.
3. 부서 테이블의 99번 부서 행을 삭제하시오.
4. 트랜잭션을 종료하고 모든 변경사항을 데이터베이스에 영구히 반영하시오.
<SELECT>
SELECT * FROM EMP;
SELECT ENAME, SAL*12 FROM EMP;
SELECT ENAME, JOB, ‘리터럴’ FROM EMP;
SELECT ENAME||JOB FROM EMP;
SELECT ENAME “이름”, JOB “보직” FROM EMP;
SELECT DISTINCT JOB FROM EMP;
ALTER SESSION SET NLS_DATE_FORMAT=’RR/MM/DD’;
SELECT EMPNO, ENAME FROM EMP WHERE ENAME=’KING’; --대소문자
SELECT EMPNO, ENAME FROM WHERE HIREDATE=’80/12/17’;
SELECT * FROM EMP WHERE SAL=5000;
SELECT * FROM EMP WHERE COMM IS NULL;
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
SELECT * FROM EMP WHERE SAL >= 1000 AND SAL <= 3000;
SELECT * FROM EMP WHERE DEPTNO IN (10, 20);
SELECT * FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20;
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘K%’;
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘SM_TH’;
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%G’;
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%E%’;
<복습문제>
1. 사원(emp) 테이블에서 급여(sal)이 1000이상이고, 81년도(hiredate)에 입사한 사원은?
2. 사원 테이블에서 직무(job)이 CLERK이고, 급여가 1000이상 2000이하인 사원은?
3. 사원 테이블에서 10번(deptno) 부서에 근무하거나, 직무가 MANAGER가 아닌 사원은?
4. 사원 테이블에서 커미션(comm)이 null인 사원은?
5. 사원 테이블에서 사원명(ename)이 'K'로 시작하는 사원은?
6. 사원 테이블에서 사원명(ename)의 세번째 글자가 'I'인 사원은?
7. 사원 중에서 MANAGER이거나 SALESMAN이면서, 급여가 1000이상인 사원은?
8. 20번 부서에 근무하는 사원 중에 직무가 'SALESMAN'이고, 급여가 2000미만인 사원은?
9. 사원 테이블에서 연봉(sal*12)이 10000이상이고, 30번 부서에 근무하는 사원은?
10. 사원 테이블에서 81년 또는 87년에 입사한 사원 중에서 20번 부서에 근무하고, 직무가 CLERK 또는 MANAGER 또는 SALESMAN인 사원은?
11. 81년도 하반기(7월 1일부터 12월 31일)에 입사한 사원 중에 커미션이 NULL이 아니고, 연봉이 20000이하이며, 20번 또는 30번 부서에 근무하는 사원은?
12. 81년도 3/4분기에 입사한 사원 중에 10번 부서에 근무하고, 이름에 모음(A,E,I,O,U)이 포함된 사원은?
<문자함수>
SELECT ENAME, LOWER(ENAME), UPPER(ENAME), INITCAP(ENAME) FROM EMP;
SELECT ENAME, JOB, CONCAT(ENAME, JOB) FROM EMP;
SELECT JOB, LENGTH(JOB), SUBSTR(JOB, 3, 2), INSTR(JOB, ‘E’, 1, 2),
REPLACE(JOB, ‘MAN’, ‘PERSON’) FROM EMP;
SELECT SAL, LPAD(SAL, 10, ‘*’), RPAD(SAL, 10, ‘*’) FROM EMP;
SELECT EMPNO, JOB,
TRIM(LEADING 'S' FROM JOB) AS LEADING,
TRIM(TRAILING 'N' FROM JOB) AS TRAILING
FROM EMP;
<숫자함수>
SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1)
FROM DUAL;
SELECT TRUNC(45.923, 2), TRUNC(45.923), TRUNC(45.923, -2)
FROM DUAL;
SELECT MOD(100, 30) FROM DUAL;
<복습문제>
1. 사원의 급여를 100으로 나눈 몫과 나머지를 출력하시오.
2. 사원의 세금(sal*0.25)을 계산하시오. 단, 소수점 3자리 이하는 반올림 할 것
3. 사원의 이름 중, 3번째 글자부터 2개의 글자를 출력하시오.
4. 사원 중 이름의 글자수가 4개 이하인 사원은?
5. 사원 중에 job이 SALESMAN인 사원들은? 단, job을 출력하는 경우, SALESMAN을 SALESPERSON으로 변경하시오
6. 사원 중 이름의 마지막 글자에서 두번째 글자가 'E'인 사원을 출력하시오
<날짜연산>
// 날짜 표시 형태 바꾸기
SQL> ALTER SESSION
2 SET NLS_DATE_FORMAT=’YYYY/MM/DD HH24:MI:SS’;
// 원래대로 표시
SQL> ALTER SESSION
2 SET NLS_DATE_FORMAT=’DD-MON-RR’;
SELECT SYSDATE FROM DUAL; -- 현재 날짜
SELECT HIREDATE, HIREDATE+1, HIREDATE-1 FROM EMP;
SELECT HIREDATE, SYSDATE, SYSDATE-HIREDATE FROM EMP;
SELECT HIREDATE, HIREDATE+1/24 FROM EMP; -- 1시간 더하기
<날짜함수>
SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;
SELECT HIREDATE, ADD_MONTHS(HIREDATE, 6) FROM EMP;
SELECT HIREDATE, NEXT_DAY(HIREDATE, ‘SUN’) FROM EMP;
SELECT HIREDATE, LAST_DAY(HIREDATE) FROM EMP;
SELECT HIREDATE, ROUND(HIREDATE, ‘MONTH’),
ROUND(HIREDATE, ‘YEAR’) FROM EMP; -- 날짜 반올림
SELECT HIREDATE, TRUNC(HIREDATE, ‘MONTH’),
TRUNC(HIREDATE, ‘YEAR’) FROM EMP; -- 날짜 내림
<복습문제>
KING 사원이 오늘까지 수령한 전체 급여의 합계 계산하시오.
여기서, 사원의 급여는 다음 항목들에 대한 전체 합계(A+B+C)이다.
A. 입사 첫달에 수령한 급여
급여 = 실제 근무일수/해당월의 전체 일수 * SAL
B. 입사한 두번째 달부터 지난달까지 수령한 급여
급여 = 지난달에서 입사한 두번째 달까지의 개월 수 * SAL
C. 이번 달 급여. 단, 오늘까지 근무한다고 가정
급여 = 실제 근무일수/이번달 전체 일수 * SAL
<변환함수>
날짜 -> 문자
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘YYYY’) FROM EMP;
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘MM’) FROM EMP;
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘DD’) FROM EMP;
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘HH’) FROM EMP;
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘MI’) FROM EMP;
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘SS’) FROM EMP;
SELECT HIREDATE, TO_CHAR(HIREDATE, ‘YYYYMMDD’) FROM EMP;
숫자->문자
SELECT SAL, TO_CHAR(SAL, ‘$9,999,999.00’) FROM EMP;
문자->숫자
SELECT SAL, TO_NUMBER(‘$1,234,567.123’, ‘$9,999,999.00’) + 1
FROM EMP;
문자->날짜
SELECT TO_DATE(‘20091225’, ‘YYYYMMDD’) + 1 FROM EMP;
SELECT TO_DATE(‘25122009’, ‘DDMMYYYY’) + 1 FROM EMP;
<복습문제>
1. 12월에 입사한 사원은?
2. 이번달(SYSDATE)에 입사한 사원은?
3. 사원명과 입사월, 입사일을 출력하시오
4. 사원명과 입사일, 입사일의 요일을 출력하시오
(요일의 포맷모델은 DAY 또는 DY)
5. 올해 크리스마스의 요일을 출력하시오
<일반함수>
SELECT EMPNO, SAL, COMM, SAL+COMM, SAL+NVL(COMM, 0)
FROM EMP;
SELECT EMPNO, ENAME, SAL, JOB,
CASE JOB WHEN 'ANALYST' THEN SAL*1.1
WHEN 'CLERK' THEN SAL*1.2
WHEN 'MANAGER' THEN SAL*1.3
WHEN 'PRESIDENT' THEN SAL*1.4
WHEN 'SALESMAN' THEN SAL*1.5
ELSE SAL
END "급여"
FROM EMP;
// 조건이 부등호 조건인 경우
SELECT EMPNO, ENAME, SAL, JOB,
CASE WHEN SAL > 3000 THEN ‘HIGH’
ELSE ‘LOW’
END "급여수준"
FROM EMP;
<복습문제>
다음과 같은 조건하에 사원들의 등급을 출력하시오
등급명 : 급여
A : 5000 이상
B : 4000 이상 5000 미만
C : 3000 이상 4000 미만
D : 3000 미만
<집계함수>
SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) FROM EMP;
SELECT COUNT(*) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
<복습문제>
1. 사원의 전체 급여는?
2. 10번 부서의 평균 급여는?
3. 20번 부서의 인원은?
<GROUP BY, HAVING, ORDER BY>
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) >= 2000;
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL> >= 2000
ORDER BY SUM(SAL) DESC;
<복습문제>
1. 학년별 평균점수가 50점 이상인 학년은?
2. 학년반별 평균점수가 52점 이상인 학년반은?
3. 성별(GUBUN : 0-남, 1-여) 평균점수는?
4. 점수가 90점 이상인 학년별 인원수?
5. 학년별 남학생의 수는?
<피벗 테이블 출력>
학년반별 90점이상의 인원수 출력하기
SELECT HAK,
COUNT(CASE BAN WHEN 1 THEN 1 END) "1",
COUNT(CASE BAN WHEN 2 THEN 1 END) "2",
COUNT(CASE BAN WHEN 3 THEN 1 END) "3",
COUNT(CASE BAN WHEN 4 THEN 1 END) "4",
COUNT(CASE BAN WHEN 5 THEN 1 END) "5",
COUNT(CASE BAN WHEN 6 THEN 1 END) "6",
COUNT(CASE BAN WHEN 7 THEN 1 END) "7",
COUNT(CASE BAN WHEN 8 THEN 1 END) "8",
COUNT(CASE BAN WHEN 9 THEN 1 END) "9",
COUNT(CASE BAN WHEN 10 THEN 1 END) "10"
FROM SUNGJUK
WHERE SCORE>=90
GROUP BY HAK;
<중간 복습 문제>
1. 사원(emp) 테이블에서 부서별(deptno) 인원수는?
2. 부서별 사원수가 3명 이상인 부서는?
3. 사원 테이블에서 부서(deptno), 직무(job)별 급여 합계는?
4. 사원 테이블에서 입사년도(hiredate)별 평균 급여는?
5. 사원 테이블에서 입사월(hiredate)별 입사인원은?
6. 부서, 직무별 급여 합계를 다음과 같은 피벗 테이블 형태로 출력하시오
DEPTNO CLERK ANALYST MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ------------ -------------- --------------
10 1300 2450 5000
20 1900 6000 2975
30 950 2850 5600
7. 성적(sungjuk) 테이블에서 짝수 번호(bun) 학생들의 학년(hak)별 평균점수는?
8. 성적 테이블에서 저학년(1학년~3학년), 고학년(4학년~6학년) 별 평균점수는?
9. 성적 테이블에서 점수(score)가 90점 이상인 학년별 인원은?
10. 다음과 같이 성적에 따른 인원 분포표를 작성하시오.
단, 인원이 50명을 초과할 때마다 '*'를 찍을 것
- A : 90점 이상
- B : 80점 이상 90점 미만
- C : 70점 이상 80점 미만
- D : 60점 이상 70점 미만
- F : 60점 미만
GRADE TOTAL GRAPH
---------- ---------- ------------------------------
A 165 ***
B 195 ***
C 202 ****
D 157 ***
F 1081 *********************
<JOIN>
SELECT DNAME, ENAME FROM DEPT, EMP; // 카르테시안 프로덕트
SELECT DNAME, ENAME
FROM DEPT, EMP
WHERE DEPT.DEPTNO=EMP.DEPTNO; // EQUI 조인
SELECT ENAME, GRADE
FROM EMP, SALGRADE
WHERE GRADE BETWEEN LOSAL AND HISAL; // NON-EQUI조인
SELECT DNAME, ENAME
FROM DEPT, EMP
WHERE DEPT.DEPTNO=EMP.DEPTNO(+); //아우터 조인
SELECT W.ENAME, M.ENAME
FROM EMP W, EMP M
WHERE W.MGR=M.EMPNO; // 셀프조인
<JOIN – SQL99>
SELECT DNAME, ENAME FROM DEPT CROSS JOIN EMP;
SELECT DNAME, ENAME FROM DEPT NATURAL JOIN EMP;
SELECT DNAME, ENAME FROM DEPT JOIN EMP
USING (DEPTNO);
SELECT DNAME, ENAME FROM DEPT JOIN EMP
ON DEPT.DEPTNO=EMP.DEPTNO;
SELECT DNAME, ENAME
FROM DEPT LEFT OUTER JOIN EMP
ON DEPT.DEPTNO=EMP.DEPTNO;
SELECT DNAME, ENAME
FROM DEPT FULL OUTER JOIN EMP
ON DEPT.DEPTNO=EMP.DEPTNO;
<복습문제>
1. 사원 테이블과 부서(DEPT) 테이블에서 부서명(DNAME), 위치(LOC), 사원명을 출력하시오. 단, 직무(JOB)이 'MANAGER'이며 1981년도에 입사한 사원만 출력하시오.
2. SALES 부서(DNAME)에 근무하는 사원들 중, 급여가 1000이상인 사원은?
3. 부서이름(DNAME)별 급여 합계를 출력하시오.
4. 사원 테이블, 부서 테이블, 급여등급(SALGRADE) 테이블에서 부서명, 위치, 사원명, 등급(GRADE)을 출력하시오. 단, 각 사원의 등급은 해당 사원의 급여가 LOSAL 이상이고 HISAL 일 때의 GRADE 값이다.
5. 사원의 급여가 많은 순서대로 등수를 부여하시오
힌트 : OUTER JOIN, COUNT, SELF JOIN, NON-EQUI JOIN
<중첩된 서브쿼리>
SELECT * FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);
SELECT * FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);
SELECT * FROM EMP
WHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO=10);
SELECT * FROM EMP
WHERE SAL > ANY (SELECT SAL FROM EMP
WHERE DEPTNO=10);
SELECT * FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP
WHERE DEPTNO=10);
SELECT * FROM EMP
WHERE (DEPTNO, JOB) IN (SELECT DEPTNO, JOB
FROM EMP
WHERE ENAME=’SMITH’);
<복습문제>
1. SCOTT 사원보다 많은 급여를 받는 사원은?
2. ADAMS 사원과 같은 직무(JOB)이며, MILLER 사원보다 급여가 적은 사원은?
3. MARTIN 사원과 같은 부서(DEPTNO)이며, WARD 사원과 같은 직무인 사원은?
4. 81년도 입사한 사원들 중에 DALLAS(LOC)에 근무하며, 사원의 평균 급여보다 많은 급여를 받는 사원은?
5. ACCOUNTING 부서에서 가장 급여가 많은 사원은?
<상관관계 서브쿼리>
예) 사원 중, 각 부서의 평균 급여보다 급여가 많은 사원은?
SELECT * FROM EMP E1
WHERE E1.SAL > (SELECT AVG(E2.SAL)
FROM EMP E2
WHERE E2.DEPTNO=E1.DEPTNO);
예) 사원 중, 부하직원이 한명 이상 있는 사원은?
SELECT * FROM EMP E1
WHERE 0 < (SELECT COUNT(*)
FROM EMP E2
WHERE E2.MGR=E1.EMPNO); // 비효율
SELECT * FROM EMP E1
WHERE EXISTS (SELECT ‘X’
FROM EMP E2
WHERE E2.MGR=E1.EMPNO);
// STOP AT – 효율
SELECT * FROM EMP
WHERE EMPNO IN (SELECT MGR FROM EMP);
// 서브쿼리 정렬 발생 – 비효율
<TOP-N>
// ROWNUM 가상컬럼 WHERE 절에 의한 조건에 의해 필터링 된 후, 리턴되는 행의 순서대로 번호를 부여한다.
SELECT ROWNUM, EMPNO, ENAME, HIREDATE FROM EMP;
// 사원 중 입사가 가장 빠른 5명의 사원은?
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE ROWNUM <= 5
ORDER BY HIREDATE; // 실패
SELECT *
FROM (SELECT EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE) E
WHERE ROWNUM <= 5; // 정렬후, ROWNUM을 부여해야 한다.
<복습문제>
1. 사원들 중, 급여를 가장 많이 받는 사원 상위 5건을 출력하시오.
2. 사원들 중, 급여를 6번째로 가장 많이 받는 사원부터 10번째까지 출력하시오.
<CTAS>
CREATE TABLE TEST
AS SELECT DEPTNO, SUM(SAL) SUM_SAL
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 1000;
<PL/SQL>
블록구조
DECLARE
변수 선언부
BEGIN
본문
EXCEPTION
예외 처리부
END;
/
예) 변수정의, 변수에 값 할당, IF문 사용
SET SERVEROUTPUT ON
DECLARE
V_A NUMBER(5);
BEGIN
V_A := 10;
IF V_A > 1 THEN
DBMS_OUTPUT.PUT_LINE('BIG');
ELSE
DBMS_OUTPUT.PUT_LINE('SMALL');
END IF;
DBMS_OUTPUT.PUT_LINE(V_A);
END;
/
예) 전형적인 PL/SQL 블록
1. DB에서 읽어온 결과를 변수에 저장
2. 조건에 따라 변수의 값을 변경
3. 변경된 값을 DB에 저장
주의 사항 : SELECT 문장은 반드시 단 한건만 리턴되어야 하며, 그 결과는 INTO 뒤에 기술된 변수에 저장하여야 한다. 만약, 두 건 이상을 데이터베이스로부터 검색해서 처리해야 할 경우에는 커서를 이용한다.
DECLARE
V_A NUMBER(5);
BEGIN
SELECT SAL INTO V_A FROM EMP WHERE ENAME='SCOTT';
IF V_A > 1000 THEN
V_A := V_A + 100;
ELSE
V_A := V_A + 500;
END IF;
UPDATE EMP SET SAL=V_A WHERE ENAME='SCOTT';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN DBMS_OUTPUT.PUT_LINE('MANY ROWS');
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('NO ROWS');
WHEN OTHERS
THEN DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR');
END;
/
예) 커서 사용
DECLARE
V_EMP EMP%ROWTYPE;
CURSOR C_EMP IS SELECT * FROM EMP;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO V_EMP;
EXIT WHEN C_EMP%NOTFOUND;
IF V_EMP.SAL > 1000 THEN
UPDATE EMP SET SAL=SAL+100 WHERE EMPNO=V_EMP.EMPNO;
ELSE
UPDATE EMP SET SAL=SAL+500 WHERE EMPNO=V_EMP.EMPNO;
END IF;
END LOOP;
CLOSE C_EMP;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SCOTT');
END;
/
<프로시저>
파라메터 모드 : IN, OUT, INOUT
CREATE OR REPLACE PROCEDURE RAISESAL
(V_EMPNO IN NUMBER, V_SAL IN NUMBER, V_RESULT OUT NUMBER)
IS
BEGIN
UPDATE EMP SET SAL=SAL+V_SAL WHERE EMPNO=V_EMPNO;
SELECT SAL INTO V_RESULT FROM EMP WHERE EMPNO=V_EMPNO;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/
VARIABLE RESULT NUMBER
EXEC RAISESAL(7844, 100, :RESULT)
PRINT RESULT
<함수>
CREATE OR REPLACE FUNCTION TAX(V_SAL NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN V_SAL*0.25;
END;
/
SELECT ENAME, SAL, TAX(SAL) FROM EMP;
<트리거>
DROP TABLE ORDERDETAILS;
DROP TABLE ORDERS;
DROP TABLE CUSTOMERS;
DROP TABLE PRODUCTS;
CREATE TABLE ORDERS
(ID NUMBER(3),
PCODE NUMBER(3),
QTY NUMBER(3));
CREATE TABLE PRODUCTS
(PCODE NUMBER(3),
PNAME VARCHAR2(20),
STOCK NUMBER(3));
INSERT INTO PRODUCTS VALUES(10, 'APPLE', 100);
INSERT INTO PRODUCTS VALUES(20, 'ORANGE', 200);
CREATE OR REPLACE TRIGGER ORDERS_INS
AFTER INSERT
ON ORDERS
FOR EACH ROW
DECLARE
V_STOCK PRODUCTS.STOCK%TYPE;
BEGIN
SELECT STOCK INTO V_STOCK
FROM PRODUCTS WHERE PCODE=:NEW.PCODE;
IF V_STOCK >= :NEW.QTY THEN
UPDATE PRODUCTS SET STOCK=STOCK-:NEW.QTY
WHERE PCODE=:NEW.PCODE;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'ERROR!! ROLLBACK~');
END IF;
END;
/
CREATE OR REPLACE TRIGGER ORDERS_DEL
AFTER DELETE
ON ORDERS
FOR EACH ROW
BEGIN
UPDATE PRODUCTS SET STOCK=STOCK+:OLD.QTY WHERE PCODE=:OLD.PCODE;
END;
/
CREATE OR REPLACE TRIGGER ORDERS_UPD
AFTER UPDATE OF QTY
ON ORDERS
FOR EACH ROW
DECLARE
V_STOCK PRODUCTS.STOCK%TYPE;
BEGIN
SELECT STOCK INTO V_STOCK
FROM PRODUCTS WHERE PCODE=:NEW.PCODE;
IF V_STOCK >= :NEW.QTY-:OLD.QTY THEN
UPDATE PRODUCTS
SET STOCK=STOCK-(:NEW.QTY-:OLD.QTY)
WHERE PCODE=:NEW.PCODE;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'ERROR!! ROLLBACK~');
END IF;
END;
/
INSERT INTO ORDERS VALUES(1, 10, 50);
INSERT INTO ORDERS VALUES(2, 10, 200);
UPDATE ORDERS SET QTY=10 WHERE ID=1;
UPDATE ORDERS SET QTY=200 WHERE ID=1;
DELETE FROM ORDERS WHERE ID=1;
<옵티마이저 모드>
규칙기반 옵티마이저
ALTER SESSION SET OPTIMIZER_MODE=RULE;
비용기반 옵티마이저
ALTER SESSION SET OPTMIZER_MODE=ALL_ROWS;
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;
통계 수집하기
ANALYZE TABLE EMP COMPUTE STATISTICS
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;
PLAN_TABLE 생성
SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql
실행계획 수집
EXPLAIN PLAN FOR
SELECT ENAME, SAL FROM EMP WHERE EMPNO=7844;
실행계획 확인
SQL>@$ORACLE_HOME/rdbms/admin/utlxpls.sql 또는
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
<인덱스>
CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);
CREATE INDEX IDX_EMP_DEPTNO_JOB ON EMP(DEPTNO, JOB);
<복합인덱스 작성시 컬럼 순서>
1. WHERE 절에서 자주 사용되는 컬럼
2. = 로 비교되는 컬럼
3. 분포도가 좋은 컬럼
4. ORDER BY 절에서 자수 사용되는 컬럼
<다양한 처리 연산과 힌트>
- FULL TABLE SCAN : FULL(테이블명)
- INDEX UNIQUE SCAN : INDEX(테이블명 인덱스명)
- INDEX RANGE SCAN : INDEX(테이블명 인덱스명)
- INDEX RANGE SCAN DESCENDING : INDEX_DESC(테이블명 인덱스명)
- INDEX FULL SCAN
- INDEX FAST FULL SCAN : INDEX_FFS(테이블명 인덱스명)
- INDEX SKIP SCAN : INDEX_SS(테이블명 인덱스명)
- INDEX MERGE
- CONCATENATION : USE_CONCAT
- INLIST ITERATOR
- USE_NL : USE_NL(테이블명 테이블명 …)
- USE_HASH : USE_HASH(테이블명 테이블명 …)
- USE_MERGE : USE_MERGE(테이블명 테이블명 …)
<실행계획을 읽는 방법>
B-Tree를 포스트 오더 방식으로 읽어나간다.
처리 순서 : 8->10->9->7->12->11->6->5->15->14->4->3->2->1
<컬럼의 최대값 구하기>
SELECT MAX(EMPNO) FROM EMP;
SELECT /*+ INDEX_DESC(EMP PK_EMP) */ EMPNO FROM EMP
WHERE ROWNUM=1;
<불필요한 함수 제거>
SELECT MAX(NVL(COMM, 0)) FROM EMP;
SELECT MAX(COMM) FROM EMP;
<불필요한 정렬 제거>
SELECT EMPNO, ENAME FROM EMP WHERE JOB=’SALESMAN’
UNION
SELECT EMPNO, ENAME FROM EMP WHERE JOB=’MANAGER’;
SELECT EMPNO, ENAME FROM EMP WHERE JOB=’SALESMAN’
UNION ALL
SELECT EMPNO, ENAME FROM EMP WHERE JOB=’MANAGER’;
<인덱스 컬럼 변경 금지>
WHERE UPPER(ENAME)=’KIM’ (X)
WHERE ENAME=UPPER(‘kim’) (O)
WHERE SAL*12=1000 (X)
WHERE SAL=1000/12 (O)
WHERE TO_CHAR(HIREDATE,’YYYYMMDD’)
BETWEEN ‘19810101’ AND ‘19811231’ (X)
WHERE HIREDATE BETWEEN TO_DATE(‘19810101’,’YYYYMMDD’)
AND TO_DATE(‘19811231’,’YYYYMMDD’) (O)
<부정 조건은 긍정 조건으로 변환>
WHERE COMM IS NOT NULL (X)
WHERE COMM > 0 (O)
WHERE ENAME JOB IS NOT NULL (X)
WHERE ENAME > ‘ ‘ (O)
WHERE SAL NOT BETWEEN 1000 AND 2000 (X)
WHERE SAL < 1000 OR SAL > 2000 (O)
WHERE SAL <> 1000 (X)
WHERE SAL < 1000 OR SAL > 1000 (O)
<선분은 점으로 비교>
WHERE DEPTNO BETWEEN 10 AND 40 (X)
WHERE DEPTNO IN (10, 20, 30, 40) (O)
<수고하셨습니다>
- 관련자료카페 : http://cafe.naver.com/tech2u