PL/SQL Cursor Exercises: Display the name of the department and their costliest employee
PL/SQL Cursor: Exercise-36 with Solution
Write a PL/SQL block to display the name of the department and their costliest employee.
Displays department name and costliest employee
Sample Solution:
PL/SQL Code:
DECLARE
CURSOR department_cur IS
SELECT department_id,
department_name,
Max(salary) maxsalary
FROM employees
join departments USING (department_id)
GROUP BY department_id,
department_name;
emp_first_name employees.first_name%TYPE;
BEGIN
dbms_output.Put_line('------------------------------------------------------------');
dbms_output.Put_line(Rpad('Name of the Department', 35)
||Rpad('First Name', 25));
dbms_output.Put_line('------------------------------------------------------------');
FOR emp_dept_cur IN department_cur LOOP
BEGIN
SELECT first_name
INTO emp_first_name
FROM employees
WHERE department_id = emp_dept_cur.department_id
AND salary = emp_dept_cur.maxsalary;
dbms_output.Put_line(Rpad(emp_dept_cur.department_name, 35)
|| Rpad(emp_first_name, 25));
EXCEPTION
WHEN too_many_rows THEN
dbms_output.Put_line(Rpad(emp_dept_cur.department_name, 35)
|| ' - More than one employee');
END;
END LOOP;
END;
/
Sample Output:
SQL> / ------------------------------------------------ Name of the Department First Name ------------------------------------------------ Finance Nancy Shipping Adam Public Relations Hermann Purchasing Den Executive Steven Administration Jennifer Accounting Shelley Human Resources Susan Marketing Michael IT Alexander Sales John PL/SQL procedure successfully completed.
Flowchart:
Improve this sample solution and post your code through Disqus
Previous: Write a PL/SQL block to display the employee ID, first name, job title and the start date of present job.
Next: Write a PL/SQL block to display the last name, first name and overpaid amount by using parameters.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- 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
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework