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);