KOSTA : 클라우드 네이티브 애플리케이션 개발 전문가 양성과정

04/16 5일차 서브쿼리

람대리 2024. 4. 16. 11:54
728x90

Lecture 6

서브쿼리

서브쿼리는 SELECT, FROM, WHERE 조건에도 올 수 있다. 서브쿼리는 어디에는 넣을 수 있다.(복잡한 서브쿼리 가능)

서브쿼리 잘하는 방법 : 안에서부터 하나하나씩 구현, 만들고 실행시켜본다. Buttom-up 방식으로 타이핑한다.

 

급여가 2850보다 많은 사원

SELECT * FROM emp WHERE sal>2850;

BLAKE의 급여

SELECT sal FROM emp WHERE ename = 'BLAKE';

급여를 BLAKE보다 많이 받는 사원

SELECT *
FROM emp
WHERE sal > (SELECT sal
			 FROM emp
             WHERE ename='BLAKE');

 

주의해야 할 점

서브쿼리 결과가 하나라면 =을 쓴다.

서브쿼리 결과가 여러개라면 IN 문법을 이용한다.

최대한 IN 문법을 이용하는 것이 좋다.

 

직업이 CLERK이고 급여가 1100보다 많이 받는 사원(서브쿼리 이용)

SELECT *
FROM emp
WHERE job = (SELECT job
			 FROM emp
             WHERE empno = 7369) AND
      sal = (SELECT sal
      	     FROM emp
             WHERE empno = 7876);

 

급여를 (최소급여)를 받는 사원

SELECT *
FROM emp
WHERE sal = (SELECT MIN(sal)
			 FROM emp);

 

각 부서별 부서번호, 최소급여 출력
단 최소급여는 (20번부서의 최소급여)보다 많아야 한다.

SELECT deptno, MIN(sal)
FROM emp
WHERE sal > (SELECT MIN(sal)
			 FROM emp
             WHERE dept = 20)
GROUP BY deptno;

 

각 부서별 부서번호,부서이름 ,최소급여 출력
단 최소급여는 (20번부서의 최소급여)보다 많아야 한다.

SELECT deptno,dname,MIN(sal)
FROM emp, dept
WHERE sal > (SELECT MIN(sal)
			 FROM emp
             WHERE dept = 20)
GROUP BY deptno,dname;

 

각 부서별 최소 급여인 사원 출력
ORA-01427: single-row subquery returns more than one row 
sal = (SELECT MIN(sal) FROM emp GROUP BY) 서브쿼리가 여러 row를 리턴하기 때문에 에러가 난다. = 대신 IN을 써보자.

SELECT deptno, ename
FROM emp
WHERE sal = (SELECT MIN(sal)
			 FROM emp
             GROUP BY dept);

 

서브쿼리 리턴값이 없어서 아무것도 나오지 않는다. 

SELECT ename, job
FROM emp
WHERE ename = (SELECT job
			   FROM emp
               WHERE ename = 'SMYTHE');

 

ANY : OR의 느낌 
ALL : AND의 느낌 
부등호일때 사용한다.
ANY : 4개값중 어느 하나의 값 보다도 큰 1300,1100,800,950

SELECT empno, ename, job
FROM emp
WHERE sal < ANY (SELECT sal
				 FROM emp
                 WHERE job = 'CLERK')
AND	  job<>'CLERK';

 

각 부서별 평균 급여
ALL : 3개값 모두 보다 커야 한다 == 2916보다 큰

SELECT empno, ename, job
FROM emp
WHERE sal > ALL
				(SELECT AVG(sal)
                 FROM emp
                 GROUP BY deptno);

 

직업이 7654 직업과 같고, 급여가 7654 급여인 사원 , 멀티컬럼 비교

SELECT *
FROM emp
WHERE (job,sal) = (SELECT job,sal
				   FROM emp
                   WHERE empno = 7654);

 

FROM절에도 서브쿼리를 쓸 수 있다. 인라인뷰(inline view)
empno, ename, deptno, dname, job, loc을 뽑아봐라

SELECT empno
FROM emp e, dept d
WHERE e.deptno = d.deptno;

 

