Hirushan De Silva

CREATE table client (
clno char(3)NOT NULL,
name varchar(12),
address varchar(30),
CONSTRAINT client_pk PRIMARY KEY(clno));

CREATE table stock(
company char(7),
price number(6,2),
dividend number(4,2),
eps number(4,2),
CONSTRAINT pk_stock PRIMARY KEY(company));

CREATE table trading(
company char(7),
exchange varchar(12),
CONSTRAINT pk_trading PRIMARY KEY(company,exchange),
CONSTRAINT fk_tra_stock FOREIGN KEY(company) REFERENCES stock(company));

CREATE table purchase(
clno char(3),
company char(7),
pdate date,
qty number(6),
price number(6,2),
CONSTRAINT pk_purchase PRIMARY KEY(clno,company,pdate),
CONSTRAINT fk_pur_tra FOREIGN KEY(company) REFERENCES stock(company),
CONSTRAINT fk_pur_clie FOREIGN KEY(clno) REFERENCES client(clno));


insert into client values('c01','John Smith','3 East Av Bentley WA 6102');
insert into client values('c02','Jill Brody','42 Bent St Perth WA 6001');

insert into stock values('BHP',10.50,1.50,3.20);
insert into stock values('IBM',70.00,4.25,10.00);
insert into stock values('INTEL',76.50,5.00,12.40);
insert into stock values('FORD',40.00,2.00,8.50);
insert into stock values('GM',60.00,2.50,9.20);
insert into stock values('INFOSYS',45.00,3.00,7.80);


insert into trading values('BHP','Sydney');
insert into trading values('BHP','New York');
insert into trading values('IBM','New York');
insert into trading values('IBM','London');
insert into trading values('IBM','Tokyo');
insert into trading values('INTEL','New York');
insert into trading values('INTEL','London');
insert into trading values('FORD','New York');
insert into trading values('GM','New York');
insert into trading values('INFOSYS','New York');

insert into purchase values('c01','BHP','02/OCT/01',1000,12.00);
insert into purchase values('c01','BHP','08/JUN/02',2000,10.50);
insert into purchase values('c01','IBM','12/FEB/00',500,58.00);
insert into purchase values('c01','IBM','10/APR/01',1200,65.00);
insert into purchase values('c01','INFOSYS','11/AUG/01',1000,64.00);

insert into purchase values('c02','INTEL','30/JAN/00',300,35.00);
insert into purchase values('c02','INTEL','30/JAN/01',400,54.00);
insert into purchase values('c02','INTEL','02/OCT/01',200,60.00);
insert into purchase values('c02','FORD','05/OCT/99',300,40.00);
insert into purchase values('c02','GM','12/DEC/00',500,55.50);