Bohemian life

select e.employee_id, e.last_name, e.department_id,

         d.department_name, d.location_id

  from employees e 

  join departments d

  on (e.department_id = d.department_id);

↓ ↓ ↓

<Join Using>

  select e.employee_id, e.last_name, department_id,

         d.department_name, d.location_id

  from employees e 

  join departments d

  using (department_id);

↓ ↓ ↓

<Oracle Syntax>

  select e.employee_id, e.last_name, e.department_id,

         d.department_name, d.location_id

  from employees e, departments d

  where e.department_id = d.department_id;

- benefits

1. 쿼리를 심플하게

2. 성능 향상 - 1) 조인 조건에 해당하는 컬럼을 빨리 찾을 수 있음

2) 메모리 절약

       3) 네트워크 부하 감소

  - Guideline

    ①

    ② 테이블 별칭을 사용하면 select 문에서 테이블 이름과 연계하여 컬럼을 구분할 때

       테이블 이름 대신 반드시 테이블 별칭을 써야 합니다. 

select emp.empno, e.ename, d.dname --> ORA-00904: "EMP"."EMPNO": 부적합한 식별자

from emp e join dept d

on e.deptno = d.deptno;

    ③

    ④

- 테이블 이름을 컬럼이름과 함께 나열

--> 테이블 별칭(alias)가 FROM 절에 정의될 경우 식별자(qualifier)로 

   테이블 이름 대신 테이블 별칭을 반드시 써야 합니다.

  - natural join에서 조인에 자동으로 사용된 컬럼과

    using 절에 사용된 컬럼에는 식별자(테이블 이름)을 붙이지 마세요.

select department_id, department_name, location_id, city

  from departments 

  join locations

  using (location_id);

<Oracle Syntax>

  select department_id, department_name, departments.location_id, city

  from departments, locations

  where departments.location_id = locations.location_id;

  cf.)  natural join, join using 에선 

조인대상이 되는 컬럼에 테이블 이름을 쓸 수 없습니다.

  select department_id, department_name, departments.location_id, city

  from departments

  natural join locations;

  select department_id, department_name, departments.location_id, city

  from departments 

  join locations

  using (location_id);

  select department_id, department_name, location_id, city

  from departments 

  join locations

  using (departments.location_id);

: 이름과 타입이 같은 모든 컬럼을 기준으로 조인을 수행

  : 문제점 (무조건 같은 이름을 기준으로 조인)

    - 속도가 느리다

    - 원치 않는 조인이 이루어질 가능성이 있다.

  : 문제해결 -> join using, join on을 사용

  select department_id, department_name, location_id, city

  from departments

  natural join locations;

<Oracle Syntax>

  select department_id, department_name, departments.location_id, city

  from departments, locations

  where departments.location_id = locations.location_id;

select department_id, department_name, location_id, city

from departments

natural join locations

where department_id in (20, 50);

↓ ↓ ↓

select department_id, department_name, departments.location_id, city

from departments, locations

where departments.location_id = locations.location_id --> join predicate

and   department_id in (20, 50); --> non-join predicate

• Join - Inner Join - Natural joins    『               

                             - Join Using          =>equijoin

     - Join On                             』

  - Outer Join - Left Join

  - Right Join

  - Full Join

  - Cross Join

• Join Predicate - equi Join 동등(=) 

                          - non equi Join 비동등(>,<....)

※Oracle join 문법

From T1,T2

Where 조인조건  and 조인조건

and 비조인조건

 ①From T1 Natural Join T2

 ②From T1 Join T2 Using(컬럼 이름)

 ③From T1 Join T2 ON(조인 조건)

 ④From employees e left outer join departments d

문제) 부서별 급여합을 아래와 같이 가로 형태로 구하기

    TOTAL        D10        D20        D30        D40

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

    29025       8750      10875       9400          0

  해답)

select deptno, sal, sal, sal, sal, sal

from emp;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , sum(sal) as d10, sum(sal) as d20, sum(sal) as d30, sum(sal) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , decode(deptno, 10, sum(sal)) as d10

    , sum(sal) as d20, sum(sal) as d30, sum(sal) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , decode(deptno, 10, sum(sal)) as d10

    , decode(deptno, 20, sum(sal)) as d20

    , decode(deptno, 30, sum(sal)) as d30

    , decode(deptno, 40, sum(sal)) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select sum(sum(sal)) as total

    , max(decode(deptno, 10, sum(sal))) as d10

    , max(decode(deptno, 20, sum(sal))) as d20

    , max(decode(deptno, 30, sum(sal))) as d30

    , max(decode(deptno, 40, sum(sal))) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select sum(sum(sal)) as total

    , nvl(max(decode(deptno, 10, sum(sal))), 0) as d10

    , nvl(max(decode(deptno, 20, sum(sal))), 0) as d20

    , nvl(max(decode(deptno, 30, sum(sal))), 0) as d30

    , nvl(max(decode(deptno, 40, sum(sal))), 0) as d40

