Bohemian life

문제 : 소속 부서의 평균 급여보다 많은 급여를 받는 사원들을 보여다오.

    i) Join

select *

from emp e,

    (select deptno, avg(sal) as avg_sal

     from emp

     group by deptno) d

where e.deptno = d.deptno

and e.sal > d.avg_sal;

    ii) Correlated Subquery

select * 

from emp e

where sal > (select avg(sal) from emp

            where deptno = e.deptno);

    iii) PL/SQL Subprogram

create or replace function uf_avg_sal(a number) return number

is

 v_ret number;

begin

 select avg(sal) into v_ret

 from emp

 where deptno = a;

 return v_ret;

end;

select * from emp e

where sal > uf_avg_sal(e.deptno);

  문제 : 직속부하직원이 3명 이상인 사원들의 모든 정보를 보여다오.

   *) 사원번호가 7566인 사원의 직속부하직원 수는? --> 서브쿼리로 사용할 것입니다.

select count(*)

from emp

where empno = 7566;

 COUNT(*)

----------

        1 --> 엉터리

↓ ↓ ↓ 

select count(*)

from emp

where mgr = 7566;

 COUNT(*)

----------

        2 --> 제대로

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

↓ ↓ ↓ 

select *

from emp o

where 2 < (select count(*)

  from emp i

  where i.mgr = o.empno);

  * JOIN 이용

select e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno

from emp e1 join emp e2

   on (e1.empno = e2.mgr)

group by e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno

having count(e1.empno) > 2;

==> 단순 무식한 방법

↓ ↓ ↓ 

select *

from emp e1 join (select mgr, count(*) as num

 from emp

 group by mgr) e2

   on (e1.empno = e2.mgr)

where e2.num > 2;

  문제 : 근무하는 직원이 4명 이상인 부서의 모든 정보를 알려다오.

select *

from dept

where 3 < (select count(*)

  from emp

  where dept.deptno = deptno);

  - 외부 쿼리의 컬럼이 내부 쿼리의 WHERE 조건절에 사용됨

  - Select 문에서

  - Update 문에서

  - Delete 문에서

  * Nested Subquery: 내부 쿼리를 먼저

    - The inner query executes first and finds a value

    - The outer query executes once, using the value from the inner query

  * Correlated Subquery: 외부 쿼리를 먼저

    - Get a candidate row (fetched by the outer query)

    - Execute the inner query using the value of the candidate row

    - Use the values resulting from the inner query to qualify or disqualify the candidate

    - Repeat until no candidate row remains

  - 한 행당 한 컬럼만 반환하는 서브쿼리

  - 조건절, DECODE 또는 CASE의 수식에서 사용 가능

  - Group By 절을 제외한 Select 문의 모든 곳에서 사용 가능

select empno, sal, (select avg(sal) from emp) as avg_sal

from emp;

drop table t1 purge;

drop table t2 purge;

create table t1 (no number, name varchar2(10));

insert into t1 values (10, 'AAA');

insert into t1 values (10, 'BBB');

insert into t1 values (20, 'AAA');

insert into t1 values (20, 'BBB');

commit;

create table t2 (no number, name varchar2(10));

insert into t2 values (10, 'AAA');

insert into t2 values (20, 'BBB');

commit;

    - nonpairwise

select * from t1

where no in (select no from t2)          -- 10, 20

and name in (select name from t2);       -- AAA, BBB

    - pairwise

select * from t1

where (no, name) in (select no, name     -- (10, AAA), (20, BBB)

                    from t2); 

  - TIMESTAMP Conversion

    * FROM_TZ         : timestmap -> timestamp with time zone

FROM_TZ(TIMESTAMP '2012-04-23', '+03:00')

    * TO_TIMESTAMP    : character string -> timestamp

    * TO_TIMESTAMP_TZ : character string -> timestamp with time zone

  - INTERVAL Conversion

    * TO_YMINTERVAL : character string -> interval year to month

    * TO_DSINTERVAL : character string -> interval day to second

select sysdate

    , sysdate + to_yminterval ('1-2')

    , sysdate + to_yminterval ('-1-2')

from dual;

alter session set nls_date_format = 'yyyy-mm-dd hh12:mi:ss';

select sysdate

    , sysdate + to_dsinterval ('1 1:1:1')

    , sysdate + to_dsinterval ('-1 1:1:1')

from dual;

  - display the time zone offset (ahead/behind UTC) for the time zone

