Oracle DBA SQL 230703

▣ SQL 복습

1. select 문의 6가지절 :
    
      select 보고 싶은 컬럼명
      from 테이블명
      where 검색조건
      group by 그룹핑할 컬럼
      having 그룹핑할 컬럼으로 검색조건
      order by 정렬할 컬럼
    
2. 함수 : 
    
    1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
    2. 복수행 함수 : max, min, avg, sum, count
    3. 데이터 분석함수 : rank, dense_rank, ntile, cume_dist, listagg, lag, lead, 누적 데이터 구하기, 기타
    
3. 조인 : 
    
    1. 오라클 조인 문법:
      1. equi join
      2. non equi join
      3. outer join
      4. self join

    2. 1999 ANSI (미국 국립 표준 협회) 조인문법 (10g)
      1. on 절을 사용한 조인
      2. using 절을 사용한 조인
      3. left/right/full outer join
      4. natural join
      5. cross join

▣ 061 여러 테이블의 데이터를 조인해서 출력하기 4(SELF JOIN)

" 자기 자신과 조인하는 조인 문법 "

예제.

select empno, ename, mgr
from emp;

mgr 의 관리자의 사원번호

예제. 사원이름, 관리자의 이름을 출력하시오 !
( 관리자라는 것은 자기 자신의 관리자를 말합니다.)

select 사원.ename, 관리자.ename
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno;

emp 와 dept 의 경우는   emp.deptno = dept.deptno  라는 조인 조건
사원 테이블과 관리자 테이블의 경우는  조인조건이 ?

  **사원.mgr =  관리자.empno -> 순서는 상관없다.**

이 조인을 self  join 이라고 합니다.   자기 자신의 테이블과 조인한다고
해서 self  join 입니다.

문제318. 사원이름, 관리자 이름을 출력하는데 사원이름이 smith 인
사원만 출력하시오 !

select e.ename as 사원이름, m.ename as 관리자이름
from emp e, emp m
where e.mgr = m.empno and e.ename='SMITH';

문제320. 사원이름, 관리자 이름을 출력하는데 관리자 보다 월급이
더 많은 사원들만 출력하시오 !

select e.ename as 사원이름, m.ename as 관리자이름
from emp e, emp m
where e.mgr = m.empno and e.sal > m.sal ;

문제321. 사원이름, 사원의 입사일, 관리자 이름, 관리자의 입사일을
출력하는데 관리자 보다 먼저 입사한 사원들만 출력하시오!

select e.ename as 사원이름, e.hiredate as "사원 입사일",

                                              m.ename   as  관리자이름,  m.hiredate  as  "관리자 입사일"

from emp e, emp m
where e.mgr = m.empno and e.hiredate < m.hiredate;

문제322. (OCP 시험) HR 계정(유져)의 employees 테이블을 이용해서
self join 을 하는데 관리자번호가 100번인 사원의 사원이름과
관리자 이름을 출력하시오 !

select e.last_name, m.last_name
from employees e, employees m
where e.manager_id = m.employee_id
and e.manager_id = 100;

▣ 062 여러 테이블의 데이터를 조인해서 출력하기 5(ON절)

• 오라클 조인 문법
  select e.last_name, m.last_name
  from employees e, employees m
  where e.manager_id = m.employee_id
  and e.manager_id = 100;
• 1999 ansi 조인 문법 (on 절을 사용한 조인)
  select e.last_name, m.last_name
  from employees e join employees m
  on ( e.manager_id = m.employee_id )
  where e.manager_id = 100;

문제323. 이름과 부서위치를 출력하는데 on 절을 사용한 조인 문법으로
구현하시오 !

• 오라클 조인 문법:
  select e.ename, d.loc
  from emp e, dept d
  where e.deptno = d.deptno ;
• on 절을 사용한 조인 문법:
  select e.ename, d.loc
  from emp e join dept d
  on ( e.deptno = d.deptno ) ;

문제324. on 절을 사용한 조인 문법으로 DALLAS 에서 근무하는 사원
들의 이름과 부서위치를 출력하시오 !

select e.ename, d.loc
from emp e join dept d
on ( e.deptno = d.deptno )
where d.loc='DALLAS';

문제325. 직업이 SALESMAN 인 사원들의 이름과 직업과 부서위치를
출력하시오 ! ( on 절을 사용한 조인문법으로 수행)

select e.ename, e.job, d.loc
from emp e join dept d
on ( e.deptno = d.deptno )
where e.job='SALESMAN';

※ 오라클 조인 문법 4가지 :

