240126 오라클 교육 day5

gununoo·2024년 1월 26일
2

DB

목록 보기
6/8
post-thumbnail
post-custom-banner

함수

SYSDATE

  • 실습1 
select sysdate 
from dual 
;

  • 실습2 
select trunc(sysdate - hiredate) as 근무일수 
from emp
;

months_between

  • 실습3 
select ename, sysdate, hiredate, 
    trunc(months_between(sysdate, hiredate)) as 근무개월
from emp;

  • 실습4 
-- 사원번호, 이름, 월급, 그리고 현재까지 누적 월급을 출력(comm 반영)
select empno, ename, sal, 
    (sal+NVL(comm,0))*trunc(months_between(sysdate, hiredate)) as 누적월급
from emp
;

add_months

  • 실습5
select ename, hiredate, add_months(hiredate, 6)
from emp;

next_day

  • 실습6
select next_day(sysdate, '수요일')
from dual
;

to_char

  • 실습7
select sysdate, to_char(sysdate, 'yyyy-mm-dd')
from dual
;

  • 실습8
select sysdate, to_char(sysdate, 'yyyy-mm-dd day HH24:MI:SS')
from dual
;

  • 실습9
select sysdate, to_char(sysdate, 'yyyy"년" mm"월 "dd"일" day HH24:MI:SS')
from dual
;

  • 실습10
select sysdate, to_char(sysdate, 'yyyy"년" mm"월 "dd"일" Q"분기" day HH24:MI:SS')
from dual
;

  • 실습11-1
    • to_char를 통해 월만 추출 
select ename, to_char(hiredate, 'MM')
from emp
;

  • 실습11-2
    • ltrim을 통해 왼쪽의 0 제거 
select ename, ltrim(to_char(hiredate, 'MM'), 0)
from emp
;

FM

  • 실습11-3
    • FM을 통해 왼쪽의 0 제거 
-- FM: to_char의 ltrim 빈문자, 0, 부호 제거
select ename, to_char(hiredate, 'FMMM')
from emp
;

  • 실습10-2
    • FM을 통해 왼쪽의 0 제거 
select sysdate, to_char(sysdate, 'FMyyyy"년" mm"월 "dd"일" Q"분기" day HH24:MI:SS')
from dual
;

to_date

  • 실습12
    • 산술연산 문자형을 날짜형으로 변환 
select sysdate-to_date('2008-01-01')
from emp
;

  • 실습13-1
    • 날짜형과 문자형 간의 비교연산 가능
    • 비교연산에서 형을 맞추는 습관 가지기 
select to_char(hiredate, 'yyyy-mm-dd hh24:mi')
from emp 
where hiredate <= to_date('1981-02-20', 'yyyy-mm-dd')
;

  • 실습13-2 
select to_char(hiredate, 'yyyy-mm-dd hh24:mi')
from emp 
where hiredate <= to_date('19810220', 'yyyymmdd')
;

  • 실습14
    • mgr이 null인 경우 ceo로 반환 
select empno, ename, NVL(mgr, 'CEO') 
from emp
where mgr is null
;
  • 에러 발생
  • mgr 컬럼의 값은 숫자형이나, 바꾸려는 'ceo'값은 문자형임
  • 해결 
select empno, ename, NVL(to_char(mgr), 'CEO') 
from emp
where mgr is null
;

Decode

  • 실습15 
select ename, deptno,
    decode(deptno, 
        10, 'ACCOUNTING',
        20, 'RESEARCH', 
        'ETC') as DNAME 
from emp
;

  • 실습16
select 
    to_char(hiredate, 'MM'),
    decode(ceil(to_number(to_char(hiredate, 'Q'))/2),
        1, '상반기',
        2, '하반기'
    ) as 반기 
from emp;

Case

  • 실습17
select ename, deptno,
    case 
        when deptno = 10 then 'ACCOUNTING'
        when deptno = 20 then 'RESEARCH'
        when deptno = 30 then 'SALES'
        when deptno = 40 then 'OPERATIONS'
    end as dname 
from emp
;

그룹 함수

  • 실습18 
select max(sal), median(sal), min(sal)
from emp
;

  • 실습19
select variance(sal), stddev(sal)
from emp
;

group by

  • 실습20
select deptno, avg(sal)
from emp 
group by deptno
;

  • 실습21 
-- 다중그룹컬럼 
select deptno, job, avg(sal)
from emp
group by deptno, job 
order by deptno, job
;

Having

  • 실습22
select deptno, avg(sal) 
from emp 
group by deptno
having avg(sal) >= 2000
;

Rollup

  • 실습23
    • 전체 합계 계산 
select deptno, sum(sal)
from emp 
group by rollup(deptno)
;

  • 실습24
    • rollup은 계층적으로 집계함
    • 부서별 소계(부분합) 뿐만 아니라, 부서총계(총합) 출력
select deptno, job, sum(sal), avg(sal) 
from emp 
group by rollup(deptno, job)
;

Join

Natural Join

  • 실습25
    • ON 키워드를 통해 명시적으로 지정하는 inner join과 달리, 자동으로 join 수행됨
    • 동일한 이름을 가진 열(column)에 대해 자동으로 조인을 수행
select *
from emp, dept
where emp.deptno = dept.deptno
;

  • 실습26
select *
from emp, salgrade 
where sal between losal and hisal
;

Self join

  • 실습27 
-- self join은 유관된 것을 찾을 때 쓰임 
select employee.ename || '의 매니저는 ' || manager.ename || '입니다'
from 
    emp employee, 
    emp manager 
where employee.mgr = manager.empno
    and manager.ename = 'KING'
;

  • 실습28 
