다중행 연산자
- IN : 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 true
- ANY, SOME : 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 true
- ALL : 메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true
- EXISTS : 서브쿼리의 결과가 존재하면 true
다중행 Sub Query
실행 결과 행이 여러 개로 나오는 서브쿼리를 가리킨다.
다중열 Sub Query
서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식이다.
상호연관 Sub Query
메인쿼리에 사용한 데이터를 서브쿼리에서 사용하고 서브쿼리의 결과 값을 다시 메인쿼리로 돌려주는 방식이다.
Scalar Sub Query
SELECT절에 사용하는 서브쿼리
반드시 하나의 결과만 반환하도록 작성해야 한다.
View(뷰)
- View? :가상 테이블, 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체
- 뷰의 사용 목적
- 편리성 : SELECT문의 복잡도를 완화하기 위해
- 보안성 : 테이블의 특정 열을 노출하고 싶지 않을 경우
Inline View, 인라인 뷰
Hierarchical Query, 계층형 쿼리
- WHERE CONNECT_BY_ISLEAF=0 : 마지막 값을 삭제
계층의 LEAF에 해당하는 결과는 출력되지 않는다. - WHERE CONNECT_BY_ISLEAF=1 : 마지막 값만 출력
Sub Query, 계층형 쿼리 연습문제
--다중행 Sub Query
--emp2, dept2
--근무지역(dept2 area)이 Pohnag Main Office인 모든 사원들의 사번, 이름, 부서번호
SELECT e.empno, e.name, e.deptno
FROM emp2 e
WHERE deptno IN (SELECT dcode
FROM dept2
WHERE area = 'Pohang Main Office');
SELECT *--e.empno, e.name, e.deptno
FROM emp2 e
WHERE EXISTS (SELECT dcode
FROM dept2
WHERE area = 'Pohang Main Office'
AND dcode = e.deptno);
SELECT *
FROM emp2 e
WHERE EXISTS (SELECT dcode
FROM dept2 d
WHERE d.dcode = e.deptno
AND d.pdept = '1000');
--다중행 Sub Query 연습문제
--1번
--emp2
--'Section head' 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력
SELECT name, position, TO_CHAR(pay, '$999,999,999')
FROM emp2
WHERE pay > ANY (SELECT MIN(pay)
FROM emp2
WHERE position = 'Section head');
--student
--전체 학생 중에서 체중이 2학년 학생들의 체중에서 가장 적은 학생보다
--몸무게가 적은 학생의 이름과 몸무게를 출력
SELECT name, grade, weight
FROM student
WHERE weight < ANY (SELECT MIN(weight)
FROM student
WHERE grade = 2);
--emp2, dept2
--각 부서별 평균 연봉을 구하고 그중에서 평균 연봉이 가장 적은
--부서의 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력
SELECT d.dname, e.name, TO_CHAR(pay, '$999,999,999')
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode
AND pay < ALL (SELECT AVG(pay)
FROM emp2
GROUP BY deptno);
--다중 컬럼 Sub Query
--student
--각 학년별로 최대 몸무게를 가진 학생들의 학년, 이름, 몸무게를 출력
SELECT grade, weight
FROM student
WHERE (grade, weight) IN (SELECT grade, MAX(weight)
FROM student
GROUP BY grade);
--professor, department
--각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력
SELECT p.profno, p.name, p.hiredate, p.deptno, d.dname
FROM professor p, department d
WHERE (p.deptno, p.hiredate) IN ( SELECT deptno, MIN(hiredate)
FROM professor
GROUP BY deptno)
AND p.deptno = d.deptno
ORDER BY hiredate;
--emp2
--직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름과, 직급, 연봉을 출력
SELECT name, position, TO_CHAR(pay, '$999,999,999')
FROM emp2
WHERE (position, pay) IN (SELECT position, MAX(pay)
FROM emp2
GROUP BY position)
ORDER BY pay;
--상호 연관 Sub Query
--emp2
--직원들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의 이름과 직급, 현재 연봉을 출력
SELECT name, position, pay
FROM emp2 e1
WHERE e1.pay >= ( SELECT AVG(pay)
FROM emp2 e2
WHERE e1.position = e2.position);
--Scalar Sub Query
SELECT e.name, (SELECT dname FROM dept2 WHERE dcode = e.deptno)
FROM emp2 e;
--Inline View
SELECT deptno, maxsal
FROM (SELECT deptno, MAX(sal) maxsal
FROM emp
GROUP BY deptno);
SELECT *
FROM (SELECT ename, job, sal, deptno
FROM emp) e,
(SELECT deptno, dname
FROM dept) d
WHERE e.deptno = d.deptno;
SELECT empname, empjob, deptno, (SELECT hiredate FROM emp WHERE ename = empname) hiredate
FROM (SELECT e.ename empname, e.job empjob, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno)
WHERE deptno = (SELECT deptno
FROM emp
WHERE ename = 'JAMES');
SELECT pempno, e.*
FROM emp2 e;
--
SELECT DECODE(deptno, ndeptno, null, deptno), p.*
FROM (
SELECT LAG(deptno) OVER(ORDER BY deptno) ndeptno, deptno, profno, name
FROM professor
) p;
--계층형 쿼리
-- King, 상위->하위
SELECT LEVEL, empno, LPAD(ename, LEVEL*6, '-')
FROM emp
--WHERE ename != 'JONES' --결과의 마지막에 제외하는 경우
--WHERE LEVEL >= 2
CONNECT BY PRIOR empno = mgr
-- AND ename != 'JONES' --JONES와 관련된 계층을 제외하는 경우
START WITH empno = 7839;
-- SMITH, 하위 -> 상위
SELECT LEVEL, LPAD(ename, LEVEL*6, '-')
FROM emp
CONNECT BY empno = PRIOR mgr
START WITH empno = 7369;
SELECT LEVEL, empno, ename
, SYS_CONNECT_BY_PATH(empno, '->')
, SYS_CONNECT_BY_PATH(ename, '->')
, CONNECT_BY_ISLEAF
, CONNECT_BY_ROOT empno
FROM emp e
WHERE LEVEL > 1
AND empno = 7369
CONNECT BY PRIOR empno = mgr;
--
SELECT e.empno, e.pempno, e.name,
LPAD(e.name, LEVEL*15, '-')
, d.dname, NVL(e.position, 'Team-Worker')
, e.name || '-' || d.dname || '-' || NVL(e.position, 'Team-Worker') "Name and Position"
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode
CONNECT BY PRIOR e.empno = e.pempno
START WITH e.empno = '19900101';
--1번
SELECT LEVEL, e.name, e.empno
, LPAD((e.name || '-' || d.dname || '-' || NVL(e.position, 'Team-Worker')), LEVEL*30, '*') "Name and Position"
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode
CONNECT BY PRIOR e.empno = e.pempno
START WITH e.empno = '19900101'
ORDER SIBLINGS BY e.name DESC;
--2번
SELECT LEVEL, e.name, e.empno
, LPAD((e.name || '-' || d.dname || '-' || NVL(e.position, 'Team-Worker')), LEVEL*50, '*') "Name and Position"
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode
CONNECT BY PRIOR e.empno = e.pempno
START WITH e.empno = '19966102';
--3번
SELECT LEVEL, e.name, e.empno
, LPAD((e.name || '-' || d.dname || '-' || NVL(e.position, 'Team-Worker')), LEVEL*50, '*') "Name and Position"
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode
CONNECT BY e.empno = PRIOR e.pempno
START WITH e.empno = '20000334';
--5번
SELECT e1.empno,
e1.name,
(SELECT COUNT(empno)
FROM emp2
WHERE LEVEL != 1
CONNECT BY PRIOR empno = pempno
START WITH empno = e1.empno) 부하직원의수
FROM emp2 e1;
--6번
SELECT LEVEL, e.name, e.empno
--, LPAD((e.name || '-' || d.dname || '-' || NVL(e.position, 'Team-Worker')), LEVEL*50, '*') "Name and Position"
, SYS_CONNECT_BY_PATH(e.name, '-') path
FROM emp2 e, dept2 d
WHERE e.deptno = d.dcode
CONNECT BY PRIOR e.empno = e.pempno
START WITH e.empno = '19966102';