User:Anonylk234/sandbox

--Tute1 create type dept_type_Cs_IT16014046 / create type employee_type_Cs_IT16014046 as object (	empno char(6),	firstname varchar(12),	lastname varchar(15),	workdept ref dept_type_Cs_IT16014046,	sex char(1),	birthdate date,	salary number(8,2) ) / create type dept_type_Cs_IT16014046 as object (	deptno char(3),	deptname varchar(36),	mgrno ref employee_type_Cs_IT16014046,	admrdept ref dept_type_Cs_IT16014046 ) / create table OREMP_Cs_IT16014046 of employee_type_Cs_IT16014046 (	constraint OREMP_Cs_PK_IT16014046 primary key(empno),	constraint OREMP_CS_NN1_IT16014046 firstname not null,	constraint OREMP_CS_NN2_IT16014046 lastname not null,	constraint OREMP_CS_chk_IT16014046 check (sex='M' or sex='F' or sex='m' or sex='f') ) / create table ORDEPT_Cs_IT16014046 of dept_type_Cs_IT16014046 (	constraint IT16014046_ORDEPT_Cs_PK primary key(deptno),	constraint IT16014046_ORDEPT_Cs_NN deptname not null,	constraint IT16014046_ORDEPT_CS_FK1 foreign key(mgrno) references OREMP_Cs_IT16014046,	constraint IT16014046_ORDEPT_CS_FK2 foreign key(admrdept) references ORDEPT_Cs_IT16014046 ) /

alter table OREMP_Cs_IT16014046 add constraint OREMP_Cs_FK_IT16014046 foreign key(workdept) references ORDEPT_Cs_IT16014046 /

insert into ORDEPT_Cs_IT16014046 values(dept_type_Cs_IT16014046('A00','SPIFFY COMPUTER SERVICE DIV', null, null)) /

insert into ORDEPT_Cs_IT16014046 values(dept_type_Cs_IT16014046('B01','PLANNING', null, (select REF(D) FROM ORDEPT_Cs_IT16014046 D WHERE d.deptno = 'A00'))) /

insert into ORDEPT_Cs_IT16014046 values(dept_type_Cs_IT16014046('C01','INFROMATION CENTRE', null, (select REF(D) FROM ORDEPT_Cs_IT16014046 D WHERE d.deptno = 'A00'))) /

insert into ORDEPT_Cs_IT16014046 values(dept_type_Cs_IT16014046('D01','DEVELOPMENT CENTRE', null, (select REF(D) FROM ORDEPT_Cs_IT16014046 D WHERE d.deptno = 'C01'))) /

/*Updating Null values*/ update ORDEPT_Cs_IT16014046 d set d.admrdept = (select ref(D) from ORDEPT_Cs_IT16014046 D where d.deptno = 'A00') where d.deptno = 'A00' /

insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000010','CHRISTINE','HAAS', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='A00'),'F','14-AUG-1953',72750)) / insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000020','MICHAEL','THOMPSON', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='B01'),'M','02-FEB-1968',61250)) / insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000030','SALLY','KWAN', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='C01'),'F','11-MAY-1971',58250)) / insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000060','IRVING','STERN', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='D01'),'M','07-JUL-1965',55555)) / insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000070','EVA','PULASKI', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='D01'),'F','26-MAY-1973',56170)) / insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000050','JOHN','GEYER', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='C01'),'M','15-SEP-1955',60175)) / insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000090','EILEEN','HENDERSON', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='B01'),'F','15-MAY-1961',49750)) /	insert into OREMP_Cs_IT16014046 values(employee_type_Cs_IT16014046('000100','THEODORE','SPENSOR', (select ref(D) FROM ORDEPT_Cs_IT16014046 D where d.deptno='B01'),'M','18-DEC-1976',46150)) /

commit / ---update- update ORDEPT_Cs_IT16014046 D set D.mgrno = (select ref(E) from OREMP_Cs_IT16014046 E where E.empno = '000010') where D.deptno = 'A00' /

update ORDEPT_Cs_IT16014046 D set D.mgrno = (select ref(E) from OREMP_Cs_IT16014046 E where E.empno = '000020') where D.deptno = 'B01' /

update ORDEPT_Cs_IT16014046 D set D.mgrno = (select ref(E) from OREMP_Cs_IT16014046 E where E.empno = '000030') where D.deptno = 'A00' /

update ORDEPT_Cs_IT16014046 D set D.mgrno = (select ref(E) from OREMP_Cs_IT16014046 E where E.empno = '000060') where D.deptno = 'C01' /

commit /

select d.deptname,d.mgrno.lastName from ORDEPT_Cs_IT16014046 d /

select e.empno,e.lastName,e.workdept.deptname from OREMP_Cs_IT16014046 e /

