Hirushan De Silva

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)