DATABASE PROGRAMMING ASSIGNMENT # 2 SOLUTION

SECTION 1: PL/SQL Fundamentals


Q1 (10 Points)    For each declaration below, indicate whether it is legal or not. Explain if it is not legal.

v_empno    NUMBER(4);
v_a, v_b, v_c    VARCHAR2(10);
v_address    VARCHAR2(30) NOT NULL;

v_in_stock    BOOLEAN := 1;

Save your answers in the file Section1Q1.

Q2 (10 Points) Your first PL/SQL program - Create an anonymous PL/SQL block to output the phrase “My PL/SQL Program Works” to the screen. Save your code in the file Section1Q2.sql.

Q3 (15 Points) Create an anonymous PL/SQL block that accepts the current salary and raise percentage through SQL*Plus substitution variables and calculate the new salary based on the input:

new salary := old salary * ( 1 + raise percentage / 100 )

The result should be stored in a PL/SQL variable and printed on the screen.

Your program should handle NULL values. A NULL value entered for either one or both of the input values is equivalent to a numerical 0 (Note: To associate NULL values for your SQL*Plus substitution variables, just enter NULL.)

Save your code in the file Section1Q3.sql.

Q4 (15 Points) Create an anonymous PL/SQL block that accepts an integer number N through SQL*Plus substitution variable and then determines for each of the numbers in the range 1 through N inclusive whether it is odd or even. Use the MOD function to determine whether a number is odd or even. For example, MOD(10,2) = 0 and MOD(11,2) = 1. Print the results on the screen.

Your program should handle NULL values. N should be set to 0 if a NULL value is entered.

Save your code in the file Section1Q4.sql.





SECTION 2: Exceptions and Sub-programs

Q1 (50 Points) Write a PL/SQL procedure, parse_name, which accepts a string representing names and returns the first name, the last name, and the title. The first name and last name returned from the procedure should be in upper cases while the title returned should presever the orginal case. The input name string is in one of the following two formats:

FIRST_NAME LAST_NAME TITLE
LAST_NAME, FIRST_NAME TITLE

The first format depicts that the name string starts with a person’s first name and last name with a space in between, followed by a space, and then the title of the person. On the other hand, the second format states that the name string starts with a person’s first name, followed by a comma and a space, and then the last name suffixed with the title with a space before it.

The following are examples of valid name strings conforming to the formats:

    1) Jane Doe Ms.
    2) Doe, Jane Ms.

1
Your procedure should return JANE for first name, DOE for last name, and Ms. for title for the above two input strings.

Also, the following are examples of valid name strings conforming to the formats:

    1) Jane Doe MS.
    2) Doe, Jane MS.
Your procedure should return JANE for first name, DOE for last name, and MS. for title for the above two input strings.

Your procedure should generate exceptions if any one of the three components of the input name string is missing. Further, a message should be printed indicating that the input string is invalid. For instance, the following input strings should cause an exception to be generated and the appropriate messages printed:

    1) Doe
    2) Jane Doe
    3) Doe, Jane

You should wrap parse_name within an anonymous PL/SQL block that accepts the name string through SQL*Plus substitution variables, calls parse_name with the input string, and then prints the first name, last name, and title returned from the parse_name procedure. You should print the title first, followed by the first name and then the last name, separated by spaces.

Save your work in the file named Section2Q1.sql.




General Notes: There are two sections to this assignment. Section1, there are four parts to this section; each part may requires you to submit a file. Section2, there is one part to this section; this part may requires you to submit a file.



So please create a folder for this assignment and submit an electronic copy of your solution files of every section/question, all in one folder zipped and named “LastName HW2” and must be submitted to your D2L/Assignment 2 Submission page. I will give you one submission locations on the course web site.


Again: For example, for assignment #2, you need to create a folder named your LastName HW2 under your c: home directory and save the Section1Q1.doc and your script files Section1Q2.sql, Section1Q3.sql, Section1Q4.sql under the subfolder Section1. Also save your script file Section2Q1.sql, under the subfolder Section2.

Then zip the folder “LastName HW2” and then submit the zipped file to your D2L/Assignment 2 Submission link



SUBMIT YOUR HW2 FOLDER AS ZIP FILE TO YOUR D2L ASSIGNMENT 2 SUBMISSION LINK FOR GRADING. Make sure only one copy submitted.














2