14일차 # Example

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;