Study Web Development

11월 24일

이전으로

Oracle SQL

2. SQL(Standard Query Language)

2-3 SELECT문

집합 연산자

• SELECT문으로 반환되는 테이블의 행 집합 간 연산
  • 두 SELECT문에 명시된 컬럼의 수가 일치해야 함
  • 두 SELECT문에서 같은 위치에 명시된 컬럼끼리 자료형이 호환 가능해야 함
  • 집합 연산을 적용한 최종 결과를 정렬하고 싶은 경우, 두 번째 SELECT문에 ORDER BY절 작성
• UNION

-- 두 테이블의 합집합을 출력
-- 중복값 제거(=정렬 작업 수행)
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;

• UNION ALL

-- 두 테이블의 합집합을 출력
-- 중복값 허용(=정렬 작업 수행하지 않음)
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;

• INTERSECT

-- 두 테이블의 교집합을 출력
-- 중복값 제거(=정렬 작업 수행)
SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

• MINUS

-- 두 테이블의 차집합, 첫 번째 테이블에서 두 번째 테이블을 제외하고 출력
-- 중복값 제거(=정렬 작업 수행)
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

Subquery

• 다른 SELECT 문장 내에 포함되어 있는 SELECT 문장
• 단일 행 서브쿼리(Single-Row Subquery)

-- 수행 결과로 하나의 행(값)만 반환하는 서브쿼리
-- 단일 행 연산자(=, >=, <=, >, <, != 등)만 사용 가능
SELECT empno, ename, job FROM emp
WHERE job=(SELECT job FROM emp WHERE empno=7369); -- empno는 PRIMARY KEY 제약 조건이 지정되었으므로 항상 단일 행 반환
SELECT ename, sal FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='BLAKE'); -- ename은 UNIQUE 제약 조건이 지정되지 않았기 때문에 데이터가 추가되면 다중 행 반환 가능

• 다중 행 서브쿼리(Multiple-Row Subquery)

-- 수행 결과로 하나 이상의 행(값)을 반환하는 서브쿼리
-- 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS 등)만 사용 가능

-- IN 연산자
SELECT empno, ename, sal FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno); -- sal의 값이 서브쿼리로 반환된 950, 800, 1300 중 하나의 값과 일치하는지 비교

-- ANY 연산자 : 서브쿼리의 결과 값 중 하나의 값만 만족되면 행을 반환
SELECT empno, ename, sal, deptno FROM emp
WHERE sal>ANY (SELECT sal FROM emp WHERE job='SALESMAN'); -- 서브쿼리로 반환된 1600, 1250, 1250, 1500에 대한 > 비교를 OR로 연결

-- ALL 연산자 : 서브쿼리의 결과 값 중 모든 값이 만족되면 행을 반환
SELECT empno, ename, sal, deptno FROM emp
WHERE sal>All (SELECT sal FROM emp WHERE deptno=20); -- 서브쿼리로 반환된 800, 2975, 3000에 대한 > 비교를 AND로 연결

• 다중 컬럼 서브쿼리(Multiple-Column Subquery)

-- 수행 결과로 2개 이상의 컬럼을 반환하는 서브쿼리
SELECT empno, ename, sal, deptno FROM emp
WHERE (deptno, sal) IN (SELECT deptno, sal FROM emp WHERE deptno=30);

• 실습 문제

