** ±×·ìÇÔ¼ö(Áý°èÇÔ¼ö)
SELECT    ±×·ìÇÔ¼ö		5)
FROM         1)
WHERE  ±×·ìÇÔ¼ö»ç¿ëºÒ°¡   ==>ÇàÀ» Á¦ÇÑÇÏ´Â Á¶°ÇÀý   2)
GROUP BY    3)
HAVING  ±×·ìÇÔ¼ö»ç¿ë    ==>±×·ìÀ» Á¦ÇÑÇÏ´Â Á¶°ÇÀý    4)
ORDER BY  ±×·ìÇÔ¼ö»ç¿ë;		6)

¹®Á¦1)EMPÅ×ÀÌºí¿¡ Á¸ÀçÇÏ´Â ¸ðµç »ç¿øµéÀÇ Àοø¼ö,±Þ¿©ÀÇ Æò±Õ,ÃÖ°í¾×,ÃÖÀú¾×À» ±¸ÇϽÿÀ.
SELECT count(*),avg(sal),max(sal),min(sal)
FROM emp;

¹®Á¦2) EMP Å×ÀÌºí¿¡¼­ ¸ðµç SALESMAN¿¡ ´ëÇÏ¿© ±Þ¿©ÀÇ Æò±Õ, ÃÖ°í¾×, ÃÖÀú¾×, Çհ踦 ±¸ÇÏ¿© Ãâ·ÂÇÏ¿©¶ó.
SELECT avg(sal),max(sal),min(sal)
FROM emp
WHERE job='SALESMAN'

¹®Á¦3) EMP Å×ÀÌºí¿¡ µî·ÏµÇ¾î ÀÖ´Â Àοø¼ö, º¸³Ê½º¿¡ NULLÀÌ ¾Æ´Ñ Àοø¼ö, º¸³Ê½ºÀÇ Æò±Õ, µî·ÏµÇ¾î ÀÖ´Â ºÎ¼­ÀÇ ¼ö¸¦ ±¸ÇÏ¿© Ãâ·ÂÇÏ¿©¶ó.
SELECT count(*),count(comm),avg(comm),avg(nvl(comm,0)),count(distinct deptno)
FROM emp;

** empÅ×ÀÌºí¿¡¼­ ÃÖ°í±Þ¿©¹× ±× ±Þ¿©¸¦ ¹Þ´Â »ç¿øÀÇÀ̸§À» Á¶È¸ÇϽÿÀ.
SELECT max(sal),ename
FROM emp;

SELECT ename,sal
FROM emp
WHERE sal=(SELECT max(sal) FROM emp);







¹®Á¦3) EMP Å×ÀÌºí¿¡¼­ ºÎ¼­º°·Î Àοø¼ö,Æò±Õ ±Þ¿©,ÃÖÀú±Þ¿©,ÃÖ°í ±Þ¿©,±Þ¿©ÀÇ ÇÕÀ» ±¸ÇÏ¿© Ãâ·ÂÇÏ¿©¶ó.
SELECT deptno,count(*),avg(sal),min(sal),max(sal),sum(sal)
FROM emp
GROUP BY deptno


¹®Á¦4) °¢ ºÎ¼­º°·Î Àοø¼ö,±Þ¿©ÀÇ Æò±Õ, ÃÖÀú ±Þ¿©, ÃÖ°í ±Þ¿©, ±Þ¿©ÀÇ ÇÕÀ» ±¸ÇÏ¿© ±Þ¿©ÀÇ ÇÕÀÌ ¸¹Àº ¼øÀ¸·Î Ãâ·ÂÇÏ¿©¶ó.
SELECT deptno,count(*),avg(sal),min(sal),max(sal),sum(sal)
FROM emp
GROUP BY deptno
ORDER BY sum(sal) desc;

¹®Á¦5) ºÎ¼­º°, ¾÷¹«º°·Î ±×·ìÁö¾î ºÎ¼­¹øÈ£, ¾÷¹«, Àοø¼ö, ±Þ¿©ÀÇ Æò±Õ, ±Þ¿©ÀÇ ÇÕÀ» Ãâ·ÂÇÏ¿©¶ó.
SELECT deptno,job,count(*),avg(sal),sum(sal)
FROM emp
GROUP BY deptno,job;

¹®Á¦6) EMPÅ×ÀÌºí¿¡¼­ ºÎ¼­º°·Î ±Þ¿©ÀÇ Çհ踦 ±¸ÇÏ°í empÅ×ÀÌºí¿¡ Á¸ÀçÇÏ´Â ¸ðµç»ç¿øµéÀÇ ±Þ¿©ÀÇ ÃÑÇհ踦 ±¸ÇϽÿÀ.

