DBM405B Lab 2 in PL/SQL – Using Conditional Controls and Looping – PL/SQL Script with 12 Screenshots – Instant Delivery – Perfect Solution

Lab Price = $22
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

Using Conditional Controls and Looping (Lab 2 of 7):
This iLab is due at the end of Week 2.
Scenario/Summary:
You will be developing SQL PL procedures that demonstrate basic flow of control statements, such as conditionals (decisions) and looping (repetition).

Upon completion of this lab, students will have hands-on experience using flow of control constructs in SQL PL.
Deliverables:
Lab Steps (to be completed in Power Systems) Points
Step 1 Create a new table 5 Pts.
Step 2 Populate the table with data 5 Pts.
Step 3 Create a procedure that uses conditional logic 15 Pts.
Step 4 Create a procedure that uses looping 15 Pts.
Step 5 Create a procedure that uses both conditional logic and looping 20 Pts.
Total Lab Points 60 Pts.
You should submit to the Dropbox a Word document containing:
• SQL PL code for each task.
• Screenshots of the output from each task (each screenshot immediately follows the associated code).
iLab Steps

Step 1: Create a New Table
a. Create a table called Branch using the following data definition parameters:

Attribute Data Type Size Restrictions
Branch Number Numeric Data 3 User-defined Primary Key
Branch Name Alphanumeric Data 30
Branch Location Alphanumeric Data 20
Number of Employees Numeric Data 3

Step 2: Populate the Table with Data
a. Add the following data to your Branch table. Use the COMMIT statement when you are done. (This need not be done inside of a procedure – just adding the records in straight SQL is fine.)
Branch_no Branch_Name Branch_Location Num_ Emps
20 Evanston Library Evanston 45
30 Oak Brook Library Oak Brook 20
40 Darien Local Library Darien 15
50 Chicago Public Library Park View NULL
b. Use a SELECT statement to display the rows you have added.
Step 3: Use Conditional Logic

a. Write a SQL PL procedure that accepts a branch number as an input parameter. The procedure should output a message of the following form (with brackets representing variable content):

For branch [branch_no], there are [num_emps] employees.

If the number of employees for the specified branch is null, the procedure should output a message of the following form:

For branch [branch_no], the number of employees is unknown.

If the branch number entered does not exist in the table, the procedure should output a message of the following form:

Branch [branch_no] does not exist.

Note: DO NOT hard-code any specific branch numbers in your procedure. Make sure that it would still work correctly if the data in the table were different.

b. Show the results of calling your procedure with the following branch numbers: 20, 50, and 70.

Step 4: Use Looping

a. Create a SQL PL procedure that takes an Integer as an input parameter. The procedure should count upward from this number by fives, stopping when it reaches or passes 100. It should output a message of the following form:

Numbers counted: [a space-separated list of the numbers it has counted]

For example, if the original number entered was 60, the procedure would output the following:
Numbers counted: 60 65 70 75 80 85 90 95 100

If the original number entered was 72, the procedure would output the following:
Numbers counted: 72 77 82 87 92 97

If the original number entered was greater than 100, the procedure would output the following:

Numbers counted:

Note: Your procedure should not hard-code any outputs for particular values – make it flexible so that it can accommodate whatever value is entered.

b. Show the results of calling your procedure with the following values: 50, 63, and 104.

Step 5: Use Conditionals and Looping

a. Write a SQL PL procedure that accepts a branch number as an input parameter. The procedure should count upward from this number by ones, testing each value to see if a corresponding branch exists and if it has a NULL number of employees. Once the procedure encounters a branch that exists and has a NULL number of employees, it should output a message of the following form:
Branch [branch_no] has an unknown number of employees.
If the procedure counts up to 100 and finds no branches having a null number of employees, it should output the following message:
No branch found with an unknown number of employees.

If the input parameter entered is greater than 100, the procedure should output the following message:
Please try a number less than or equal to 100.

Note: DO NOT hard-code any specific branch numbers in your procedure. Make sure that it would still work correctly if the data in the table were different.

b. Test your procedure using the following input values: 20, 55, and 104.

END OF LAB 2

Relevant Material
Lab Price = $22
Please feel free to send us your queries at: support@iqrajavaid.com
Screenshots
Lab2: Step5 -Output
Lab2: Step5 -Output
Lab2: Step5 - Execution
Lab2: Step5 – Execution
Lab2: Step3 - Output
Lab2: Step3 – Output
Lab2: Step2 - TableCreation
Lab2: Step2 – TableCreation
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on support@iqrajavaid.com
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $22
Please feel free to send us your queries at: support@iqrajavaid.com

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply