151006 DB 함수 연습과제

1 이름의 첫 글자가 k 보다 크고 y 보다 작은 직원의 이름, 부서, 업무를 조회 / 단 이름순으로 정렬

SELECT ename, deptno, job from emp where ename >'K%' and ename<'Y%' order by ename;

2. 오늘부터 12월 25일 까지 몇일 남았는가

select sysdate - To_Date('2015/12/25') from dual;

3. 모든 직원이 현재까지 근무한 근무 일수를 몇주 몇일로 출력하시오. / 단 근무일수가 많은 사람순으로 조회

select ename, hiredate, trunc((sysdate-hiredate)/7)weeks, round (mod((sysdate-hiredate),7),0) days from emp order by sysdate - hiredate desc;

4. 10번 부서 직원들에 한해서 현재까지의 근무개월수를 조회하시오.

select ename, hiredate, sysdate,trunc(months_between(sysdate,hiredate),0)  from emp where deptno =10 order by months_between(sysdate,hiredate)desc;

5. 20번 부서 직원들에 한해서 입사일자로 부터 5개월이 지난 후의 날짜를 조회

select ename, deptno, add_months(hiredate,5) from emp;

6. 모든 직원에 대해 입사한 달의 근무일수를 조회

select empno, ename, hiredate, last_day(hiredate)-hiredate from emp order by last_day(hiredate)-hiredate desc;

7. 현재 급여에 15%가 증가된 급여를 계산하여 사번, 이름, 업무, 급여, 증가된급여 를 조회

select empno, ename, job, sal, sal*1.15 from emp;

8.이름, 입사일, 입사일로부터 현재까지의 근무개월수, 급여, 급여총계를 조회

select ename, hiredate, sysdate,trunc(months_between(sysdate,hiredate)) TMonths, sal, sal*trunc(months_between(sysdate,hiredate)) Tsal from emp ;

9, 업무가 analyst 면 급여를 10% 증가시키고, clerk 면 15% 증가, manager 면 20% 증가시켜서 이름, 업무, 급여, 증가된 급여를 조회

select ename, job, sal, decode( job , 'ANALYST' , sal*1.10, 'CLERK',sal*1.15,'MANAGER',sal*1.20 ) 증가된급여 from emp;

10. 부서별로 급여평군, 최고급여를 조회하는데, 단 급여평균이 높은 순으로 조회하고 급여 평균이 2000이상인 부서만 조회

select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal)>=2000 order by avg(sal) desc; 

11. 같은 업무 내에서 부서별 평균급여, 최고급여, 인원수를 조회

select deptno, avg(sal), max(sal), count(*) from emp group by deptno;

12. 인원수, 보너스에 null 이 아닌 인원수, 보너스의 평균 ( 보너스가 null 이 아닌 평균, 널을 포함한 평균)

등록되어있는 부서의 수 ( 중복제외 ) 를 구하여 조회

select count(distinct(deptno)) 부서수, count(*) 인원수, count(comm) 보너스받는놈수, avg(comm) 받는놈평균보너스, avg(nvl(comm,0)) 안받는놈포함평균보너스  from emp;

13. 부서인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 조회

select deptno, count(empno), sum(sal) from emp having count(empno)>4 group by deptno ;

14. 급여가 최대 2000 이상인 부서에 대해 부서번호, 평균급여, 급여의 합 을 조회

select deptno, avg(sal), sum(sal), max(sal)  from emp having max(sal)>2000 group by deptno;

15. 최고급여와 최소급여의 차이는 얼마인가

select max(sal)-min(sal)&최소급여 from emp ;

select max(sal)-min(sal)급여차이 from emp ;

16 예시)

년도 count min max avg sum

-----------------------------------------------

80 1 800 800 800 800

81 10 950 5000 2282.5 22825

82 2 1300 3000 2150 4300

83 1 1100 1100 1100 1100

select to_char(hiredate,'YY')년도,count(empno)count, min(sal)min, max(sal)max, avg(sal)avg, sum(sal)sum from emp group by to_char(hiredate,'YY') order by to_char(hiredate,'YY');

17 예시)

total 1980 1981 1982 1983

----------------------------------------

14 1 10 2 1

select count(empno)Total, count(decode(to_char(hiredate,'YYYY'),1980,1980)) "1980", count(decode(to_char(hiredate,'YYYY'),1981,1981)) "1981" , count(decode(to_char(hiredate,'YYYY'),1982,1982)) "1982", count(decode(to_char(hiredate,'YYYY'),1983,1983)) "1983" from emp;  

18 예시)

업무  10 20 30 total

analyst 6000 6000

clerk 1300 1900 950 4150

manager 2450 2975 2850 8275

president 5000 5000

saalesman 5600 5600

select job, sum(decode(deptno,10,sal))"10",sum(decode(deptno,20,sal))"20",sum(decode(deptno,30,sal))"30",sum(sal)Total from emp group by job order by job;