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;

DBMSIII Lab5 Answers

CREATE TYPE manufacture_ty as OBJECT(
name varchar(20),
country varchar(20)
)
/

CREATE TYPE product_ty AS OBJECT(
modelno number,
type varchar(10),
price number(8,2),
name ref manufacture_ty
)NOT FINAL;
/


CREATE TYPE computer_ty UNDER product_ty (
speed number,
ram number,
hdisksize number,
rdspeed varchar2(3),
rdtype varchar2(3)
)NOT FINAL;
/

CREATE TYPE printer_ty UNDER product_ty(
PRType varchar2(8),
ifcolour varchar2(5)
)
/

CREATE TYPE pc_ty UNDER computer_ty(
monitorsize number(2),
casetype varchar2(5)
)
/

CREATE TYPE laptop_ty UNDER computer_ty(
screen number(8,2),
weight number(8,2)
)
/


CREATE TABLE manufacture_tab of manufacture_ty(
constraint pk_manufacture_tabl primary key(name)
)
/

CREATe TABLE product_tab of product_ty
(
constraint pk_product_tab primary key (modelno),
constraint fk_procut_tab foreign key(name) references manufacture_tab
)
/


insert into manufacture_tab values('ACER','SINGAPORE');
insert into manufacture_tab values('COMPAQ','USA');
insert into manufacture_tab values('IBM','USA');
insert into manufacture_tab values('TOSHIBA','JAPAN');
insert into manufacture_tab values('HP','USA');
insert into manufacture_tab values('CANON','JAPAN');

insert into product_tab values(
pc_ty(1001,'PC',1799,(select ref(b) from manufacture_tab b where b.name='ACER'),2700,256,40,'48x','CD',15,'tower'))
/

insert into product_tab values(
pc_ty(1002,'PC',2499,(select ref(b) from manufacture_tab b where b.name='ACER'),3500,512,60,'64x','DVD',17,'tower'))
/

insert into product_tab values(
pc_ty(1003,'PC',1999,(select ref(b) from manufacture_tab b where b.name='ACER'),1800,512,30,'48x','DVD',15,'flat'))
/

insert into product_tab values(
pc_ty(1004,'PC',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),1800,256,40,'64x','DVD',15,'flat'))
/

insert into product_tab values(
pc_ty(1005,'PC',2499,(select ref(b) from manufacture_tab b where b.name='IBM'),4000,512,60,'64x','DVD',15,'flat'))
/

insert into product_tab values(
pc_ty(1006,'PC',2119,(select ref(b) from manufacture_tab b where b.name='IBM'),2300,1024,40,'96x','DVD',17,'tower'))
/

insert into product_tab values(
pc_ty(1007,'PC',2299,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2400,512,80,'64x','DVD',17,'tower'))
/

insert into product_tab valueS(
pc_ty(1008,'PC',1999,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2700,256,30,'96x','CD',15,'tower'))
/

insert into product_tab values(
pc_ty(1009,'PC',1699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),3200,512,80,'64x','DVD',17,'tower'))
/

---------------

insert into product_tab values(
laptop_ty(2001,'LP',1448,(select ref(b) from manufacture_tab b where b.name='ACER'),1700,256,40,'48x','CD',12.1,3.5))
/

-----------

insert into product_tab values(
laptop_ty(2002,'LP',2559,(select ref(b) from manufacture_tab b where b.name='ACER'),2200,512,30,'64x','DVD',15.1,3.1))
/

insert into product_tab values(
laptop_ty(2003,'LP',2799,(select ref(b) from manufacture_tab b where b.name='ACER'),1866,512,60,'48x','DVD',15.1,2.8))
/

insert into product_tab values(
laptop_ty(2004,'LP',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),1866,256,40,'64x','DVD',12.1,3.5))
/

insert into product_tab values(
laptop_ty(2005,'LP',1499,(select ref(b) from manufacture_tab b where b.name='IBM'),1700,256,60,'64x','DVD',12.1,3.1))
/

insert into product_tab values(
laptop_ty(2006,'LP',2119,(select ref(b) from manufacture_tab b where b.name='IBM'),3700,1024,80,'64x','DVD',15.7,3.1))
/

insert into product_tab values(
laptop_ty(2007,'LP',2229,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2900,512,80,'48x','DVD',15.1,3.5))
/

insert into product_tab values(
laptop_ty(2008,'LP',999,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),1700,256,30,'96x','DVD',12.1,3.6))
/

