Chapter 3. Working with Multiple Tables

select * 
from ( select * from emp ) e
where not exists (select 1 
                  from (select  * 
                        from v1 ) v  
				  where v.empno = e.empno)

select * 
from ( select * from emp ) e
where not exists (select null 
                  from (select  * 
                        from v1 ) v  
				  where v.empno     = e.empno)
UNION ALL
select * 
from (select  *  from v1 ) v
where not exists (select null 
                  from   ( select * from emp ) e
				  where v.empno     = e.empno)

select e.empno,
	   e.ename,
	   e.sal,
	   e.deptno,
	   e.sal*(case when eb.type = 1 then .1
	                  when eb.type = 2 then .2
	                  else .3
			  end) as bonus
from emp e INNER JOIN emp_bonus eb
	 ON e.empno  = eb.empno
where e.deptno = 10

select deptno, sum(sal) as total_sal
	  from emp
	 where deptno = 10
	 group by deptno

select d.deptno,
	       d.total_sal,
	       sum(e.sal*case when eb.type = 1 then .1
	                      when eb.type = 2 then .2
	                      else .3 end) as total_bonus
	  from emp e INNER JOIN
	       emp_bonus eb
           ON e.empno = eb.empno
           INNER JOIN 
	       (select deptno, sum(sal) as total_sal
	        from emp
	        where deptno = 10
	        group by deptno
	        ) d
	 on e.deptno = d.deptno
	 group by d.deptno,d.total_sal

 select e.deptno, ( select sum(sal) as total_sal
	  from emp
	 where deptno = 10
	 group by deptno ) total_sal,
        sum(e.sal*(case when eb.type = 1 then .1
                      when eb.type = 2 then .2
                      else .3
              end)) total_bonus
 from emp e INNER JOIN emp_bonus eb
 ON e.empno = eb.empno
 WHERE e.deptno = 10

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
	select 1111,'YODA','JEDI',null,hiredate,sal,comm,null
	  from emp
	 where ename = 'KING';


select d.deptno,d.dname,e.ename
from dept d full outer join emp e
on (d.deptno=e.deptno);

select d.deptno,d.dname,e.ename
from dept d right outer join emp e
on (d.deptno=e.deptno)
union
select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno);

Chapter 4. Inserting, Updating, Deleting

select deptno,
	       ename,
	       sal      as orig_sal,
	       sal*.10  as amt_to_add,
	       sal*1.10 as new_sal
	  from emp
	 where deptno=20
	 order by 1,5

SELECT e.DEPTNO,e.ENAME,e.SAL,ns.sal new_sal, e.COMM, ns.sal/2 new_comm
 FROM sqladvdb.emp e INNER JOIN new_sal ns 
   ON e.deptno = ns.deptno

UPDATE emp e INNER JOIN new_sal ns
ON e.deptno=ns.deptno
SET e.sal = ns.sal, e.comm = ns.sal*0.5

–>