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
- pl/sql
- 2021-09-28
- mhanasmh00489829403
الأجوبة
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;
/Sample Output:
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.
أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال
معلومات ذات صلة