PUFW PL/SQL Fundamentals Oracle SQL Code Paper

Description

1 attachmentsSlide 1 of 1attachment_1attachment_1

Unformatted Attachment Preview

CSC 352/452: DATABASE PROGRAMMING
ASSIGNMENT # 2 (100 Points) Due Date 7/31 11:59pm
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
v_a, v_b, v_c
v_address
v_in_stock
NUMBER(4);
VARCHAR2(10);
VARCHAR2(30) NOT NULL;
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)
2)
3)
Doe
Jane Doe
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 require you to submit a file. Section2, there is one part to this section;
this part may require 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

Purchase answer to see full
attachment

User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.

Reviews, comments, and love from our customers and community:

Article Writing

Keep doing what you do, I am really impressed by the work done.

Alexender

Researcher

PowerPoint Presentation

I am speechless…WoW! Thank you so much!

Stacy V.

Part-time student

Dissertation & Thesis

This was a very well-written paper. Great work fast.

M.H.H. Tony

Student

Annotated Bibliography

I love working with this company. You always go above and beyond and exceed my expectations every time.

Francisca N.

Student

Book Report / Review

I received my order wayyyyyyy sooner than I expected. Couldn’t ask for more.

Mary J.

Student

Essay (Any Type)

On time, perfect paper

Prof. Kate (Ph.D)

Student

Case Study

Awesome! Great papers, and early!

Kaylin Green

Student

Proofreading & Editing

Thank you Dr. Rebecca for editing my essays! She completed my task literally in 3 hours. For sure will work with her again, she is great and follows all instructions

Rebecca L.

Researcher

Critical Thinking / Review

Extremely thorough summary, understanding and examples found for social science readings, with edits made as needed and on time. Transparent

Arnold W.

Customer

Coursework

Perfect!

Joshua W.

Student

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>