쿼리 실습(1)

--1. 덧셈연산자를 이용하여 모든 사원에 대해서 $300의 급여 인상을 계산한 후 사원의 이름, 급여, 인상된 급여를 출력하시오.
select ename 성명, sal 급여 ,sal+300 as 인상급여
from emp;

--2. 사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오
--, 연간 총수입은 월급에 12를 곱한 후 $100의 상여금을 더해서 계산하시오.
select ename 성명, sal 급여, (sal*12)+100 총수입
from emp
order by 3 desc
;

--3. 급여가 2000을 넘는 사원의 이름과 급여를 표현, 급여가 많은 것부터 작은 순으로 출력하시오.
select ename 성명, sal 급여
from emp
where sal>2000
order by sal desc 
;

--4. 사원번호가 7788인 사원의 이름과 부서번호를 출력하시오.
select deptno 부서번호, ename 성명 
from emp
where empno = 7788
;

--5. 급여가 2000에서 3000 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.
select ename 성명, sal 급여
from emp
where not (sal between 2000 and 3000)
;

--6. 1981년 2월 20일 부터 1981년 5월 1일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.
select ename 성명, job 담당업무, hiredate 입사일
from emp
where hiredate between '810220' and '810501'
;

--7. 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 영문자순으로 출력하시오.
select ename 이름, deptno 부서번호
from emp
where deptno >= 20 and deptno <= 30
order by 1 desc
;

--8. 사원의 급여가 2000에서 3000사이에 포함되고 부서번호가 20 또는 30인 사원의 이름, 
--급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.
select ename 성명, sal 급여, deptno 부서번호
from emp
where sal between 2000 and 3000 
and deptno in (20,30)
order by 1
;

--9. 1981년도에 입사한 사원의 이름과 입사일을 출력하시오. (like 연산자와 와일드카드 사용)
select ename 성명, hiredate 입사일
from emp
where hiredate like '81%'
;
 

--10. 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.
select ename 성명, job 담당업무
from emp
where mgr is null
; 

--11. 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되 
--급여 및 커미션을 기준으로 내림차순 정렬하여 표시하시오.
select ename 성명, sal 급여, comm 커미션
from emp
where not (comm is null or comm = 0)
order by 2 desc, 3 desc
;

--12. 이름의 세번째 문자가 R인 사원의 이름을 표시하시오.
select ename 성명
from emp
where ename like ('__R%')
;

--13. 이름에 A와 E를 모두 포함하고 있는 사원의 이름을 표시하시오.
select ename 성명
from emp
where ename like '%A%'
and ename like '%E%'
;

--14. 담당업무가 CLERK, 또는 SALESMAN이면서 
--급여가 $1600, $950 또는 $1300이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.
select ename 성명, job 담당업무, sal 급여
from emp
where job in ('CLERK','SALESMAN')
and sal not in (1600,950,1300)
;

--15. 커미션이 $500 이상인 사원의 이름과 급여 및 커미션을 출력하시오.
select ename 성명, sal 급여, comm 커미션
from emp
where comm >= 500
;

--16. SUBSTR 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.
select ename 성명,substr(hiredate,1,2) 입사년도, substr(hiredate,4,2) 입사월
from emp
;

--17. SUBSTR 함수를 사용하여 4월에 입사한 사원을 출력하시오.
select ename 성명, substr(hiredate,4,2) 입사월
from emp
where substr(hiredate,4,2) in ('04');
;

--18. MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.
select ename 성명, empno 사원번호
from emp
where mod(empno,2) = 0
;

--19. 입사일을 년도는 2자리(YY), 월은 숫자(MON)로 표시하고 요일은 약어 (DY)로 지정하여 출력하시오.
select hiredate 입사일, to_char(hiredate,'YY/MON/DD DY') 입사일2
from emp
;

--20. 올해 몇 칠이 지났는지 출력하시오. 
--현재날짜에서 올해 1월 1일을 뺀 결과를 출력하고 TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
select trunc(sysdate-to_date('2018/01/01','YYYY/MM/DD')) days
from dual;

--21. 사원들의 상관 사번을 출력하되 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
select e1.ename 사원명, nvl(e1.mgr,0) 상관번호, nvl(e2.ename,0) 상관명
from emp e1, emp e2
where e1.mgr = e2.empno(+)
order by 2
;

--22. DECODE 함수로 직급에 따라 급여를 인상하도록 하시오. 
--직급이 ‘ANALIST'인 사원은 200, ‘SALESMAN’인 사원은 180, 
--‘MANAGER’인 사원은 150, ‘CLERK”인 사원은 100을 인상하시오.
select ename 성명, job 담당업무, sal 급여,
        decode(job,'ANALYST',sal+200,
                    'SALESMAN',sal+180,
                    'MANAGER',sal+150,
                    'CLERK', sal+100,
                    'PRESIDENT', sal+0
                    ) 인상급여
from emp              
;

--23. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. 평균에 대해서는 정수로 반올림하시오.
select max(sal) 최고액, min(sal) 최저액, sum(sal) 총액, round(avg(sal),0) 평균
from emp
;

--24. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균 액을 출력하시오. 평균에 대해서는 정수로 반올림 하시오.
select job 담당업무 , max(sal) 최고급여, min(sal) 최저급여, sum(sal) 급여총합, round(avg(sal),0) 평균급여
from emp
group by job
;

--25. COUNT(*) 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.
select job 담당업무, count(*) "사원 수"
from emp 
group by job
order by 2 desc
;

--26. 관리자 수를 나열하시오.
select job 담당업무 ,count(*) 관리자수
from emp
where job = 'MANAGER'
group by job
;

--27. 급여 최고액, 급여 최저액의 차액을 출력하시오.
select max(sal)-min(sal) 차액
from emp
;

--28. 직급별 사원의 최저 급여를 출력하시오. 
--관리자를 알 수 없는 사원의 최저 급여가 2000 미만인 그룹은 제외시키고 
--결과를 급여에 대한 내림차순으로 정렬하여 출력하시오.
select job 담당업무, min(sal) 최저급여
from emp
where mgr is not null
group by job
having min(sal) >= 2000
order by 2 desc
;

--28-2. 직급별 사원의 최저 급여를 출력하시오. 
--관리자를 알 수 없는 사원의 최저 급여가 2000 미만인 그룹은 제외시키고 
--결과를 급여에 대한 내림차순으로 정렬하여 출력하시오.
select
from emp
where sal <
(select min(sal)
from emp
where mgr is null
group by job
);

--29. 각 부서에 대해 부서번호, 사원 수, 부서 내의 모든 사원의 평균 급여를 출력하시오. 
--평균 급여는 소수점 둘째 자리로 반올림 하시오.
select e1.deptno 부서번호, e1.count 사원수, dept.dname 부서명, e1.avg 평균급여 
from 
(select deptno, count(*) as count ,round(avg(sal),2) as avg
from emp
group by deptno 
) e1, dept
where e1.deptno = dept.deptno
;

--30. 각 부서에 대해 부서번호 이름, 지역 명, 사원 수, 부서내의 모든 사원의 평균 급여를 출력하시오. 
--평균 급여는 정수로 반올림 하시오. DECODE 사용.
select   deptno
       , decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS') as 부서명
       , decode(deptno,10,'NEW YORK',20,'DALLAS',30,'CHICAGO',40,'BOSTON') as 지역명,count(*) 사원수
       , round(avg(sal),0) 평균급여
from emp
group by deptno
;

--31. 업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오. 
--별칭은 각 job, dno, 부서 10, 부서 20, 부서 30, 총액으로 지정하시오. ( hint. Decode, group by )
select job
, deptno dno
, decode(deptno,10,sum(sal)) as 부서10
, decode(deptno,20,sum(sal)) as 부서20
, decode(deptno,30,sum(sal)) as 부서30
, sum(sal) 총액
from emp
group by job,deptno
order by job,deptno 
;

--32. EQUI 조인을 사용하여 SCOTT 사원의 부서번호와 부서 이름을 출력하시오.
select d2.deptno 부서번호, d2.dname 부서명
from (
select deptno
from emp
where ename = 'SCOTT'
) d1, dept d2
where d1.deptno = d2.deptno 
;

--33. INNER JOIN과 ON 연산자를 사용하여 
--사원 이름과 함께 그 사원이 소속된 부서이름과 지역 명을 출력하시오.
select ename 성명, dname 부서명, loc 지역명
from
emp inner join dept
on emp.deptno = dept.deptno
;

--34. INNER JOIN과 USING 연산자를 사용하여 
--10번 부서에 속하는 모든 담당 담당 업무의 고유 목록(한 번씩만 표시)을 부서의 지역명을 포함하여 출력 하시오.
select distinct(job) 담당업무, deptno 부서번호, dname 부서명, loc 부서지역
from emp inner join dept using(deptno)
where deptno=10
;

--35. NATURAL JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서이름, 지역 명을 출력하시오.
select ename 성명, dname 부서명, loc 부서지역, comm 커미션액
from emp natural join dept
where not (comm is null or comm = 0)
order by 4 desc
;

