abc /*dummy type for co-dependant objects*/ create type dep_t_k / /*employee object*/ create type emp_t_k as object( EMPNO char(6), FIRSTNAME varchar(12), LASTNAME varchar(15), WORKDEPT ref dep_t_k, SEX char(1), BIRTHDATE date, SALARY number(8,2) ) / /*department object - replace dummy type (same name difference as object)*/ create type dep_t_k as object( DEPTNO char(3), DEPTNAME varchar(36), MGRNO ref emp_t_k, ADMRDEPT ref dep_t_k ) / /*employee table*/ create table OREMP_k of emp_t_k( constraint OREMP_k_pk primary key(EMPNO), constraint OREMP_k_nn1 FIRSTNAME not null, constraint OREMP_k_nn2 LASTNAME not null, constraint OREMP_k_chk1 check(Sex in('M','m','F','f')) ) / /*department table*/ create table ORDEPT_k of dep_t_k( constraint ORDEPT_k_pk primary key(DEPTNO), constraint ORDEPT_k_nn1 DEPTNAME not null ) / /*add references to employee table*/ alter table OREMP_k add constraint OREMP_k_fk1 foreign key(WORKDEPT) references ORDEPT_k / alter table ORDEPT_k add constraint ORDEPT_k_fk1 foreign key(MGRNO) references OREMP_k / alter table ORDEPT_k add constraint ORDEPT_k_fk2 foreign key(ADMRDEPT) references ORDEPT_k / ***********inserting values*************//department table/// insert into ORDEPT_k values(dep_t_k ('A00', 'SPIFFY COMPUTER SERVICE DIV.',null,null)) / insert into ORDEPT_k values(dep_t_k ('B01', 'PLANNING',null,(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'A00'))) / insert into ORDEPT_k values(dep_t_k ('C01', 'SPIFFY COMPUTER SERVICE DIV.',null,(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'A00'))) / insert into ORDEPT_k values(dep_t_k ('D01', 'SPIFFY COMPUTER SERVICE DIV.',null,(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'C00'))) / *************************inserting values to employee table**************************** insert into OREMP_k values(emp_t_k('000010','CHRISTINE','HAAS',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'A00'),'F','14/AUG/53','72750')) / insert into OREMP_k values(emp_t_k('000020','MICHEAL','THOMPSON',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'B01'),'M','02/FEB/68','61250')) / insert into OREMP_k values(emp_t_k('000030','SALLY','KWAN',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'C01'),'F','11/MAY/71','58750')) / insert into OREMP_k values(emp_t_k('000060','IRVIN','STERN',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'D01'),'M','07/JUL/65','55555')) / insert into OREMP_k values(emp_t_k('000070','EVA','PULASKI',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'D01'),'F','26/MAY/73','56170')) / insert into OREMP_k values(emp_t_k('000050','JOHN','GEYER',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'C01'),'M','15/SEP/55','60175')) / insert into OREMP_k values(emp_t_k('000090','EILERN','HENDERSON',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'B01'),'F','15/MAY/61','49750')) / insert into OREMP_k values(emp_t_k('00100','THEODOR','SPENSOR',(SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'B01'),'M','18/DEC/76','46150')) / **********************************UPDATE THE NULL VALUES******************************************************************************* UPDATE ORDEPT_k d SET D.ADMRDEPT = (SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'A00') WHERE d.DEPTNO = 'A00' / UPDATE ORDEPT_k d1 SET d1.MGRNO = (SELECT REF(E) FROM OREMP_k E WHERE E.EMPNO = '00010') WHERE d1.DEPTNO = 'A00' / UPDATE ORDEPT_k d SET d.ADMRDEPT = (SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'A00') WHERE d.DEPTNO = 'B01' / UPDATE ORDEPT_k d SET d.ADMRDEPT = (SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'A00') WHERE d.DEPTNO = 'C01' / UPDATE ORDEPT_k d SET d.ADMRDEPT = (SELECT REF(d) FROM ORDEPT_k d WHERE d.DEPTNO = 'C01') WHERE d.DEPTNO = 'D01' / ***********************************TUTE 01****************************************** (a) Get the department name and manager’s lastname for all departments. SELECT D.DEPTNAME,D.MGRNO.LASTNAME AS MANAGER FROM ORDEPT_k D / (B)Get the employee number, lastname and the department name of every employee. SELECT E.EMPNO,E.LASTNAME, E.WORKDEPT.DEPTNAME AS DEPTNAME FROM OREMP_k E / (c) For each department, display the department number, department name, and name of the administrative department. SELECT D.DEPTNO,D.DEPTNAME, D.ADMRDEPT.DEPTNAME AS ADMRDNAME FROM ORDEPT_k D / (d) For each department, display the department number, department name, the name of the administrative department and the last name of the manager of the administrative department. SELECT D.DEPTNO,D.DEPTNAME, D.ADMRDEPT.DEPTNAME AS ADMRNAME, D.ADMRDEPT.MGRNO.LASTNAME AS ADMANAGER FROM ORDEPT_k D / (e) Display the employee number, firstname, lastname and salary of every employee, along with lastname and salary of the manager of the employee’s work department. SELECT E.EMPNO,E.FIRSTNAME,E.LASTNAME,E.SALARY, E.WORKDEPT.MGRNO.LASTNAME AS MANAGER, E.WORKDEPT.MGRNO.SALARY AS MGRSAL FROM OREMP_k E / (f) Show the average salary for men and the average salary for women for each department. Identify the department by both department number and name. SELECT E.WORKDET.DEPTNO AS DEPTNO, E.WOKDEPT.DEPTNAME AS DEPTNAME, E.SEX, AVG(E.SALARY) AS AVGSAL FROM OREMP_k E GROUP BY E.WORKDEPT.DEPTNAME, E.WORKDEPT.DEPTNAME, E.SEX / ---------------------------------------------------------------------------------------------------------------------- ==================CREATE VARRAY========================================= CREATE TYPE EXCHANGE1_CS_VATY AS VARRAY(5) OF VARCHAR(12) / ==================CREATE STOCK TYPE===================================== CREATE TYPE STOCK AS OBJECT( COMPANY VARCHAR(10), CPRICE NUMBER(6,2), EXCHANGES EXCHANGE1_CS_VATY, LDIVIDENTNO NUMBER(4,2), EPS NUMBER(4,2) ) / =========================CREATE ADDRESS TYPE============================ CREATE TYPE ADDRESS AS OBJECT( STREET_NUM VARCHAR(10), STREET_NAME VARCHAR(15), SUBRUP VARCHAR(25), STATE_ VARCHAR(15), PIN VARCHAR(5) ) / ======================CREATE INVESTMENT_TYPE============================ CREATE TYPE INVESTMENT AS OBJECT( COMPANY REF STOCK, P_PRICE NUMBER(6), DATE_ DATE, QTY NUMBER(5) ) / ===========================IF THERE IS ANY ERRORS======================= SHOW ERROR =============================CREATE A TYPE TO MERGE====================== CREATE TYPE INVESTENT_TABLE AS TABLE OF INVESTMENT / =============================CREATE CLIENT TYPE=========================== CREATE TYPE CLIENT AS OBJECT( CLNO CHAR(5), NAME VARCHAR(11), ADDRESS EXCHANGE1_CS_VATY, INVESTMENTS INVESTENT_TABLE ) / =============================CREATING STOCK TABLE========================= CREATE TABLE STOCK_TB OF STOCK( CONSTRAINT STOCK_PK PRIMARY KEY (COMPANY) ) / ==========================CREATE NESTED TABLE CLIENT====================== CREATE TABLE CLIENT_TB OF CLIENT( CONSTRAINT CLIENT_PK PRIMARY KEY (CLNO) ) NESTED TABLE INVESTMENTS STORE AS INVESTMENT_NEST / ==========================ADD SCOPE BECAUSE WE CANT ADD REF TO NESTED TABLE DIRECTLY== ALTER TABLE INVESTMENT_NEST ADD SCOPE FOR (COMPANY) IS STOCK_TB / =====================INSERT DATA TO THE STOCK TABLE================================== INSERT INTO STOCK_TB VALUES('BHP', 10.50,EXCHANGE1_CS_VATY('SYDNEY','NEW YORK'),1.50,3.20) / INSERT INTO STOCK_TB VALUES('IBM', 70.00,EXCHANGE1_CS_VATY('NEW YORK','LONDON','TOKYO'),4.25,10.00) / INSERT INTO STOCK_TB VALUES('INTEL', 76.50,EXCHANGE1_CS_VATY('NEW YORK','LONDON'),5.00,12.40) / INSERT INTO STOCK_TB VALUES('FORD', 40.00,EXCHANGE1_CS_VATY('NEW YORK'),2.00,8.50) / INSERT INTO STOCK_TB VALUES('GM', 60.00,EXCHANGE1_CS_VATY('NEW YORK'),2.50,9.20) / INSERT INTO STOCK_TB VALUES('INFOSYS', 45.00,EXCHANGE1_CS_VATY('NEW YORK'),3.00,7.80) / =======================INSERT DATA INTO CLIENT TABLE====================================== INSERT INTO CLIENT_TB VALUES('C00','JOHN SMITH', EXCHANGE1_CS_VATY('3','EAST AV','BENTLY','WA','6102'), INVESTMENT_NEST((SELECT REF(S) FROM ===========NOMAN======================================================================== INSERT INTO client1_cs_otab VALUES('C00', 'John Smith', address1_cs_ty('3', 'East Av', 'Bentley', 'WA', '6102'), investment1_cs_ntty( investment1_cs_ty((SELECT REF(s) FROM stock1_cs_otab s WHERE s.company='BHP'), '02-OCT-2001', 100, 12.00), investment1_cs_ty((SELECT REF(s) FROM stock1_cs_otab s WHERE s.company='BHP'), '08-JUN-2002', 2000, 10.50), investment1_cs_ty((SELECT REF(s) FROM stock1_cs_otab s WHERE s.company='IBM'), '12-FEB-2000', 500, 58.00), investment1_cs_ty((SELECT REF(s) FROM stock1_cs_otab s WHERE s.company='IBM'), '10-APR-2001', 1200, 65.00), investment1_cs_ty((SELECT REF(s) FROM stock1_cs_otab s WHERE s.company='INFOSYS'), '11-AUG-2001', 1000, 64.00) ) ) / ---------------------------------------------------------------------------------------------------------------- -- (03) --(a) For each client, get the client's name, and the list of the client's investments -- with stock name, current price, last dividend and earnings per share. select distinct c.name, i.company.company, i.company.cprice, i.company.ldividend,i.company.eps from client_otab c, table(c.investments) i / --(b) Get the list of all clients and their share investments, showing the client name, -- and for each stock held by the client, the name of the stock, total number of shares -- held, and the average purchase price paid by the client for the stock. Average price -- is the total purchase value paid by a client for a given stock (value=qty*price) -- divided by the total quantity held by the client. select c.name, i.company.company, sum(i.qty) total_qty, sum(i.qty*i.pprice)/sum(i.qty) APP from client_otab c, table(c.investments) i group by c.name, i.company.company / --(c) For each stock traded in New York, find the quantity held by each client, and -- its current value (value=qty*price). Display stock (company) name, client name, -- number of shares held, and the current value of the shares. select c.name,i.company.company, sum(i.qty), sum(i.qty*i.company.cprice) current_value from client_otab c, table(c.investments) i, table(i.company.exchanges) e where e.column_value='New York' group by c.name,i.company.company / --(d) Find the total purchase value of investments for all clients. Display client name -- and total purchase value of the client's portfolio. select c.name, sum(i.qty*i.pprice) total_pprice from client_otab c, table(c.investments) i group by c.name / --(e) For each client, list the book profit (or loss) on the total share investment. -- Book profit is the total value of all stocks based on the current prices less -- the total amount paid for purchasing them. select c.name, sum(i.qty*(i.company.cprice-i.pprice)) book_profit from client_otab c, table(c.investments) i group by c.name / --4. Suppose John sold all his INFOSYS stocks to Jill, and Jill sold all her GM stocks -- to John today at the current prices. Update the database for these two transactions. -- Use the query 3(a) to check if the update worked correctly. (Here, the term update -- does not necessarily mean using the update statement of SQL.) insert into table( select c.investments from client_otab c where c.name = 'Jill Brody') select i.company,sysdate,i.qty,i.company.cprice from client_otab c, table(c.investments) i where c.name = 'John Smith' and i.company.company='INFOSYS' / delete table( select c.investments from client_otab c where c.name = 'John Smith') i where i.company.company='INFOSYS' / insert into table( select c.investments from client_otab c where c.name = 'John Smith') select i.company,sysdate,i.qty,i.company.cprice from client_otab c, table(c.investments) i where c.name = 'Jill Brody' and i.company.company='GM' / delete table( select c.investments from client_otab c where c.name = 'Jill Brody') i where i.company.company='GM' / commit; -- to verify -------------- select i.company.company,i.pdate,i.pprice,i.company.cprice,i.qty from client_otab c, table(c.investments) i where c.name = 'Jill Brody' / select i.company.company,i.pdate,i.pprice,i.company.cprice,i.qty from client_otab c, table(c.investments) i where c.name = 'John Smith' /