Question

1 Approved Answer

Posted on Sep 26, 2024

2. For this exercise, you use the employees and departments table. Create a PL/SQL block that fetches and displays the names of the five departments

2. For this exercise, you use the employees and departments table. Create a PL/SQL block that fetches and displays the names of the five departments with the most employees (Hint: use a join condition). For each of these departments, display the department name and the number of employees. Order your output so that the department with the most employees is displayed first. Use %ROWTYPE and the explicit cursor attribute %ROWCOUNT.

3. Look again at the block you created in question 2. What if you wanted to display 10 departments instead of 5? There are only 7 rows in the departments table. What do you think would happen?

4. In real life we would not know how many rows the table contained. Modify your block from question 2 so that it will exit from the loop when either 10 rows have been fetched and displayed, or when there are no more rows to fetch. Test the block again.

5. Modify the following PL/SQL block so that it uses a cursor FOR loop. Keep the explicit cursor declaration in the DECLARE section. Test your changes.

DECLARE CURSOR wf_currencies_cur IS SELECT currency_code, currency_name

FROM wf_currencies ORDER BY currency_name; v_curr_code wf_currencies.currency_code%TYPE; v_curr_name wf_currencies.currency_name%TYPE;

BEGIN OPEN wf_currencies_cur; LOOP FETCH wf_currencies_cur

INTO v_curr_code, v_curr_name; EXIT WHEN wf_currencies_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_curr_code || ' ' || v_curr_name);

END LOOP;

CLOSE wf_currencies_cur;

END;

7. Write and run a PL/SQL block which produces a listing of departments and their employees. Use the departments and employees tables. In a cursor FOR loop, retrieve and display the department_id and department_name for each department, and display a second line containing ---------- as a separator. In a nested cursor FOR loop, retrieve and display the first_name, last_name and salary of each employee in that department, followed by a blank line at the end of each department. Order the departments by department_id, and the employees in each department by last_name.

You will need to declare two cursors, one to fetch and display the departments, the second to fetch and display the employees in that department, passing the department_id as a parameter.

Your output should look something like this (only the first few departments are shown):

10 Administration

-----------------------------

Jennifer Whalen 4400

20 Marketing

-----------------------------

Pat Fay 6000

Michael Hartstein 13000

50 Shipping

-----------------------------

Curtis Davies 3400

Randall Matos 2600

Kevin Mourgos 5800

Trenna Rajs 3500

Peter Vargas 2500

image text in transcribed

