Hirushan Eranga De Silva

8:19 AM

SLIIT DMBSIII-Practicle 1(queries)

Posted by Hirushan

Part 1:

SELECT DISTINCT c.name,s.company,s.price,s.dividend,s.eps
FROM client c,stock s,purchase p
WHERE c.clno=p.clno AND s.company=p.company
ORDER BY c.name
;

Part 2:
SELECT c.name,p.company,sum(p.qty) as TOTAL_QTY,sum(p.qty*p.price)/sum(p.qty)as APP
FROM client c,purchase p,stock s
WHERE c.clno=p.clno AND s.company=p.company
GROUP BY p.company,c.name
ORDER BY c.name
;

Part 3:
SELECT p.company,c.name,sum(p.qty) as TOT_QTY,sum(p.qty*s.price) as Current_value
From trading t,stock s,client c,purchase p
Where t.exchange='New York' AND s.company=t.company AND c.clno=p.clno AND p.company=s.company
Group by p.company,c.name
Order by p.company
;

Part 4:

client name,purchase*qty

SELECT c.name,sum(p.qty*p.price)AS TOTAL_PRICE
FROM client c,purchase p
WHERE c.clno=p.clno
GROUP BY c.name
ORDER BY c.name
;

Party 5:

SELECT c.name,sum(p.qty*s.price)-sum(p.qty*p.price) as Book_Profit
FROM client c,stock s,purchase p
WHERE c.clno=p.clno AND s.company=p.company
GROUP BY c.name
ORDER BY c.name
;

6:44 AM

SLIIT DMBSIII-Practicle 1(tables)

Posted by Hirushan

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