CODEWITHSUNDEEP
sqloracle-databaseselectnull
Pené David II
Pené David II

Reputation: 11

how to display null value attributes?

This is the question:

Create a query to list all the employees who joined this organization before any clerks were hired and who earn more than any manager.

This is what I have so far:

select ename
from emp
where hiredate<any(select hiredate from emp
where job='CLERK')
and job!='CLERK'
and sal>any(select sal from emp
where empno=super);

-But one of the employees don't have a supervisor (which is null) so it doesn't show any of the employees.

Upvotes: 0

Views: 1450

Answers (1)

Szilard Barany
Szilard Barany

Reputation: 1135

Something like this?

SELECT ename
FROM   emp
WHERE  hiredate < ANY (
           SELECT hiredate
           FROM   emp
           WHERE  job = 'CLERK'
       )
   AND job <> 'CLERK'
   AND sal > ANY (
           SELECT sal
           FROM   emp
           WHERE  empno IN (
                      SELECT super
                      FROM   emp
                      WHERE  super IS NOT NULL
                  )
           );

Is it a homework? If yes, should be labelled as such.

Upvotes: 1

Related Questions