SELECT null "ºÎ¼­¹× Àüü",sum(sal)
FROM emp
UNION ALL
SELECT deptno,sum(sal)
FROM emp
GROUP BY deptno;


SELECT deptno,job,sum(sal)
FROM emp
GROUP BY rollup(deptno,job);

SELECT deptno,job,sum(sal)
FROM emp
GROUP BY grouping sets((deptno,job),(deptno),());

SELECT deptno,job,sum(sal)
FROM emp
GROUP BY cube(deptno,job);

SELECT deptno,job,sum(sal)
FROM emp
GROUP BY grouping sets((),(job),(deptno),(deptno,job));


SELECT deptno,sum(sal)
FROM emp
GROUP BY grouping sets((),deptno);


¹®Á¦7) EMP Å×ÀÌºí¿¡¼­ ºÎ¼­ ÀοøÀÌ 4¸íº¸´Ù ¸¹Àº ºÎ¼­ÀÇ ºÎ¼­¹øÈ£, Àοø¼ö, ±Þ¿©ÀÇ ÇÕÀ» Ãâ·ÂÇÏ¿©¶ó.
SELECT deptno,count(*),sum(sal)
FROM emp
GROUP BY deptno
HAVING count(*)>4;

¹®Á¦8)EMP Å×ÀÌºí¿¡¼­ ¿ù±ÞÀÇ ÇÕÀÌ 5000À» ÃÊ°úÇÏ´Â ¾÷¹«¿¡ ´ëÇÏ¿© ¾÷¹«¸í ¹× ¿ù±Þ¿© Çհ踦 Ãâ·ÂÇÏ¿©¶ó. ´Ü SALESMANÀº Á¦¿ÜÇÏ°í ¿ù ±Þ¿© ÇÕ°è·Î Á¤·Ä(³»¸²Â÷¼ø)ÇÏ¿©¶ó.
SELECT job,sum(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING sum(sal) >5000
ORDER BY sum(sal) desc;



** Á¶Àι®¹ý
1)ORACLE JOIN¹®¹ý
 - cartesian product
 - equi join
 - non equi join
 - self join
 - outer join

2)ANSI JOIN¹®¹ý (9iºÎÅÍ »ç¿ë°¡´É)
 - cross join
 - natural join
 -usingÀýÀ» ÀÌ¿ëÇÑ join
 - join~ onÀýÀ» ÀÌ¿ëÇÑ join
 - left/right/full outer join

¹®Á¦1) EMP Å×À̺í°ú DEPT Å×À̺íÀ» Cartesian ProductÇÏ¿© »ç¿ø¹øÈ£,À̸§,¾÷¹«,ºÎ¼­¹øÈ£,ºÎ¼­¸í,±Ù¹«Áö¸¦ Ãâ·ÂÇÏ¿©¶ó.
SELECT e.empno,e.ename,e.job,e.deptno "»ç¿øºÎ¼­¹øÈ£",d.deptno "ºÎ¼­ºÎ¼­¹øÈ£",d.dname,d.loc
FROM emp e, dept d;

SELECT e.empno,e.ename,e.job,e.deptno "»ç¿øºÎ¼­¹øÈ£",d.deptno "ºÎ¼­ºÎ¼­¹øÈ£",d.dname,d.loc
FROM emp e CROSS JOIN dept d;

¹®Á¦2) EMP Å×ÀÌºí¿¡¼­ ¸ðµç»ç¿øµéÀÇ »ç¿ø¹øÈ£,À̸§,¾÷¹«,ºÎ¼­¹øÈ£,±×µéÀÌ ±Ù¹«ÇÏ´Â ºÎ¼­¸í,±Ù¹«Áö¸¦ Ãâ·ÂÇÏ¿©¶ó.
SELECT e.empno,e.ename,e.job,e.deptno "»ç¿øºÎ¼­¹øÈ£",d.deptno "ºÎ¼­ºÎ¼­¹øÈ£",d.dname,d.loc
FROM emp e, dept d
WHERE e.deptno= d.deptno;

SELECT empno,ename,job,deptno,dname,loc
FROM emp NATURAL JOIN dept;

SELECT empno,ename,job,deptno,dname,loc
FROM emp JOIN dept USING (deptno);

SELECT e.empno,e.ename,e.job,e.deptno "»ç¿øºÎ¼­¹øÈ£",d.deptno "ºÎ¼­ºÎ¼­¹øÈ£",d.dname,d.loc
FROM emp e  JOIN dept d
ON (e.deptno=d.deptno);

¹®Á¦3) SALESMANÀÇ »ç¿ø¹øÈ£,À̸§,±Þ¿©,ºÎ¼­¸í,±Ù¹«Áö¸¦ Ãâ·ÂÇÏ¿©¶ó
SELECT e.empno,e.ename,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno and e.job='SALESMAN';

