DBMSIII Lab4 Answers

--1

--a)
ALTER TYPE stock3_t ADD MEMBER FUNCTION getYield RETURN FLOAT CASCADE;

--B)
ALTER TYPE stock3_t ADD MEMBER FUNCTION toUSD(rate IN FLOAT) RETURN FLOAT CASCADE;

--c)
ALTER TYPE stock3_t ADD MEMBER FUNCTION countExchanges RETURN INTEGER CASCADE;

--d)
ALTER TYPE client3_t ADD MEMBER FUNCTION totalPurchase RETURN FLOAT CASCADE;

--e)
ALTER TYPE client3_t ADD MEMBER FUNCTION totalProfit RETURN FLOAT CASCADE;



CREATE OR REPLACE TYPE BODY stock3_t AS
MEMBER FUNCTION getYield RETURN FLOAT IS
BEGIN
RETURN ( (SELF.dividend / SELF.currprice)*100 );
END getYield;

MEMBER FUNCTION toUSD(rate IN FLOAT) RETURN FLOAT IS
BEGIN
RETURN (SELF.currprice * rate);
END toUSD;

MEMBER FUNCTION countExchanges RETURN INTEGER IS
countEx INTEGER;
BEGIN
SELECT COUNT(e.COLUMN_VALUE) INTO countEx
FROM TABLE(SELF.exchangeS)e ;
RETURN countEx;
END countExchanges;

END;
/

CREATE OR REPLACE TYPE BODY client3_t AS
MEMBER FUNCTION totalPurchase RETURN FLOAT IS
total FLOAT;
BEGIN
SELECT SUM(s.purchaseprice*s.qty) INTO total
FROM TABLE(SELF.investments)s;
RETURN total;
END totalPurchase;

MEMBER FUNCTION totalProfit RETURN FLOAT IS
profit FLOAT;
BEGIN
SELECT SUM(s.qty *(s.company.currprice - s.purchaseprice) ) INTO profit
FROM TABLE (SELF.investments)s;
RETURN profit;
END totalProfit;

END;
/

--2)
--a)
SELECT s.company AS CNAME,s.exchangeS AS EXCHANGES ,s.toUSD(0.74) AS USDPRICE ,s.getYield() AS YIELD
FROM stock3 s;

--b)
SELECT s.company AS CNAME ,s.currprice AS CPRICE ,s.countExchanges() AS CNT_eXCHANGES
FROM stock3 s
WHERE s.countExchanges() > 1 ;

--c)
SELECT DISTINCT c.name AS NAME ,I.company.company AS STK_NAME,I.company.getYield() AS YIELD,I.company.currprice AS CPRICE,I.company.eps AS EPS
FROM client3 c, TABLE(c.investments)I ;

--d)
SELECT DISTINCT c.name AS NAME ,c.totalPurchase() AS PVALUE
FROM client3 c;

--e)
SELECT DISTINCT c.name AS NAME ,c.totalProfit() AS PROFIT
FROM client3 c;