-- 1) 'BLAKE'와 같은 부서에 있는 사원들의 이름과 입사일을 구하는데 'BLAKE'는 제외하고 출력하시오.
-- BLAKE가 중복되지 않는 경우
SELECT ename, hiredate FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename='BLAKE')
AND ename!='BLAKE';
-- BLAKE가 중복되는 경우
SELECT ename, hiredate FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename='BLAKE')
AND ename!='BLAKE';
-- 2) 평균 급여보다 많은 급여를 받는 사원들의 사원 번호, 이름, 월급을 출력하는데 월급이 높은 순으로 출력하시오.
SELECT empno, ename, sal FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;
-- 3) 10번 부서에서 급여를 가장 적게 받는 사원과 동일한 급여를 받는 사원의 이름과 월급을 출력하시오.
SELECT ename, sal FROM emp
WHERE sal=(SELECT MIN(sal) FROM emp WHERE deptno=10);
-- 4) 사원 수가 3명이 넘는 부서의 부서명과 사원 수를 출력하시오.
-- 서브쿼리 사용하는 경우
SELECT dname, NVL(cnt, 0) FROM dept
LEFT JOIN (SELECT deptno, COUNT(empno) AS cnt FROM emp GROUP BY deptno) USING(deptno)
WHERE cnt>3;
-- 서브쿼리 사용하지 않는 경우
SELECT dname, COUNT(empno) FROM emp RIGHT JOIN dept USING(deptno)
GROUP BY dname HAVING COUNT(empno)>3;
-- 5) 사원 번호가 7844인 사원보다 빨리 입사한 사원의 이름과 입사일을 출력하시오.
SELECT ename, hiredate FROM emp
WHERE hiredate<(SELECT hiredate FROM emp WHERE empno=7844);
-- 6) 관리자가 KING인 모든 사원의 이름과 급여를 출력하시오.
SELECT ename, sal FROM emp
WHERE mgr=(SELECT empno FROM emp WHERE ename='KING');
-- 7) 20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 사원의 이름과 부서명, 급여, 급여 등급을 출력하시오.
SELECT ename, dname, sal, grade FROM emp
JOIN dept USING(deptno)
JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE sal=(SELECT MAX(sal) FROM emp WHERE deptno=20);
-- 8) 총 급여(sal+comm)를 평균 급여(sal)보다 많이 받는 사원의 부서 번호, 이름, 총 급여, 커미션을 출력하시오.
	-- (커미션 받으면 O, 받지 않으면 X로 표시하고 별칭을 "comm 유무"로 지정)
-- NVL2 함수를 사용하는 경우
SELECT deptno, ename, sal+NVL(comm,0),
NVL2(comm,'O','X') "comm 유무"
FROM emp
WHERE sal+NVL(comm,0)>(SELECT AVG(sal) FROM emp);
-- CASE문을 사용하는 경우
SELECT deptno, ename, sal+NVL(comm,0),
	CASE WHEN comm IS NOT NULL THEN 'O'
		ELSE 'X'
	END "comm 유무"
FROM emp
WHERE sal+NVL(comm,0)>(SELECT AVG(sal) FROM emp);
-- 9) CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여, 지역명을 출력하시오.
SELECT ename, sal, loc FROM emp JOIN dept USING(deptno)
WHERE sal>(SELECT AVG(sal) FROM emp JOIN dept USING(deptno) WHERE loc='CHICAGO');
SELECT ename, sal, loc FROM emp JOIN dept USING(deptno)
WHERE sal>(SELECT AVG(sal) FROM emp
	WHERE deptno=(SELECT deptno FROM dept WHERE loc='CHICAGO'));
-- 10) 커미션이 없는 사원들 중 월급이 가장 높은 사원의 이름과 급여 등급을 출력하시오.
SELECT ename, grade FROM emp JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE sal=(SELECT MAX(sal) FROM emp WHERE comm IS NULL);
-- 11) SMITH의 관리자의 이름과 부서명, 근무 지역을 출력하시오.
SELECT ename, dname, loc FROM emp JOIN dept USING(deptno)
WHERE empno=(SELECT mgr FROM emp WHERE ename='SMITH');

2-4 INSERT문

• 테이블에 행 단위로 데이터를 삽입

-- 전체 데이터 삽입
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (8000, 'PIKA', 'SALESMAN', 7698, '99/01/22', 1700, 200, 30);

-- 전체 데이터를 삽입하는 경우, 컬럼명 생략 가능
INSERT INTO emp
VALUES (8001, 'CHU', 'ANALYST', 7566, '99/02/02', 3000, NULL, 20);

