CODEWITHSUNDEEP
sql
Sergiu Postei
Sergiu Postei

Reputation: 25

I need to display all employees which were hired before their managers. Can somebody help me?

Table photo

I need to run an SQL query which will display all the employees who were hired before their managers and they have the minimum salary for his function.

SELECT ENAME,JOB, SALAR FROM EMP
WHERE JOB = 'SALESMAN'
OR JOB = 'CLERK'
OR JOB = 'ANALYST'

Upvotes: 0

Views: 2955

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use window functions:

select . . .
from (select e.*, m.hiredate as mgr_hiredate,
             min(salar) over (partition by job) as min_salar
      from emp e left join
           emp em
           on e.mgr = m.empno
     ) em
where hiredate < mgr_hiredate and salar = min_salar;

Upvotes: 2

Alexey S. Larionov
Alexey S. Larionov

Reputation: 7937

You can join EMP to itself to add details about each employee's manager (we're interested in his HIREDATE). Later in WHERE section you can check that manager's HIREDATE is later than corresponding employee's.

You can also pre-calculate all minimal salaries for each job, then join this information to each employee and later you can check that employee's salary is equal to the minimal.

SELECT EMPNO, ENAME
FROM EMP e
INNER JOIN EMP m ON e.MGR = m.EMPNO
INNER JOIN (
  SELECT JOB, MIN(SALAR) as MINSALAR
  FROM EMP
  GROUP BY JOB
) s ON s.JOB = e.JOB
WHERE m.HIREDATE > e.HIREDATE AND e.SALAR = s.MINSALAR

Upvotes: 2

Related Questions