insert into product_tab values(
laptop_ty(2009,'LP',699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),1800,128,40,'64x','DVD',15.1,2.9))
/

insert into product_tab values(
laptop_ty(2010,'LP',1699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),2750,512,30,'96x','CD',12.1,3.0))
/

------------
insert into product_tab values(
printer_ty(3001,'PR',231,(select ref(b) from manufacture_tab b where b.name='HP'),'ink-jet','True'))
/
-------------
insert into product_tab values(
printer_ty(3002,'PR',267,(select ref(b) from manufacture_tab b where b.name='CANON'),'ink-jet','True'))
/

insert into product_tab values(
printer_ty(3003,'PR',390,(select ref(b) from manufacture_tab b where b.name='HP'),'laser','False'))
/

insert into product_tab values(
printer_ty(3004,'PR',439,(select ref(b) from manufacture_tab b where b.name='CANON'),'ink-jet','True'))
/

insert into product_tab values(
printer_ty(3005,'PR',200,(select ref(b) from manufacture_tab b where b.name='CANON'),'bubble','True'))
/

insert into product_tab values(
printer_ty(3006,'PR',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),'laser','True'))
/

insert into product_tab values(
printer_ty(3007,'PR',350,(select ref(b) from manufacture_tab b where b.name='HP'),'laser','False'))
/

--Q4.
--a

select p.name.name,treat (value(p) as pc_ty).speed
from product_tab p
where value(p) IS OF(pc_ty) and
treat(value(p) as pc_ty).hdisksize>=60
/

--b

select p.modelno,p.price
from product_tab p
where p.name.name like 'C%'
/

--c
select distinct p.name.name
from product_tab p
where value(p) is of (only pc_ty)
/

--OR

--C-
select distinct p.name.name
from product_tab p
where value(p) IS OF (pc_ty) and
p.name.name NOT IN (select p1.name.name
from product_tab p1
where value(p) IS OF(printer_ty))

--d
select p.name.name
from product_tab p
where value(p) is of (pc_ty)
group by p.name.name
having count(p.modelno)>=3
/

--e
select p.name.name,avg(treat(value(p) as laptop_ty).screen)
from product_tab p
where value(p) IS OF (laptop_ty)
group by p.name.name
/


--Q5-A)

alter type product_ty add member function print return integer cascade
/

create or replace type body product_ty AS
MEMBER function print return integer is
begin
return self.modelno;
end print;
end;
/


--b
alter type computer_ty add overriding member function
print return integer cascade
/

create or replace type body computer-Ty as
overriding mbmerber function print return integer is
begin
return 'Model NO:'||self.modelno||'SPEED:'||self.speed||'RAM:'||self.ram||'HDSIZE'||self.hdiskskize
end print;
end;
/


select p.print()
from product_tab p


select p.print()
from product_tab p
where value(p) is of (pc_ty)

select p.print()
from product_tab p
where value(p) IS OF (computer_ty) 

DBMSIII Lab1 Answers

create table client(
clno char(3),
name varchar(12),
address varchar(30),
constraint client_pk primary key(clno) 
)
/

create table stock(
company char(7),
price number(6,2),
divident number(4,2),
eps number(4,2),
constraint stock_pk primary key(company)
)
/

create table trading(
company char(7),
exchange varchar(12),
constraint trading_pk primary key(company,exchange),
constraint trading_fk 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 purchase_pk primary key(clno,company,pdate),
constraint purchase_fk1 foreign key(clno) references client(clno),
constraint purchase_fk2 foreign key(company) references stock(company)
)
/

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 NY')
/
insert into trading values('IBM','NY,Lon,Tok')
/
insert into trading values('INTEL','NY,London')
/
insert into trading values('FORD','NY')
/
insert into trading values('GM','NY')
/
insert into trading values('INFOSYS','NY')
/
insert into purchase values('c01','BHP','2/oct/01',1000,12.00)
/
insert into purchase values('c01','BHP','08-Jun-01',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)
/

c) 
select c.name,p.company,sum(p.qty) total_qty, sum(p.qty * s.price) c
from client c, purchase p, stock s, trading t
where c.clno = p.clno and p.company = s.company 
and s.company  = t.company and t.exchange = 'New York'
group by p.company, c.name;

d) select c.name, sum(p.qty * p.price) total_pprice
from client c,purchase p
where c.clno = p.clno 
griup by c.name

e) select c.name, sum(p.qty *(s.price - p.price)) book_profit
from client c, purchase p, stock s
where c.clno = p.clno and p.company = s.company 
group by c.name;

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
/