사전문제 1) 가장 많은 사원을 관리하는 관리자의 이름
--1. 관리자별로 관리자의 이름과 관리하는 사원수를 출력
select m.ename, count(*) 사원수 from emp e, emp m where e.mgr=m.empno group by m.ename;
--2. 1번 쿼리를 인라인뷰로 해서 최대값을 출력
select max(사원수) from (select m.ename, count(*) 사원수 from emp e, emp m where e.mgr=m.empno group by m.ename);
--3. 2번 쿼리를 서브쿼리로 해서 관리하는 사원의 최대값
--4. 3번에 해당하는 관리자의 이름을 출력
--답 :
select ename from emp where ename =
(select m.ename from emp e, emp m where e.mgr=m.empno group by m.ename having count(*)= max(count(m.ename)));
사전문제 2)관리하는 직원이 두 명 이상인 관리자의 평균 연봉
select avg(avg(m.sal)) from emp e, emp m where e.mgr=m.empno group by m.ename having count(*)>=2;
<상관관계 서브쿼리>
: 메인쿼리에서 서브쿼리로 컬럼이 넘어가서 서브쿼리가 실행된 다음 실행결과가 메인쿼리의 조건으로 해석되는 쿼리
-- 자신의 직책의 평균 급여 이상의 급여를 받는 사원의 이름, 직책, 급여를 출력
select ename, job, sal from emp e where sal >= (select avg(sal) from emp where emp.JOB=e.job);
-- 상관관계 서브쿼리를 이용해서 부서별 최고 급여자의 정보를 출력
select * from emp e where sal = (select max(sal) from emp where e.deptno = emp.deptno);
-- 상관관계 서브쿼리를 이용해서 직책별 최저 급여자의 정보를 출력
select * from emp e where sal = (select min(sal) from emp where e.job = emp.job);
-- 이름, 부서명을 상관관계 서브쿼리로 출력
select ename, (select dname from dept where dept.deptno = emp.deptno) from emp;
* select 다음에 사용된 서브쿼리 : scalar 서브쿼리
-- 인사 테이블에서 남성의 평균급여 이상의 급열르 받는 남성 사원의 이름, 급여를 급여순으로 출력
-- 급여 : basicpay, 남성 substr(ssan, 8,1)
select name, basicpay from insa i where basicpay >= (select avg(basicpay) from insa where substr(ssn, 8,1)='1' and i.name = insa.name) order by basicpay;
-- 부서번호와 부서번호별 최저금여액, 부서번호별 최고급여액을 상관관계 서브쿼리로 출력
select buseo, (select min(basicpay) from insa where i.buseo=insa.BUSEO) 최저급여액, (select max(basicpay) from insa where i.buseo=insa.buseo) 최고급여액 from insa i;
<다중행 서브쿼리>
: 하나이상의 행을 리턴하는 서브 쿼리
select * from emp where sal >= (select min(sal) from emp group by deptno);
* 일반 연산자는 사용을 못하고 다중행 서브쿼리 연산자를 사용해야 한다 : in, any(or), all(and)
-- 부서별 최고급여중 max() 이상 : and
select * from emp where sal >= all(select max(sal) from emp group by deptno);
-- 부서별 최고급여중 min() 이상 : or
select * from emp where sal >= any(select max(sal) from emp group by deptno);
-- 관리자 역할을 수행하는 사원의 정보를 출력
select * from emp where empno in (select mgr from emp where mgr is not null);
* 집합연산자의 경우에는 is not null이 항상 붙어온다고 생각하자.
select * from emp where empno = any(select mgr from emp where mgr is not null);
-- 관리자 역할을 수행하지 않는 사원의 정보를 출력
select * from emp where empno not in (select mgr from emp where mgr is not null);
select * from emp where empno != all (select mgr from emp where mgr is not null);
-- 30번 부서의 최고 급여자 이상의 급여를 받는 사람을 출력. 집합 연산자를 사용할 것
select * from emp where sal >= all(select sal from emp where deptno=30);
-- 다중행 서브쿼리를 이용해 20번이나 30번 부서의 최소급여보다 많은 급여를 받는 사원의 정보 출력
select * from emp e where sal >= any(select sal from emp where deptno in (20, 30) and e.empno=emp.empno) ;
-- MANAGER 중 최소 급여자보다 많은 급여를 받는 사원의 사번, 이름, 급여를 출력. (다중행 서브쿼리)
select empno, ename, sal from emp e where sal >= all(select sal from emp where job='MANAGER' and e.empno=emp.empno);
-- comm 수령액이 최고액인 사원의 정보 출력(다중행 서브쿼리)
select * from emp e where comm >= all(select comm from emp where comm>0);
-- emp 에서 sal 가 최고인 사원을 제외한 나머지 사원의 정보 출력(다중행 서브쿼리)
select * from emp where sal < any(select sal from emp);
--1. 인사테이블에서 '이순애' 직원이상의 급여를 받는 직원 출력
select * from insa where basicpay >= (select basicpay from insa where name='이순애');
-- 2. 인사테이블에서 '이순애' 직원 이상의 급여를 받는 남자직원의 이름과 부서명을 출력
select name, buseo from insa where basicpay >= (select basicpay from insa where name='이순애') and substr(ssn,8,1)='1';
-- 3. 상관관계 서브쿼리를 사용해 소속 부서의 평균 급여보다 많은 급여를 받는 사원을 출력
select * from emp e where sal > (select avg(sal) from emp m where e.deptno= m.deptno);
select * from insa i where basicpay > (select avg(basicpay) from insa where i.buseo=insa.buseo);
-- 4. 최소 급여자의 급여를 초과하는 급여를 받는 사원의 사번, 이름, 직책, 급여를 출력. 다중행연산자 이용
select empno, ename, job, sal from emp where sal > any(select sal from emp);
-- 5. 인사에서 직원 전체 평균 급여보다 많은 급여를 받는 직원을 찾아 부서와 인원수를 출력
select buseo, count(num) from insa where basicpay > (select avg(basicpay) from insa) group by buseo;
-- 6. 부서번호가 30인 어떤 사원보다도 먼저 입사한 사원을 출력
select * from emp where hiredate < all(select hiredate from emp where deptno=30);
-- 7. emp2에서 과장 중 최소급여자 보다 많은 급여를 받는 사원의 이름, 직책, 급여를 출력.
-- 다중행 연산자를 사용하고 급여는 천단위 구분기호를 적용하시오.
select name, position, to_char(pay, '9,999,999,999') from emp2 where pay > any(select pay from emp2 where position='과장');
-- 8. 영업부의 최소 급여 초과, 최대 급여 미만의 급여를 받는 직원들의 이름, 부서명 출력. 다중행 연산자를 사용
select name, buseo from insa where basicpay > any(select basicpay from insa where buseo='영업부') and basicpay < any(select basicpay from insa where buseo='영업부') order by name;