다중컬럼 서브쿼리

<다중 컬럼 서브쿼리> : 여러 개의 컬럼을 비교

ex1 > 30번 부서에 있는 임의의 사원의 급여, 보너스와 일치하는 사원의 정보 출력
select * from emp where sal in (select sal from emp where deptno=30)
and nvl(comm,-1) in(select nvl(comm, -1) from emp where deptno=30); --6명 나온다.

 

ex2 > 부서별 부서명과 최초입사자의 이름과 최후입사자의 이름을 출력하시오.
select deptno, (select ename from emp e where e.deptno=dept.deptno and hiredate=(select min(hiredate)from emp where emp.deptno=e.deptno)) 최초입사자,
(select ename from emp e where e.deptno=dept.deptno and hiredate = (select max(hiredate) from emp where emp.deptno=e.deptno)) 최후입사자 from dept;

 

<pairewise 서브쿼리 vs unpairwise 서브쿼리> 

ex> 부서번호가 30번인 사원들과 월급이 같고 커미션도 같은 사원들의 이름과 월급과 커미션과 부서번호를 출력

* non pairwise
select ename, sal, comm, deptno from emp where sal in (select sal from emp where deptno=30) and comm in(select comm from emp where deptno=30);
* pairwise
select ename, sal, comm, deptno from emp where (sal, comm) in (select sal, comm from emp where deptno =30);

 

ex>  각 job별로 최소 급여를 받는 사원의 정보를 서브 페어와이즈 서브쿼리로 출력하시오.
select * from emp where (job, sal) in (select job, min(sal) from emp group by job);

 

 

--1. 직업이 SALESMAN인 사원들중에서의 최대급여보다 더 많은 급여를  받는 사원들의 이름과 월급을 출력하시오
select ename, sal from emp where sal >(select max(sal) from emp where job = 'SALESMAN'); 

 

--2. 30번 부서 사원의 comm과 일치하는 커미션을 받는 사원의 정보 출력
select * from emp where comm in (select comm from emp where deptno=30); 

 

--3. 30번 부서 사원의 comm과 일치하지 않는 커미션을 받는 사원의 정보 출력
  -- in을 사용할 때는 null이 포함되어 있어도 결과 출력에 영향 없다.
  -- not in의 경우 ()에 널 포함되면 안 나온다.
select * from emp where comm not in(select nvl(comm,0) from emp where deptno=30); 

 

--4. 관리자가 아닌 사원을 출력(not in 사용)
select * from emp where empno not in (select nvl(mgr,0) from emp); 

 

--5. 관리자가 아닌 사원을 출력(all 또는 any 사용)
select * from emp where empno != all(select nvl(mgr,0) from emp); 

 

--6. 관리자인 사원을 출력(in을 사용한 다음 all로 변경)
select * from emp where empno in (select mgr from emp);
select * from emp where empno = any(select mgr from emp); 

 

--7. 부서별 기본급이 가장 높은 사람 출력. (이름, 부서, 기본급)
select name, buseo, basicpay from insa where (buseo, basicpay) in (select buseo, max(basicpay) from insa group by buseo); 

 

--8. 부서번호가 30번인 사원들과 월급이 같고 커미션도 같은 사원들의 이름과 월급과 커미션과 부서번호를 non pairwise로 출력
select ename, sal, comm, deptno from emp where sal in (select sal from emp where deptno=30) and comm in(select comm from emp where deptno=30); 

 

--9. 위 예제를 pairwise로 출력
select ename, sal, comm, deptno from emp where (sal, comm) in (select sal, comm from emp where deptno =30); 

 

--10. 사원 테이블에서 채시라와 직급이 같거나 급여가 같은 사람을 출력
select * from sawon where SAJOB in(select sajob from sawon where saname = '채시라') or sapay in(select sapay from sawon where saname = '채시라'); 

 

--11. 스칼라 서브 쿼리를 이용해 first_name, 부서명, 직책명을 출력하시오
select first_name, (select department_name from departments d where d.DEPARTMENT_ID=e.DEPARTMENT_ID), job_id from employees e; 

 

--12 스칼라 서브 쿼리를 이용해 first_name, 부서명, 직책명을 출력하되 부서명이 없는 사원은 제외하시오
select first_name, (select department_name from departments d where d.DEPARTMENT_ID = e.DEPARTMENT_ID), job_id from employees e
where (select department_name from departments d where d.DEPARTMENT_ID = e.DEPARTMENT_ID) is not null; 

 

--13. 스칼라 서브 쿼리를 이용해 부서명, 인원수, 급여평균을 출력하시오
select (select dname from dept d where d.deptno=e.deptno), count(*), avg(sal) from emp e group by e.deptno;   

 

--14. 사원 테이블에서 채시라와 직급과 급여가 같은 사원을 출력
select * from sawon where (sajob, SAPAY) in(select sajob, sapay from sawon where saname='채시라'); 

 

--15. 사원 테이블에서 부장이면서 관리하는 사원이 없는 사람의 정보 출력
select * from sawon where sajob='부장' and (select count(*) from sawon s where s.SAMGR = sawon.sabun)=0; 

 

--16. 자신의 관리자 이상의 급여를 받는 사원의 first_name, 급여를 출력
select first_name, salary from employees where salary >= (select salary from employees s where s.EMPLOYEE_ID=employees.MANAGER_ID); 

 

--17. 급여등급이 2등급인 사원들을 대상으로 부서명, 평균급여, 인원수를 출력
select (select dname from dept where dept.deptno=emp.deptno) 부서명, avg(sal), count(*) from emp, salgrade s where sal between losal and hisal group by emp.deptno; 

 

--18. 아래의 pairwise 방식의 SQL을 non pairwise 방식으로 수정
select ename, sal, comm, deptno from  emp where  ( sal, nvl(comm,0) ) in  ( select  sal, nvl(comm,0)from emp where deptno = 20 );
select ename, sal, comm, deptno from emp where sal in(select sal from emp where deptno = 20) and nvl(comm,0) in (select nvl(comm,0) from emp where deptno = 20); 

 

--19. student에서 각 학년별로 최대 키를 가진 학생들의 학년과 이름과 키를 출력
select grade, name, height from student where (grade, height) in (select grade, max(height) from student group by grade);

 

 

 

 

* update 와 rollback

update emp set sal = 1500 where empno=7934;  --7934번 MILLER의 급여를 1500으로 변경
rollback; -- update 하기 전 상태로 되돌림.