1. equi join
2. non equi join
3. outer join
4. self join

※ 조인을 크게 두가지로 나눈 다면 ?

1. inner join
2. outer join

outer join 이 아니면 다 inner join 입니다.

문제326. (복습문제) emp 와 salgrade 를 조인해서 이름, 월급과
급여등급(grade) 를 출력하는데 3등급만 출력하시오 !

• 오라클 조인문법: 1. equi join, 2. non equi join, 3. outer join , 4. self join select e.ename, e.sal, s.grade
  from emp e, salgrade s
  where e.sal between s.losal and s.hisal

                and  s.grade = 3;

• 1999 ansi 조인문법 : 1. on 절을 사용한 조인문법 select e.ename, e.sal, s.grade
  from emp e join salgrade s
  on ( e.sal between s.losal and s.hisal )
  where s.grade = 3;
  

문제327. (3개의 테이블 조인) emp 와 dept 와 salgrade 를 조인해서
급여등급이 3등급인 사원들의 이름과 월급과 부서위치와
grade(급여등급) 을 출력하시오 !

• 오라클 조인문법:
  select e.ename, e.sal, d.loc, s.grade
  from emp e, dept d, salgrade s
  where e.deptno = d.deptno and e.sal between s.losal and s.hisal
  and s.grade = 3;
• 1999 ANSI 문법: on 절을 사용한 조인 문법
  select e.ename, e.sal, d.loc, s.grade
  from emp e join dept d
  on ( e.deptno = d.deptno )
  join salgrade s
  on ( e.sal between s.losal and s.hisal )
  where s.grade = 3;

문제328.(OCP 시험) HR 유져의 employees 와 departments 와 jobs 를
조인해서 last_name, deparment_name, job_title 을 출력하시오!
(on 절을 사용한 조인 문법으로 수행)

select e.last_name, d.department_name, j.job_title
from employees e join departments d
on ( e.department_id = d.department_id )
join jobs j
on ( e.job_id = j.job_id );

▣ 063 여러 테이블의 데이터를 조인해서 출력하기 5(USING절)

예제. 이름과 부서위치를 출력하는데 using 절을 사용한 조인으로
수행하시오 !

select e.ename, d.loc
from emp e join dept d
using ( deptno );

※ 설명 : using 절에 연결고리가 되는 컬럼인 deptno 만 작성하면 됩니다.
이때 주의할 사항이 e.deptno 또는 d.deptno 라고 하면
에러가 발생합니다. (OCP 와 SQLD 시험)

select e.ename, d.loc
from emp e join dept d
using ( e.deptno );

※ using 절을 사용한 조인은 조인 조건이 이퀄 조건이었던 equi 조인만
가능하고 between .. and 로 주었던 non equi 조인은 구현 안됩니다.

select e.ename, e.sal, s.grade
from emp e join salgrade s
using ( ? ) ;

문제329. employees 테이블과 departments 테이블을 서로 조인해서
last_name, department_name 을 출력하는데

using 절을 사용한 조인문법으로 구현하시오 !

select e.last_name, d.department_name
from employees e join departments d
using (department_id) ;

※ using 절에 괄호를 안쓰면 에러가 발생합니다.

▣ 064 여러 테이블의 데이터를 조인해서 출력하기 6(NATURAL JOIN)

예제. 이름과 부서위치를 출력하는데 natural join 으로 수행하시오 !

select e.ename, d.loc
from emp e natural join dept d ;

※ 오라클이 알아서 조인조건을 찾아서 조인해줍니다.

문제330. (OCP시험) 부서위치, 부서위치별 토탈월급을 출력하시오 !

• 오라클 조인 문법 ( equi join ) select d.loc, sum(e.sal)
  from emp e, dept d
  where e.deptno = d.deptno
  group by d.loc;
  
• ANSI 문법( using 절) select d.loc, sum(e.sal)
  from emp e join dept d
  using (deptno)
  group by d.loc;
  
• ANSI 문법( natural join ) select d.loc, sum(e.sal)
  from emp e natural join dept d
  group by d.loc;
  

점심시간 문제!

select d.loc as 부서위치, 
       listagg(ename || '(' || sal || ')', ',') 
       within group (order by sal desc) as 사원
  from emp e, dept d
  where e.deptno = d.deptno
  group by d.loc;

▣ 065 여러 테이블의 데이터를 조인해서 출력하기 7(LEFT/RIGHT OUTER JOIN)

예제. 이름과 부서위치를 출력하는데 아우터 조인을 사용해서
사원 테이블에는 없는 부서번호인데 부서 테이블에는 있는 부서번호
에 대한 부서위치를 출력하시오 !