employees EMPLOYEE_ID FIRST_NAME 100 Steven 101 Neena 102 Lex 103 Alexander 104 Bruce 107 Diana 124 Kevin 141 Trenna 142 Curtis 143| Randall 144 Peter 149 Eleni 174 Ellen 176 Jonathon 178 Kimberely 200 Jennifer 201 Michael 202 Pat 205 Shelley 206 William LAST_NAME King Kochhar De Haan Hunold Ernst Lorentz Mourgos Rais Davies Matos EMAIL SKING INKOCHHAR LDEHAAN AHUNOLD BERNST DLORENTZ KMOURGOS TRAJS CDAVIES TRMATOS PVARGAS EZLOTKEY EABEL JTAYLOR KGRANT JWHALEN MHARTSTE PFAY SHIGGINS WGIETZ PHONE_NUMBER HIRE_DATE JOB_ID 515.123.4567 17-Jun-87 AD_PRES 515.123.4568 21-Sep-89 AD_VP 515.123.4569 13-Jan-93| AD_VP 590.423.4567 03-Jan-90 IT_PROG 590.423.4568 21-May-91 IT_PROG 590.423.5567 07-Feb-99 IT_PROG 650.123.5234 16-Nov-99 ST_MAN 650.121.8009 17-Oct-95 ST_CLERK 650.121.2994 29-Jan-97 ST_CLERK 650.121.2874 15-Mar-98 ST_CLERK 650.121.2004 09-Jul-98 ST_CLERK 011.44. 1344.429018 29-Jan-00 SALMAN 011.44.1644.429267 11-May-96 SA_REP 011.44.1644.429265 24-Mar-98 SA_REF 011.44. 1644.429263 24-May-99 SA_REP 515. 123.4444 17-Sep-87 AD_ASST 515. 123.5555 17-Feb-96 MK_MAN 603.123.6666 17-Aug-97 MK_REP 515. 123.8080 07-Jun-94 AC_MGR 515.123.8181 07-Jun-94 AC_ACCOUNT SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID 240001 90 17000 100 90 17000) 100 90 9000 102 60 6000 103 60 4200 103 60 5800 100 50 35001 124 50 3100 124 50 26001 124 50 2500 124 50 10500 0.2 100 80 110001 0.3 149 80 8600 0.2 149 80 7000 0.15 149 4400 101 10 130001 100 20 6000 2011 20 12000 101 110 8300 205 110 Vargas Zlotkey Abel Taylor Grant Whalen Hartstein Fay Higgins Gietz departments DEPARTMENT_ID DEPARTMENT_NAME 10 Administration 20 Marketing 50 Shipping 60 IT 80 Sales 90 Executive 110 Accounting 190 Contracting MANAGER_ID LOCATION_ID 200 1700 201 1800 124 1500 103 1400 149 2500 100 1700 2051 1700 1700 jobs JOB_ID AD_PRES AD_VP AD_ASST AC_MGR AC_ACCOUNT SA_MAN SA_REP ST_MAN ST_CLERK IT_PROG MK_MAN MK_REP JOB_TITLE MIN_SALARY MAX_SALARY President 20000 40000 Administration Vice President 15000 30000 Administration Assistant 3000 6000 Accounting Manager 8200 16000 Public Accountant 4200 9000 Sales Manager 10000 20000 Sales Representative 60001 12000 Stook Manager 55001 8500 Stock Clerk 20001 5000 Programmer 40001 10000 Marketing Manager 9000 15000 Marketing Representative 4000 9000 employees EMPLOYEE_ID FIRST_NAME 100 Steven 101 Neena 102 Lex 103 Alexander 104 Bruce 107 Diana 124 Kevin 141 Trenna 142 Curtis 143| Randall 144 Peter 149 Eleni 174 Ellen 176 Jonathon 178 Kimberely 200 Jennifer 201 Michael 202 Pat 205 Shelley 206 William LAST_NAME King Kochhar De Haan Hunold Ernst Lorentz Mourgos Rais Davies Matos EMAIL SKING INKOCHHAR LDEHAAN AHUNOLD BERNST DLORENTZ KMOURGOS TRAJS CDAVIES TRMATOS PVARGAS EZLOTKEY EABEL JTAYLOR KGRANT JWHALEN MHARTSTE PFAY SHIGGINS WGIETZ PHONE_NUMBER HIRE_DATE JOB_ID 515.123.4567 17-Jun-87 AD_PRES 515.123.4568 21-Sep-89 AD_VP 515.123.4569 13-Jan-93| AD_VP 590.423.4567 03-Jan-90 IT_PROG 590.423.4568 21-May-91 IT_PROG 590.423.5567 07-Feb-99 IT_PROG 650.123.5234 16-Nov-99 ST_MAN 650.121.8009 17-Oct-95 ST_CLERK 650.121.2994 29-Jan-97 ST_CLERK 650.121.2874 15-Mar-98 ST_CLERK 650.121.2004 09-Jul-98 ST_CLERK 011.44. 1344.429018 29-Jan-00 SALMAN 011.44.1644.429267 11-May-96 SA_REP 011.44.1644.429265 24-Mar-98 SA_REF 011.44. 1644.429263 24-May-99 SA_REP 515. 123.4444 17-Sep-87 AD_ASST 515. 123.5555 17-Feb-96 MK_MAN 603.123.6666 17-Aug-97 MK_REP 515. 123.8080 07-Jun-94 AC_MGR 515.123.8181 07-Jun-94 AC_ACCOUNT SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID 240001 90 17000 100 90 17000) 100 90 9000 102 60 6000 103 60 4200 103 60 5800 100 50 35001 124 50 3100 124 50 26001 124 50 2500 124 50 10500 0.2 100 80 110001 0.3 149 80 8600 0.2 149 80 7000 0.15 149 4400 101 10 130001 100 20 6000 2011 20 12000 101 110 8300 205 110 Vargas Zlotkey Abel Taylor Grant Whalen Hartstein Fay Higgins Gietz departments DEPARTMENT_ID DEPARTMENT_NAME 10 Administration 20 Marketing 50 Shipping 60 IT 80 Sales 90 Executive 110 Accounting 190 Contracting MANAGER_ID LOCATION_ID 200 1700 201 1800 124 1500 103 1400 149 2500 100 1700 2051 1700 1700 jobs JOB_ID AD_PRES AD_VP AD_ASST AC_MGR AC_ACCOUNT SA_MAN SA_REP ST_MAN ST_CLERK IT_PROG MK_MAN MK_REP JOB_TITLE MIN_SALARY MAX_SALARY President 20000 40000 Administration Vice President 15000 30000 Administration Assistant 3000 6000 Accounting Manager 8200 16000 Public Accountant 4200 9000 Sales Manager 10000 20000 Sales Representative 60001 12000 Stook Manager 55001 8500 Stock Clerk 20001 5000 Programmer 40001 10000 Marketing Manager 9000 15000 Marketing Representative 4000 9000