Hirushan De Silva

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
;