Hirushan De Silva

DBMS prac3 Answer's Provided by Iranga & Sachit talagala


------------------ CREATE TYPES & TABLES QUERIES ------------------------------------------------------------------------------------

CREATE TYPE exchangeS_arr AS VARRAY(5) OF varchar(12)
/


CREATE TYPE stock3_t AS OBJECT
(
company char(7),
currprice number(6,2),
exchangeS exchangeS_arr,
dividend number(4,2),
eps number(4,2)
)
/


CREATE TYPE address_t AS OBJECT
(
streetNo char(4),
streetName varchar(20),
suburb varchar(20),
state varchar(10),
pin char(5)
)
/


CREATE TYPE Investments_t AS OBJECT
(
company REF stock3_t,
purchaseprice number(6,2),
pdate date,
qty number(6)
)
/


CREATE TYPE Investments_tbl AS TABLE OF Investments_t
/


CREATE TYPE client3_t AS OBJECT
(
clno char(3),
name varchar(12),
address address_t,
investments Investments_tbl
)
/


CREATE TABLE stock3 OF stock3_t
(
constraint stock_pk primary key(company)
)
/


CREATE TABLE client3 OF client3_t
(
constraint client_pk primary key(clno)
)
NESTED TABLE investments STORE AS Invest_ntbl
/



------------------- DROP QUERIES--------------------------------------------------------------------------------------


DROP TABLE client3;

DROP TABLE stock3;


DROP TYPE client3_t;

DROP TYPE Investments_tbl;

DROP TYPE Investments_t;

DROP TYPE address_t;

DROP TYPE stock3_t;

DROP TYPE exchangeS_arr;



---------------- INSERT QUERIES -------------------------------------------------------------------------------------


INSERT INTO stock3 VALUES ( stock3_t ( 'BHP', 10.50, exchangeS_arr( 'Sydney', 'New York'), 1.50, 3.20 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'IBM', 70.00, exchangeS_arr( 'New York','London', 'Tokyo'), 4.25, 10.00 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'INTEL', 76.50, exchangeS_arr('New York','London'), 5.00, 12.40 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'FORD', 40.00, exchangeS_arr( 'New York'), 2.00, 8.50 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'GM', 60.00, exchangeS_arr( 'New York'), 2.50, 9.20 ));
INSERT INTO stock3 VALUES ( stock3_t ( 'INFOSYS', 45.00, exchangeS_arr( 'New York'), 3.00, 7.80 ));




INSERT INTO client3 VALUES ( client3_t ('C01','John Smith',address_t( '3','East', 'Bentley', 'WA', '6102') ,
Investments_tbl ( Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='BHP'), 12.00,'02-Oct-2001',1000),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='BHP'), 10.50,'08-Jun-2002',2000),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='IBM'), 58.00,'12-Feb-2000',500),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='IBM'), 65.00,'10-Apr-2001',1200),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INFOSYS'), 64.00,'11-Aug-2010',1000)
)));



INSERT INTO client3 VALUES ( client3_t ('C02','Jill Brody',address_t( '42','Bent St', 'Perth', 'WA', '6001') ,
Investments_tbl ( Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INTEL'), 35.00,'30-Jan-2000',300),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INTEL'), 54.00,'30-Jan-01',400),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INTEL'), 60.00,'02-Oct-01',200),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='FORD'), 40.00,'05-Oct-99',300),
Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='GM'), 55.50,'12-Dec-00',500)
)));



----------------- SELECT QUERIES ------------------------------------------------------------------------------------


SELECT * FROM stock3;

SELECT * FROM client3;



---------------- QUERIES --------------------------------------------------------------------------------------------

--a)

SELECT DISTINCT C.name, I.company.company,I.company.currprice,I.company.dividend,I.company.eps
FROM client3 C, TABLE(C.investments) I;


--b)

SELECT DISTINCT C.name,I.company.company,SUM(I.qty) as Total_Qty,SUM(I.purchaseprice*I.qty)/SUM(I.qty) AS AVGPRICE
FROM client3 C, TABLE(C.investments) I
GROUP BY C.name,I.company.company;


--c)

SELECT C.name,I.company.company AS STOCK,SUM(I.qty) as Total_Qty ,SUM(I.company.currprice*I.qty) AS CURRVAL
FROM client3 C, TABLE(C.investments) I,TABLE(I.company.exchangeS) E
WHERE E.COLUMN_VALUE = 'New York'
GROUP BY C.name,I.company.company;


--d)

SELECT C.name,SUM(I.qty*i.purchaseprice) AS PVALUE
FROM client3 C, TABLE(C.investments) I
GROUP BY C.name;


--e)

SELECT C.name,SUM(I.qty*I.company.currprice)-SUM(I.qty*i.purchaseprice) AS PROFIT
FROM client3 C, TABLE(C.investments) I
GROUP BY C.name;



--4)---------------------------------------------------------
DELETE TABLE(SELECT c.investments FROM client3 c WHERE C.name = 'John Smith' ) i
WHERE i.company = (SELECT ref(S) FROM stock3 S WHERE S.company='INFOSYS');

INSERT INTO TABLE( SELECT c.investments FROM client3 c WHERE C.name = 'Jill Brody')
VALUES(Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='INFOSYS'), 64.00,'11-Aug-2010',1000));


DELETE TABLE(SELECT c.investments FROM client3 c WHERE C.name = 'Jill Brody' ) i
WHERE i.company = (SELECT ref(S) FROM stock3 S WHERE S.company='GM');

INSERT INTO TABLE( SELECT c.investments FROM client3 c WHERE C.name = 'John Smith')
VALUES(Investments_t ( (SELECT ref(S) FROM stock3 S WHERE S.company='GM'), 55.50,'12-Dec-00',500));