create table client(
clno char(3),
name varchar(12),
address varchar(30),
constraint client_pk primary key(clno)
)
/
create table stock(
company char(7),
price number(6,2),
divident number(4,2),
eps number(4,2),
constraint stock_pk primary key(company)
)
/
create table trading(
company char(7),
exchange varchar(12),
constraint trading_pk primary key(company,exchange),
constraint trading_fk 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 purchase_pk primary key(clno,company,pdate),
constraint purchase_fk1 foreign key(clno) references client(clno),
constraint purchase_fk2 foreign key(company) references stock(company)
)
/
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 NY')
/
insert into trading values('IBM','NY,Lon,Tok')
/
insert into trading values('INTEL','NY,London')
/
insert into trading values('FORD','NY')
/
insert into trading values('GM','NY')
/
insert into trading values('INFOSYS','NY')
/
insert into purchase values('c01','BHP','2/oct/01',1000,12.00)
/
insert into purchase values('c01','BHP','08-Jun-01',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)
/
c)
select c.name,p.company,sum(p.qty) total_qty, sum(p.qty * s.price) c
from client c, purchase p, stock s, trading t
where c.clno = p.clno and p.company = s.company
and s.company = t.company and t.exchange = 'New York'
group by p.company, c.name;
d) select c.name, sum(p.qty * p.price) total_pprice
from client c,purchase p
where c.clno = p.clno
griup by c.name
e) select c.name, sum(p.qty *(s.price - p.price)) book_profit
from client c, purchase p, stock s
where c.clno = p.clno and p.company = s.company
group by c.name;
