# View the Exhibit and examine the structure of the EMP table. You want to display the names and salaries of only those employees who earn the highest salaries in their departments. Which two SQL statements give the required output? (Choose two.)
1. SELECT ename, sal
FROM emp e
WHERE sal = (SELECT MAX(sal)
FROM emp
WHERE deptno = e.deptno);
2. SELECT ename, sal
FROM emp
WHERE sal = ALL (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
3. SELECT ename, sal
FROM emp e
WHERE EXISTS (SELECT MAX(sal)
FROM emp WHERE deptno = e.deptno);
4. SELECT ename, sal
FROM emp
NATURAL JOIN (SELECT deptno, MAX(sal) sal
FROM emp
GROUP BY deptno);
query 1 uses a correlated subquery to find the maximum salary for the given department:
1. SELECT ename, sal
FROM emp e
WHERE sal = (SELECT MAX(sal)
FROM emp
WHERE deptno = e.deptno);
conceptually that query is processed like this:
for E in (select * from emp)
loop
select max(sal) into l_sal from emp where deptno = E.DEPTNO;
if (l_sal = E.SAL)
then
OUTPUT RECORD;
end if;
end loop;so it only outputs records when a record has a salary that matches the high salary for that department.
So, that query is one of the queries that returns the set of employees making the highest salary in their respective department.
Query #2 only would work if the maximum salary in every department was the same:
2. SELECT ename, sal
FROM emp
WHERE sal = ALL (SELECT MAX(sal)
FROM emp
GROUP BY deptno);conceptually, the subquery "select max(sal) from emp group by deptno" would executed. We'd have a set of salaries. Then we would read each row from emp and output that row if and only if the salary in that row equaled EVERY VALUE in that set. Now, the only way it could equal EVERY VALUE in that set would be if the set contained only one distinct value. So, in general, this query would not work to return the highest paid people by deptno.
Query #3
3. SELECT ename, sal
FROM emp e
WHERE EXISTS (SELECT MAX(sal)
FROM emp WHERE deptno = e.deptno);
that is a silly one. it is equivalent to:
3. SELECT ename, sal
FROM emp e
select max( column ) from table - that always returns a row, always. An aggregate without a group by always returns at least and at most one row. So, the where exists is always satisfied...
1* select ename from emp where exists ( select max(1) from dual where 1=0 )
ops$tkyte%ORA10GR2> /
ENAME
----------
SCOTT
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
query number 4:
4. SELECT ename, sal
FROM emp
NATURAL JOIN (SELECT deptno, MAX(sal) sal
FROM emp
GROUP BY deptno);
we (conceptually) develop a set that has deptno and the maximum sal. We then natural join this set to the EMP table. Now, a natural join (which no self respecting programmer would ever consider using) joins two sets together by comparing the column names and adding a join condition between every column with the same name.
So, that query is the same as:
select emp.ename, emp.sal
from emp,
(select deptno, max(sal) max_sal from emp group by deptno) D
where emp.deptno = d.deptno
and emp.sal = d.max_sal
that would again return the set of people in EMP that make the highest salary in their departement.