SELECT e.empno,e.ename,e.job,e.deptno "»ç¿øºÎ¼­¹øÈ£",d.deptno "ºÎ¼­ºÎ¼­¹øÈ£",d.dname,d.loc
FROM emp e  JOIN dept d
ON (e.deptno=d.deptno and e.job='SALESMAN');


¹®Á¦4) »ç¿ø¹øÈ£,À̸§,¾÷¹«,±Þ¿©,±Þ¿©ÀÇ µî±ÞÀ» Ãâ·ÂÇÏ¿©¶ó.(emp¿Í salgradeÅ×À̺íÀÌ¿ë)
SELECT e.empno,e.ename,e.job,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal between s.losal and s.hisal;

SELECT e.empno,e.ename,e.job,e.sal,s.grade
FROM emp e JOIN salgrade s
ON (e.sal between s.losal and s.hisal);

¹®Á¦5) EMP Å×À̺í°ú DEPT Å×ÀÌºí¿¡¼­ »ç¿øµéÀÇ À̸§,¾÷¹«,±×µéÀÌ ±Ù¹«ÇÏ´Â ºÎ¼­¸í,À§Ä¡¸¦ Ãâ·ÂÇϽÿÀ.(´Ü, »ç¿øÀÌ Á¸ÀçÇÏÁö ¾ÊÀº ºÎ¼­ÀÇ Á¤º¸µµ ÇÔ²² Ãâ·ÂÇϽÿÀ.)

SELECT e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno 
UNION ALL
SELECT e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+);

SELECT e.ename,e.job,d.dname,d.loc
FROM emp e RIGHT OUTER JOIN dept d
ON (e.deptno=d.deptno);

¹®Á¦6) EMP Å×ÀÌºí¿¡¼­ ¸ðµç »ç¿øµéÀÇ À̸§,¾÷¹« ¹× ±× »ç¿øµéÀ» °ü¸®ÇÏ´Â °ü¸®ÀÚÀ̸§À» Ãâ·ÂÇÏ¿©¶ó.
SELECT e.ename "»ç¿øÀ̸§" ,e.job "¾÷¹«",m.ename "°ü¸®ÀÚÀ̸§"
FROM emp e,emp m
WHERE e.mgr=m.empno;

SELECT e.ename "»ç¿øÀ̸§" ,e.job "¾÷¹«",m.ename "°ü¸®ÀÚÀ̸§"
FROM emp e JOIN emp m
ON (e.mgr=m.empno);

** Set operatorÀÇ¿¹
-emp¿Í deptÅ×À̺íÀÇ °øÅëµÇ´Â deptno°ªÀÌ ¾î¶²°ªÀÎÁö Á¶È¸
select deptno
from emp
intersect
select deptno
from dept;

¹®Á¦0) EMP Å×ÀÌºí¿¡¼­ SCOTTÀÇ ±Þ¿©º¸´Ù ¸¹Àº ±Þ¿©¸¦ ¹Þ´Â »ç¿øÀÇ »ç¿ø¹øÈ£,À̸§,´ã´ç¾÷¹«,±Þ¿©¸¦ Ãâ·ÂÇÏ¿©¶ó.
SELECT empno,ename,job,sal
FROM emp
WHERE sal >(SELECT sal FROM emp
	      WHERE ename='SCOTT');

¹®Á¦1) EMP Å×ÀÌºí¿¡¼­ »ç¿ø¹øÈ£°¡ 7521ÀÎ »ç¿ø°ú ¾÷¹«°¡ °°°í ±Þ¿©°¡ 7934ÀÎ »ç¿øº¸´Ù ¸¹Àº »ç¿øÀÇ »ç¿ø¹øÈ£,À̸§,´ã´ç¾÷¹«,ÀÔ»çÀÏÀÚ,±Þ¿©¸¦ Ãâ·ÂÇÏ¿©¶ó.
select empno,ename,job,hiredate,sal
from emp
where job=(select job from emp 
	   where empno=7521)         
        and sal >(select sal from emp
	        where empno=7934)

¹®Á¦2) EMP Å×ÀÌºí¿¡¼­ Æò±Õ±Þ¿©º¸´Ù ÀûÀº ±Þ¿©¸¦ ¹Þ´Â »ç¿øÀÇ »ç¿ø¹øÈ£,À̸§,´ã´ç¾÷¹«,±Þ¿©,ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó.
select empno,ename,job,sal,deptno
from emp
where sal < (select avg(sal) from emp);