• 오라클 조인 문법:
  select e.ename, d.loc
  from emp e, dept d
  where e.deptno (+) = d.deptno ;
• ANSI 문법 :
  select e.ename, d.loc
  from emp e right outer join dept d
  on ( e.deptno = d.deptno );

  ※ 오른쪽에 outer  한 데이터가 출력됩니다.

• 오라클 조인 문법:
  select e.ename, d.loc
  from emp e, dept d
  where e.deptno = d.deptno (+) ;
• ANSI 문법 :
  select e.ename, d.loc
  from emp e left outer join dept d
  on ( e.deptno = d.deptno );

문제331. HR 유져의 employees 테이블과 departments 테이블을
서로 조인해서 last_name 과 department_name 을 출력하는데
부서 테이블에는 존재하는 deparment_name 인데 해당 사원이
아직 배치되지 않는 부서명도 출력하시오 !

select e.last_name, d.department_name
from employees e right outer join departments d
on ( e.department_id = d.department_id );

문제332. (SQLD) 위의 결과를 다시 출력하는데 last_name 이 null 값이
맨 위에 나오게 하시오 !

select e.last_name, d.department_name
from employees e right outer join departments d
on ( e.department_id = d.department_id )
order by e.last_name asc nulls first;

문제333. (self join) 사원 이름, 관리자인 사원의 이름을 출력하시오
( on 절을 사용한 조인 문법으로 수행 )

• 오라클 조인 문법:
  select e.ename as 사원이름, m.ename as 관리자이름
  from emp e , emp m
  where e.mgr = m.empno ;
• ANSI 문법( ON 절을 사용한 조인)
  select e.ename as 사원이름, m.ename as 관리자이름
  from emp e join emp m
  on ( e.mgr = m.empno );

※ king 의 mgr 번호가 null 이기 때문에 inner 조인으로는 출력되지
않습니다.

문제334. (OCP)위의 결과에서 사원이름쪽에 KING 이 나오게 하시오 !

select e.ename as 사원이름, m.ename as 관리자이름
from emp e left outer join emp m
on ( e.mgr = m.empno );

▣ 066 여러 테이블의 데이터를 조인해서 출력하기 8(FULL OUTER JOIN)

오라클 조인문법으로는 지원하지 않는 아우터 조인 문법이고
오로지 ANSI 조인문법으로만 구현할 수 있는 문법

예제1. 아래의 오라클 조인 문법이 수행되는지 확인하시오 !

SELECT e.ename, d.loc
from emp e, dept d
where e.deptno (+) = d.deptno (+);

ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
이런 오류가 발생합니다. ANSI 문법으로는 가능합니다.

select e.ename, d.loc
from emp e full outer join dept d
on ( e.deptno = d.deptno );

문제335. 위의 SQL에 실행계획을 확인하시오 !

explain plan for
select e.ename, d.loc
from emp e full outer join dept d
on ( e.deptno = d.deptno );

select * from table(dbms_xplan.display) ;

▣ 067 집합 연산자로 데이터를 위아래로 연결하기 1(UNION ALL)

조인(join) --> 두 테이블의 결과를 양옆으로 연결해서 보여주는 문법
집합연산자 --> 두 테이블의 결과를 위아래로 연결해서 보여주는 문법

• 집합 연산자의 종류 4가지
  1. union all : 합집합
  2. union : 합집합 + 중복 데이터 제거
  3. intersect : 교집합
  4. minus : 차집합

문제336. 부서번호, 부서번호별 토탈월급을 출력하시오 !

select deptno, sum(sal)
from emp
group by deptno;

문제337. 사원 테이블의 전체 토탈월급을 출력하시오 !

select sum(sal)
from emp;

SUM(SAL)
---------
    33025

문제338. rollup 을 이용해서 부서번호, 부서번호별 토탈월급을 출력
하는데 맨 밑에 전체 토탈월급이 출력되게하시오 !

select deptno, sum(sal)
from emp
group by rollup(deptno);

문제339. 위의 결과를 rollup 을 이용하지 않고 union all 로 구현해보시오

select deptno, sum(sal)
from emp
group by deptno
union all
select to_number(null) as deptno, sum(sal)
from emp
order by deptno asc;

※ 합집합 연산자 사용할 때 주의할 사항