-- 값이 입력되지 않는 컬럼 생략 가능
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno) -- comm 생략
VALUES (8002, 'MU', 'CLERK', 7902, '99/03/03', 900, 20);

2-5 UPDATE문

• 테이블에서 행 단위로 데이터를 갱신

UPDATE emp SET mgr=7902
WHERE empno=8000; -- UNIQUE하지 않은 컬럼으로 조건을 지정할 경우 복수의 행이 갱신될 수 있음
UPDATE emp SET ename='RAICHU', sal=3500, comm=100
WHERE empno=8001;

-- (주의) WHERE절을 명시하지 않고 UPDATE문을 수행하면 모든 행이 갱신됨
UPDATE emp SET ename='CHU';

2-6 DELETE문

• 테이블에서 행 단위로 데이터를 삭제

DELETE FROM emp WHERE empno=8002; -- UNIQUE하지 않은 컬럼으로 조건을 지정할 경우 복수의 행이 삭제될 수 있음
-- DELETE * FROM emp WHERE empno=8001; -- DELETE문에 컬럼명 명시하면 에러

-- (주의) WHERE절을 명시하지 않고 DELETE문을 수행하면 모든 행이 삭제됨
DELETE FROM emp;

2-8 트랜잭션

• DBMS에서 작업의 한 단위
  • 이전의 COMMIT이 일어난 후, 다음의 COMMIT이 일어나기 전까지 실행된 연산(=SQL문)들의 모음
  • 데이터베이스의 상태를 변환시키는 작업의 단위
  • 데이터베이스 병행 제어 및 복구시 처리되는 작업의 단위
• 원자성(Atomicity=All or Nothing)
  • 트랜잭션은 분해 불가능한 최소 단위로, 연산 전체가 데이터베이스에 반영되거나 어느 하나도 반영되지 않아야 함
• 일관성(Consistency)
  • 트랜잭션이 완료된 후 데이터베이스는 일관성 있는 상태를 유지해야 함
• 격리성(Isolation)
  • 각 트랜잭션은 동시 수행되는 다른 트랜잭션들의 중간 연산 결과에 접근할 수 없음
• 지속성(Durablility)
  • 트랜잭션이 완료되어 데이터베이스에 반영된 연산 결과는 시스템 장애가 발생하더라도 손실되지 않아야 함

트랜잭션의 제어

• COMMIT
  • 보류 중인(=메모리상에서 작업한) 모든 데이터 변경 내용을 영구 저장하고 현재 트랜잭션을 종료(=완료)
  • DDL, DCL 문장을 실행하거나, SQL*Plus를 정상 종료(=QUIT 명령어)하는 경우 자동 COMMIT 발생
• ROLLBACK
  • 보류 중인(=메모리상에서 작업한) 모든 데이터 변경 내용을 취소하고 현재 트랜잭션을 종료(=재시작/폐기)
  • 시스템 장애가 일어나거나, SQL*Plus를 비정상 종료하는 경우 자동 ROLLBACK 발생
• SAVEPOINT
  • 부분적인 ROLLBACK이 가능하게끔 트랜잭션의 중간점을 정의하는 작업
  • 자바와 연동시에는 잘 사용되지 않으며, 데이터베이스를 단독으로 사용하며 트랜잭션 작업량이 많은 경우에 유용
• ROLLBACK TO
  • 지정된 저장점으로 ROLLBACK하고, 해당 저장점 이후에 생성된 모든 변경 내용과 저장점을 취소
• 저장점을 만들고 변경 내역을 부분적으로 취소하기

