Practice Exercise for PL/SQL Block

1. Create a PL/SQL block that computes the commission amount for a given employee based on the
employee’s salary.
a) Use the DEFINE command to provide the employee ID.
b) If the employee’s salary is less than $5,000, display the bonus amount for the employee as
10% of the salary.
c) If the employee’s salary is between $5,000 and $10,000, display the bonus amount for the
employee as 15% of the salary.
d) If the employee’s salary exceeds $10,000, display the bonus amount for the employee
20% of the salary.
e) If the employee’s salary is NULL, display the bonus amount for the employee as 0.
f) Test the PL/SQL block for each case using the following test cases, and check each bonus
amount.

2. Create a PL/SQL block to retrieve the name of each department from the DEPARTMENTS table and
print each department name on the screen, incorporating an INDEX BY table. Save the code in a
file called [Link] by clicking the Save Script button. Save the script with a .sql extension.
a) Declare an INDEX BY table, MY_DEPT_TABLE, to temporarily store the name of the
departments.
b) Using a loop, retrieve the name of all departments currently in the DEPARTMENTS table
and store them in the INDEX BY table. Use the following table to assign the value for
DEPARTMENT_ID based on the value of the counter used in the loop.

COUNTER DEPARTMENT_ID
1 10
2 20
3 50
4 60
5 70
6 80
7 90

c) Using another loop, retrieve the department names from the PL/SQL table and print them
to the screen, using DBMS_OUTPUT.PUT_LINE

Practice Exercise for Composite Data types

1. Write a PL/SQL block to print information about a given country.

a) Declare a PL/SQL record based on the structure of the COUNTRIES table.
 b) Use the DEFINE command to provide the country ID. Pass the value to the PL/SQL block through a
iSQL*Plus substitution variable.
c) Use DBMS_OUTPUT.PUT_LINE to print selected information about the country. A sample output is
shown below.

d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.

2. Create a PL/SQL block to retrieve the name of each department from the DEPARTMENTS table and print
each department name on the screen, incorporating an INDEX BY table. Save the code in a file called
[Link] by clicking the Save Script button. Save the script with a .sql extension.
a) Declare an INDEX BY table, MY_DEPT_TABLE, to temporarily store the name of the departments.
b) Using a loop, retrieve the name of all departments currently in the DEPARTMENTS table and store
them in the INDEX BY table. Use the following table to assign the value for DEPARTMENT_ID based
on the value of the counter used in the loop.

COUNTER DEPARTMENT_ID
1 10
2 20
3 50
4 60
5 80
6 90
7 110

c) Using another loop, retrieve the department names from the INDEX BY table and print them to the
screen, using DBMS_OUTPUT.PUT_LINE. The output from the program is shown on the next page
 Practice Exercise for Cursors
1. Create a new table for storing the salaries of the employees.

CREATE TABLE top_dogs

( salary NUMBER(8,2));

2. Create a PL/SQL block that determines the top employees with respect to salaries.
a) Accept a number n from the user where n represents the number of top n earners from the
EMPLOYEES table. For example, to view the top five earners, enter 5.
Note: Use the DEFINE command to provide the value for n. Pass the value to the
PL/SQL block through a iSQL*Plus substitution variable.
b) Gather the salaries of the top n people from the EMPLOYEES table. There should be no duplication
in the salaries. If two employees earn the same salary, the salary should be picked up only once.
c) Store the salaries in the TOP_DOGS table.
d) Test a variety of special cases, such as n = 0 or where n is greater than the number of employees in
the EMPLOYEES table. Empty the TOP_DOGS table after each test. The output shown represents
the five highest salaries in the EMPLOYEES table
Practice Exercise for Procedure

1. Create and invoke the ADD_JOB procedure and consider the results.
a) Create a procedure called ADD_JOB to insert a new job into the JOBS table. Provide the ID
and title of the job, using two parameters.
b) Compile the code, and invoke the procedure with IT_DBA as job ID and Database
Administratoras job title. Query the JOBStable to view the results.

c) Invoke your procedure again, passing a job ID of ST_MAN and a job title of Stock Manager.
What happens and why?

2.
3. Create a procedure called UPD_JOB to modify a job in the JOBS table.
a) Create a procedure called UPD_JOB to update the job title. Provide the job ID and a new
title, using two parameters. Include the necessary exception handling if no update
occurs.
b) Compile the code; invoke the procedure to change the job title of the job ID IT_DBA to Data
Administrator. Query the JOBS table to view the results. Also check the exception handling
by trying to update a job that does not exist (you can use job ID IT_WEB and job title Web
Master).

