SQL ÄÄÆÄÀÏ·¯´Â Á¶È¸ Á¶ÀÛÀ» À̵¿½ÃÄÑ ÃÖ¼Ò¼öÀÇ Á¶ÀÛ°ú ¼ú¾î¸¦ °®´Â Á¶È¸¸¦ ±¸¼ºÇÒ ¼ö ÀÖµµ·Ï Á¶È¸¸¦ ÀçÀÛ¼ºÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â SQL ÄÄÆÄÀÏ·¯·Î ¿Å°ÜÁú ¼ö ÀÖ´Â ÀϺΠÁ¶ÀÛÀ» ¼³¸íÇϱâ À§ÇØ Á¦°øµË´Ï´Ù.
Á¶È¸ ÀçÀÛ¼º Áß¿¡ ÃÖÀûÈ ¾Ë°í¸®ÁòÀº DISTICT Á¶ÀÛÀÌ ¼öÇàµÇ´Â À§Ä¡¸¦ À̵¿ÇÏ¿© ÀÌ Á¶ÀÛÀÇ ºñ¿ëÀ» ÁÙÀÏ ¼ö ÀÖ½À´Ï´Ù. Á¦°øµÈ ¿¹¿¡¼, DISTINCT Á¶ÀÛÀÌ ¿ÏÀüÈ÷ Á¦°ÅµË´Ï´Ù.
Á¶È¸ ÀçÀÛ¼ºÁß¿¡, º¸´Ù ¸¹Àº ¼±ÅÃÀû ¼ú¾î°¡ º¸´Ù ÀÏÂï Á¶È¸¿¡ Àû¿ëµÉ ¼ö ÀÖµµ·Ï Àû¿ëµÇ´Â ¼ú¾îÀÇ ¼ø¼¸¦ º¯°æÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÆÄƼ¼ÇµÈ µ¥ÀÌÅͺ£À̽º ȯ°æ¿¡¼´Â µ¥ÀÌÅͺ£À̽º ÆÄƼ¼Ç°£ÀÇ °á°ú ÁýÇÕ À̵¿ÀÌ °í°¡ÀÇ ÀÛ¾÷ÀÔ´Ï´Ù. ´Ù¸¥ µ¥ÀÌÅͺ£À̽º ÆÄƼ¼ÇÀ¸·Î ºê·Îµåij½ºÆ®ÇØ¾ß ÇÏ´Â µ¥ÀÌÅÍÀÇ ¾ç°ú ºê·Îµåij½ºÆ®ÀÇ ¼ö¸¦ ÁÙÀÌ´Â °ÍÀº Á¶È¸ ÀçÀÛ¼ºÀÇ ¸ñÀû Áß ÇϳªÀÔ´Ï´Ù.
EMPNO Ä÷³ÀÌ EMPLOYEE Å×À̺íÀÇ ±âº» Ű·Î Á¤ÀÇµÈ °æ¿ì,
SELECT DISTINCT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE
À§ÀÇ Á¶È¸´Â DISTINCTÀýÀ» Á¦°ÅÇÏ¿© ´ÙÀ½°ú °°ÀÌ ÀçÀÛ¼ºµË´Ï´Ù.
SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE
À§ÀÇ ¿¹¿¡¼´Â, ±âº» ۰¡ ¼±ÅõDZ⠶§¹®¿¡, SQL ÄÄÆÄÀÏ·¯´Â ¸®ÅϵǴ °¢ ÇàÀÌ ÀÌ¹Ì °íÀ¯ÇÒ °ÍÀ̶ó´Â °ÍÀ» ¾Ë°í ÀÖ½À´Ï´Ù. ÀÌ °æ¿ì, DISTINCT Ű¿öµå´Â ºÒÇÊ¿äÇÕ´Ï´Ù. Á¶È¸°¡ ÀçÀÛ¼ºµÇÁö ¾ÊÀº °æ¿ì, ÃÖÀûÈ ¾Ë°í¸®ÁòÀº Ä÷³ÀÌ °íÀ¯ÇÏ´Ù´Â °ÍÀ» º¸ÀåÇÒ ¼ö ÀÖµµ·Ï ÇÊ¿äÇÑ Ã³¸®(¿¹: Á¤·Ä)¸¦ Æ÷ÇÔÇÏ´Â Ç÷£À» ±¸ÃàÇÏ°Ô µË´Ï´Ù.
ÀϹÝÀûÀ¸·Î, ¼ú¾î°¡ Àû¿ëµÇ´Â ·¹º§À» º¯°æÇÏ¸é ¼º´ÉÀÌ º¸´Ù °³¼±µÉ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¸é, "D11" ºÎ¼ÀÇ ¸ðµç Á÷¿øÀÇ ¸ñ·ÏÀ» Á¦°øÇÏ´Â ´ÙÀ½ÀÇ ºä¸¦ °¡Á¤ÇϽʽÿÀ.
CREATE VIEW D11_EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, PHONENO, SALARY, BONUS, COMM) AS SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO, SALARY, BONUS, COMM FROM EMPLOYEE WHERE WORKDEPT = 'D11'
±×¸®°í ´ÙÀ½ÀÇ Á¶È¸¸¦ °¡Á¤ÇϽʽÿÀ.
SELECT FIRSTNME, PHONENO FROM D11_EMPLOYEE WHERE LASTNAME = 'BROWN'
ÄÄÆÄÀÏ·¯ÀÇ Á¶È¸ ÀçÀÛ¼º ´Ü°è´Â ¼ú¾î LASTNAME = 'BROWN'À» ºä D11_EMPLOYEE¿¡ ³Ö½À´Ï´Ù. ÀÌ·¸°Ô Çϸé, ÇØ´ç ¼ú¾î°¡ ´õ ºü¸£°í °á±¹¿¡´Â º¸´Ù ´õ È¿À²ÀûÀ¸·Î Àû¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. ÀÌ ¿¹¿¡¼ ½ÇÇàµÇ´Â ½ÇÁ¦ Á¶È¸´Â ´ÙÀ½°ú °°½À´Ï´Ù.
SELECT FIRSTNME, PHONENO FROM EMPLOYEE WHERE LASTNAME = 'BROWN' AND WORKDEPT = 'D11'
¼ú¾îÀÇ Çª½Ã´Ù¿îÀº ºä·Î Á¦ÇѵÇÁö ¾Ê½À´Ï´Ù. ¼ú¾î°¡ Ǫ½Ã´Ù¿îµÉ ¼ö ÀÖ´Â ´Ù¸¥ »óȲ¿¡´Â UNION, BROUP BY ¹× À¯µµ Å×À̺í(derived table)(Áßø Å×À̺í Ç¥Çö½Ä ¶Ç´Â °øÅë Å×À̺í Ç¥Çö½Ä)ÀÌ ÀÖ½À´Ï´Ù.
ÆÄƼ¼ÇµÈ µ¥ÀÌÅͺ£À̽º ȯ°æ¿¡¼ SQL ÄÄÆÄÀÏ·¯´Â ´ÙÀ½ Á¶È¸¸¦ ÀçÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÇÁ·Î±×·¡¹Ö ÇÁ·ÎÁ§Æ®¿¡¼ ÀÏÇÏ¸ç ±Þ¿©°¡ Æò±Õº¸´Ù ÀûÀº ¸ðµç Á÷¿øÀ» ãÀ¸½Ã¿À.
SELECT P.PROJNO, E.EMPNO, E.LASTNAME, E.FIRSTNAME, E.SALARY+E.BONUS+E.COMM AS COMPENSATION FROM EMPLOYEE E, PROJECT P WHERE P.EMPNO = E.EMPNO AND P.PROJNAME LIKE '%PROGRAMMING%' AND E.SALARY+E.BONUS+E.COMM < (SELECT AVG(E1.SALARY+E1.BONUS+E1.COMM) FROM EMPLOYEE E1, PROJECT P1 WHERE P1.PROJNAME LIKE '%PROGRAMMING%' AND P1.PROJNO = A.PROJNO AND E1.EMPNO = P1.EMPNO)
ÀÌ Á¶È¸´Â »ó°ü °ü°è°¡ ÀÖ°í PROJECT¿Í EMPLOYEE µÑ´Ù PROJNO¿¡ ´ëÇØ ÆÄƼ¼ÇµÉ ¼ö ¾ø±â ¶§¹®¿¡ °¢ ÇÁ·ÎÁ§Æ®¿¡¼ °¢ µ¥ÀÌÅͺ£À̽º·ÎÀÇ ºê·Îµåij½ºÆ®°¡ °¡´ÉÇÕ´Ï´Ù. ¶ÇÇÑ, ºÎ¼Ó Á¶È¸´Â ¿©·¯ ¹ø Æò°¡µÇ¾î¾ß ÇÕ´Ï´Ù.
SQL ÄÄÆÄÀÏ·¯´Â ´ÙÀ½°ú °°ÀÌ Á¶È¸¸¦ ÀçÀÛ¼ºÇÕ´Ï´Ù.
WITH DIST_PROJS(PROJNO, EMPNO) AS (SELECT DISTINCT PROJNO, EMPNO FROM PROJECT P1 WHERE P1.PROJNAME LIKE '%PROGRAMMING%')
AVG_PER_PROJ(PROJNO, AVG_COMP) AS (SELECT P2.PROJNO, AVG(E1.SALARY+E1.BONUS+E1.COMM) FROM EMPLOYEE E1, DIST_PROJS P2 WHERE E1.EMPNO = P2.EMPNO GROUP BY P2.PROJNO)
SELECT P.PROJNO, E.EMPNO, E.LASTNAME, E.FIRSTNAME, E.SALARY+E.BONUS+E.COMM AS COMPENSATION FROM PROJECT P, EMPLOYEE E, AVG_PER_PROG A WHERE P.EMPNO = E.EMPNO AND P.PROJNAME LIKE '%PROGRAMMING%' AND P.PROJNO = A.PROJNO AND E.SALARY+E.BONUS+E.COMM < A.AVG_COMP
ÀçÀÛ¼ºµÈ SQL Á¶È¸´Â ÇÁ·ÎÁ§Æ®´ç AVG_COMP(AVG_PRE_PROJ)¸¦ °è»êÇÑ ´ÙÀ½, °á°ú¸¦ EMPLOYEE Å×À̺íÀ» Æ÷ÇÔÇÏ´Â ¸ðµç µ¥ÀÌÅͺ£À̽º ÆÄƼ¼ÇÀ¸·Î ºê·Îµåij½ºÆ®ÇÒ ¼ö ÀÖ½À´Ï´Ù.