-- SCOTT과 동일한 부서에서 근무하는 사원의 이름 
select collegue.ename
from 
    emp employee,
    emp collegue 
where employee.deptno = collegue.deptno 
    and employee.ename = 'SCOTT'
    and collegue.ename <> 'SCOTT'
;

Outer Join

  • 실습29
-- Right Outer Join 
-- 40부서는 있는데 사원이 없는 경우 
-- NULL로 채워서 행 추가 
select e.ename, d.dname, d.deptno
from emp e, dept d 
where e.deptno(+) = d.deptno
;

Fully Outer Join

  • 실습30
select e.ename, d.dname, d.deptno
from emp e, dept d 
where e.deptno(+) = d.deptno
union 
select e.ename, d.dname, d.deptno
from emp e, dept d 
where e.deptno = d.deptno(+)
;

Inner Join

  • 실습31 
-- 안시표준(다른 DBMS 호환 고려하면 가급적 안시표준 사용) 
select *
from emp 
inner join dept 
    on emp.deptno = dept.deptno
;

다중조인

  • 실습32 
-- 다중조인 
-- scott의 사번, 부서명, 급여, 급여등급 출력 
select *
from emp e, dept d, salgrade s 
where e.deptno = d.deptno -- E, D 조인 
    and e.sal between s.losal and s.hisal -- E, S 조인 
    and e.ename = 'SCOTT'
;

퀴즈

-- 사원의 이름이 다섯자인 사원의 이름을 출력하라.

select *
from emp
where length(ename) = 5
;

-- 입사일을 "1996년 5월 14일" 의 형식으로 사원의 이름, 입사일을 출력하라.

select ename, to_char(hiredate, 'fmyyyy"년" mm"월" dd"일"')
from emp 
;

-- 입사일 부터 지금까지의 날짜수를 출력하라. 

select trunc(sysdate - hiredate)
from emp 
;

-- 출력양식은 부서번호, 이름, 입사일, 현재일, 근무일수, 근무년수, 근무월수, 근무주수를 출력하라.(소수점 이하 절삭)

select empno, ename, hiredate, sysdate, 
    trunc(sysdate-hiredate) as 근무일수,
    trunc((sysdate-hiredate)/365) as 근무년수,
    trunc(months_between(sysdate, hiredate)) as 근무월수,
    trunc((sysdate-hiredate)/7) as 근무주수
from emp
;

-- 회사내의 최소급여와 최대급여의 차이를 구하라

select max(sal) - min(sal)
from emp 
;

-- JOB과 그 JOB에 속한 사원수를 출력하라.

select job, count(ename) 
from emp 
group by job 
;

-- 10번 부서 급여의 평균, 표준편차, 최고, 최저, 인원수를 구하여 출력하라.

select trunc(avg(sal)), trunc(stddev(sal)), max(sal), min(sal), count(ename) 
from emp 
where deptno = 10
;

-- 각 부서별 급여의 평균, 표준편차, 최고, 최저, 인원수를 구하여 출력하라.

select deptno, trunc(avg(sal)), trunc(stddev(sal)), max(sal), min(sal), count(ename) 
from emp 
group by deptno
order by deptno
;

-- 각 부서별 입사연도별 급여평균을 구하여 출력하라.(부서별 평균 총계와 모든 부서의 평균 총계를 함께 출력)

select deptno, to_char(hiredate, 'yy'), trunc(avg(sal))
from emp 
group by rollup(deptno, to_char(hiredate, 'yy'))
;

-- 모든 부서의 '81'년 반기별 급여총액을 구하여 출력하라.
단 부서이름과 부서별 총계 출력

SELECT 
    d.DNAME, 
    CASE 
        WHEN TO_CHAR(e.HIREDATE, 'MM') <= '06' THEN '상반기'
        ELSE '하반기' 
    END AS Half,
    SUM(e.SAL) AS Total_Salary
FROM emp e
JOIN dept d ON e.DEPTNO = d.DEPTNO
WHERE TO_CHAR(e.HIREDATE, 'YYYY') = '1981'
GROUP BY ROLLUP (d.DNAME, 
                 CASE 
                     WHEN TO_CHAR(e.HIREDATE, 'MM') <= '06' THEN '상반기'
                     ELSE '하반기' 
                 END)
;

-- 이름에 "M"자가 들어간 사원들의 이름,부서명,급여를 구하라.

select *
from emp 
where ename like '%M%'
;

-- 위치가 시카고인 부서에 속한 사원들의 이름과 부서명을 출력하라.

select ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno
    and loc = 'CHICAGO'
;

-- 10번 부서 사원과 20번 부서 사원을 행병합하라(행중복허용)

SELECT *
FROM emp
WHERE DEPTNO = 10
    UNION ALL
SELECT *
FROM emp
WHERE DEPTNO = 20
;

-- 부서별, 급여등급별 급여등급개수를 출력하라. 

select dname, grade, count(grade)
from 
    (select *
    from emp e, dept d, salgrade s 
    where e.deptno = d.deptno -- E, D 조인 
        and e.sal between s.losal and s.hisal -- E, S 조인 
    )
group by rollup(dname, grade)
;

profile
gununoo
take a look
이전 포스트

240125 오라클 교육 day4

다음 포스트

240129 오라클 교육 day6

post-custom-banner

4개의 댓글

comment-user-thumbnail
박영빈
2024년 1월 26일

벌써 금요일이네요 이번주도 마무리 잘하세요~~~

1개의 답글
comment-user-thumbnail
HeoJongWon
2024년 1월 26일

잘 보고 갑니다 ~ 정리를 깔끔하게 잘하셨네요 감사합니다.~

1개의 답글