--36. EQUI 조인과 WildCARD를 사용하여 
--이름에 ‘A’가 포함된 모든 사원의 이름과 부서명을 출력하시오.
select ename 성명, dname 부서명
from emp, dept
where emp.deptno = dept.deptno
and emp.ename like '%A%'
;

--37. NATUAL JOIN을 이용하여 NEW YORK에 근무하는 
--모든 사원의 이름, 업무, 부서번호 및 부서명을 출력하시오.
select ename 성명, dname 부서명, deptno 부서지역
from emp natural join dept
where dept.loc = 'NEW YORK'
;

--38. SELF JOIN을 사용하여 사원의 이름 및 사원번호를 관리자 번호와 함께 출력하시오.
select e2.empno 사원번호, e2.ename 사원명, e1.ename 관리자명, e1.empno 관리자번호
from emp e1, emp e2
where e1.empno(+) = e2.mgr
;

--39. OUTER JOIN, SELF JOIN을 사용하여 관리자가 없는 사원을 포함하여 
--사원번호를 기준으로 내림차순 정렬하여 출력하시오.
select e2.empno 사원번호, e2.ename 사원명, e2.mgr 관리자
from emp e1, emp e2
where e1.empno(+) = e2.mgr
order by e2.empno desc
;

--40. SELF JOIN을 사용하여 
--지정한 사원의 이름, 부서번호, 지정한 사원과 동일한 부서에서 
--근무하는 사원을 출력하시오. ( SCOTT )
select e2.empno 사원번호, e2.ename 사원명, e2.deptno 부서번호
from emp e1, emp e2
where e1.ename = 'SCOTT'
and e1.deptno = e2.deptno
and e1.empno = e2.empno
;

--41. SELF JOIN을 사용하여 WARD 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하시오.
select ename 사원명, hiredate 입사일
from emp
where hiredate > (select hiredate from emp where ename ='WARD') 
order by 2;

--42. SELF JOIN 을 사용하여 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 출력하시오.
select e1.ename 관리자, e1.hiredate 관리자입사일, e2.ename 사원명, e2.hiredate 사원입사일 
from
(select distinct e1.empno, e1.ename, e1.hiredate
from emp e1, emp e2
where e1.empno = e2.mgr) e1, emp e2
where e1.empno = e2.mgr
and e1.hiredate > e2.hiredate
;
 

--43. 사원 번호가 7788인 사원과 담당 업무가 같은 사원을 표시(사원 이름과 담당업무)하시오.
 select emp.empno 사원번호, emp.ename 사원명, emp.job 담당업무
 from
 (select job, empno
 from emp
 where empno = 7788) e1, emp
 where e1.job = emp.job
 ;

 -- 사원번호 7788인 사원 제외 결과 
 select empno 사원번호, ename 사원명, job 담당업무 
 from emp
 where job = (select job from emp where empno = 7788)
 and empno != 7788
 ;

--44. 사원번호가 7499인 사원보다 급여가 많은 사원을 표시하시오. 사원이름과 담당 업무
select ename 사원명, job 담당업무, sal 급여 
from emp
where sal > (select sal from emp where empno = 7499)
;

--45. 최소급여를 받는 사원의 이름, 담당업무 및 급여를 표시하시오. (그룹함수 사용)
select ename 성명, job 담당업무, sal 급여
from
(select ename, job, sal
from emp
group by ename, job, sal
order by 3) e1
where rownum=1
;
 

--46. 평균급여가 가장 적은 직급의 직급 이름과 직급의 평균을 구하시오.
select job 담당업무 ,sal 평균급여
from
(select job, avg(sal) as sal
from emp
group by job
order by 2)
where rownum=1
;
 

--47. 각 부서의 최소 급여를 받는 사원의 이름, 급여, 부서번호를 표시하시오.
select ename 성명, sal 급여, deptno 부서번호
from emp
where sal in 
(select min(sal)
from emp
group by deptno)
;
 

--48. 담당업무가 ANALYST 인 사원보다 급여가 적으면서 
--업무가 ANALYST가 아닌 사원들을 표시(사원번호, 이름, 담당 업무, 급여)하시오.
select e2.empno 사원번호, e2.ename 사원명, e2.job 담당업무, e2.sal 급여 
from
(select distinct(sal) sal
from emp
where job ='ANALYST'
) e1, emp e2
where e1.sal > e2.sal
and e2.job != ('ANALYST');

--49. 부하직원이 없는 사원의 이름을 표시하시오.
select ename 성명
from emp
where empno not in
(select distinct(mgr)
from emp
where mgr is not null)
;