We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 4_3_1 || Merge ÇÔ¼ö¸¦ ÀÌ¿ëÇÑ µ¥ÀÌÅÍ Ã³¸® ¿¹ 1MERGE INTO BONUSES D -- 󸮴ë»óÅ×À̺íUSING ( SELECT EMPLOYEE_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80 ) S -- Á¤º¸Á¦°øÁýÇÔON ( D.EMPLOYEE_ID = S.EMPLOYEE_ID ) -- ¿¬°á°í¸®WHEN MATCHED THEN ____UPDATE SET D.BONUS = D.BONUS+S.SALARY*.01 -- Á¸Àç ½Ã Ã³¸® DELETE WHERE (S.SALARY > 8000)WHEN NOT MATCHED THENINSERT ( D.EMPLOYEE_ID, D.BONUS ) -- ºÎÀç½Ã ó¸®VALUES ( S.EMPLOYEE_ID, S.SALARY*0.1 );
¡á¿¹ 4_3_2 || Merge ÇÔ¼ö¸¦ ÀÌ¿ëÇÑ µ¥ÀÌÅÍ Ã³¸® ¿¹ 2MERGE INTO POR_VOC_LOG_INFO DUSING ( SELECT :IN_LOG_YMD LOG_YMD, :IN_LOG_EMP_NO LOG_EMP_NO FROM DUAL ) SON ( D.LOG_YMD = S.LOG_YMD AND D.LOG_EMP_NO = S.LOG_EMP_NO )WHEN MATCHED THEN UPDATE SET____ D.LOG_CNT = D.LOG_CNT+1WHEN NOT MATCHED THEN INSERT (D.LOG_YMD, D.LOG_EMP_NO, D.LOG_CNT) VALUES (:IN_LOG_YMD, :IN_LOG_EMP_NO, 1);
¡á¿¹ 4_3_3 || ºÐ¼® ÇÔ¼öÀÇ »ç¿ë ¿¹ SELECT SABUN, ENG_NAME, JOIN_GBN_CODE, COUNT(SABUN) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS COUNT_MEM FROM INSA;
¡á¿¹ 4_3_4 || SALARYÀÇ µ¥ÀÌÅÍ °ªÀ¸·Î ¼øÀ§ ¸Å±â´Â ÇÔ¼öµé »ç¿ë ¿¹SELECT ROWNUM, T1.*FROM ( SELECT SABUN, ENG_NAME, SALARY, JOIN_GBN_CODE, RANK() OVER( PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC ) AS RANK, DENSE_RANK() OVER( PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC ) AS DENSE_RANK, ROW_NUMBER() OVER( PARTITION BY JOIN_GBN_CODE ORDER BY SALARY DESC ) AS ROW_NUMBER FROM INSA ) T1WHERE JOIN_GBN_CODE = 'RGL'AND ROWNUM <= 10;
¡á¿¹ 4_3_5 || TOP-N ºÐ¼® ÇÔ¼öÀÇ »ç¿ë ¿¹ --SQL1 SELECT ROWNUM, T1.*FROM ( SELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, JOIN_GBN_CODE FROM INSA WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013' ORDER BY SABUN DESC ) T1 WHERE JOIN_GBN_CODE = 'RGL' AND ROWNUM <=5; --SQL2 SELECT T1.*FROM ( SELECT SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, JOIN_GBN_CODE, ROW_NUMBER() OVER( PARTITION BY JOIN_GBN_CODE ORDER BY JOIN_DAY DESC ) AS ¼øÀ§ FROM INSA WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013' ORDER BY SABUN DESC ) T1WHERE JOIN_GBN_CODE = 'RGL'AND ¼øÀ§ <= 5ORDER BY ¼øÀ§;
¡á¿¹ 4_3_6 || INDEX »ç¿ëÀ¸·Î RANK Á¶È¸SELECT ROWNUM, T1.*FROM ( SELECT /*+ INDEX_DESC(INSA INSA_PK) */ SABUN, ENG_NAME, NVL(JOIN_DAY,SUBSTR(SABUN,1,8)) AS JOIN_DAY, JOIN_GBN_CODE FROM INSA WHERE SUBSTR(NVL(JOIN_DAY,SUBSTR(SABUN,1,8)),1,4) = '2013' ) T1WHERE JOIN_GBN_CODE = 'RGL'AND ROWNUM <=5;