view 만들기

CREATE VIEW v_empdept as (SELECT e.empno, e.ename, e.deptno, d.dname, e.job, d.loc
						  FROM emp e, dept d
                          WHERE e.deptno = d.deptno);

 

CREATE OR REPLACE FORCE VIEW 만들거나 아니면 있으면 덮어씌우자 

CREATE OR REPLACE FORCE VIEW AS v_empdept (SELECT e.empno, e.ename, e.deptno, d.dname, e.job, d.loc
										   FROM emp e, dept d
                                           WHERE e.deptno = d.deptno);

 

뷰의 특징

-뷰는 공간차지를 하지 않는다. 

-테이블과 동기화되어 수정되면 뷰도 수정된다. 

-보안상의 이유로 사용된다. 

-성능상의 이유로 사용된다. 

 

직업이 CLERK이거나 MANAGER이고, LOC은 DALLAS이고, empno/ename/job/dname/loc을 출력하시오. 단 인라인뷰를 사용하시오

--직업이 CLERK이거나 MANAGER이고, LOC은 DALLAS이고, empno/ename/job/dname/loc을 출력하시오. 단 인라인뷰를 사용하시오

SELECT *
FROM (SELECT * FROM emp WHERE job IN ('CLERK','MANAGER')) e, --7
	 (SELECT * FROM dept WHERE loc = 'DALLAS')d --1
WHERE e.deptno = d.deptno;

SELECT empno,ename,job,dname,loc
FROM (SELECT *
	  FROM emp, dept
      WHERE emp.deptno = dept.deptno AND
      		job IN ('CLERK','MANAGER')AND
            loc = 'DALLAS');

 

SELECT절 서브쿼리 : PIVOT(행,열 자리바꿈)효과 정산 or 회원 정보를 알아볼 때 자주 쓰인다.
7369 사원의 이름
7499 사원의 직업

-- SELECT절 서브쿼리 : PIVOT(행,열 자리바꿈)효과 정산 or 회원 정보를 알아볼 때 자주 쓰인다.
--7369 사원의 이름
--7499 사원의 직업

SELECT (SELECT ename
		FROM emp
        WHERE empno = 7369) as ename, (SELECT job
        							   FROM emp
                                       WHERE empno = 7499) as job
FROM dual;

 

 

문제

-사원번호가 7499인 사원의 직업, 부서번호와 일치하는 사원의 정보를 출력하라

SELECT *
FROM emp
WHERE (job,deptno) = (SELECT job,deptno
					  FROM emp
                      WHERE empno = 7499);

 

-10번 부서의 사원들과 같은 월급을 받는 사원들의 이름, 월급, 부서번호를 출력하라

SELECT ename, sal, deptno
FROM emp
WHERE sal IN (SELECT sal
			  FROM emp
              WHERE deptno = 10);

 

-커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호를 출력하라.

커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호를 출력하라.
SELECT ename, sal, deptno
FROM emp
WHERE (deptno,sal) IN (SELECT deptno,sal
					   FROM emp
                       WHERE comm IS NOT NULL OR
                       comm>0);
 
 SELECT ename, sal, deptno
 FROM emp
 WHERE (deptno,sal) IN (SELECT deptno,sal
 						FROM emp
                        WHERE NVL(comm,0)!=0);

 

-커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호, 부서이름을 출력하라.

-커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호, 부서이름을 출력하라.
SELECT e.ename, e.sal, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND
	  (d.deptno, e.sal) IN (SELECT deptno, sal
      						FROM emp
                            WHERE NVL(comm,0)!=0);

 

 

-MGR이 'KING'인 모든 사원의 이름과 급여를 출력하라

SELECT ename, sal
FROM (SELECT *
	  FROM emp
      WHERE mgr IN (SELECT emp.empno
      				FROM emp
                    WHERE ename='KING'));

 

현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하시오
대한민국 국내 포밋 YYYY/MM/DD HH24:MI:SS

SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI:SS') as 현재시간,
	   TO_CHAR(sysdate+1/24,'YYYY/MM/DD HH24:MI:SS') as 한시간후
FROM dual;