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)
)