select d.deptno,d.deptname,d.admrdept.deptname from ORDEPT_Cs_IT16014046 d /

select d.deptno,d.deptname,d.admrdept.deptname,d.admrdept.mgrno.lastName from ORDEPT_Cs_IT16014046 d /

select e.empno,e.firstname,e.lastname,e.salary,e.workdept.mgrno.lastname,e.workdept.mgrno.salary from OREMP_Cs_IT16014046 e /

select e.workdept.deptname, e.workdept.deptno, e.sex, AVG(e.salary) as AVERAGE_SALARY from OREMP_Cs_IT16014046 e group by e.workdept.deptname, e.workdept.deptno, e.sex / ---Tute2-- ---creating types and objects--- /*object type table

/*Step 01-creating a varray*/ create type exch_varray_IT16014046 as varray(5) of varchar(50); /

/*Step 02- creating stock type object*/ create type stock_type_IT16014046 as object ( company varchar(10), currentPrice number(6,2), Exchange_Traded exch_varray_IT16014046, Last_Devident number(6,2), Earinings_Per_Share number(6,2) ) /

/*Step 03- creating address type object*/ create type address_type_IT16014046 as object ( Streetno varchar(5), Streetname varchar(50), suburb varchar(60), state varchar(5), pin varchar(10) ) /

/*Step 04- creating investment type object*/ create type investment_type_IT1614046 as object ( Company ref stock_type_IT16014046, Purchase_Price number(6,2), DDate date, Qty number(6) ) /

/*Step 05- coverting investment_type_IT16014046 object to table type*/ create type investment_table_IT1614046 as table of investment_type_IT1614046 /

/*Step 06- creating client type object*/ create type client_type_IT1014046 as object ( Client_no char(4), Name varchar(20), Address address_type_IT16014046, Investment investment_table_IT1614046 ) / -creating tables- /*Step 07- cretaing table stock table*/ create table stock_table_IT16014046 of stock_type_IT16014046 ( constraint stock_table_pk primary key (Company) ) /

/*Step 08- cretaing table client table */ create table client_table_IT16014046 of client_type_IT1014046 ( constraint client_table_pk primary key (Client_no) ) nested table Investment store as investment_nttble /

commit / /*Step 09- Altering table*/ alter table investment_nttble add scope for (company) IS stock_table_IT16014046 /

-inserting data--- /*Step 10-Insert data to stock table*/ insert into stock_table_IT16014046 values(stock_type_IT16014046('BHP',10.50,exch_varray_IT16014046('Sydney','New York'),1.50,3.20 )) /

insert into stock_table_IT16014046 values(stock_type_IT16014046('IBM',70.00,exch_varray_IT16014046('New York','London','Tokyo'),4.25,10.00 )) /

insert into stock_table_IT16014046 values(stock_type_IT16014046('INTEL',76.50,exch_varray_IT16014046('New York','London'),5.00,12.40 )) /

insert into stock_table_IT16014046 values(stock_type_IT16014046('FORD',40.00,exch_varray_IT16014046('New York'),2.00,8.50 )) /

insert into stock_table_IT16014046 values(stock_type_IT16014046('GM',60.00,exch_varray_IT16014046('New York'),2.50,9.20 )) /

insert into stock_table_IT16014046 values(stock_type_IT16014046('INFOSYS',45.00,exch_varray_IT16014046('New York'),3.00,7.80 )) /

commit /

/*Step 11-Insert data to Employee table*/ insert into client_table_IT16014046 values (client_type_IT1014046('C001','John Smith',address_type_IT16014046('3','East Av','Bently','WA','6102'), investment_table_IT1614046(investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'BHP'),12.00,'02-OCT-2001',1000),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'BHP'),10.50,'08-JUN-2002',2000),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'IBM'),58.00,'12-FEB-200',500),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'IBM'),65.00,'10-APR-2001',1200),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'INFOSYS'),64.00,'11-AUG-2001',1000)))) /

commit /

insert into client_table_IT16014046 values (client_type_IT1014046('C002','Jil Brody',address_type_IT16014046('42','Bent St','Perth','WA','6001'), investment_table_IT1614046(investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'INTEL'),35.00,'30-JAN-2000',300),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'INTEL'),54.00,'30-JAN-2001',400),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'INTEL'),60.00,'02-OCT-2001',200),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'FORD'),40.00,'05-OCT-1999',300),									investment_type_IT1614046		((select ref(s) from stock_table_IT16014046 s		where s.Company = 'GM'),55.50,'12-DEC-2000',500)))) /

commit /

select c.name, i.company.company as Stock_Name, i.company.currentPrice as Current_Price, i.company.Last_Devident as Last_Devident, i.company.Earinings_Per_Share as Earinings_Per_Share from client_table_IT16014046 c, Table(c.Investment)i /

Tute3 -- (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' /