CREATE TABLE  MARKET (
market_Id    NUMBER PRIMARY KEY,
city        VARCHAR2(15),
state       VARCHAR2(3),
region      CHAR(1)
);

insert into market values (1,'Melb','Vic','A');
insert into market values (2,'Adelaide','SA','A');
insert into market values (3,'Brisbane','Qld','A');
insert into market values (4,'Perth','WA','A');

CREATE TABLE  PRODUCT (
product_Id    NUMBER PRIMARY KEY,
name         VARCHAR2(20),
category     VARCHAR2(15),
price        NUMBER(5,2)
);

insert into product values (1001,'Pens','Stationary',5.00);
insert into product values (1002,'Pencil','Stationary',2.00);
insert into product values (1003,'Book','Stationary',6.00);
insert into product values (1004,'Ink','Stationary',3.50);
select * from product;

CREATE TABLE  TIME (
time_Id      DATE PRIMARY KEY,
week        NUMBER(2),
month       CHAR(3),
quarter     NUMBER(1),
year	        NUMBER(4)
);

insert into time values (sysdate,1,'MAR',3,1993);
insert into time values (sysdate,2,'SEP',4,1995);
insert into time values (sysdate,3,'SEP',4,1995);
insert into time values ('15-OCT-2000',1,'NOV',2,1997);
insert into time values ('16-DEC-2012',3,'OCT',3,1996);
select * from time;
update time set TIME_ID='6-JUL-2012' where year=1995 and week=2;

CREATE TABLE SALES (
market_Id      NUMBER  REFERENCES MARKET,
product_Id     NUMBER  REFERENCES PRODUCT,
time_Id        DATE    REFERENCES TIME,
sales_Amt     NUMBER(7,2),
PRIMARY KEY (market_Id,product_Id,time_Id)
);

insert into sales 
(market_Id,product_Id,time_Id,sales_Amt)
values (1,1002,(SELECT time_Id from time where year = 1997),2500);

SQL> select to_date('15-OCT-2000') from dual;
select to_date('15-OCT-2000') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select to_date('15-OCT-2000','DD-MON-YYYY') from dual;
select to_date('15-OCT-2000','DD-MON-YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

orclz> insert into sales
  2  (market_Id,product_Id,time_Id,sales_Amt)
  3  values (1,1002,(SELECT time_Id from time where year = 1997),2500);

1 row created.

orclz> select * from time;

TIME_ID                   WEEK MON    QUARTER       YEAR
------------------- ---------- --- ---------- ----------
2016-06-18:08:07:04          1 MAR          3       1993
0006-07-20:12:00:00          2 SEP          4       1995
0015-10-20:00:00:00          1 NOV          2       1997
0016-12-20:12:00:00          3 OCT          3       1996

orclz>

insert into time values ('04-MAR-1991',1,'MAR',3,1993);
insert into time values ('06-JUL-2012',2,'SEP',4,1995);
insert into time values ('17-JUN-2016',3,'SEP',4,1995);
insert into time values ('15-OCT-2000',1,'NOV',2,1997);
insert into time values ('16-DEC-2012',3,'OCT',3,1996);

insert into time values (to_date('04-03-1991','DD-MM-YYYY'),1,'MAR',3,1993);