¹®Á¦3) EMP Å×ÀÌºí¿¡¼­ ºÎ¼­º° Æò±Õ±Þ¿©°¡, 20¹øºÎ¼­ÀÇ Æò±Õ±Þ¿©º¸´Ù ³ôÀº, ºÎ¼­ÀÇ ºÎ¼­ÄÚµå ¹× Æò±Õ±Þ¿©¸¦ Ãâ·ÂÇÏ¿©¶ó.
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)> (select avg(sal)
		from emp
		where deptno=20);

¹®Á¦4) EMP Å×ÀÌºí¿¡¼­ ¾÷¹«º° Æò±Õ±Þ¿©Áß °¡Àå ³·Àº Æò±Õ±Þ¿©¸¦ °®´Â ¾÷¹«¸í°ú ±× Æò±Õ±Þ¿©¸¦ Ãâ·ÂÇÏ¿©¶ó.
select job,avg(sal)
from emp
group by job
having avg(sal)=(select min(avg(sal))
		from emp
		group by job);

** ¼­ºêÄõ¸®ÀÇ Á¾·ù

1)´ÜÀÏÇà ¼­ºêÄõ¸® :¸®ÅÏ°ªÀÌ 1°³ÀÎ °æ¿ì, ÀϹݺñ±³¿¬»êÀÚ »ç¿ë
2)´ÙÁßÇà ¼­ºêÄõ¸® : ¼­ºêÄõ¸®ÀÇ ¸®ÅÏ°ªÀÌ 2°³ ÀÌ»óÀÎ °æ¿ì
	whereÀý,havingÀý¿¡¼­ »ç¿ëµÇ´Â °æ¿ì ¿¬»êÀÚ¸¦ 
	IN,ANY,ALLÀ» »ç¿ëÇÏ¿©¾ß ÇÑ´Ù. 
3)´ÜÀÏÄ÷³ ¼­ºêÄõ¸®
4)´ÙÁßÄ÷³ ¼­ºêÄõ¸® :Ä÷³À» ½ÖÀ» ÀÌ·ç¾î¼­ ºñ±³ÇØ¾ß ÇÏ´Â °æ¿ì
5)correlated ¼­ºêÄõ¸®(»óÈ£°ü·Ã ¼­ºêÄõ¸®):¼­ºêÄõ¸®³»¿¡ ¸ÞÀÎÄõ¸®ÀÇ Å×À̺íÀÌ »ç¿ëµÇ´Â °æ¿ì·Î¼­ ¸ÞÀÎÄõ¸® Å×À̺íÀÇ ÇàÀÇ °¹¼ö¸¸Å­ ¼­ºêÄõ¸®°¡ ½ÇÇàµÊ,Èí»ç Á¶ÀÎó·³ ¼º´ÉÀÌ ÁÁÁö ¾ÊÀ½.¼º´ÉÇâ»óÀ» À§Çؼ­ Ä÷³¿¡ À妽º »ý¼ºÇØ¾ß ÇÔ.


**  deptÅ×ÀÌºí¿¡ Á¸ÀçÇÏÁö ¾Ê´Â ºÎ¼­Äڵ带 °®´Â »ç¿øÀÇ Á¤º¸¸¦ Á¶È¸ÇϽÿÀ.
SELECT *
FROM emp
WHERE deptno NOT IN (SELECT deptno FROM dept);

SELECT *
FROM emp e
WHERE not exists (SELECT 'X' FROM dept d WHERE e.deptno=d.deptno);


6)INLINE VIEW :FROMÀý¿¡ ¼±¾ðÇÑ ¼­ºêÄõ¸®,¼º´ÉÇâ»ó,Äõ¸®¹®À» simpleÇÏ°Ô Ã³¸®ÇÒ¼ö ÀÖÀ½.



¹®Á¦5) EMP Å×ÀÌºí¿¡¼­ ¾÷¹«º°·Î ÃÖ¼Ò ±Þ¿©¸¦ ¹Þ´Â »ç¿øÀÇ »ç¿ø¹øÈ£,À̸§,¾÷¹«,ÀÔ»çÀÏÀÚ,±Þ¿©,ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó.
SELECT empno,ename,job,hiredate,sal,deptno
FROM emp
WHERE (job,sal) IN (SELECT job,min(sal) FROM emp GROUP BY job);


¹®Á¦6) EMP¿Í DEPT Å×ÀÌºí¿¡¼­ ¾÷¹«°¡ MANAGERÀÎ »ç¿øÀÇ À̸§,¾÷¹«,ºÎ¼­¸í,±Ù¹«Áö¸¦ Ãâ·ÂÇÏ¿©¶ó
SELECT e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno and e.job='MANAGER';

SELECT e.ename,e.job,d.dname,d.loc
FROM (SELECT * FROM emp WHERE job='MANAGER') e,dept d
WHERE e.deptno=d.deptno;