[oracle] 연습1

EMP 테이블

DEPT 테이블

SALGRADE 테이블

-- 1. EMP TABLE 에서 이름, 급여, 커미션 금액,
-- 총액 (SAL + COMM) 을 구하여 총액이 많은 순서로 출력하라. 
-- 단 커미션이 NULL인 사람은 제외한다.
select ename 이름, sal 급여, nvl(COMM, 0) 커미션금액,
       sal+nvl(COMM, 0) 총액
from emp
order by 총액;
 
 
-- 2. EMP와 DEPT TABLE 을
-- inner JOIN 하여 부서번호, 부서명, 이름, 급여를 출력하라.
select d.DEPTNO, dname, ENAME, SAL
from DEPT d, EMP e
    where d.DEPTNO = e.DEPTNO;
 
 
--Ansi SQL (표준 SQL)
 
-- 3. EMP 테이블에서
-- 사원번호가 7521인 사원과 업무가 같고,
-- 급여가 7934인 사원보다 많은 사원의
-- 사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
-- where 단일행 subquery
select empno, ename, job, HIREDATE, SAL
from emp
    where job = (select job from emp where EMPNO='7521')
        and sal > (select sal from emp where EMPNO='7934');
 
 
-- 4. EMP 테이블에서 평균급여보다 적은 급여를 받는 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하라.
-- 단일행 subquery
select  avg(sal) from emp;
 
select EMPNO, ename, job, sal, d.deptno
from emp e, dept d
where e.DEPTNO = d.DEPTNO
 
-- 5. EMP TABLE 에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과 같이 출력하라.
-- SMITH의 매니저는 FORD이다.
-- || (oracle) , concat( mysql )
-- 5.1 (self join) 같은 emp 테이블 2개를 조인합니다.
select e.ENAME || '의 매니저는 ' || m.ENAME || '이다'
from EMP e, EMP m
where e.MGR = m.EMPNO;
 
-- 5.2 inline view
select e.ename || '의 매니저는 ' || m.ENAME || '이다'
from emp e, (select  * from emp)  m
where e.MGR = m.EMPNO;
 
 
--6. ALLEN의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.
-- where 단일행 subquery
select ename, DEPTNO, SAL, job from EMP
where job = (select job from emp where ename = 'ALLEN');
 
--7. EMP 테이블에서 SALES 부서 사원의 이름,업무,부서번호를 출력하는 SELECT문을 작성하시오.
--7.1 Nested Query
select ename, job, e.DEPTNO
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO and d.DNAME = 'SALES';
 
--7.2 Inline View
select  ename, JOB, DEPTNO
from emp
where deptno = (select DEPTNO from DEPT where DNAME='SALES');
 
select DEPTNO from DEPT where DNAME='SALES';
 
select empno, job, d.deptno
from emp e, (select DEPTNO, dname from dept where DNAME='SALES') d
where d.DEPTNO= e.DEPTNO;
 
--8. EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 사원에 대해
-- 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오.
-- 단 사원번호 순으로 출력하여라.
-- where in 다중행 subquery
select EMPNO, ename, SAL
    from emp
where ENAME like '%T%' order by  EMPNO;
 
select empno, ename, sal from emp
where deptno in (select deptno from emp where ename like '%T%');
 
-- 9. 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하라.
-- where 단일행 subquery
select EMPNO, ENAME, dname, to_char(HIREDATE, 'yyyy-mm-dd'), LOC, SAL
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO and SAL > (select round(avg(sal)) from emp);
 
select round(avg(sal)) from emp;
 
 
-- 10. EMP 테이블에서 관리자중에서 부하직원을 2명이상 관리하는 관리자의 이름을 출력하세요.
-- select mgr,count(*) as emp_cnt
--     from emp
--     group by mgr having emp_cnt >= 2;
 
-- 10.1 Nested Query (where 절에 subquery)
select ENAME
from emp
where EMPNO in (select mgr from emp group by mgr having count(*)>=2);
 
