¡á¿¹ 4_1_19 || Çհ踦 ±¸ÇÔ - GROUP BY ¿Í ROLLUP
____SELECT DECODE(JOIN_GBN_CODE,NULL,'ÇÕ°è',JOIN_GBN_CODE) AS GBN_CODE,
COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY ROLLUP(JOIN_GBN_CODE);
¡á¿¹ 4_1_20 || ¼Ò°è¸¦ ±¸ÇÔ - GROUP BY ¿Í ROLLUP
SELECT DECODE(JOIN_GBN_CODE,NULL,'¼Ò°è',JOIN_GBN_CODE) AS GBN_CODE,
SEX, COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY ROLLUP(JOIN_GBN_CODE), SEX;
¡á¿¹ 4_1_21 || ¼Ò°è¿Í Çհ踦 ÇÔ²² ±¸ÇÔ - GROUP BY ¿Í ROLLUP
SELECT DECODE(JOIN_GBN_CODE,NULL,'ÇÕ°è',JOIN_GBN_CODE) AS GBN_CODE,
DECODE(SEX,NULL,DECODE(JOIN_GBN_CODE,NULL,' ','¼Ò°è'),SEX) AS SEX,
COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY ROLLUP(JOIN_GBN_CODE,SEX);
¡á¿¹ 4_1_22 || Çհ踦 ±¸ÇÔ - GROUP BY ¿Í CUBE
SELECT DECODE(JOIN_GBN_CODE,NULL,'ÇÕ°è',JOIN_GBN_CODE) AS GBN_CODE,
COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY CUBE(JOIN_GBN_CODE);
¡á¿¹ 4_1_23 || ¼Ò°è¸¦ ±¸ÇÔ - GROUP BY ¿Í CUBE
SELECT DECODE(JOIN_GBN_CODE,NULL,'¼Ò°è',JOIN_GBN_CODE) AS GBN_CODE,
SEX, COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY CUBE(JOIN_GBN_CODE), SEX;
¡á¿¹ 4_1_24 || ¼Ò°è¿Í Çհ踦 ÇÔ²² ±¸ÇÔ - GROUP BY ¿Í CUBE
SELECT DECODE(JOIN_GBN_CODE,NULL,'ÇÕ°è',JOIN_GBN_CODE) AS GBN_CODE,
DECODE(SEX,NULL,DECODE(JOIN_GBN_CODE,NULL,' ','¼Ò°è'),SEX) AS SEX,
COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY CUBE(JOIN_GBN_CODE,SEX);
¡á ¿¹ 4_1_25 || Çհ踦 ±¸ÇÔ - GROUP BY ¿Í GROUPING SETS
SELECT DECODE(JOIN_GBN_CODE,NULL,'ÇÕ°è',JOIN_GBN_CODE) AS GBN_CODE,
COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY GROUPING SETS(JOIN_GBN_CODE,());
¡á¿¹ 4_1_26 || ¼Ò°è¸¦ ±¸ÇÔ - GROUP BY ¿Í GROUPING SETS
SELECT DECODE(JOIN_GBN_CODE,NULL,'¼Ò°è',JOIN_GBN_CODE) AS GBN_CODE,
SEX, COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY GROUPING SETS((JOIN_GBN_CODE,SEX),(SEX));
¡á¿¹ 4_1_27 || ¼Ò°è¿Í Çհ踦 ÇÔ²² ±¸ÇÔ - GROUP BY ¿Í GROUPING SETS
SELECT DECODE(JOIN_GBN_CODE,NULL,DECODE(SEX,NULL,'ÇÕ°è','¼Ò°è'),JOIN_GBN_CODE)
AS GBN_CODE,
SEX, COUNT(SABUN), MIN(ENG_NAME),
COUNT(ENG_NAME), ROUND(AVG(SALARY))
FROM INSA
GROUP BY GROUPING SETS((JOIN_GBN_CODE,SEX),(SEX),());
¡á¿¹ 4_1_28 || Nested Loop Join SQL¹®
SELECT /*+ ordered use_nl(t t1) */
T.SABUN, T.ENG_NAME, T.JOIN_GBN_CODE, T.SALARY,
T1.CMP_NAME, T1.CMP_PHONE, T1.CMP_ADDR1
FROM INSA T, INSA_COMPANY T1
WHERE T.CMP_REG_NO = T1.CMP_REG_NO
AND T.SABUN BETWEEN '2012121201' AND '2012123199'
AND T1.CMP_REG_NO = '2222222206';
¡á¿¹ 4_1_29 || Sort Merge Join SQL¹®
SELECT /*+ LEADING(T1 T) USE_MERGE(T) */
T.SABUN, T.ENG_NAME, T.JOIN_GBN_CODE, T.SALARY,
T1.CMP_NAME, T1.CMP_PHONE, T1.CMP_ADDR1
FROM INSA T, INSA_COMPANY T1
WHERE T.CMP_REG_NO = T1.CMP_REG_NO;
¡á¿¹ 4_1_30 || Hash Join SQL¹®
SELECT T.SABUN, T.ENG_NAME, T.JOIN_GBN_CODE, T.SALARY,
T1.CMP_NAME, T1.CMP_PHONE, T1.CMP_ADDR1
FROM____ INSA T, INSA_COMPANY T1
WHERE T.CMP_REG_NO = T1.CMP_REG_NO;