PL/SQL Cursor Exercises: Display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history
PL/SQL Cursor: Exercise-29 with Solution
Write a PL/SQL block to display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history.
Display department name, manager, employees count
DECLARE CURSOR dpt_cur IS SELECT d.department_id id, department_name name, Nvl(first_name, ' ') manager FROM departments d left outer join employees e ON ( d.manager_id = e.employee_id ); emp_count NUMBER(3); job_hist_count NUMBER(3); BEGIN FOR dept_all IN dpt_cur LOOP SELECT Count(*) INTO emp_count FROM employees WHERE department_id = dept_all.id; SELECT Count(*) INTO job_hist_count FROM job_history WHERE department_id = dept_all.id; dbms_output.Put_line(Rpad(dept_all.name, 20) || Rpad(dept_all.manager, 15) || To_char(emp_count, '9999') || To_char(job_hist_count, '9999')); END LOOP; END; /
SQL> / Public Relations Hermann 1 0 Shipping Adam 45 2 Finance Nancy 6 0 Marketing Michael 2 1 Accounting Shelley 2 2 IT Alexander 5 1 Executive Steven 3 2 Human Resources Susan 1 0 Purchasing Den 6 0 Sales John 34 2 Administration Jennifer 1 0 Payroll 0 0 Recruiting 0 0 Retail Sales 0 0 Government Sales 0 0 IT Helpdesk 0 0 NOC 0 0 IT Support 0 0 Operations 0 0 Contracting 0 0 Construction 0 0 Manufacturing 0 0 Benefits 0 0 Shareholder Services 0 0 Control And Credit 0 0 Corporate Tax 0 0 Treasury 0 0 PL/SQL procedure successfully completed.
Improve this sample solution and post your code through Disqus
Previous: Write a PL/SQL block to display department name, head of the department,city, and employee with highest salary.
Next: Write a block in PL/SQL to displays employee name and number of jobs he or she done at past. Displays employee name and job count by job
What is the difficulty level of this exercise?
- New Content published on w3resource :
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework