--execute UTLXPLAN.sql & SampleDB.sql
select c.clno, c.name
from client c, purch p
where c.clno = p.clno and p.qty>1000;
--the following will find the better query plan
explain plan for
select c.clno, c.name
from client c, purch p
where c.clno = p.clno and p.qty>1000;
--to see the query plan
select * from PLAN_TABLE;
--to format the output use the utlxpls_mod.sql file
--to see the values
alter session set optimizer_mode=all_rows;
alter session set "_optimizer_cost_model" = cpu;
--after altering again explaing the plan & select * from PLAN_TABLE, then run the utlxpls_mod.sql, then your cost values should be there
--getting another query plan
explain plan for
select t.*
from trading t
where t.exchange='Tokyo';
--PLAN_TABLE
Plan Table
--------------------------------------------------------------------------------
| Operation and options | Object | cost | cpu_cost | io_cost |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 4 |125588 | 3 |
| TABLE ACCESS FULL |TRADING | 4 |125588 | 3 |
--------------------------------------------------------------------------------
TABLE ACCESS FULL - since there is no index in the table, when you create index the cost will be reduced.
--creating index for the trading table
create index index_3 on trading(exchange);
--after creating the index the cost reduces
Plan Table
--------------------------------------------------------------------------------
| Operation and options | Object | cost | cpu_cost | io_cost |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 |21735 | 2 |
| TABLE ACCESS BY INDEX ROWID |TRADING | 3 |21735 | 2 |
| INDEX RANGE SCAN |INDEX_3 | 2 |14443 | 1 |
--------------------------------------------------------------------------------
--to drop an index
drop index [index_name];
--all the indexes will be stored inside the user_indexes table
select index_3
from user_indexes
where table_name='TRADING'
/
--if you used simple letters for trading when creating the table, you have to use capital letters while selecting the index [TRADING]
select index_name
from user_indexes
where table_name='TRADING'
/
INDEX_NAME
------------------------------
INDEX_3
SYS_C0023391
--SYS_C0023391 is the system generated index, and is created for primary keys.
--INDEX_3 is the index we created
select DBMS_METADATA.GET_DDL('INDEX', u.index_name)
from user_indexes u
where table_name = 'TRADING'
/
DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "DIT7C1_0055"."INDEX_3" ON "DIT7C1_0055"."TRADING" ("EXCHANGE")
CREATE UNIQUE INDEX "DIT7C1_0055"."SYS_C0023391" ON "DIT7C1_0055"."TRADING" (
--creating index for client table
create index index_3 on client
DBMS 7 answer provided by Russel Dharmarathne!..
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)
--DBMS ANSWER's Provided by Iranga & Sachit --
--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;
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));
Part A)
CREATE TYPE dept_t;
/
CREATE TYPE emp_t as OBJECT
(
EMPNO char(6),
FIRSTNAME varchar(12),
LASTNAME varchar(15),
WORKDEPT REF dept_t,
SEX char(1),
BIRTHDATE date,
SALARY number(8,2)
)
/
**completing de created dummi type**
CREATE TYPE dept_t as OBJECT
(
DEPTNO char(3),
DEPTNAME varchar(36),
MGRNO REF emp_t,
ADMRDEPT REF dept_t
)
/
--b)
CREATE TABLE OREMP of emp_t
(
constraint empt_pk primary key (EMPNO),
constraint emp_c1 CHECK(FIRSTNAME IS NOT NULL),
constraint emp_c2 CHECK(LASTNAME IS NOT NULL)
)
/
ALTER TABLE OREMP ADD CONSTRAINT empt_fk FOREIGN KEY(WORKDEPT) references ORDEPT;
CREATE TABLE ORDEPT of dept_t
(
constraint dept_pk primary key (DEPTNO),
constraint dept_c1 CHECK(DEPTNAME IS NOT NULL),
CONSTRAINT dept_fk1 FOREIGN KEY(MGRNO) references OREMP,
CONSTRAINT dept_fk2 FOREIGN KEY(ADMRDEPT) references ORDEPT
)
/
DROP TABLE OREMP;
DROP TABLE ORDEPT;
--c)
insert into OREMP values('000010','CHRISTINE','HAAS',NULL,'F','14-AUG-53',72750);
insert into OREMP values('000020','MICHAEL','THOMPSON',NULL,'M','02-FEB-68',612550);
insert into OREMP values('000030','SALLY','KWAN',NULL,'F','11-MAY-71',58250);
insert into OREMP values('000060','IRVING','STERN',NULL,'M','07-JUL-65',55555);
insert into OREMP values('000070','EVA','PULASKI',NULL,'F','26-MAY-73',56170);
insert into OREMP values('000050','JOHN','GEYER',NULL,'M','15-SEP-55',60175);
insert into OREMP values('000090','EILEEN','HENDERSON',NULL,'F','15-MAY-61',49750);
insert into OREMP values('000100','THEODORE','SPENSER',NULL,'M','18-DEC-76',46150);
insert into ORDEPT values('A00','SPIFFY COMPUTER SERVICE DIV',NULL,NULL);
insert into ORDEPT values('B01','PLANNING',NULL,NULL);
insert into ORDEPT values('C01','INFORMATION CENTRE ',NULL,NULL);
insert into ORDEPT values('D01','DEVELOPEMENT CENTRE',NULL,NULL);
select * from OREMP;
select * from ORDEPT;
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='A00')
WHERE EMPNO='000010';
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='B01')
WHERE EMPNO='000020';
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='C01')
WHERE EMPNO='000030';
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='D01')
WHERE EMPNO='000060';
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='D01')
WHERE EMPNO='000070';
update OREMP
SET WORKDEPT = (select ref(b)
from ORDEPT b
where DEPTNO ='C01')
WHERE EMPNO='000050';
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='B01')
WHERE EMPNO='000090';
update OREMP
SET WORKDEPT = (select ref(D)
from ORDEPT D
where D.DEPTNO ='B01')
WHERE EMPNO='000100';
------------
update ORDEPT
SET MGRNO = (select ref(E)
from OREMP E
where E.EMPNO ='000010')
where DEPTNO ='A00';
update ORDEPT
SET ADMRDEPT =( select ref (E)
from ORDEPT E
where E.DEPTNO ='A00')
WHERE DEPTNO='A00';
update ORDEPT
SET MGRNO = (select ref(E)
from OREMP E
where E.EMPNO ='000020')
where DEPTNO ='B01';
update ORDEPT
SET ADMRDEPT =( select ref (E)
from ORDEPT E
where E.DEPTNO ='A00')
WHERE DEPTNO='B01';
update ORDEPT
SET MGRNO = (select ref(E)
from OREMP E
where E.EMPNO ='000030')
where DEPTNO ='C01';
update ORDEPT
SET ADMRDEPT =( select ref (E)
from ORDEPT E
where E.DEPTNO ='A00')
WHERE DEPTNO='C01';
update ORDEPT
SET MGRNO = (select ref(E)
from OREMP E
where E.EMPNO ='000060')
where DEPTNO ='D01';
update ORDEPT
SET ADMRDEPT =( select ref (E)
from ORDEPT E
where E.DEPTNO ='C01')
WHERE DEPTNO='D01';
--A)
SELECT D.DEPTNAME, D.MGRNO.LASTNAME
FROM ORDEPT D;
--B)
SELECT E.EMPNO, E.LASTNAME, E.WORKDEPT.DEPTNAME
FROM OREMP E;
--C)
SELECT D.DEPTNO, D.DEPTNAME, D.ADMRDEPT.DEPTNAME
FROM ORDEPT D;
--D)
SELECT E.EMPNO, E.FIRSTNAME,E.LASTNAME, E.SALARY, E.WORKDEPT.MGRNO.LASTNAME,E.WORKDEPT.MGRNO.SALARY
FROM OREMP E;
--E)
SELECT E.WORKDEPT.DEPTNO,E.WORKDEPT.DEPTNAME, E.SEX, AVG(E.SALARY)
FROM OREMP E
GROUP BY E.WORKDEPT.DEPTNO,E.WORKDEPT.DEPTNAME,E.SEX;
Part 1:
SELECT DISTINCT c.name,s.company,s.price,s.dividend,s.eps
FROM client c,stock s,purchase p
WHERE c.clno=p.clno AND s.company=p.company
ORDER BY c.name
;
Part 2:
SELECT c.name,p.company,sum(p.qty) as TOTAL_QTY,sum(p.qty*p.price)/sum(p.qty)as APP
FROM client c,purchase p,stock s
WHERE c.clno=p.clno AND s.company=p.company
GROUP BY p.company,c.name
ORDER BY c.name
;
Part 3:
SELECT p.company,c.name,sum(p.qty) as TOT_QTY,sum(p.qty*s.price) as Current_value
From trading t,stock s,client c,purchase p
Where t.exchange='New York' AND s.company=t.company AND c.clno=p.clno AND p.company=s.company
Group by p.company,c.name
Order by p.company
;
Part 4:
client name,purchase*qty
SELECT c.name,sum(p.qty*p.price)AS TOTAL_PRICE
FROM client c,purchase p
WHERE c.clno=p.clno
GROUP BY c.name
ORDER BY c.name
;
Party 5:
SELECT c.name,sum(p.qty*s.price)-sum(p.qty*p.price) as Book_Profit
FROM client c,stock s,purchase p
WHERE c.clno=p.clno AND s.company=p.company
GROUP BY c.name
ORDER BY c.name
;
CREATE table client (
clno char(3)NOT NULL,
name varchar(12),
address varchar(30),
CONSTRAINT client_pk PRIMARY KEY(clno));
CREATE table stock(
company char(7),
price number(6,2),
dividend number(4,2),
eps number(4,2),
CONSTRAINT pk_stock PRIMARY KEY(company));
CREATE table trading(
company char(7),
exchange varchar(12),
CONSTRAINT pk_trading PRIMARY KEY(company,exchange),
CONSTRAINT fk_tra_stock 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 pk_purchase PRIMARY KEY(clno,company,pdate),
CONSTRAINT fk_pur_tra FOREIGN KEY(company) REFERENCES stock(company),
CONSTRAINT fk_pur_clie FOREIGN KEY(clno) REFERENCES client(clno));
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');
insert into trading values('BHP','New York');
insert into trading values('IBM','New York');
insert into trading values('IBM','London');
insert into trading values('IBM','Tokyo');
insert into trading values('INTEL','New York');
insert into trading values('INTEL','London');
insert into trading values('FORD','New York');
insert into trading values('GM','New York');
insert into trading values('INFOSYS','New York');
insert into purchase values('c01','BHP','02/OCT/01',1000,12.00);
insert into purchase values('c01','BHP','08/JUN/02',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);