-- 해당 조건을 만족하는 매니저가 여러 명있기 때문에 where in으로 해야 한다.
select mgr from emp group by mgr having count(*)>=2;
 
-- 10.2 Inline View (from 절에 subquery)
select e.ename
from emp e, (select mgr from EMP group by mgr having count(*)>=2 ) m
where e.EMPNO = m.MGR;
 
 
-- 11.EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
select ENAME, JOB
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO and LOC = 'CHICAGO';
 
 
-- 11.1.1 Nested Query ( where 절에 in 구문에  subquery 대입 )
select  ename, JOB
from emp e
where e.DEPTNO in (select DEPTNO from DEPT where loc = 'CHICAGO');
 
-- 11.2 Inline View
select ename, JOB
from emp e1, (select DEPTNO from dept where loc = 'CHICAGO') e2
where e1.DEPTNO = e2.DEPTNO;
 
select a.deptno, a.loc, b.job
from dept a, emp b
where a.DEPTNO= b.DEPTNO and a.loc='CHICAGO';
 
-- 12.EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성하시오.
-- 단 업무별, 월급이 많은 순으로 출력하여라.
-- where 단일행 subquery
select ename, job, DEPTNo, sal
from EMP
where job = (select job from emp where ENAME='JONES')
   or sal >= (select sal from emp where ENAME='FORD')
order by job, sal desc;
 
 
-- order by sal;
-- 13. EMP 테이블에서 업무별로 최소 급여를 받는 사원의
-- 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라.
 
-- inline view
select EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
from EMP
where SAL in (select min(sal) from emp group by JOB);
 
-- 14. emp와 dept 테이블에서 업무가 manager인 사원의 이름, 업무, 부서명, 근무지를 출력하여라.
-- Inline View 를 사용하자
select ename, job, DNAME, LOC
from emp e, dept d
where e.DEPTNO=e.DEPTNO and e.job='MANAGER';
 
select ename, job, DNAME, LOC
from (select ename, job, DEPTNO
    from emp where job= 'MANAGER') e,
     dept d
where e.DEPTNO = d.DEPTNO;
 
-- 15. EMP 테이블에서 30번 부서원 중 최저급여를 받는 사원을 제외한
-- 나머지 사원들의 모든 정보를 출력하는 SELECT문을 작성하시오.
select *
from emp
where DEPTNO = '30'
  and sal not in
      (select min(sal) from emp group by DEPTNO);
 
-- # Multi Column Subquery
 
-- # 16. EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를
-- 출력하는 SELECT문을 작성하시오.(말단사원: 다른 사원을 관리하지 않는 사원)
-- # - ORACLE : NVL(VALUE1, VALUE2)
-- # - MSSQL  : ISNULL(VALUE1, VALUE2)
-- # - MYSQL  : IFNULL(VALUE1, VALUE2)
select EMPNO, ename , job, DEPTNO
from emp
where HIREDATE in
      (select max(HIREDATE) from emp group by JOB);
 
 
-- # 17. EMP 테이블에서
-- 사원번호, 이름, 업무, 급여, 급여의 등급을 출력하되
-- 3등급 이상인 사원의 정보만을 출력하세요.
-- # (emp와 salgrade 테이블을 이용);
select EMPNO, ename, job, sal, grade
from emp, SALGRADE
where sal between LOSAL and HISAL and GRADE >= 3;
 
 
-- # 18. 부서번호, 부서에 속한
-- 직원수, 부서명, 도시명을 출력하세요.
-- # 직원수가 5명이상인 부서만 출력하세요. ( inline view )
select totalEMP, dname, LOC
from DEPT,
     (select count(*) totalEMP
     from emp
     group by DEPTNO)
where totalEMP >= 5;
 
 
-- # 19. EMP 테이블에서
-- 적어도 한 명 이상으로부터
-- 보고를 받을 수 있는
-- 사원의 업무,이름,사원번호,부서번호를 출력하시오.
-- # (즉 관리자를 출력하세요)
select job, ename, DEPTNO
from emp
where EMPNO in
      (select mgr
      from emp
      group by mgr
      having count(*)>=1)