SQL 02 조건문/조인/그룹함수

100을 30으로 나눴을 때 나머지를 구하시오.

select mod(100, 30) from dual;

23000을 0으로 고정하여 표현.

select to_char(23000, ‘000,000,000’)
from dual;

23000을 유동적으로 표현.

select to_char(23000, '999,999')
from dual;

23000을 고정적으로 표현.

select to_char(23000, '000,000')
from dual;

23000을 $를 붙이고 유동적으로 표현.

select to_char(23000, '$999,999')
from dual;

comm값과 comm 널값을 0으로 변환 후 100 더한 값을 출력하시오.

select comm, nvl(comm, 0) +100 as new_comm
from emp;

<조건문 : DECODE>

analyst면  A로, clerk면 C로 manager면 M으로 그외는 O로 표현하시오.

select job,
       decode(job, 'ANALYST', 'A',
                   'CLERK',   'C',
                   'MANAGER', 'M',
                              'O') as "Naming"
from emp;

부서번호가 10이면 영업부, 20이면 개발부, 30이면 홍보부, 그 외는 미정으로 출력하시오.

select ename, deptno, 
       decode(deptno, 10, '영업부',
                      20, '개발부',
                      30, '홍보부',
                          '미정') as "부서"
from emp;

<조건문 :  case when then>

부서번호가 10이면 영업부, 20이면 개발부, 30이면 홍보부, 그 외는 미정으로 출력하시오.

select ename, deptno,
       case 
           WHEN deptno=10 then '영업부',
           WHEN deptno=20 then '개발부',
           WHEN deptno=30 then '홍보부',
                  else '미정'
       end NEW 
from emp;

<join>

사원번호/사원이름/부서번호/부서이름을 출력하시오.

select e.empno , e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;

<inner join>

사원번호/사원이름/부서번호/부서이름을 출력하시오.

select e.empno , e.ename, d.deptno, d.dname
from emp e inner join dept d
on e.deptno = d.deptno;

<join>

부서번호가 10인 사원번호/사원이름/부서번호/부서이름을 출력하시오.

select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
      and d.deptno=10;

<inner join>

부서번호가 10인 사원번호/사원이름/부서번호/부서이름을 출력하시오.

select e.empno, e.ename, d.deptno, d.dname
from emp e inner join dept d
on e.deptno = d.deptno
where d.deptno=10;

<outer join>

사원이름/부서번호/부서이름을 출력하시오

select e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno;

select e.ename, d.deptno, d.dname
from emp e right outer join dept d
     -- 더 큰걸 기준
     on e.deptno = d.deptno;

<outer join을 명시 - from T1 outer join T2 on 조건>

사원번호/사원이름/부서번호/부서이름/보너스를 출력하시오.

select e.empno, e.ename, d.deptno, d.dname, b.bonus
from emp e 
    right outer join dept d 
        on e.deptno = d.deptno
    left outer join bonus b 
        on e.empno = b.empno;

<(+)를 이용하여 outer join 표현>

select e.empno, e.ename, d.deptno, d.dname, b.bonus
from emp e, dept d, bonus b
where e.deptno(+) = d.deptno
      and e.empno = b.empno(+)
      -- 모자른 쪽에 (+)를 주자.
order by deptno;

bonus 테이블 변경

alter table bonus drop(ename);
alter table bonus drop(job);
alter table bonus drop(comm);
 
alter table bonus add(empno number(4));
 
alter table bonus rename column sal to bonus;
 
insert
into bonus(empno, bonus)
values(7369, 300);
 
insert
into bonus(empno, bonus)
values(7499, 100);

모든 사원의 사원번호/사원이름/사수번호/사수이름을 출력하시오.

select e1.empno, e1.ename, e2.empno as mgrno, e2.ename as mgrname
from emp e1, emp e2
where e1.mgr = e2.empno(+);

select e1.empno, e1.ename, e2.empno as mgrno, e2.ename as mgrname
from emp e1 left outer join emp e2
     on e1.mgr = e2.empno;

JONES의 급여보다 많이 받는 사원이름/급여를 출력하시오.

select ename, sal
from emp
where sal > (select sal
             from emp
             where ename = 'JONES');

직업이 PRESIDENT인 사원과 같은 부서에서 일하는 사원정보를 출력하시오.

select *
from emp
where deptno = (select deptno
                from emp
                where job ='PRESIDENT');

부서번호가 JAMES와 같거나 급여가 JONES보다 많이 받는 사원을 출력하시오.

select *
from emp
where deptno = (select deptno
                from emp
                where ename = 'JAMES')
or sal > (select sal
          from emp
          where ename = 'JONES');

그룹함수 사용

select min(sal), max(sal), avg(sal), sum(sal), count(sal)
from emp;

최소 급여를 받는 사원과 같은 부서에서 일하는 사원을 출력하시오.

select *
from emp
where deptno = (select deptno
                from emp
                where sal = (select min(sal) 
                             from emp));

직업이 PRESIDENT이거나 SALESMAN인 사원과 같은 부서에서 일하는 사원을 출력하시오.

select *
from emp
where deptno in (select deptno
                 from emp 
                 where job in ('PRESIDENT', 'SALESMAN'));

                 

JONES의 급여보다 많이 받는 사원이름/급여/부서명을 출력하시오.

select e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
      and e.sal > (select sal
                   from emp
                   where ename ='JONES');

select e.ename, e.sal, d.dname
from emp e inner join dept d
on e.deptno = d.deptno
where e.sal > (select sal
               from emp
               where ename ='JONES');