DBS501 Lab 2 Solution

You need to provide BOTH --- Code and Output




This is NOT a valid scale. Must be C or F.
PL/SQL procedure successfully completed.


Your converted temperature in F is exactly 86 PL/SQL procedure successfully completed.


Your converted temperature in C is exactly -31.7 PL/SQL procedure successfully completed.
999
102
101

PL/SQL procedure successfully completed.

109
PL/SQL procedure successfully completed.
3) Write a PL/SQL block that will ask for a Positive Integer input and then will calculate the sum of all Even ( or Odd) integers between 1 and that value, depending whether the input value is Even or Odd. Use WHILE LOOP control logic. Here are the possible outputs:

12
Please enter a Positive Integer:
The sum of Even integers between 1 and 12 is 42 PL/SQL procedure successfully completed.
901
Please enter a Positive Integer:
The sum of Odd integers between 1 and 901 is 203401 PL/SQL procedure successfully completed.
4) Write a PL/SQL block that will ask for department’s Location Id and then will figure out how many departments exist there and how many employees work on that location. Based on these answers you will construct a Nested For Loop that will display these answers in a numeric format like shown below. The first location 1800 has only one department with 2 employees, while the other location 1400 has 3 departments with 7 employees altogether (after the Update).
Obviously, without an explicit cursor you can NOT show how many employees works in each department. So, this is just an exercise in creating nested loops.
But, firstly you need to relocate departments 40 and 70 to location 1400. Do NOT commit, you will rollback it when you finish your block.
UPDATE Departments SET location_id = 1400
WHERE department_id IN (40,70) ;
1800
Enter valid Location Id:

Outer Loop: Department #1
* Inner Loop: Employee #1
* Inner Loop: Employee #2
PL/SQL procedure successfully completed.

1400
Enter valid Location Id:
Outer Loop: Department #1
* Inner Loop: Employee #1
* Inner Loop: Employee #2
* Inner Loop: Employee #3
* Inner Loop: Employee #4
* Inner Loop: Employee #5
* Inner Loop: Employee #6
* Inner Loop: Employee #7
Outer Loop: Department #2
* Inner Loop: Employee #1
* Inner Loop: Employee #2
* Inner Loop: Employee #3
* Inner Loop: Employee #4
* Inner Loop: Employee #5
* Inner Loop: Employee #6
* Inner Loop: Employee #7
Outer Loop: Department #3
* Inner Loop: Employee #1
* Inner Loop: Employee #2
* Inner Loop: Employee #3
* Inner Loop: Employee #4
* Inner Loop: Employee #5
* Inner Loop: Employee #6
* Inner Loop: Employee #7
PL/SQL procedure successfully completed.
ROLLBACK
Rollback complete.