from emp

group by deptno;

  문제) 부서별 직무별 급여합 구하기 (아래와 같이 나타내시오.)

JOB            TOTAL        D10        D20        D30        D40

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

ANALYST         6000          0       6000          0          0

CLERK           4150       1300       1900        950          0

MANAGER         8275       2450       2975       2850          0

PRESIDENT       5000       5000          0          0          0

SALESMAN        5600          0          0       5600          0

 cf.) select deptno, job, sum(sal)

from emp

group by deptno, job

  select job, nvl(decode(deptno, 10, sal), 0) as d10

            , nvl(decode(deptno, 20, sal), 0) as d20

            , nvl(decode(deptno, 30, sal), 0) as d30

            , nvl(decode(deptno, 40, sal), 0) as d40

  from emp;

↓ ↓ ↓ 

  select job, sum(nvl(decode(deptno, 10, sal), 0)) as d10

            , sum(nvl(decode(deptno, 20, sal), 0)) as d20

            , sum(nvl(decode(deptno, 30, sal), 0)) as d30

            , sum(nvl(decode(deptno, 40, sal), 0)) as d40

  from emp

  group by job;

  문제) 연도별 입사자수 구하기

    TOTAL      Y1983      Y1982      Y1981   PREVIOUS

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

       14          1          2         10          1

  select to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY')

  from emp

  order by 1 desc;

  select to_char(hiredate, 'YYYY'), decode(to_char(hiredate, 'YYYY'), '1983', '1983') as Y1983

                                  , decode(to_char(hiredate, 'YYYY'), '1982', '1982') as Y1982

                                  , decode(to_char(hiredate, 'YYYY'), '1981', '1981') as Y1981

                                  , decode(to_char(hiredate, 'YYYY'), '1983', null, '1982', null, '1981', null, 'Previous') as Prev

  from emp

  order by 1 desc;

  select count(decode(to_char(hiredate, 'YYYY'), '1983', '1983')) as Y1983

       , count(decode(to_char(hiredate, 'YYYY'), '1982', '1982')) as Y1982

       , count(decode(to_char(hiredate, 'YYYY'), '1981', '1981')) as Y1981

       , count(decode(to_char(hiredate, 'YYYY'), '1983', null, '1982', null, '1981', null, 'Previous')) as Prev

  from emp

  order by 1 desc;

oj_SQL.txt

- nested to a depth of two. 

  select max(avg(salary))

  from employees

  group by department_id;

  select department_id, max(avg(salary))

  from employees

  group by department_id;

  SELECT   department_id, AVG(salary), max(salary)

  FROM     employees

  GROUP BY department_id;

  SELECT   department_id, AVG(salary), max(salary)

  FROM     employees

  GROUP BY department_id

  HAVING   max(salary) > 10000;

  SELECT   department_id, AVG(salary)

  FROM     employees

  GROUP BY department_id

  HAVING   max(salary) > 10000;

- 그룹 함수는 WHERE 절과 GROUP BY 절에서 사용할 수 없습니다.

  select deptno, sum(sal)

  from emp

  group by deptno;

   -----------

  select deptno, sum(sal)

  from emp                   -- 부서 테이블

  where deptno <> 20         -- 부서번호 20번

  group by deptno;

  select deptno, sum(sal)

  from emp                   /* 부서 테이블 */

  group by deptno            /* 부서별 그룹 */

  having deptno <> 20;

   -----------

  select deptno, sum(sal)

  from emp

  WHERE sum(sal) >= 9000 --> 오류: (WHERE 절에) ORA-00934: 그룹 함수는 허가되지 않습니다

  group by deptno;          

  select deptno, sum(sal)

  from emp

  group by deptno

  HAVING sum(sal) >= 9000; --> 가능

   -----------

  select detpno, sum(sal)

  from emp

  group by deptno, sum(sal); --> 오류: (GROUP BY 절에) ORA-00934: 그룹 함수는 허가되지 않습니다

- select-list에 단일 컬럼 또는 수식을 그룹 함수와 같이 사용하려면

   반드시 group by 절에 해당 단일 컬럼 또는 수식을 명시해 주어야 합니다.

- Group by 절에 명시되었다고 반드시 select-list 에 명시될 필요는 없습니다.

* Guidelines

    -

    -

    -

    -

각자 알아서 해보세요~~

select avg(comm) "커미션 사원의 평균 커미션"

, avg(nvl(comm, 0)) "모든 사원의 평균 커미션"

from emp;

SQL> select sum(deptno), sum(distinct deptno) from emp;