Using Conditional Controls and Looping

 Using Conditional Controls and Looping (Lab 2 of 7): 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 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 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 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 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. 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