DBMSIII Lab3

create table client(
CREATE TYPE exchanges_t AS VARRAY(5) OF VARCHAR2(12)
/
CREATE TYPE stock_t AS OBJECT(
   company      char(7),
   price    number(6,2),
   exchanges exchanges_t,
   dividend number(4,2),
   eps      number(4,2)
)
/

CREATE TYPE address_t AS OBJECT(
   street_number CHAR(10),
   street_name CHAR(20),
   suburb VARCHAR2(20),
   state VARCHAR2(15),
   pin CHAR(15)
)
/

CREATE TYPE investment_t AS OBJECT(
   company REF stock_t,
   pprice NUMBER(6,2),
   pdate DATE,
   qty NUMBER(6)
)
/

CREATE TYPE investment_ntt AS TABLE OF investment_t
/

CREATE TYPE client_t AS OBJECT(
   name    varchar2(12),
   address address_t,
   investments investment_ntt
)
/

CREATE TABLE stock_tab OF stock_t(
   company PRIMARY KEY
)
/

CREATE TABLE client_tab OF client_t (name primary key)
NESTED TABLE investments STORE AS investments_nttab
/

ALTER TABLE investments_nttab ADD SCOPE FOR (company) IS stock_tab
/

INSERT INTO stock_tab VALUES (stock_t('BHP',10.5,exchanges_t('Sydney','New York'),1.5,3.2))
/
INSERT INTO stock_tab VALUES (stock_t('IBM',70.00,exchanges_t('New York','London','Tokyo'),4.25,10.00))
/
INSERT INTO stock_tab VALUES (stock_t('INTEL',76.5,exchanges_t('Sydney','New York'),5,12.4))
/
INSERT INTO stock_tab VALUES (stock_t('FORD',40,exchanges_t('New York'),2,8.5))
/
INSERT INTO stock_tab VALUES (stock_t('GM',60,exchanges_t('New York'),2.5,9.2))
/
INSERT INTO stock_tab VALUES (stock_t('INFOSYS',45,exchanges_t('New York'),3,7.8))
/

INSERT INTO client_tab VALUES (client_t('John Smith',
                               address_t ('3','East Av','Bently','WA','6102'),
                               investment_ntt(
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='BHP'), 12.00,'02-OCT-2001',1000),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='BHP'), 10.50,'08-JUN-2002',2000),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='IBM'), 58.00,'12-FEB-2000',500),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='IBM'), 65.00,'10-APR-2001',1200),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INFOSYS'), 64.00,'11-AUG-2001',1000)
                               )
                              )
)
/
INSERT INTO client_tab VALUES (client_t('Jill Brody',
                               address_t ('42','Bent st','Perth','WA','6001'),
                               investment_ntt(
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 35.00,'30-JAN-2000',300),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 54.00,'30-JAN-2001',400),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 60.00,'02-OCT-2001',200),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='FORD'), 40.00,'05-OCT-1999',300),
                                       investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='GM'), 55.50,'12-DEC-2000',500)
                               )
                              )
)
/

--1
SELECT DISTINCT c.name, i.company.company, i.company.price, i.company.dividend,i.company.eps
FROM client_tab c, TABLE(c.investments) i
ORDER BY c.name
/

--2
SELECT c.name, i.company.company, SUM(i.qty) tot,AVG(i.company.price) Avgpprice
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name, i.company.company
ORDER BY c.name
/


SELECT c.name, i.company.company, SUM(i.qty) tot,SUM(i.qty*i.pprice)/SUM(i.qty)
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name, i.company.company
ORDER BY c.name
/



--3
SELECT c.name, SUM(i.qty), (SUM(i.qty)*i.company.price)
FROM client_tab c,
TABLE(SELECT investments FROM client_tab WHERE name=c.name) i,
TABLE(SELECT exchanges FROM stock_tab WHERE company=i.company.company) e
WHERE e.COLUMN_VALUE='New York'
GROUP BY c.name,i.company.company,i.company.price
/
--4
SELECT c.name, (SUM(i.qty*i.pprice)) tot
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name
/
--5
SELECT c.name, SUM(i.company.price*i.qty)-SUM(i.qty*i.pprice)
FROM client_tab c, TABLE(c.investments) i
GROUP BY c.name
/