Project 7: Advanced SQL*Plus Scripting

Objectives

After completing this lab you should be able to:

Reference Material

Due for Project 7

Turn in the SQL file used to produce the script pictured in figures 1, 2, and 3.

Lab Assignment Steps

  1. Your guidelines are to make your script mimic the following screen captures.
  2. Figure 1 - First prompt the user for employee id, name, and title information.

    Figure 2 - Next, prompt the user for the department. Also, show all valid departments. This is important since there is a Referential Integrity constraint on the employee table which requires valid department IDs.

    Figure 3 - Next, prompt the user for the employee's supervisor. As with department id, show all valid supervisors (assume titles are equal to 'Director' or 'Manager'). Finally, insert the row into the EMPLOYEE table with the information provided by the user.

  3. OPTIONAL STEP: Before terminating the script you should ask the user if they want to enter data for another employee. Add to your sqlplus script the commands to restart if the user types Y for yes after an appropriate prompt.