4. Create a procedure called DEL_JOB to delete a job from the JOBS table.
a) Create a procedure called DEL_JOB to delete a job from the JOBStable. Include
the necessary exception handling if no job is deleted.
b) Compile the code; invoke the procedure using job ID IT_DBA. Query the JOBStable
to view the results.
5. Create a procedure called QUERY_EMPto query the EMPLOYEEStable, retrieving the salary and
job ID for an employee when provided with the employee ID.
a) Create a procedure that returns a value from the SALARYand JOB_IDcolumns
for a specified employee ID. Use host variables for the two OUTparameters
salary and job ID.

b) Compile the code, invoke the procedure to display the salary and job ID for employee ID 120.
c) Invoke the procedure again, passing an EMPLOYEE_IDof 300. What happens and why?
Practice Exercise for Function

1. Create and invoke the Q_JOB function to return a job title.

a) Create a function called Q_JOB to return a job title to a host variable.
b) Compile the code; create a host variable G_TITLE and invoke the function with job ID
SA_REP. Query the host variable to view the result.

2. Create a function called ANNUAL_COMP to return the annual salary by accepting two parameters:
an employee’s monthly salary and commission. The function should address NULL values.
a) Create and invoke the function ANNUAL_COMP, passing in values for monthly salary and
commission. Either or both values passed can be NULL, but the function should still return
an annual salary, which is not NULL. The annual salary is defined by the basic formula:
(salary*12) + (commission_pct*salary*12)
b) Use the function in a SELECT statement against the EMPLOYEES table for department 80.

3. Create a procedure NEW_EMP, to insert a new employee into the EMPLOYEES table. The
procedure should contain a call to the VALID_DEPTID function to check whether the department
ID specified for the new employee exists in the DEPARTMENTS table.
a) Create the function VALID_DEPTID to validate a specified department ID. The function
should return a BOOLEAN value.
b) Create the procedure NEW_EMP to add an employee to the EMPLOYEES table. A new row
should be added to the EMPLOYEES table if the function returns TRUE. If the function
returns FALSE, the procedure should alert the user with an appropriate message.
c) Define default values for most parameters. The default commission is 0, the default salary
is 1000, the default department number is 30, the default job is SA_REP, and the default
manager ID is 145. For the employee’s ID, use the sequence EMPLOYEES_SEQ. Provide the
last name, first name, and e-mail address of the employee
d) Test your NEW_EMP procedure by adding a new employee named Jane Harris to
department 15. Allow all other parameters to default. What was the result?
e) Test your NEW_EMP procedure by adding a new employee named Joe Harris to
department 80. Allow all other parameters to default. What was the result?
Practice for Package:

1. Create a package specification and body called JOB_PACK. (You can save the package body and
specification in two separate files.) This package contains your ADD_JOB, UPD_JOB, and DEL_JOB
procedures, as well as your Q_JOB function.

Note: Use the code in your previously saved script files when creating the package.

a) Make all the constructs public.

Note: Consider whether you still need the stand-alone procedures and functions you just packaged.

b) Invoke your ADD_JOB procedure by passing values IT_SYSAN and SYSTEMS ANALYST as
parameters.

2. Create and invoke a package that contains private and public constructs.

a) Create a package specification and package body called EMP_PACK that contains your
NEW_EMP procedure as a public construct, and your VALID_DEPTID function as a private
construct. (You can save the specification and body into separate files.)

b) Invoke the NEW_EMP procedure, using 15 as a department number. Because the

department ID 15 does not exist in the DEPARTMENTS table, you should get an error
message as specified in the exception handler of your procedure.
Invoke the NEW_EMP procedure, using an existing department ID 80.
Practice Exercise for Trigger

1. Changes to data are allowed on tables only during normal office hours of 8:45 a.m. until 5:30
p.m., Monday through Friday.
Create a stored procedure called SECURE_DML that prevents the DML statement from executing outside
of normal office hours, returning the message, “You may only make changes during normal office hours.”
2. a. Create a statement trigger on the JOBS table that calls the above procedure.
b. Test the procedure by temporarily modifying the hours in the procedure and attempting to insert a new
record into the JOBS table. (Example: replace 08:45 with 16:45; This attempt results in an error message)
After testing, reset the procedure hours as specified in question 1 and recreate the procedure as in
question 1 above.

If you have time:

3. Employees should receive an automatic increase in salary if the minimum salary for a job is increased.
Implement this requirement through a trigger on the JOBS table.
a) Create a stored procedure named UPD_EMP_SAL to update the salary amount. This procedure
accepts two parameters: the job ID for which salary has to be updated, and the new minimum
salary for this job ID. This procedure is executed from the trigger on the JOBS table.
b) Create a row trigger named UPDATE_EMP_SALARY on the JOBS table that invokes the procedure
UPD_EMP_SAL, when the minimum salary in the JOBS table is updated for a specified job ID.
c) Query the EMPLOYEES table to see the current salary for employees who are programmers

d) Increase the minimum salary for the Programmer job from 4,000 to 5,000.

e) Employee Lorentz (employee ID 107) had a salary of less than 4,500. Verify that her salary has
been increased to the new minimum of 5,000.