PL/SQL Cursor Exercises: Display department name, head of the department,city, and employee with highest salary
PL/SQL Cursor: Exercise-28 with Solution
Write a PL/SQL block to display department name, head of the department,city, and employee with highest salary. Display name, city, hod, emp with highest salary
DECLARE CURSOR dpt_cur IS SELECT d.department_id id, department_name dptname, city, Nvl(first_name, '...') manager FROM departments d left outer join employees e ON ( d.manager_id = e.employee_id ) join locations l USING(location_id) ORDER BY 2; emp_name employees.first_name%TYPE; emp_max_salary employees.salary%TYPE; BEGIN FOR dept_all IN dpt_cur LOOP SELECT Max(salary) INTO emp_max_salary FROM employees WHERE department_id = dept_all.id; IF emp_max_salary IS NULL THEN emp_name := '...'; ELSE SELECT first_name INTO emp_name FROM employees WHERE department_id = dept_all.id AND salary = emp_max_salary; END IF; dbms_output.Put_line(Rpad(dept_all.dptname, 20) || Rpad(dept_all.manager, 15) || Rpad(dept_all.city, 20) || Rpad(emp_name, 20)); END LOOP; END; /
SQL> / Accounting Shelley Seattle Shelley Administration Jennifer Seattle Jennifer Benefits ... Seattle ... Construction ... Seattle ... Contracting ... Seattle ... Control And Credit ... Seattle ... Corporate Tax ... Seattle ... Executive Steven Seattle Steven Finance Nancy Seattle Nancy Government Sales ... Seattle ... Human Resources Susan London Susan IT Alexander Southlake Alexander IT Helpdesk ... Seattle ... IT Support ... Seattle ... Manufacturing ... Seattle ... Marketing Michael Toronto Michael NOC ... Seattle ... Operations ... Seattle ... Payroll ... Seattle ... Public Relations Hermann Munich Hermann Purchasing Den Seattle Den Recruiting ... Seattle ... Retail Sales ... Seattle ... Sales John Oxford John Shareholder Services... Seattle ... Shipping Adam South San Francisco Adam Treasury ... Seattle ... PL/SQL procedure successfully completed.
Improve this sample solution and post your code through Disqus
Previous: Write a PL/SQL block to show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch.
Next: 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.
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