°ü¸® ¾È³»¼­


Á¶ÀÛ º´ÇÕ

SQL ÄÄÆÄÀÏ·¯´Â °¡Àå ÀûÀº ¼öÀÇ Á¶ÀÛ, ƯÈ÷ SELECT Á¶ÀÛÀ» °®µµ·Ï Á¶È¸¸¦ ±¸¼ºÇÏ·Á°í ÇÒ ¶§, Á¶È¸ Á¶ÀÛÀ» º´ÇÕÇϱâ À§ÇÑ Á¶È¸¸¦ ´Ù½Ã ÀÛ¼ºÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â SQL ÄÄÆÄÀÏ·¯·Î º´ÇÕµÉ ¼ö ÀÖ´Â ÀϺΠÁ¶ÀÛÀ» ¼³¸íÇϱâ À§ÇØ Á¦°øµË´Ï´Ù.

¿¹ - ºä º´ÇÕ

EMPLOYEE Å×À̺íÀÇ ´ÙÀ½°ú °°Àº µÎ °³ÀÇ ºä¿¡ ¾×¼¼½ºÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. Çϳª´Â ±³À° ¼öÁØÀÌ ³ôÀº Á÷¿øÀ» º¸¿©ÁÖ°í, ´Ù¸¥ Çϳª´Â $35,000À» ³Ñ°Ô ¹ö´Â Á÷¿øÀ» º¸¿©ÁÝ´Ï´Ù.

   CREATE VIEW EMP_EDUCATION (EMPNO, FIRSTNME, LASTNAME, EDLEVEL) AS
   SELECT EMPNO, FIRSTNME, LASTNAME, EDLEVEL
     FROM EMPLOYEE
    WHERE EDLEVEL > 17
   CREATE VIEW EMP_SALARIES (EMPNO, FIRSTNAME, LASTNAME, SALARY) AS
   SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
     FROM EMPLOYEE
    WHERE SALARY > 35000

ÀÌÁ¦ ±³À° ¼öÁØÀÌ ³ô°í $35,000À» ³Ñ°Ô ¹ö´Â Á÷¿øÀ» ³ª¿­ÇÏ´Â ´ÙÀ½ÀÇ Á¶È¸¸¦ ¼öÇàÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.

   SELECT E1.EMPNO, E1.FIRSTNME, E1.LASTNAME, E1.EDLEVEL, E2.SALARY
     FROM EMP_EDUCATION E1,
          EMP_SALARIES  E2
    WHERE E1.EMPNO = E2.EMPNO

Á¶È¸ ÀçÀÛ¼ºÁß¿¡ ÀÌµé µÎ °³ÀÇ ºä´Â º´ÇÕµÇ¾î ´ÙÀ½ Á¶È¸¸¦ ÀÛ¼ºÇÏ°Ô µË´Ï´Ù.

   SELECT E1.EMPNO, E1.FIRSTNME, E1.LASTNAME, E1.EDLEVEL, E2.SALARY
     FROM EMPLOYEE E1,
          EMPLOYEE E2
    WHERE E1.EMPNO = E2.EMPNO
      AND E1.EDLEVEL > 17
      AND E2.SALARY  > 35000

»ç¿ëÀÚ°¡ ÀÛ¼ºÇÑ SELECT¹®À» Æ÷ÇÔÇÏ´Â µÎ °³ÀÇ ºä·ÎºÎÅÍ SELECT¹®À» º´ÇÕÇÏ¿©, ÃÖÀûÈ­ ¾Ë°í¸®ÁòÀº ¾×¼¼½º Ç÷£À» ¼±ÅÃÇÒ ¶§º¸´Ù ¸¹Àº ¼±ÅûçÇ×À» °í·ÁÇÒ ¼ö ÀÖ½À´Ï´Ù. ¶ÇÇÑ, º´ÇÕµÈ µÎ °³ÀÇ ºä°¡ µ¿ÀÏÇÑ ±âº» Å×À̺íÀ» »ç¿ëÇÏ´Â °æ¿ì, Ãß°¡ ÀçÀÛ¼º ÀÛ¾÷ÀÌ ¿¹ - Áߺ¹ Á¶ÀÎ Á¦°Å¿¡ ±â¼úµÈ °Íó·³ ¼öÇàµÉ ¼ö ÀÖ½À´Ï´Ù.

¿¹ - ºÎ¼Ó Á¶È¸¿¡¼­ Á¶ÀÎÀ¸·ÎÀÇ º¯È¯

SQL ÄÄÆÄÀÏ·¯´Â ´ÙÀ½ÀÇ ºÎ¼Ó Á¶È¸¸¦ Æ÷ÇÔÇÏ´Â Á¶È¸¸¦ °¡Á®¿É´Ï´Ù.

   SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO
     FROM EMPLOYEE
   WHERE WORKDEPT IN
         (SELECT DEPTNO
            FROM DEPARTMENT
           WHERE DEPTNAME = 'OPERATIONS')

±×¸®°í, À̰ÍÀ» ´ÙÀ½°ú °°Àº ¾ç½ÄÀÇ Á¶ÀÎ Á¶È¸·Î º¯È¯ÇÕ´Ï´Ù.

   SELECT DISTINCT EMPNO, FIRSTNME, LASTNAME, PHONENO
     FROM EMPLOYEE EMP,
          DEPARTMENT DEPT
    WHERE EMP.WORKDEPT = DEPT.DEPTNO
      AND DEPT.DEPTNAME = 'OPERATIONS'

ÀϹÝÀûÀ¸·Î Á¶ÀÎÀº ºÎ¼Ó Á¶È¸º¸´Ù ÈξÀ ´õ È¿À²ÀûÀ¸·Î ½ÇÇàµË´Ï´Ù.

¿¹ - Áߺ¹ Á¶ÀÎ Á¦°Å

Á¾Á¾ ºÒÇÊ¿äÇÑ Á¶ÀÎÀ» Æ÷ÇÔÇÏ´Â Á¶È¸°¡ ÀÛ¼ºµÇ°Å³ª »ý¼ºµÉ ¼ö ÀÖ½À´Ï´Ù. ¶ÇÇÑ, ´ÙÀ½°ú °°Àº Á¶È¸°¡ ¿¹ - ºä º´ÇÕ¿¡ ±â¼úµÈ Á¶È¸ ÀçÀÛ¼º ´Ü°èÁß¿¡ »ý¼ºµÉ ¼ö ÀÖ½À´Ï´Ù.

   SELECT E1.EMPNO, E1.FIRSTNME, E1.LASTNAME, E1.EDLEVEL, E2.SALARY
     FROM EMPLOYEE E1,
          EMPLOYEE E2
    WHERE E1.EMPNO = E2.EMPNO
      AND E1.EDLEVEL > 17
      AND E2.SALARY  > 35000

ÀÌ Á¶È¸¿¡¼­, SQL ÄÄÆÄÀÏ·¯´Â Á¶ÀÎÀ» Á¦°ÅÇϰí ÀÌ Á¶È¸¸¦ ´Ü¼øÈ­ÇÒ ¼ö ÀÖ½À´Ï´Ù.

   SELECT EMPNO, FIRSTNME, LASTNAME, EDLEVEL, SALARY
     FROM EMPLOYEE
    WHERE EDLEVEL > 17
      AND SALARY  > 35000

´Ù¸¥ ¿¹¿¡¼­´Â EMPLOYEE ¹× DEPARTMENT »ùÇà Å×ÀÌºí°£ÀÇ ºÎ¼­ ¹øÈ£¿¡ ÂüÁ¶ Á¦ÇÑÁ¶°ÇÀÌ ÀÖ´Ù°í °¡Á¤ÇÕ´Ï´Ù. ù¹øÂ° ºä°¡ ÀÛ¼ºµË´Ï´Ù.

   CREATE VIEW PEPLVIEW
      AS SELECT FIRSTNME, LASTNAME, SALARY, DEPTNO, DEPTNAME, MGRNO
            FROM EMPLOYEE E DEPARTMENT D
            WHERE E.WORKDEPT = D.DEPTNO