select tz_offset('Asia/Seoul')

    , tz_offset('us/eastern')

    , tz_offset('-02:00')

    , tz_offset(sessiontimezone)

    , tz_offset(dbtimezone)

from dual;

select tz_offset('sessiontimezone') from dual;

==> 오류: ORA-01882: 시간대 영역을 찾을 수 없습니다.

  - extracts the value of a specified datetime field (# 5-10 참조)

select hiredate, extract(month from hiredate) from emp;

select hiredate, extract(year month from hiredate) from emp;

==> 오류: ORA-00907: 누락된 우괄호

==> 필드는 한 개씩만

  cf.) select hiredate, to_char(hiredate, 'YYYY') from emp;

==> 거의 같지만 데이터 타입이 NUMBER 가 아니고 VARCHAR2 입니다.

select hiredate, to_number(to_char(hiredate, 'YYYY')) from emp;

select sysdate, extract(timezone_region from sysdate) from dual;

==> 오류: ORA-30076: 발췌 소스에 발췌 필트가 부적당합니다 

select extract(timezone_region from current_timestamp) from dual;

create table wty (

 prod_id number primary key

, wty_time interval year(3) to month);

insert into wty values (111, interval '6' month);

insert into wty values (123, interval '100' year);

==> ERROR : ORA-01873: the leading precision of the interval is too small

insert into wty values (222, '1001-1');

==> ERROR : ORA-01873: the leading precision of the interval is too small

insert into wty values (222, '10-1');

commit;

create table cust_order (

 order_id number primary key

, cust_name varchar2(30) not null

, prod_id number references wty(prod_id)

, pur_date date default sysdate

);

create sequence order_seq;

insert into cust_order values (order_seq.nextval, 'a', 222, default);

insert into cust_order (order_id, cust_name, prod_id)

               values (order_seq.nextval, 'b', 111);

insert into cust_order values (order_seq.nextval, 'b', 123, to_date('2011, 12, 25', 'YYYY, MM, DD'));

==> ERROR: ORA-02291: integrity constraint (java20.SYS_C0024045) violated - parent key not found

insert into cust_order values (order_seq.nextval, '', 222, sysdate - 7);

==> ERROR: ORA-01400: cannot insert NULL into ("java20"."CUST_ORDER"."CUST_NAME")

commit;

select * from wty;

select * from cust_order;

  문제) 주문 번호, 고객 이름, 구매 일자 및 고객이 구매한 

물품의 보증기간과 보증기간이 끝나는 날을 알고 싶다.

col cust_name format a20

col wty_time format a15

select c.order_id, c.cust_name, c.pur_date

    , w.wty_time, c.pur_date + w.wty_time as expire_day

from wty w join cust_order c

        on (w.prod_id = c.prod_id);

select c.order_id, c.cust_name, c.pur_date

    , w.wty_time, c.pur_date + w.wty_time as expire_day

from wty w, cust_order c

        where w.prod_id = c.prod_id;

- YEAR  : any integer (+, -)

  - MONTH : 0 ~ 11

  - DAY   : any integer (+, -)

  - HOUR

  - MINUTE

  - SECOND

select current_date

    , current_date

      + interval '10-2' year to month

      + interval '10 2:35:17' day to second

from dual;

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

    - the difference between two datetime values

      * INTERVAL YEAR () TO MONTH

      * INTERVAL DAY () TO SECOND ()

    - interval literals ==> http://goo.gl/J1UDm

select interval '123-9' year to month from dual;

==> ERROR : ORA-01873: the leading precision of the interval is too small

select interval '123-9' year(3) to month(2) from dual;

==> ERROR : ORA-00923: FROM keyword not found where expected

select interval '123-11' year(3) to month from dual;

select interval '123-12' year(3) to month from dual;

==> ERROR : ORA-01843: not a valid month

select interval '123--11' year(3) to month from dual;

==> ERROR : ORA-01867: the interval is invalid

select interval '123+10' year(3) to month from dual;

==> ERROR : ORA-01867: the interval is invalid

select interval '123 7' year(3) to month from dual;

==> ERROR : ORA-01867: the interval is invalid

select interval '123' year(3) to month from dual;

==> ERROR : ORA-01867: the interval is invalid

select interval '123-0' year(3) to month from dual;

select interval '123' year(3) from dual;

select interval '6' to month from dual;

==> ERROR: ORA-30089: missing or invalid <datetime field>

