Bohemian life

문제) 월급 상위 3명?

  select *

  from emp

  where rownum <= 3

  order by sal desc;   --> 엉터리 (select max(sal) from emp; ==> 5000)

     ↑ ↑

1)  select rownum, e.*

    from emp e;

2)  select rownum, e.*

    from emp e

    where rownum <= 3;

3)  select rownum, e.*

    from emp e

    order by sal desc;

4)  select rownum, e.*

    from emp e

    where rownum <= 3

    order by e.sal desc;

==> 정렬 이후가 아니라 정렬 한참 전인 WHERE 절을 통과한 순서대로 행 순서(ROWNUM)가 정해짐

    select rownum as no, e.*

    from emp e

    order by e.sal desc;

==> rownum이 먼저 결정되고 난 뒤 정렬이 이루어집니다.

   따라서 서브쿼리에서 rownum을 쓸 이유가 없습니다.

  해결책) 서브쿼리내에서 order by 사용하고 메인쿼리의 WHERE 절에서 rownum 사용

    select *

    from (select * -- in-line view

          from emp

          order by sal desc)

    where rownum <= 3;

# Pseudocolumn ==> http://goo.gl/NKgL8  참고하세요.

   : behaves like a table column, but is not actually stored in the table

  select rownum as no, empno, ename, sal

  from emp;

  select rownum as no, empno, ename, sal

  from emp

  where rownum <= 3;

  select rownum as no, rownum*2 as no2, rownum*10 as no3

  from emp

  where rownum <= 3;

  select rownum, rowid, empno, ename, sal

  from emp;

  SELECT salary

  FROM   employees

  WHERE  job_id = 'IT_PROG';

  SELECT employee_id, last_name, job_id, salary

  FROM   employees

  WHERE  salary < ANY (SELECT salary

                       FROM   employees

                       WHERE  job_id = 'IT_PROG')

  AND    job_id <> 'IT_PROG';

        salary <> ALL (9000, 6000, 4200)

        ==>     (salary <> 9000)   ==> salary NOT IN (9000, 6000, 4200)

            AND (salary <> 6000)

            AND (salary <> 4200)

  * 만약에 서브쿼리의 리턴 값 중에 null이 하나라도 있다면 <>ALL은 어떻게 될까요?

     => 조건이 N, F이 됩니다. 

     => 로우가 하나도 리턴되지 않습니다.

    salary := 8000 이라면

        where salary <> ALL (9000, null, 4200)

               ↓ ↓

        where salary NOT IN (9000, null, 4200) 

               ↓ ↓

        where (salary <> 9000) AND (salary <> null) AND (salary <> 4200)

                       T                    N                     T

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

  drop table t1 purge;

  drop table t2 purge;

  create table t1 (col1 number);

  insert into t1 values (1000);

  insert into t1 values (2000);

  insert into t1 values (3000);

  create table t2 (col1 number);

  insert into t2 values (1500);

  insert into t2 values (2500);

  commit;

  select * from t1

  where col1 > all (select col1 from t2);

         ↓ ↓

  select * from t1

  where col1 > all (1500, 2500);

  select * from t1

  where col1 > (select max(col1) from t2);

      ==> >ALL은 more than the maximum

SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (SELECT   MIN(salary)

                     FROM     employees

                     GROUP BY department_id);

            ↓ ↓

   SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (  7000,

     17000,

      6000,

      8300,

      2500,

      8600,

      4200,

      4400);

    * deptno IN (10, 20, 30)     === (deptno = 10) OR (deptno = 20) OR (deptno = 30)

      deptno NOT IN (10, 20, 30) === (deptno <> 10) AND (deptno <> 20) AND (deptno <> 30)

  cf.) 부서별 최소 급여 사원

SQL> SELECT last_name, salary, department_id

  2  FROM   employees

  3  WHERE  (department_id, salary) IN (SELECT   department_id, MIN(salary)

  4                                     FROM     employees

  5                                     GROUP BY department_id);

SELECT employee_id, last_name

FROM   employees

WHERE  salary = (SELECT MIN(salary)

FROM     employees

GROUP BY department_id);

==> 하나가 아닌 여러 행이 리턴되므로 =(single-row operator)를 사용할 수 없습니다.

SELECT   MIN(salary)

FROM     employees

GROUP BY department_id;

* 해결책: =을 in으로 변경

Having clause

select department_id, min(salary)

from employees

group by department_id;

↓ ↓ ↓

select department_id, min(salary)

from employees

group by department_id

having min(salary) > (select min(salary)

                     from employees

                     where department_id = 20);

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

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;