1. 위의 SQL과 아래의 SQL의 컬럼의 갯수가 동일해야 합니다.
2. 위의 SQL과 아래의 SQL의 컬럼의 유형이 동일해야 합니다.
3. 가급적 컬럼명도 동일해야 해줘야 ORDER BY 할 때 깔끔하게 정렬됩니다.
4. order by 절은 맨 아래에 있는 쿼리문에만 사용할 수 있습니다. (별칭은 맨 위에 컬럼)

문제340. 아래의 SQL의 결과를 UNION ALL 로 구현하시오 !

select deptno, job, sum(sal)
from emp
group by grouping sets( (deptno), (job) );

답:
select deptno, to_char( null ) as job , sum(sal)
from emp
group by deptno
union all
select to_number(null) as deptno, job , sum(sal)
from emp
group by job;

문제341. 아래의 SQL의 결과를 union all 로 수행하시오 !

select to_char( hiredate, 'RRRR'), job, sum(sal)
from emp
group by grouping sets( ( to_char(hiredate,'RRRR') ), (job) );

답:

select to_char( hiredate, 'RRRR'), to_char(null) as job, sum(sal)
from emp
group by to_char( hiredate, 'RRRR')
union all
select to_char(null) , job, sum(sal)
from emp
group by job ;

문제342. 위의 결과를 아래와 같이 정렬해서 출력하시오

select to_char( hiredate, 'RRRR') as 년도, to_char(null) as job, sum(sal)
from emp
group by to_char( hiredate, 'RRRR')
union all
select to_char(null) as 년도 , job, sum(sal)
from emp
group by job
order by 년도;

delete from emp
where job is null;

문제343. 아래와 같이 결과를 출력하시오 !

select to_char( hiredate, 'RRRR') as 년도, to_char(null) as job, sum(sal)
from emp
group by to_char( hiredate, 'RRRR')
union all
select to_char(null) as 년도 , job, sum(sal)
from emp
group by job
order by 년도 asc, job asc ;

▣ 068 집합 연산자로 데이터를 위아래로 연결하기 2(UNION)

• union 이 union all 과 다른점
  1. 중복행이 제거
  2. 맨앞의 컬럼을 기준으로 결과가 정렬이 됩니다.

문제344. 부서번호가 10번, 20번인 사원들의 부서번호, 부서번호별
토탈월급을 출력하시오 !

select deptno, sum(sal)
from emp
where deptno in ( 10, 20 )
group by deptno;

문제345. 부서번호가 20번, 30번인 사원들의 부서번호, 부서번호별
토탈월급을 출력하시오 !

select deptno, sum(sal)
from emp
where deptno in ( 20, 30 )
group by deptno;

문제346. 문제344번의 결과와 345번의 결과를 위아래로 연결해서 출력
하시오 ! (union all )

select deptno, sum(sal)
from emp
where deptno in ( 10, 20 )
group by deptno
union all
select deptno, sum(sal)
from emp
where deptno in ( 20, 30 )
group by deptno;

문제347. 위의 결과를 union 으로 수행하시오 !

select deptno, sum(sal)
from emp
where deptno in ( 10, 20 )
group by deptno
union

select deptno, sum(sal)
from emp
where deptno in ( 20, 30 )
group by deptno;

※ 만약에 굳이 중복행 제거하지 않아도되고 정렬을 할 필요도 없다면
union 보다는 union all 을 사용하는게 성능상 더 좋습니다.

OCP 178 번

select nvl(comm, 9000 )
from (

select comm
from emp
where empno in ( 7566, 7654, 7499 )
union

select comm
from emp
where empno in ( 7499, 7844, 7900 )

);

※ union all 은 null 을 중복제거 하지 않지만 union 은 null 도 중복제거 합니다

문제348. (오늘의 마지막 문제) 다음과 같이 결과를 출력하세요 !

직업별 부서번호별 토탈월급들을 출력하는데 맨 옆과 맨 아래에 토탈값들을
출력하시오 !

-- sum + decode

select NVL(job, '토탈') as 직업, sum(decode(deptno, 10, sal, null)) as "10",
                               sum(decode(deptno, 20, sal, null)) as "20",
                               sum(decode(deptno, 30, sal, null)) as "30",
                               sum(sal) as 토탈
  from emp
  group by rollup(job)
 -- group by grouping sets(job, ())
 -- order by 직업;

-- pivot 

select nvl(job,'토탈')as 직업 ,sum("10") as "10" ,sum("20") as "20" ,
                             sum("30") as "30",sum(토탈) as 토탈
  from(select job, deptno, sal, sum(sal) over (partition by job) as 토탈
         from emp )

pivot (sum(sal) for deptno in(10,20,30))
group by rollup (job)
order by job asc;