COMMIT; -- 이전 트랜잭션 종료
SELECT * FROM emp; -- 현재 트랜잭션 시작
SAVEPOINT first;
INSERT INTO dept VALUES (50, 'TEST', 'SAVEPOINT');
UPDATE dept SET deptno=60 WHERE dname='TEST';
SAVEPOINT second;
DELETE FROM dept WHERE deptno=60;
ROLLBACK TO second; -- DELETE 취소
ROLLBACK TO first; -- INSERT, UPDATE, SAVEPOINT second 취소
-- ROLLBACK TO second; -- 저장점 first로 ROLLBACK하였으므로, 그 이후에 생성된 저장점 second는 취소되어 더 이상 해당 저장점으로 ROLLBACK할 수 없음
ROLLBACK; -- SELECT, SAVEPOINT first 취소

과제

• JOIN과 Subquery를 이용하는 문제와 답 10쌍 작성

-- 1) 직무가 CLERK인 사원들의 관리자를 구하고, 관리자의 이름, 직무, 부서명을 출력하시오.
SELECT ename, job, dname FROM emp JOIN dept USING(deptno)
WHERE empno IN (SELECT mgr FROM emp WHERE job='CLERK');
-- 2) 부서명이 RESEARCH인 사원들의 부서 번호, 이름, 관리자 이름, 관리자 부서 번호를 출력하시오.
SELECT e.deptno, e.ename, m.deptno AS mgr_deptno, m.ename AS mgr_ename FROM emp e JOIN emp m ON e.mgr=m.empno
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='RESEARCH');
-- 3) 근무지가 CHICAGO인 사원들의 이름, 직무, 급여 등급을 출력하시오.
SELECT ename, job, grade FROM emp JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE deptno=(SELECT deptno FROM dept WHERE loc='CHICAGO');
-- 4) 급여가 급여 등급 4의 상한보다 낮은 사원들의 이름, 직무, 급여를 출력하시오.
SELECT dname, job, ename, sal FROM emp
JOIN dept USING(deptno)
WHERE sal<(SELECT hisal FROM salgrade WHERE grade=4);
-- 5) 직무가 SALESMAN인 사원들의 관리자를 구하고, 관리자의 이름, 직무, 부서명을 출력하시오.
SELECT ename, job, dname FROM emp JOIN dept USING(deptno)
WHERE empno IN (SELECT mgr FROM emp WHERE job='SALESMAN');
-- 6) 부서명이 SALES인 사원들의 부서 번호, 이름, 관리자 이름, 관리자 부서 번호를 출력하시오.
SELECT e.deptno, e.ename, m.deptno AS mgr_deptno, m.ename AS mgr_ename FROM emp e JOIN emp m ON e.mgr=m.empno
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');
-- 7) 근무지가 NEW YORK인 사원들의 이름, 직무, 급여 등급을 출력하시오.
SELECT ename, job, grade FROM emp JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE deptno=(SELECT deptno FROM dept WHERE loc='NEW YORK');
-- 8) 부서별 평균 급여가 급여 등급 4의 하한보다 높은 부서의 이름과 근무지, 평균 급여를 출력하시오.
SELECT dname, loc, dept_avg FROM dept
JOIN (SELECT deptno, AVG(sal) dept_avg FROM emp GROUP BY deptno) USING(deptno)
WHERE dept_avg>(SELECT losal FROM salgrade WHERE grade=4);
-- 9) 부서별 평균 급여가 급여 등급 4의 하한보다 높은 부서에서 근무하는 사원들의 이름, 직무, 급여를 출력하시오.
SELECT ename, job, sal FROM emp
JOIN (SELECT deptno, AVG(sal) dept_avg FROM emp GROUP BY deptno) USING(deptno)
WHERE dept_avg>(SELECT losal FROM salgrade WHERE grade=4);
-- 10) 부서별 최저 급여가 급여 등급 2의 하한보다 낮은 부서에서 근무하는 사원들의 이름, 급여, 근무지, 부서별 최저 급여를 출력하시오.
SELECT ename, sal, loc, dept_min FROM emp JOIN dept USING(deptno)
JOIN (SELECT deptno, MIN(sal) dept_min FROM emp GROUP BY deptno) USING(deptno)
WHERE dept_min<(SELECT losal FROM salgrade WHERE grade=2);

다음으로