SAMPLE PL/SQL ASSIGNMENTS

PL/SQL

Anonymous Block

1. Write a PL/SQL block that selects the maximum department number in the department table and store it in a SQL*PLUS variable. And print the results to screen.

2. Create a PL/SQL block to insert a new department number into the Departments table. Use maximum dept number fetched from above and adds 10 to it. Use SQL*PLUS substitution variable for department name. Leave the location AS null.

3. Create a PL/SQL  block to update the location for an existing department. Use substitution variable for dept no. and dept location.

4. Create a PL/SQL Block to delete the department created in exercise 2.    Print to the screen the number of rows affected.

5. 1. Write a PL/SQL  block which accepts employee name, basic and should display

Employee name, PF and net salary.

HRA=31% of basic salary

DA=15% of basic salary

Net salary=basic+HRA+DA-PF

If the basic is less than 3000 PF is 5% of basic salary.

If the basic is between 3000 and 5000 PF is 7% of basic salary.

If the basic is between 5000 and 8000 PF is 8% of basic salary.

6. Write a PL/SQL block to find the salary grade of the specified employee.

            If grade is 1 display ‘the employee is junior engineer’

            If grade is 2 display ‘the employee is engineer’

            If grade is 3 display ‘the employee is lead engineer’

            If grade is 4 display ‘the employee is Manager’

            If grade is 5 display ‘the employee is Project manager’

(Use case expression)

7. Wrtie a PL/SQL block to award an employee with the bonus.

Bonus is 15% of commission drawn by the employee. If the employee does not earn any commission then display a message that ‘employee does not earn any commission’. Otherwise add bonus to the salary of the employee. The block should accept an input for the employee number.

8. Write a PL/SQL block which displays the department name, total no of employees in the department, avg salary of the employees in the department for all the departments from department 10 to department 40 in the Dept table.If no employees are working in the  department ,then display a message that no emplyees are working in that department.

9 .Write a PL/SQL block which accepts employee number and finds the average salary of the employees working in the department where that employee works.

If his salary is more than the average salary of his department, then display message that ‘employee’s salary is more than average salary’ else display ‘employee’s salary is less than average salary’

Procedures and functions

1. Create a procedure that deletes rows from the emp

table. It should accept 1 parameter, job; only delete the

employee’s with that job. Display how many employees were

deleted. Write a PL/SQL block to invoke the procedure.

2. Change the above procedure so that it returns the number of

employees removed via an OUT parameter. Write a PL/SQL block

to invoke the procedure and display how many employees

Were deleted.

3. Convert the above program to a function. Instead of using

an OUT parameter for the number of employees deleted, use

the functions return value. Write a program to invoke the

function and display how many employees were deleted.

4. Create a table having the following structure

Accounts(Account_id, branch_name, amount_balance)  

               a. Write a PL/SQL procedure to perform withdraw operation that only permits a withdrawal if there sufficient funds in the account. The procedure should take Account_id and withdrawal amount as input.

               b. Write a procedure to deposit money into someone's account. The procedure should accept account_id and deposit amount.

               c. Write a procedure to transfer money from one person's account to another. The procedure should table two account_id’s one for giver and one for receiver and the amount to be transferred. 

Cursors and Data Types as in 3GL

1. Write a PL/SQL block to accept an employee number. and use a record variable to store the record of that employee. and insert it into retired_employee table.

Retired_employee table has the following structure

Retired_employee (empno, ename, hiredate, leaveDate, salary, mgr_id, deptno).

Set the  leavedate to the current date.

2. Write a PL/SQL Block to create a PL/SQL table which can store grade and on of employees with that grade. Get the information about the grade and number of employees with that grade and store it in the PL/SQL table. Then retrieve the information from the PL/SQL table and display it on the screen in the following way.

No of employees with the grade 1 are 3

No of employees with the grade 2 are 2

No of employees with the grade 3 are 1

No of employees with the grade 4 are 2

No of employees with the grade 5 are 5

           

Cursors

1. Write a program that gives all employees in department 10 a

15% pay increase. Display a message displaying how many

Employees were awarded the increase.

2. Write a PL/SQL block and use cursor to retrieve the details of the employees with grade 5.and then display employee no,job_id ,max_sal and min_sal and grade for all these employees.

3. Write a PL/SQL block that copies all departments to a table

called old_dept. Do not use a cursor FOR loop. Display

how many rows were copied.

4. Display the names of employees who are working for Department 30.

