| help on collections [message #448657] |
Wed, 24 March 2010 09:48  |
oraclefrend
Messages: 2
Registered: March 2010
Location: New Delhi
|
Junior Member |
|
|
1.Create an anonymous PL/SQL block to do the following.
Traditionally we use PL/SQL table of Index by Binary_interger.We would like to use a collection of index by varchar2.
Define a PL/SQL table which has only one column that stores number with index as varchar2 type.
Store the emp.sal in PL/SQL table with ename as index.
Define a cursor from table emp.
Loop through the cursor and display name from cursor variable and salary from the collection for each employee.
2.------
Create an anonymous PL/SQL block to do the following
Use Oracle data dictionary to find if a FK constraint exists on table emp.deptno to dept.deptno
If exists Display error message Constraint Exists with the name of the constraint
else create a FK constraint using dynamic SQL.
1. Create an object Type with the following structure.
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2. Create a collection (PL/SQL Table) for the above object type.
II. Create an anonymous PL/SQL Block for the following.
3. Load the following data into the collection.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT
--------------------------------------------------------------------------------
----------
--------------------------------------------------------------------------------
----------
--------------------------------------------------------------------------------
----------
--------------------------------------------------------------------------------
----------
--------------------------------------------------------------------------------
8369 SMITH CLERK 7902 17-DEC-80 800 200 10
8499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 10
WARD SALESMAN 7698 22-FEB-81 1250 20
8566 JONES MANAGER 7839 02-APR-81 2975 20
8654 MARTIN SALESMAN 7698 28-SEP-81 1250 30
8698 BLAKE MANAGER 7839 01-MAY-81 2850 500 30
8782 CLARK MANAGER 7839 09-JUN-81 2450 40
8788 SCOTT ANALYST 7566 30-FEB-87 3000 40
8839 KING PRESIDENT 17-NOV-81 5000 20
4. Insert the above data into emp table using bulk binds. Limit 3 records at a time.
5. Capture the errors and display them.
6. Commit transactions.
--------------------------------------------------------------------------------
4
--------------------------------------------------------------------------------
USE ANALYTIC FUNCTIONS for the below...
1. In a single select query display ename, job, hitedate, sal and cumulative sal.
2. In a single select query display ename, job, hitedate, sal and cumulative sal for each department.
3. In a single select query display ename, job, sal, hiredate, no of days between next hire within the department.
5
--------------------------------------------------------------------------------
Modify the below statement using table emp in WITH clause
select ename, job, hitedate, sal, comm, deptname
from emp, dept
where emp.dept = dep.dept
and emp.dept = 10
union
select ename, job, hitedate, sal, comm, deptname
from emp, dept
where emp.dept = dep.dept
and emp.dept = 20
union
select ename, job, hitedate, sal, comm, deptname
from emp, dept
where emp.dept = dep.dept
and emp.dept = 30
6
--------------------------------------------------------------------------------
In a single query display the department name and total salary for each department from table scott.dept
7
--------------------------------------------------------------------------------
Create a procedure p_get_emp_data with three parameters piv_col_name varchar2, piv_value varchar2, poc ref_cursor
Based on the value in piv_col_name and piv_value query the emp table and return the result set in a Cursor.
Validate the value of piv_col_name, it must be a valid column_name in table emp
Hint: Use Oracle data dictionary to validate the column name.
8
--------------------------------------------------------------------------------
Write a Anonymous PL/SQL block to do the following.
1. Increase the salary of all employees other than managers by 6%+(50$+no_of_days difference with the last joined employee within the department).
New salary of employee cannot be more the salary of his manager.
Compute the increase in tax amount, assuming that tax is 30% of new salary. Also compute the total tax for the year.
Do not update the record but print the department Name, Employee Name, Old salary, new salary, increase in tax, total tax for year order by department name and employee name.
9
--------------------------------------------------------------------------------
1. How would you force the optimizer to use the primary key index when querying table emp.
2. If the query plan is using the index how would you for the optimizer not to use the index.
3. Display the query plan for the query
select ename, job, hitedate, sal, comm, deptname
from emp, dept
where emp.dept = dep.dept
and emp.dept = 10
4. A session (sid = 112) is updating a record on emp which was locked by another session.
Which data dictionary view will reveal the lock information?
5. A Proc was executing fine until yesterday. Suddenly the users are complaining performance issue.
How would you diagnose the problem. Write step by step instructions.
6. How would to gather statistics in oracle release 8i and 9i above?
7. Explain in few words when the optimizer would use nested loop / hash join
|
|
|
|
|
|
|
|
|
|
| Re: help on collections [message #448665 is a reply to message #448657] |
Wed, 24 March 2010 10:06  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
There is no solution for ANY version as you put in your title.
You have to focus on one or two versions.
Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
[Updated on: Wed, 24 March 2010 10:06] Report message to a moderator |
|
|
|
| Re: help on collections [message #448666 is a reply to message #448657] |
Wed, 24 March 2010 10:06 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Do you really think we're going to do your homework for you?
Copying and pasting a complete assignment onto the web and expecting people to do it for you is the height of laziness.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
