Iterative Control: WHILE and FOR Loops

1. Write a PL/SQL block to display the country_id and country_name values from the

WF_COUNTRIES table for country_id whose values range from 51 through 55. Use a

WHILE loop. Increment a variable from 51 through 55. Test your variable to see when it

reaches 55. EXIT the loop after you have displayed the 5 countries.

2. Write a PL/SQL block to display the country_id and country_name values from the

WF_COUNTRIES table for country_id whose values range from 51 through 55 in the

reverse order. Use a FOR loop.

3. new_emps table

A. Execute the following statements to build a new_emps table.

DROP TABLE new_emps;

CREATE TABLE new_emps AS SELECT * FROM emp;

ALTER TABLE new_emps ADD stars VARCHAR2(50);

B. Create a PL/SQL block that inserts an asterisk in the stars column for every whole $500

of an employees salary. For example, if an employee has salary of $4000, the string

******** would be inserted. Use the following code as a starting point.

DECLARE

v_empno new_emps.empno%TYPE := &empno;

v_asterisk new_emps.stars%TYPE := *;

v_sal_in_5hundreds new_emps.sal%TYPE;

BEGIN

SELECT NVL(TRUNC(sal/500), 0)

INTO v_sal_5hundreds

FROM new_emps

WHERE empno = v_empno;

FOR

?

END LOOP;

UPDATE new_emps

SET stars = v_asterisk

WHERE empno = v_empno;

END;

/

C. Test your code using empno 7788, 7456, and 7698.

D. Execute a SQL statement to check your results.

Iterative Control: WHILE and FOR Loops

1. Write a PL/SQL block to display the country_id and country_name values from the

WF_COUNTRIES table for country_id whose values range from 51 through 55. Use a

WHILE loop. Increment a variable from 51 through 55. Test your variable to see when it

reaches 55. EXIT the loop after you have displayed the 5 countries. (4 points)

2. Write a PL/SQL block to display the country_id and country_name values from the

WF_COUNTRIES table for country_id whose values range from 51 through 55 in the

reverse order. Use a FOR loop. (4 points)

3. new_emps table (4 points)

A. Execute the following statements to build a new_emps table.

DROP TABLE new_emps;

CREATE TABLE new_emps AS SELECT * FROM emp;

ALTER TABLE new_emps ADD stars VARCHAR2(50);

B. Create a PL/SQL block that inserts an asterisk in the stars column for every whole $500

of an employees salary. For example, if an employee has salary of $4000, the string

******** would be inserted. Use the following code as a starting point.

DECLARE

v_empno new_emps.empno%TYPE := &empno;

v_asterisk new_emps.stars%TYPE := *;

v_sal_in_5hundreds new_emps.sal%TYPE;

BEGIN

SELECT NVL(TRUNC(sal/500), 0)

INTO v_sal_5hundreds

FROM new_emps

WHERE empno = v_empno;

FOR

?

END LOOP;

UPDATE new_emps

SET stars = v_asterisk

WHERE empno = v_empno;

END;

/

C. Test your code using empno 7788, 7456, and 7698.

D. Execute a SQL statement to check your results.Iterative Control: WHILE and FOR Loops

1. Write a PL/SQL block to display the country_id and country_name values from the

WF_COUNTRIES table for country_id whose values range from 51 through 55. Use a

WHILE loop. Increment a variable from 51 through 55. Test your variable to see when it

reaches 55. EXIT the loop after you have displayed the 5 countries. (4 points)

2. Write a PL/SQL block to display the country_id and country_name values from the

WF_COUNTRIES table for country_id whose values range from 51 through 55 in the

reverse order. Use a FOR loop. (4 points)

3. new_emps table (4 points)

A. Execute the following statements to build a new_emps table.

DROP TABLE new_emps;

CREATE TABLE new_emps AS SELECT * FROM emp;

ALTER TABLE new_emps ADD stars VARCHAR2(50);

B. Create a PL/SQL block that inserts an asterisk in the stars column for every whole $500

of an employees salary. For example, if an employee has salary of $4000, the string

******** would be inserted. Use the following code as a starting point.

DECLARE

v_empno new_emps.empno%TYPE := &empno;

v_asterisk new_emps.stars%TYPE := *;

v_sal_in_5hundreds new_emps.sal%TYPE;

BEGIN

SELECT NVL(TRUNC(sal/500), 0)

INTO v_sal_5hundreds

FROM new_emps

WHERE empno = v_empno;

FOR

?

END LOOP;

UPDATE new_emps

SET stars = v_asterisk

WHERE empno = v_empno;

END;

/

C. Test your code using empno 7788, 7456, and 7698. Execute a SQL statement to check your results.