5. Write a PL/SQL Block that mimics selecting all columns and rows

from the dept table. There is no need to format the output,

just select all columns and all rows. Use a cursor FOR loop.

6. Write a PL/SQL block to display the top 6 employees with respect to salaries using cursors.

7. Use a cursor to retrieve the department number and the department name from the dept table. Pass the department number to another cursor to retrieve from the emp table the details of employee name, job, hiredate and salary of all the employees who work in that department.

8.Write a procedure Raise_salary which gives a specified hike to all the employees working in a specified department.The procedure should take department number and percemtage of hike as input.(Use for update and where current of)

Exception Handling

1. Write a PL/SQL block to select the name of the employee with a given salary value.

If the salary entered returns more than one row,Handle the exception with an appropriate exception handler and insert into SALARY_MESSAGES table the message “more than one employee with a salary of <salary>”

If the salary entered does not return any rows ,handle the exception

With an appropriate exception handler and and insert into the SALARY_MESSAGES table the message “no employee with a salary of <salary>”

If the salary entered returns only one row,insert into the SALARY_MESSAGES table ,the emloyee’s name and the salary amount.

Handle any other exception with an appropriate handler and insert into the SALARY_MESSAGES table the message “some other error occurred”.Test the block for a variety of test cases.

2. Write a PL/SQL block to remove a specified department from the department table. If there are employees in that department, print a message to the user that the department cannot be removed.

(Use pragma exception init)

3. Write a PL/SQL program to update the salary and Department number in the employees

table using SQL*Plus bind variables (or initialized local variables if you prefer). Include the following

exception handling capabilities:

- Define, raise and handle an exception for salary values not in the range of 800 to 5000.

- Define and associate (EXCEPTION_INIT) and exception with the referential integrity constraint

violated system exception (-2291) and use it to handle attempts to set the deptno  to a

value not found in the Department table.

- Provide simple error handling for any other exceptions

Packages

Read the following specification:-

Develop a package that will act as an API (Application Programming Interface) for the items table. We need to protect our data and want to ensure no one writes any code that will directly Access this table.

Here is the structure of the items table: -

Column Data Type Description

item_id NUMBER Internal ID for item

item_number VARCHAR2(10) User item number

description VARCHAR2(30) Item description

status VARCHAR2(1) [T]est or [L]ive

Cost NUMBER Standard cost of item

We need the item_id column to be a sequential number (use

items_item_id_s sequence)

The following business rules must be applied:-

• An item is created as a test item and with a zero cost.

• A procedure or function must be called to promote the item from test

to live. An item cannot be made live with a zero cost.

• Only test items can be removed

We need an API to provide the developer the following facilities:-

• Create new items

• Promote items from test to live

• Remove items

• Change item cost

All API functions and procedures should work with the item_id.

Create a package to implement the above. Remember, try and

work out the requirements for the package first. Determine your

public and private procedures/functions and any data that might

Be needed.

Triggers

1. To compliment the package developed in the last section, the user

has come up with the following addition to the specification.

Implement the above specification using triggers.

When items are removed using the new API you provided, we need to

ensure the item is archived in a table called items_archive.

We also want any changes in item cost to be audited, record the details

of each change in the auditing_cost table.

2. An HR system has an emp table that holds a row for each employee within the company. Each record in the table has a manager field, (mgr) that holds the id for the employee's manager. Write a trigger so that when a manager record is deleted, the mgr field of that manager's employees is set to NULL. In other words, implement the following SQL statement:

WHEN AN EMPLOYEE IS DELETED,

   UPDATE employee SET

      mgr = null

   WHERE

      mgr = employee id of the deleted employee

3. Create one additional table job_salary having columns

Job_salary(job,min_sal,max_sal)

And insert following records into the table.

('CLERK', 800, 1300);

('ANALYST', 3000, 3500);

('SALESMAN', 1250, 1600);

('MANAGER', 2450, 2975);

('PRESIDENT', 5000, 5500);

Write a trigger on emp table so that when a new employee record is inserted or updated and If the new salary has been decreased or does not lie within the salary range  for that job or if more than 10% hike is given, then it should raise exceptions for these three cases mentioned above. And trigger should be executed for employees other than the president.

4. Write a trigger on the table job_salary so that when a record is deleted or updated from this table, the trigger should fire and has to check whether employees having the deleted job exist. If yes, it should raise an exception. and if it is updation, and if there are employees in the emp  table whose salary does not lie within the modified range, then restore old salary ranges.