We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 4_2_1 || ±âº» ____SELECT Äõ¸®SELECT T2.ACAD_ABILITY, AVG(T1.AGE) AS AVG_AGEFROM CHAPTER_1 T1, CHAPTER_2 T2WHERE T1.SABUN = T2.SABUNGROUP BY T2.ACAD_ABILITYHAVING T2.ACAD_ABILITY='´ëÁ¹';
¡á¿¹ 4_2_2 || ±âº» SELECT Äõ¸®SELECT T1.SABUN AS SABUN, T1.NAME AS NAME, T1.AGE, T2.ACAD_ABILITY, T2.MAJOR_STUDY, T2.GRAT_YMFROM CHAPTER_1 T1, CHAPTER_2 T2WHERE T1.SABUN = T2.SABUNAND T2.ACAD_ABILITY = '´ëÁ¹' ORDER BY AGE DESC;
¡á¿¹ 4_2_3 || ±âº» SELECT Äõ¸®SELECT T2.ACAD_ABILITY, NVL(T2.MAJOR_STUDY, ' - '), COUNT(*) TOTAL_COUNTFROM CHAPTER_1 T1, CHAPTER_2 T2WHERE T1.SABUN = T2.SABUNGROUP BY ROLLUP(T2.ACAD_ABILITY, T2.MAJOR_STUDY)HAVING T2.ACAD_ABILITY = '´ëÁ¹';
¡á¿¹ 4_2_4 || ±âº» SELECT Äõ¸®--SQL1SELECT AVG(AGE) AVG_AGEFROM TALBLE_1;
--SQL2SELECT SABUN, NAME, AGE FROM TABLE_1WHERE AGE <= '27';
¡á¿¹ 4_2_5 || SUBQUERY¸¦ È°¿ëÇÑ µ¥ÀÌÅÍ Á¶È¸SELECT SABUN, NAME, AGEFROM CHAPTER_1WHERE AGE <= ( SELECT AVG(AGE) AS AVG_AGE FROM CHAPTER_1 );
¡á¿¹ 4_2_6 || SELECT¹®SELECT DECODE(T2.RECT_REG_NO,NULL,'ÇÕ°è',T2.RECT_REG_NO) AS ä¿ëµî·Ï¹øÈ£, DECODE( T2.RECT_REG_NO,NULL,'',MAX(T2.PJT_NAME)) AS ÇÁ·ÎÀèÆ®¸í, DECODE( T2.RECT_REG_NO,NULL,'',MAX(T2.PJT_PLACE)) AS ÇÁ·ÎÀèÆ®Àå¼Ò, DECODE( T2.RECT_REG_NO,NULL,'',MAX(DECODE(T2.PUT_START_DAY,NULL,'0001/01/01',TO_CHAR(T2.PUT_START_DAY,'YYYY/MM/DD')))) AS ÅõÀÔ½ÃÀÛÀÏ, DECODE( T2.RECT_REG_NO,NULL,'',MAX(DECODE(T2.PUT_END_DAY,NULL,'9999/12/31',TO_CHAR(T2.PUT_END_DAY,'YYYY/MM/DD')))) AS ÅõÀÔÁ¾·áÀÏÀÚ, DECODE( T2.RECT_REG_NO,NULL,'ÃÑ'||COUNT(T1.SABUN)||'¸í',MAX(T1.NAME)||(DECODE(COUNT(T1.SABUN),0,'0¸í',DECODE(COUNT(T1.SABUN),1,'','¿Ü'||(COUNT(T1.SABUN)-1)||'¸í')))) AS ÅõÀÔÀοø¼ö FROM INSA T1,--Àλ縶½ºÅÍ ( SELECT TT1.RECT_REG_NO ,TT1.PJT_NAME ,TT1.PJT_PLACE ,TT1.PUT_START_DAY ,TT1.PUT_END_DAY ,TT2.SABUN FROM INSA_RECT_NOTICE TT1, --ä¿ë°ø°í INSA_EMP_PJT TT2 --»ç¿øº°ÇÁ·ÎÁ§Æ® WHERE TT1.RECT_REG_NO = TT2.RECT_REG_NO(+) AND TT1.PJT_NAME LIKE :IN_PJT_NAME||'%' --ÇÁ·ÎÁ§Æ®¸íÀ» ¹Þ´Â´Ù. ) T2WHERE T2.SABUN = T1.SABUN(+)GROUP BY ROLLUP(T2.RECT_REG_NO)ORDER BY T2.RECT_REG_NO;
¡á¿¹ 4_2_7 || SELECT¹® SELECT T1.CMP_NAME AS ¾÷ü¸í, T2.NAME AS ¼º¸í, (SELECT CODE_NAME FROM CMM_CODE_DETAIL TT2 WHERE T2.POS_GBN_CODE = TT2.CODE_NO AND TT2.CLASS_CODE = 'C03' --Á÷À§±¸¹®ÄÚµå) AS Á÷À§¸í, (SELECT CODE_NAME FROM____ CMM_CODE_DETAIL TT2 WHERE T2.CLASS_GBN_CODE = TT2.CODE_NO AND TT2.CLASS_CODE = 'B01' --µî±Þ±¸ºÐÄÚµå) AS µî±Þ¸í, T4.PJT_NAME AS ÇÁ·ÎÁ§Æ®¸í, T3.PUT_START_DAY AS ÅõÀÔ½ÃÀÛÀÏÀÚ, T3.PUT_END_DAY AS ÅõÀÔÁ¾·áÀÏÀÚ, T1.SALE_COMPT_NAME AS ¿µ¾÷´ã´çÀÚ, T1.CMP_PHONE AS ¾÷üÀüȹøÈ£FROM INSA_COMPANY T1, --¾÷ü INSA T2, --Àλ縶½ºÅÍ INSA_EMP_PJT T3, --»ç¿øº°ÇÁ·ÎÁ§Æ® INSA_RECT_NOTICE T4 --ä¿ë°ø°íWHERE 1=1 AND T1.CMP_REG_NO = T2.CMP_REG_NO AND T2.SABUN = T3.SABUN AND T3.RECT_REG_NO = T4.RECT_REG_NO AND ( (T3.PUT_START_DAY>=TO_DATE(:IN_F_PUT_START_DAY,'YYYYMMDD') AND T3.PUT_START_DAY <TO_DATE(:IN_T_PUT_START_DAY,'YYYYMMDD')+1) OR (T3.PUT_END_DAY>=TO_DATE(:IN_F_PUT_START_DAY,'YYYYMMDD') AND T3.PUT_END_DAY <TO_DATE(:IN_T_PUT_START_DAY,'YYYYMMDD')+1) ) AND T1.CMP_NAME LIKE :IN_CMP_NAME||'%';