Bohemian life

 문제) 회사에서 급여가 제일 낮은 사원의 이름, 직급, 급여를 나타내세요.

main query : 사원의 이름, 직급, 급여 출력하는데 

    서브쿼리의 결과를 이용하여 걸러냄

subquery : 급여가 제일 낮은 사원의 사원번호 구함

    해답)

select first_name||' '||last_name as name

    , job_id, salary

from employees

where salary = (select min(salary)

from employees);

문제) employees 테이블에서 사원번호가 141번인 직원의 job_id와 같은 직원을 출력하세요.

 단 141번 직원은 제외하세요.

SELECT last_name, job_id

FROM   employees

WHERE  job_id = (SELECT job_id

                FROM   employees

                WHERE  employee_id = 141)

AND   employee_id <> 141;

※지연이 왈 ~~회사에서 서브쿼리가 중요하다고 잘 알아두라고 함.

# Subquery란?

  또 다른 select 문에 포함된 select 문.

# 서브쿼리를 이용한 문제 해결

  예) Who has a salary greater than Abel's?

      => Mainquery: Which employees have salaries greater than Abel's salary(subquery)?

         Subquery: What is Abel's salary?

select last_name, salary

from employees

where salary > [Abel's salary];

           +

select last_name, salary

from employees

where last_name = 'Abel';

↓ ↓ ↓

select last_name, salary

from employees

where salary > (select salary

               from employees

               where last_name = 'Abel');

↓ ↓ ↓

select last_name, salary

from employees

where salary > 11000;

cf.) Analytic function(=Window function)을 사용하면 쉽게 구할 수 있습니다.

drop table test purge;

create table test

as

select empno, sal

from emp

where rownum <= 3;

- 원하는 결과는 아래와 같습니다.  

    EMPNO        SAL   누적합

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

     7369        800      800

     7499       1600     2400

     7521       1250     3650

  select * from test;

select t1.empno, t1.sal, t2.empno, t2.sal

from test t1 join test t2

on (t1.empno >= t2.empno)

order by 1;

     ↓ ↓ ↓

select t1.empno, t1.sal, sum(t2.sal) as 누적합

from test t1 join test t2

on (t1.empno >= t2.empno)

group by t1.empno, t1.sal

order by t1.empno;

  * Analytic function (Window function) 을 이용한 해법

==> http://goo.gl/iMTAz

select empno, sal

    , sum(sal) over (order by empno) as 누적합

from test;

문제) 사원의 총수, 급여가 1500 이상인 사원의 수와 비율을 아래와 같이 알고 싶습니다.

      (비율은 소숫점 이하 둘째자리까지 반올림)

총사원수 급여>=1500      비율

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

      14          8    57.14%

풀이) 아래와 같은 집합을 생각하세요.

     총수 급여>=1500

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

      800        

     1600       1600

     1250       

     2975       2975

     1250       

     2850       2850

     2450       2450

     3000       3000

     5000       5000

     1500       1500

     1100       

      950        

     3000       3000

     1300   

위 집합의 각 컬럼에 count 함수를 적용하면 문제를 해결할 수 있습니다.

위와 같은 집합을 만들기 위한 쿼리를 작성해보면

     1) DECODE 함수 사용

select sal, sal

from emp;

↓ ↓ ↓ 

select sal as 총사원수

, decode(trunc(sal/1500), 0, to_number(null), sal) as "급여>=1500"

from emp; 

↓ ↓ ↓ 

select count(sal) as 총사원수

, count(decode(trunc(sal/1500), 0, to_number(null), sal)) as "급여>=1500"

from emp; 

↓ ↓ ↓ 

select count(sal) as 총사원수

, count(decode(trunc(sal/1500), 0, to_number(null), sal)) as "급여>=1500"

, round(count(decode(trunc(sal/1500), 0, to_number(null), sal)) / count(sal) * 100, 2)||'%' as 비율

from emp; 

     2) CASE expression 사용

select sal, sal

from emp;

↓ ↓ ↓ 

select sal as 총사원수

, case when sal >= 1500 then sal end as "급여>=1500"

from emp;

↓ ↓ ↓ 

select count(sal) as 총사원수

, count(case when sal >= 1500 then sal end) as "급여>=1500"

from emp;

↓ ↓ ↓ 

select count(sal) as 총사원수

, count(case when sal >= 1500 then sal end) as "급여>=1500"

, count(case when sal >= 1500 then sal end) / count(sal) as 비율

from emp;

↓ ↓ ↓ 

select count(sal) as 총사원수

, count(case when sal >= 1500 then sal end) as "급여>=1500"

, round(count(case when sal >= 1500 then sal end) / count(sal) * 100, 2)||'%' as 비율

from emp;

- A join condition is omitted

  - A join condition is invalid

  - All rows in the first table are joined to all rows in the second table

select count(*) from employees;

select count(*) from departments;

  select last_name, department_name

  from employees

  cross join departments;

    160 개의 행이 선택되었습니다.

   ↓ ↓

<Oracle Syntax>

  select last_name, department_name

  from employees, departments;

SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e FULL OUTER JOIN departments d

  ON   (e.department_id = d.department_id) ;

      ↓ ↓

<Oracle Syntax>

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e, departments d

  WHERE  e.department_id (+) = d.department_id (+);

SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e RIGHT OUTER JOIN departments d

  ON   (e.department_id = d.department_id) ;

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e JOIN departments d

  ON   (e.department_id = d.department_id) ;

     ==> 위의 OUTER JOIN과 출력결과를 비교해 보세요.

      ↓ ↓

<Oracle Syntax> ==> outer join할 때는 (+) 기호를 WHERE 조건에 사용합니다.

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e, departments d

  WHERE  e.department_id (+) = d.department_id;

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e LEFT OUTER JOIN departments d

  ON   (e.department_id = d.department_id) ;

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e JOIN departments d

  ON   (e.department_id = d.department_id) ;

     ==> 위의 OUTER JOIN과 출력결과를 비교해 보세요.

      ↓ ↓

  <Oracle Syntax> ==> outer join할 때는 (+) 기호를 WHERE 조건에 사용합니다.

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e, departments d

  WHERE  e.department_id = d.department_id (+);

; 조인 조건이 동등 조건이 아닌 조인 방식

  set lines 120

  select *

  from emp e join salgrade g

  on e.sal between g.losal and g.hisal;

      ↓ ↓

<Oracle Syntax>

  select *

  from emp e, salgrade g

  where e.sal between losal and hisal;

  SELECT employee_id, city, department_name

  FROM   employees e 

  JOIN   departments d

  ON     d.department_id = e.department_id 

  JOIN   locations l

  ON     d.location_id = l.location_id;

      ↓ ↓

<Oracle Syntax>

  SELECT employee_id, city, department_name

  FROM   employees e, departments d, locations l

  WHERE  d.department_id = e.department_id 

  AND    d.location_id = l.location_id;

SELECT e.employee_id, e.last_name, e.department_id, 

         d.department_id, d.location_id

  FROM   employees e JOIN departments d

  ON     (e.department_id = d.department_id)

  AND    e.manager_id = 149 ;

  ==> AND 대신 WHERE를 써도 됩니다.

쉬어가는 문제) 부서별 평균급여와 부서이름을 출력하는 쿼리를 작성하세요.

- Join On 사용

  select e.empno, e.ename, m.empno, m.ename

  from emp e

  join emp m

  on (e.mgr = m.empno);

↓ ↓ ↓

<Oracle Syntax>

  select e.empno, e.ename, m.empno, m.ename

  from emp e, emp m

  where e.mgr = m.empno;