select interval '6' month from dual;

select current_date

    , current_date + interval '1-1' year to month as p_p

    , current_date - interval '-1-1' year to month as m_m

    , current_date + interval '-1-1' year to month as p_m

    , current_date - interval '1-1' year to month as m_p

from dual;

select interval '1:10:12' hour to second from dual;

  - YEAR            : -4712 ~ 9999 (0은 제외)

  - MONTH           : 01 ~ 12

  - DAY             : 01 ~ 31

  - HOUR            : 00 ~ 23

  - MINUTE          : 00 ~ 59

  - SECOND          : 00 ~ 59.9(N)

  - TIMEZONE_HOUR   : -12 ~ 14

  - TIMEZONE_MINUTE : 00 ~ 99

insert into ts_test values ('2012-JAN-04', '12-01-04', '12-01-04');

insert into ts_test values ('2012-JAN-04', '12-01-04 13:01:11', '12-01-04 13:01:11');

insert into ts_test values ('2012-JAN-04', '12-01-04 13:01:11 +04:00', '12-01-04 13:01:11');

insert into ts_test values ('71-JAN-04', '1971-01-04 13:01:11.123456789 +04:00', '2071-01-04 13:01:11');

commit;

select * from ts_test;

alter session set nls_date_format = 'YYYY-MM-DD';

select * from ts_test;

    alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';

    alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';

select * from ts_test;

- consists of datetime fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

                                 TimeZone_Hour, TimeZone_Minute, TimeZone_Region, TimeZone_Abbr

  1) TIMESTAMP ( TIMEZONE 저장 X         )

  2) TIMESTAMP WITH TIME ZONE ( TIMEZONE 저장 O, 출력 O )

  3) TIMESTAMP WITH LOCAL TIME ZONE ( TIMEZONE 저장 O, 출력 X )

- stored   : normalized to the db time zone

- retrieved: adjusted to the session time zone

alter session set time_zone = '+09:00';

drop table ts_test purge;

create table ts_test (

 ts timestamp(0)

, ts_wtz timestamp(4) with time zone

, ts_wltz timestamp with local time zone --> default: 6자리

);

insert into ts_test values (current_date, current_date, current_date);

insert into ts_test values (sysdate, sysdate, sysdate);

commit;

col ts format a30;

col ts_wtz format a35;

col ts_wltz format a30;

select * from ts_test;

alter table ts_test

 modify (ts timestamp(9), ts_wtz timestamp(3) with time zone);

==> ERROR : ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading field precision

alter table ts_test

 modify (ts timestamp(9), ts_wtz timestamp(7) with time zone);

select * from ts_test;

- datetime format elements ==> http://goo.gl/q8Nz8

   cf.) alter session set nls_language = american;

    - date format: (nls_date_format)

select current_date, current_timestamp, localtimestamp from dual;

    alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS AM';

==> ERROR: ORA-01818: 'HH24' precludes use of meridian indicator

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS.';

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS.S';

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS.SS';

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS.SSS';

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS.SSSSS';

    - timestamp format: (nls_timestamp_format)

    alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';

alter session set nls_date_format = 'RR-MON-DD HH:MI:SS PM';

select current_date, current_timestamp, localtimestamp from dual;

    - timestamp with time zone format: (nls_timestamp_tz_format)

    alter session set nls_timestamp_tz_format = 'YYYY+MM+DD HH/MI/SS^FF (TZH:TZM)';

select current_date, current_timestamp, localtimestamp from dual;

  * current_date      : DATE (in the session time zone)

  * current_timestamp : TIMESTAMP WITH TIME ZONE (in the session time zone)

  * localtimestamp    : TIMESTAMP (in the session time zone)

   cf.) sysdate      : DATE (for the OS where the database server resides)

    - sysdate vs. current_date

alter session set time_zone = 'Asia/Seoul';

select sysdate as Server, current_date as "Session" from dual;

alter session set time_zone = '-10:00';

select sysdate as Server, current_date as "Session" from dual;

alter session set time_zone = '+03:00';

select sysdate as Server, current_date as "Session" from dual;

    - current_date vs. current_timestamp vs. localtimestamp

col current_date format a25

col current_timestamp format a35

col localtimestamp format a25

alter session set time_zone = '+09:00';

select current_date, current_timestamp, localtimestamp from dual;

alter session set time_zone = '+03:00';

select current_date, current_timestamp, localtimestamp from dual;