10. Oracle 중간연습문제

26> 사원이름 및 사원번호, 해당 관리자이름 및 관리자 번호를 출력하되, 각 컬럼명을 employee,emp#,manager,mgr#으로 표시하여 출력하라.

SELECT s.ename "employee", s.empno "emp#", e.ename "manager", s.mgr "mgr#"
FROM emp s, emp e
WHERE s.mgr = e.empno;

27> 모든 사원의 이름,직업,부서이름,급여 및 등급을 출력하라.

SELECT e.ename, e.job, d.dname, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

28> Smith보다 늦게 입사한 사원의 이름 및 입사일을 출력하라.

SELECT ename, hiredate
FROM emp
WHERE hiredate > (SELECT hiredate FROM emp WHERE ename =  'SMITH');

29> 자신의 관리자보다 먼저 입사한 모든 사원의 이름, 입사일, 관리자의 이름, 관리자의 입사일을 출력하되 각각 컬럼명을 Employee,EmpHiredate, Manager,MgrHiredate로 표시하여 출력하라.

SELECT e.ename, e.hiredate, s.ename, s.hiredate
FROM emp e, emp s
WHERE e.mgr = s.empno AND e.hiredate  < s.hiredate;

30> 모든 사원의 급여 최고액,최저액,총액 및 평균액을 출력하되 각 컬럼명을 Maximum, Minimum, Sum, Average로 지정하여 출력하라.

SELECT MAX(sal) "Maximum", MIN(sal) "Minimum", SUM(sal) "Sum", AVG(sal) "Average"
FROM emp;

  
31> 각 직업별로 급여 최저액.최고액,총액 및 평균액을 출력하라.

SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal)
FROM emp
GROUP BY job;

 32> 직업이 동일한 사람 수를 직업과 같이 출력하라.

SELECT COUNT(job), job
FROM emp
GROUP BY job;

  
33> 관리자의 수를 출력하되, 관리자 번호가 중복되지 않게하라. 그리고, 컬럼명을 Number of Manager로 지정하여 출력하라.

SELECT COUNT(DISTINCT(mgr)) "Number of Manager"
FROM emp;

  
34> 최고 급여와 최저 급여의 차액을 출력하라.

SELECT (MAX(sal) - MIN(sal))
FROM emp;

  
35> 관리자 번호 및 해당 관리자에 속한 사원들의 최저 급여를 출력하라. (단, 관리자가 없는 사원 및 최저 급여가 1000 미만인 그룹은 제외시키고 급여를 기준으로 출력 결과를 내림차순으로 정렬하라.)

SELECT mgr, MIN(sal)
FROM emp
WHERE mgr IS NOT NULL
GROUP BY mgr
HAVING MIN(sal)>=1000
ORDER BY MIN(sal);

36> 부서별로 부서이름, 부서위치, 사원 수 및 평균 급여를 출력하라. 그리고 각각의 컬럼명을 부서명,위치,사원의 수,평균급여로 표시하라.

SELECT e.deptno, d.dname, d.loc, COUNT(e.empno), AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname, e.deptno, d.loc;

37> Smith와 동일한 부서에 속한 모든 사원의 이름 및 입사일을 출력하라. 단, Smith는 제외하고 출력하시오

SELECT ename, hiredate
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH')
AND ename != 'SMITH';

38> 자신의 급여가 평균 급여보다 많은 모든 사원의 사원 번호, 이름, 급여를 표시하는 질의를 작성하고 급여를 기준으로 결과를 내림차순으로 정렬하라.

SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;

  
 39> 이름에 T가 들어가는 사원의 속한 부서에서 근무하는 모든 사원의 사원번호 및 이름을 출력하라.

SELECT empno, ename
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%');

  
40> 부서위치가 Dallas인 모든 사원의 이름,부서번호 및 직위를 출력하라.

SELECT ename, deptno, job
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS');

* 다른 방법

SELECT E.ENAME, E.DEPTNO, E.JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND D.LOC='DALLAS';

  
41> KING에게 보고하는 모든 사원의 이름과 급여를 출력하라.

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

  
42> Sales 부서의 모든 사원에 대한 부서번호, 이름 및 직위를 출력하라.

SELECT d.deptno, e.ename, e.job
FROM emp e, dept d
WHERE d.dname = 'SALES';

  
43> 자신의 급여가 평균 급여보다 많고 이름에 T가 들어가는 사원과 동일한 부서에 근무하는 모든 사원의 사원 번호, 이름 및 급여를 출력하라.

SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
AND deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%');

  
44> 커미션을 받는 사원과 급여가 일치하는 사원의 이름,부서번호,급여를 출력하라.

SELECT ename, deptno, sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE comm IS NOT NULL);

45> Dallas에서 근무하는 사원과 직업이 일치하는 사원의 이름,부서이름, 및 급여를 출력하시오

SELECT e.ename, d.dname, e.sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.job IN (SELECT e.job FROM emp e, dept d WHERE e.deptno = d.deptno AND d.loc = 'DALLAS');

46> Scott과 동일한 급여 및 커미션을 받는 모든 사원의 이름, 입사일 및 급여를 출력하시오

SELECT ename, hiredate, sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE ename = 'SCOTT')
AND NVL (comm,0) IN (SELECT NVL(comm,0) FROM emp WHERE ename = 'SCOTT');

47> 직업이 Clerk 인 사원들보다 더 많은 급여를 받는 사원의 사원번호, 이름, 급여를 출력하되, 결과를 급여가 높은 순으로 정렬하라.

SELECT empno, ename, sal
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE job = 'CLERK')
ORDER BY sal DESC;

48> 이름에 A가 들어가는 사원과 같은 직업을 가진 사원의 이름과 월급, 부서번호를 출력하라.

SELECT ename, sal, deptno
FROM emp
WHERE job IN (SELECT job FROM emp WHERE ename LIKE '%A%');

49> New  York 에서 근무하는 사원과 급여 및 커미션이 같은 사원의 사원이름과 부서명을 출력하라.

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.sal IN (SELECT e.sal FROM dept d WHERE d.loc = 'NEW YORK')
AND NVL(comm,0) IN (SELECT NVL(comm,0) FROM emp e WHERE d.loc = 'NEW YORK');

50> Dallas에서 근무하는 사원과 직업 및 관리자가 같은 사원의 사원번호,사원이름, 직업,월급,부서명,커미션을 출력하되 커미션이 책정되지 않은 사원은 NoCommission으로 표시하고, 커미션의 컬럼명은 Comm으로 나오게 출력하시오. (단, 최고월급부터 출력되게 하시오)

SELECT e.empno, e.ename, e.job, e.sal, d.dname, NVL((TO_CHAR(e.comm)), 'NoCommission') "Comm"
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job IN (SELECT job FROM emp e, dept d WHERE e.deptno = d.deptno AND loc='DALLAS')
AND mgr IN (SELECT mgr FROM emp e, dept d WHERE e.deptno = d.deptno AND loc='DALLAS')
ORDER BY sal DESC;