±×·± ´ÙÀ½, ´ÙÀ½°ú °°Àº Á¶È¸°¡ ÀÛ¼ºµË´Ï´Ù.

   SELECT LASTNAME, SALARY
      FROM PEPLVIEW

´ÙÀ½°ú °°ÀÌ Á¶È¸°¡ º¯°æµË´Ï´Ù.

   SELECT LASTNAME, SALARY
     FROM EMPLOYEE
      WHERE WORKDEPT NOT NULL

ÀÌ·¯ÇÑ »óȲ¿¡¼­´Â, Á¶È¸¸¦ ´Ù½Ã ÀÛ¼ºÇÒ ¼ö ÀÖ´Ù´Â »ç½ÇÀ» ¾Ë´õ¶óµµ ±âÁ¸ Å×ÀÌºí¿¡ ´ëÇÑ ¾×¼¼½º ±ÇÇÑÀÌ ¾ø±â ¶§¹®¿¡ ÀÛ¼ºÇÏÁö ¸øÇÒ ¼ö ÀÖ½À´Ï´Ù. »ç¿ëÀÚ´Â ºä¿¡ ´ëÇÑ ¾×¼¼½º ±ÇÇѸ¸À» °¡Áú ¼ö ÀÖ½À´Ï´Ù. µû¶ó¼­, ÀÌ·¯ÇÑ À¯ÇüÀÇ ÃÖÀûÈ­´Â µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ ³»¿¡¼­ ¼öÇàµÇ¾î¾ß ÇÕ´Ï´Ù.

´ÙÀ½°ú °°Àº °æ¿ì¿¡ ÂüÁ¶ ¹«°á¼º Á¶ÀÎ ³»¿¡ Áߺ¹ÀÌ ÀÖÀ» ¼ö ÀÖ½À´Ï´Ù.

¿¹ - °øÀ¯ ÃѰè

Á¶È¸ ³»¿¡ ¿©·¯ °³ÀÇ ÇÔ¼ö¸¦ »ç¿ëÇÏ¸é ½Ã°£ÀÌ °É¸®´Â ¸î °¡Áö °è»êÀ» »ý¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. Á¶È¸ ³»¿¡¼­ ¼öÇàµÇ¾î¾ß ÇÏ´Â °è»êÀÇ ¼ö¸¦ ÁÙÀÌ¸é ´õ °³¼±µÈ Ç÷£À» »ý¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. SQL ÄÄÆÄÀÏ·¯´Â ´ÙÀ½°ú °°Àº ´ÙÁß ÇÔ¼ö¸¦ »ç¿ëÇÏ´Â Á¶È¸¸¦ ÃëÇϰí

   SELECT SUM(SALARY+BONUS+COMM) AS OSUM,
     AVG(SALARY+BONUS+COMM) AS OAVG,
     COUNT(*) AS OCOUNT
   FROM EMPLOYEE;

Á¶È¸¸¦ ´ÙÀ½ ¹æ¹ýÀ¸·Î º¯È¯ÇÕ´Ï´Ù.

   SELECT OSUM,
     OSUM/OCOUNT
     OCOUNT
   FROM (SELECT SUM(SALARY+BONUS+COMM) AS OSUM,
     COUNT(*) AS OCOUNT
   FROM EMPLOYEE) AS SHARED_AGG;

ÀÌ ÀçÀÛ¼ºÀº Á¶È¸¿¡¼­ µÎ °³ÀÇ µ¡¼À°ú µÎ °³ÀÇ °è¼ö¸¦ ÇÑ °³ÀÇ µ¡¼À°ú ÇÑ °³ÀÇ °è¼ö·Î ÁÙÀÔ´Ï´Ù.


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