¡á¿¹ 4_2_1 || ±âº» ____SELECT Äõ¸®
SELECT T2.ACAD_ABILITY, AVG(T1.AGE) AS AVG_AGE
FROM CHAPTER_1 T1, CHAPTER_2 T2
WHERE T1.SABUN = T2.SABUN
GROUP BY T2.ACAD_ABILITY
HAVING 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_YM
FROM CHAPTER_1 T1, CHAPTER_2 T2
WHERE T1.SABUN = T2.SABUN
AND T2.ACAD_ABILITY = '´ëÁ¹'
ORDER BY AGE DESC;
¡á¿¹ 4_2_3 || ±âº» SELECT Äõ¸®
SELECT T2.ACAD_ABILITY, NVL(T2.MAJOR_STUDY, ' - '), COUNT(*) TOTAL_COUNT
FROM CHAPTER_1 T1, CHAPTER_2 T2
WHERE T1.SABUN = T2.SABUN
GROUP BY ROLLUP(T2.ACAD_ABILITY, T2.MAJOR_STUDY)
HAVING T2.ACAD_ABILITY = '´ëÁ¹';
¡á¿¹ 4_2_4 || ±âº» SELECT Äõ¸®
--SQL1
SELECT AVG(AGE) AVG_AGE
FROM TALBLE_1;
--SQL2
SELECT SABUN, NAME, AGE
FROM TABLE_1
WHERE AGE <= '27';
¡á¿¹ 4_2_5 || SUBQUERY¸¦ È°¿ëÇÑ µ¥ÀÌÅÍ Á¶È¸
SELECT SABUN, NAME, AGE
FROM CHAPTER_1
WHERE 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||'%' --ÇÁ·ÎÁ§Æ®¸íÀ» ¹Þ´Â´Ù.
) T2
WHERE 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||'%';