The code below using basic loop. Please edit the codes, use cursor for loop (For dummy_index...

     

Transcribed Image Text:

The code below using basic loop. Please edit the codes, use cursor for loop ("For dummy_index IN cursor_name LOOP"). The output of your new codes should be the same as the original program. DECLARE CURSOR C IS SELECT FROM WHERE BEGIN No 1st Name email bosaid 1 2 3 4 5 END; (*---- OPEN C LOOP DBMS OUTFUT.put line (No DBMS_OUTPUT.put_line last name, email, Manager_id employees Job ID 'SA_MAN' END LOOP: CLOSE C varchar2 (261; varchar2 (26); number (6) Emp last Name FETCH INTO 1stName, email, bossid ; EXIT WHEN NOTFOUND; DBMS_OUTPUT.put_line (rpad | CROWCOUNT, 7) 11 RPAD (1stName, 201 11 RPAD (email, 15) || bossid); Emp last Name Russell Partnera. Errazuriz Cambrault Zlotkey Email Email JRUSSEL KPARTNER AERRAZUR GCAMBRAU EZLOTKEY Boss ID Boss ID'); 100 100 100 100 100 Q2. Assume that the company has decided a one-time bonus for the employees in department 80, the amount is decided as below. For employees that get commission pet equal to or great than 25: if salary equals to or is more than $10,000, the bonus will be $1,300 if salary equals to or is more than $7,000 and less than $10,000, the bonus will be $1,200 if salary is less than $7,000, the bonus will be $1,100. For employees that get commission pct less than .25 and equal to or great than .15: if salary equals to or is more than $10,000, the bonus will be $1,200 if salary equals to or is more than $7,000 and less than $10,000, the bonus will be $1,100 All other employees in department 80 will get $900. Write an anonymous PL/SQL program, retrieve the info needed for each employee (at department 80 only), calculate the amount of bonus he/she should get. Your program will accumulate the bonus from each employee, print out the total amount of the bonuses. (for submission, no need to print out individual bonus) Q3. This question will use some view(table) in the system catalog: "All_tab_columns". In system catalog, the view "All_tables" lists all tables that user can read, but it does not list the details of the columns in each table. The view "All_tab_columns" lists the "owner" of the tables, the "table_name" and information of columns under each table, such as the "column_name", the column "data type" and the length of data type of that column. The command "desc All_tab_columns" will display the info related. Do not simply "select * from all_tab_columns", the output is not easy to read. You need to use SQL Plus commands to format the output, such as: column Owner format A10 column table_name format A30 In this question, you will write an anonymous PL/SQL program. The program will define a cursor with two parameters. The select statement will retrieve the column_name, data type and data_length from the view All_tab_columns for a certain "owner" and a certain "table_name" that will be passed to it through the parameters. In the executable section, you will open that cursor two times, first time with the parameters of USER and 'DEPARTMENTS, the second time with USER and 'LOCATIONS. Your program will print out the information you have retrieved. Following lines are just for your reference, not needed for this question. These SQL and SQL Plus commands will display the table-column info of all tables you have created: Column table_name format A25 Column column_name format A25 Column data type format A15 select table_name, column_name, data type, data_length from all_tab_columna where owner user; Here "user" is the current login account. Q4. Cursor for update, based on table employees. Write an anonymous PL/SQL program, it will increase the salary 3% of their current salary (it means salary will be salary * 1.03 ) for those employees whose salary is less than 2,400 and without commission. Your program should declare a cursor with "FOR UPDATE" request, thus it can request the system to lock those records retrieved and change these records later. After update, print out some info about those affected employees, display their ID, last name, old salaries (before this time increase) and new salary. It is better to rollback after the print out and before the end of program. (in the real life, program should commit the changes if everything runs fine). Q5. Cursor Variable Define a strong cursor variable type, name it Loc_CurTyp. Its return type is based on table LOCATIONS. Locations%ROWTYPE, Then declare a cursor variable of this type. In your program, you will open the cursor for two times. The first time, you will open the cursor variable for a select statement as SELECT FROM LOCATIONS WHERE country_id = 'US' Your program will print out the location ID, street_address and city for these locations. (please pay attention, the Cursor-For loop does NOT work for cursor variable) Second time. You open that cursor variable for those locations that are currently used by some departments having department head (manager_id in department table) assigned. If you have difficult, please refer to the hints at the end of this file In the second time, beside print out all the attributes as in the first time, you also need to print out the country ID. Hints: 1. Hints for Q1. this is a simple exercise. First run the given codes and get the output. Then change the LOOP statements, using For dummy_ind in cursor_name,... remember the steps discussed in the class. 2. Hints for Q2. retrieve each employee's info in department 80, inside the loop, we need if- then, or case to assign the value of the bonus. It could be easier to handle two variables, one is bonus for individual employee, another total-bonus, for example: OPEN C ; Loop Fetch cursor_name into variable_list : EXIT when NOTFOUND; IF comm >= 25 THEN IF sal >= THEN ELSIF sal >=.. THEN ELSE bonus : bonus = bonus 1 Nested IF THEN ; # 7 END IF; ELSIF (comm < IF sal > END IF ELSE END IF bonus : end of nested IF END LOOP; and comm > .) THEN -- Nested IF THEN Total total + bonus ; Bonus: 0; --reset the bonus to 0 for next employee. 3. Hints for Q3. using cursor with parameter, such as: Cursor cursor_name owner in IN varchar2, table in IN varchar2) IS select column_name, from all_tab_columns where owner owner in and table name= table in; then later: OPEN cursor_name (USER, DEPARTMENTS ); for basic loop for cursor for loop Or FOR dumny indx in cursor name (USER, 4. Hints for Q4. Just exercise on cursor for UPDATE, inside the loop, for each individual employee, if that qualifies for update, then use the where current of cursor_name clause: UPDATE employees SET salary... WHERE current of cursor_name; 5. Hints for Q5, cursor variable, similar as example on the notes. Define a strong type, then define a variable for this type. In the loop, open the cursor variable two times. Recall, for cursor variable, we cannot use Cursor for loop. Second time open the cursor: OPEN cursor variable_name FOR SELECT FRON LOCATIONS WHERE location_id in (select location id from departments where manager id is not null); The code below using basic loop. Please edit the codes, use cursor for loop ("For dummy_index IN cursor_name LOOP"). The output of your new codes should be the same as the original program. DECLARE CURSOR C IS SELECT FROM WHERE BEGIN No 1st Name email bosaid 1 2 3 4 5 END; (*---- OPEN C LOOP DBMS OUTFUT.put line (No DBMS_OUTPUT.put_line last name, email, Manager_id employees Job ID 'SA_MAN' END LOOP: CLOSE C varchar2 (261; varchar2 (26); number (6) Emp last Name FETCH INTO 1stName, email, bossid ; EXIT WHEN NOTFOUND; DBMS_OUTPUT.put_line (rpad | CROWCOUNT, 7) 11 RPAD (1stName, 201 11 RPAD (email, 15) || bossid); Emp last Name Russell Partnera. Errazuriz Cambrault Zlotkey Email Email JRUSSEL KPARTNER AERRAZUR GCAMBRAU EZLOTKEY Boss ID Boss ID'); 100 100 100 100 100 Q2. Assume that the company has decided a one-time bonus for the employees in department 80, the amount is decided as below. For employees that get commission pet equal to or great than 25: if salary equals to or is more than $10,000, the bonus will be $1,300 if salary equals to or is more than $7,000 and less than $10,000, the bonus will be $1,200 if salary is less than $7,000, the bonus will be $1,100. For employees that get commission pct less than .25 and equal to or great than .15: if salary equals to or is more than $10,000, the bonus will be $1,200 if salary equals to or is more than $7,000 and less than $10,000, the bonus will be $1,100 All other employees in department 80 will get $900. Write an anonymous PL/SQL program, retrieve the info needed for each employee (at department 80 only), calculate the amount of bonus he/she should get. Your program will accumulate the bonus from each employee, print out the total amount of the bonuses. (for submission, no need to print out individual bonus) Q3. This question will use some view(table) in the system catalog: "All_tab_columns". In system catalog, the view "All_tables" lists all tables that user can read, but it does not list the details of the columns in each table. The view "All_tab_columns" lists the "owner" of the tables, the "table_name" and information of columns under each table, such as the "column_name", the column "data type" and the length of data type of that column. The command "desc All_tab_columns" will display the info related. Do not simply "select * from all_tab_columns", the output is not easy to read. You need to use SQL Plus commands to format the output, such as: column Owner format A10 column table_name format A30 In this question, you will write an anonymous PL/SQL program. The program will define a cursor with two parameters. The select statement will retrieve the column_name, data type and data_length from the view All_tab_columns for a certain "owner" and a certain "table_name" that will be passed to it through the parameters. In the executable section, you will open that cursor two times, first time with the parameters of USER and 'DEPARTMENTS, the second time with USER and 'LOCATIONS. Your program will print out the information you have retrieved. Following lines are just for your reference, not needed for this question. These SQL and SQL Plus commands will display the table-column info of all tables you have created: Column table_name format A25 Column column_name format A25 Column data type format A15 select table_name, column_name, data type, data_length from all_tab_columna where owner user; Here "user" is the current login account. Q4. Cursor for update, based on table employees. Write an anonymous PL/SQL program, it will increase the salary 3% of their current salary (it means salary will be salary * 1.03 ) for those employees whose salary is less than 2,400 and without commission. Your program should declare a cursor with "FOR UPDATE" request, thus it can request the system to lock those records retrieved and change these records later. After update, print out some info about those affected employees, display their ID, last name, old salaries (before this time increase) and new salary. It is better to rollback after the print out and before the end of program. (in the real life, program should commit the changes if everything runs fine). Q5. Cursor Variable Define a strong cursor variable type, name it Loc_CurTyp. Its return type is based on table LOCATIONS. Locations%ROWTYPE, Then declare a cursor variable of this type. In your program, you will open the cursor for two times. The first time, you will open the cursor variable for a select statement as SELECT FROM LOCATIONS WHERE country_id = 'US' Your program will print out the location ID, street_address and city for these locations. (please pay attention, the Cursor-For loop does NOT work for cursor variable) Second time. You open that cursor variable for those locations that are currently used by some departments having department head (manager_id in department table) assigned. If you have difficult, please refer to the hints at the end of this file In the second time, beside print out all the attributes as in the first time, you also need to print out the country ID. Hints: 1. Hints for Q1. this is a simple exercise. First run the given codes and get the output. Then change the LOOP statements, using For dummy_ind in cursor_name,... remember the steps discussed in the class. 2. Hints for Q2. retrieve each employee's info in department 80, inside the loop, we need if- then, or case to assign the value of the bonus. It could be easier to handle two variables, one is bonus for individual employee, another total-bonus, for example: OPEN C ; Loop Fetch cursor_name into variable_list : EXIT when NOTFOUND; IF comm >= 25 THEN IF sal >= THEN ELSIF sal >=.. THEN ELSE bonus : bonus = bonus 1 Nested IF THEN ; # 7 END IF; ELSIF (comm < IF sal > END IF ELSE END IF bonus : end of nested IF END LOOP; and comm > .) THEN -- Nested IF THEN Total total + bonus ; Bonus: 0; --reset the bonus to 0 for next employee. 3. Hints for Q3. using cursor with parameter, such as: Cursor cursor_name owner in IN varchar2, table in IN varchar2) IS select column_name, from all_tab_columns where owner owner in and table name= table in; then later: OPEN cursor_name (USER, DEPARTMENTS ); for basic loop for cursor for loop Or FOR dumny indx in cursor name (USER, 4. Hints for Q4. Just exercise on cursor for UPDATE, inside the loop, for each individual employee, if that qualifies for update, then use the where current of cursor_name clause: UPDATE employees SET salary... WHERE current of cursor_name; 5. Hints for Q5, cursor variable, similar as example on the notes. Define a strong type, then define a variable for this type. In the loop, open the cursor variable two times. Recall, for cursor variable, we cannot use Cursor for loop. Second time open the cursor: OPEN cursor variable_name FOR SELECT FRON LOCATIONS WHERE location_id in (select location id from departments where manager id is not null);

Answer rating: 100% (QA)

The image you have shared appears to contain the text of a coding assignment involving SQL and PLSQL ... View the full answer