How to use one column to get value from the other in PL/SQL

Asked

Viewed 39 times

0

I have the following tables in PL/SQL:

employees (
  employee_ed, first_name, last_name, phone_number, hire_data,
  job_id, salary, manager_id, department_id
)
departments (
  department_id, department_name, maganer_id, location_id
)

With them, I need to develop several exercises, but among them I had difficulty in:

  1. List the name and department (id) of the employee who earns the most than your manager (WITHOUT JOIN)
  2. List all departments and the names of their respective departments managers (WITHOUT JOIN)
  3. List the department name and the employee who wins more than your manager (WITH JOIN) 21.List the department name and employee with highest salary of each department (COM JOIN) 22.Distinctly show the department that has the average of the salary difference between employee and manager greater than R$10 mil (WITH JOIN)

After some research, I developed the following code for exercise 20, but it is not yet correct.

SELECT F.first_name, D.department_name
FROM employees F JOIN departments D
ON F.department_id = D.department_id
WHERE F.salary > all (SELECT salary FROM employees
WHERE department_id IN ( SELECT manager_id FROM departments ) );

My difficulty is finding itself in the question of "knowing who is the manager" or make this link between who is the manager of whom. For example, I can list the person who earns the most in a department, but I don’t know how to check if she earns more than the manager.

  • See if it helps https://www.oracletutorial.com/oracle-basics/oracle-self-join/

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.