SQL문 연습001

select distinct
a.ename||' '||a.job||' '||d.grade as 사원,
b.ename||' '||b.job as 관리자,
c.loc
from emp a, emp b, dept c, salgrade d
where b.empno=NVL(a.mgr,a.empno) and a.deptno=c.deptno and (d.losal<a.sal and a.sal<d.hisal);

d.grade를 산출하는게 포인트..

잘못된 예)
select
a.ename||' '||a.job||' '||
(select distinct d.grade from emp a, salgrade d where d.losal<a.sal and a.sal<d.hisal)
as 사원,
b.ename||''||b.job as 관리자,
c.loc
from emp a, emp b, dept c, salgrade d
where b.empno=NVL(a.mgr,a.empno) and a.deptno=c.deptno
order by a.SAL ASC;

부속질의에서 답이 여러개 반환됨


select ename from emp where sal=(select max(sal) from emp);

select ename from emp where sal=max(select sal from emp);