°ü¸® ¾È³»¼­


Á¶ÀÛ À̵¿

SQL ÄÄÆÄÀÏ·¯´Â Á¶È¸ Á¶ÀÛÀ» À̵¿½ÃÄÑ ÃÖ¼Ò¼öÀÇ Á¶ÀÛ°ú ¼ú¾î¸¦ °®´Â Á¶È¸¸¦ ±¸¼ºÇÒ ¼ö ÀÖµµ·Ï Á¶È¸¸¦ ÀçÀÛ¼ºÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â SQL ÄÄÆÄÀÏ·¯·Î ¿Å°ÜÁú ¼ö ÀÖ´Â ÀϺΠÁ¶ÀÛÀ» ¼³¸íÇϱâ À§ÇØ Á¦°øµË´Ï´Ù.

¿¹ - DISTINCT Á¦°Å

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 ÄÄÆÄÀÏ·¯´Â ´ÙÀ½°ú °°ÀÌ Á¶È¸¸¦ ÀçÀÛ¼ºÇÕ´Ï´Ù.

ÀçÀÛ¼ºµÈ SQL Á¶È¸´Â ÇÁ·ÎÁ§Æ®´ç AVG_COMP(AVG_PRE_PROJ)¸¦ °è»êÇÑ ´ÙÀ½, °á°ú¸¦ EMPLOYEE Å×À̺íÀ» Æ÷ÇÔÇÏ´Â ¸ðµç µ¥ÀÌÅͺ£À̽º ÆÄƼ¼ÇÀ¸·Î ºê·Îµåij½ºÆ®ÇÒ ¼ö ÀÖ½À´Ï´Ù.


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]