Chapter 2. Sorting Query Results select ename,sal,comm, case when comm is null then 0 else 1 end as comm_is_not_null from emp select ename, sal, comm from (select ename, sal, comm, case when comm is null then 0 else 1 end as comm_is_not_null from emp) as x ORDER BY comm_is_not_null desc, comm desc; select ENAME,SAL,COMM,JOB from emp order by case when job = 'SALESMAN' then comm else sal end select ENAME,SAL,COMM,JOB, case when job = 'SALESMAN' then comm else sal end ORDER_BY_VAL FROM emp ORDER BY 5 Chapter 3. Working with Multiple Tables select ename as ename_and_dname, deptno from emp where deptno = 10 select '--------------',null select dname, deptno from dept select ename as ename_and_dname, deptno from emp where deptno = 10 UNION ALL select '--------------',null UNION ALL select dname, deptno from dept select e.ename, d.loc from emp e INNER JOIN dept d ON e.deptno = d.deptno where e.deptno = 10 select e.empno,e.ename,e.job,e.sal,e.deptno from emp e INNER JOIN V33 v ON (e.ename = v.ename AND e.job = v.job AND e.sal = v.sal) create table new_dept(deptno integer); insert into new_dept values (10); insert into new_dept values (50); insert into new_dept values (null); select e.ename, d.loc, eb.received from emp e INNER JOIN dept d ON e.deptno = d.deptno LEFT JOIN emp_bonus eb ON e.empno=eb.empno order by d.loc select e.ename, d.loc, (select 'update me scalar subquery') received from emp e INNER JOIN dept d ON e.deptno = d.deptno order by d.loc select e.ename, d.loc, (select received from emp_bonus eb where e.empno = eb.empno) received from emp e INNER JOIN dept d ON e.deptno = d.deptno order by d.loc create view V1 as select * from emp where deptno != 10 union all select * from emp where ename = 'WARD' select e.empno,e.ename,e.job,e.mgr,e.hiredate, e.sal,e.comm,e.deptno, count(*) as cnt from emp e group by empno,ename,job,mgr,hiredate,sal,comm,deptno select v1.empno,v1.ename,v1.job,v1.mgr,v1.hiredate, v1.sal,v1.comm,v1.deptno, count(*) as cnt from v1 group by empno,ename,job,mgr,hiredate,sal,comm,deptno select * from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate, e.sal,e.comm,e.deptno, count(*) as cnt from emp e group by empno,ename,job,mgr,hiredate,sal,comm,deptno ) as e where not exists ( select null from (select v1.empno,v1.ename,v1.job,v1.mgr,v1.hiredate, v1.sal,v1.comm,v1.deptno, count(*) as cnt from v1 group by empno,ename,job,mgr,hiredate,sal,comm,deptno) as v where v.empno = e.empno and v.ename = e.ename and v.job = e.job and coalesce(v.mgr,0) = coalesce(e.mgr,0) and v.hiredate = e.hiredate and v.sal = e.sal and v.deptno = e.deptno and v.cnt = e.cnt and coalesce(v.comm,0) = coalesce(e.comm,0) ) select * from (select v1.empno,v1.ename,v1.job,v1.mgr,v1.hiredate, v1.sal,v1.comm,v1.deptno, count(*) as cnt from v1 group by empno,ename,job,mgr,hiredate,sal,comm,deptno) as v where not exists ( select null from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate, e.sal,e.comm,e.deptno, count(*) as cnt from emp e group by empno,ename,job,mgr,hiredate,sal,comm,deptno ) as e where v.empno = e.empno and v.ename = e.ename and v.job = e.job and coalesce(v.mgr,0) = coalesce(e.mgr,0) and v.hiredate = e.hiredate and v.sal = e.sal and v.deptno = e.deptno and v.cnt = e.cnt and coalesce(v.comm,0) = coalesce(e.comm,0) )