SQL ÄÄÆÄÀÏ·¯´Â °¡Àå ÀûÀº ¼öÀÇ Á¶ÀÛ, ƯÈ÷ SELECT Á¶ÀÛÀ» °®µµ·Ï Á¶È¸¸¦ ±¸¼ºÇÏ·Á°í ÇÒ ¶§, Á¶È¸ Á¶ÀÛÀ» º´ÇÕÇϱâ À§ÇÑ Á¶È¸¸¦ ´Ù½Ã ÀÛ¼ºÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â SQL ÄÄÆÄÀÏ·¯·Î º´ÇÕµÉ ¼ö ÀÖ´Â ÀϺΠÁ¶ÀÛÀ» ¼³¸íÇϱâ À§ÇØ Á¦°øµË´Ï´Ù.
SELECT¹®¿¡¼ ºä¸¦ »ç¿ëÇϸé Å×À̺íÀÇ Á¶ÀÎ ¼ø¼¸¦ Á¦ÇÑÇÒ ¼ö ÀÖÀ¸¸ç Å×À̺íÀÇ Áߺ¹µÈ Á¶ÀÎÀÌ ¹ß»ýÇÒ ¼öµµ ÀÖ½À´Ï´Ù. Á¶È¸ ÀçÀÛ¼ºÁß¿¡ ºä¸¦ º´ÇÕÇÏ¿© ÀÌ·¯ÇÑ Á¦ÇÑ»çÇ×À» ¾ø¾Ù ¼ö ÀÖ½À´Ï´Ù.
ÃÖÀûÈ ¾Ë°í¸®ÁòÀÌ SELECT¹®¿¡¼ ºÎ¼Ó Á¶È¸¸¦ ãÀ» °æ¿ì, Å×À̺íÀÇ ¼ø¼ 󸮿¡ ´ëÇÑ ¼±ÅÃÀ¸·Î Á¦ÇÑµÉ ¼ö ÀÖ½À´Ï´Ù.
Á¶È¸ ÀçÀÛ¼ºÁß¿¡ ÃÖÀû鵃 SELECT¹®À» ´õ ´Ü¼øÈÇÒ ¼ö ÀÖµµ·Ï Áߺ¹µÈ Á¶ÀÎÀ» Á¦°ÅÇÒ ¼ö ÀÖ½À´Ï´Ù.
´Ù¸¥ ÇÔ¼ö¸¦ »ç¿ëÇÒ ¶§ Á¶È¸¸¦ ÀçÀÛ¼ºÇÏ¸é ¼öÇàµÇ¾î¾ß ÇÏ´Â °è»êÀÇ ¼ö¸¦ ÁÙ¿© ÁÙ ¼ö ÀÖ½À´Ï´Ù.
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;
ÀÌ ÀçÀÛ¼ºÀº Á¶È¸¿¡¼ µÎ °³ÀÇ µ¡¼À°ú µÎ °³ÀÇ °è¼ö¸¦ ÇÑ °³ÀÇ µ¡¼À°ú ÇÑ °³ÀÇ °è¼ö·Î ÁÙÀÔ´Ï´Ù.