Berikut adalah penggunaan CTE ( Common Table Expression ) Pada Oracle
1. Buat table
create table STUDENT_MARKS
(
STUDENT_ID NUMBER,
STUDENT_NAME VARCHAR2(100),
TELUGU NUMBER(3),
HINDI NUMBER(3),
ENGLISH NUMBER(3),
MATHS NUMBER(3),
PHYS NUMBER(3),
CHEM NUMBER(3),
MARKS NUMBER default "TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM",
PERCENTAGE NUMBER default ("TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM")*100/600
);
2. Isikan data
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (1, 'VISWANATH', 87, 84, 74, 97, 74, 74);
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (2, 'VINITH', 88, 83, 79, 100, 79, 89);
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (3, 'RAVI', 87, 52, 59, 100, 98, 68);
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (4, 'RAGHU', 88, 79, 85, 89, 69, 96);
3. Tampilkan Data
SELECT *
FROM STUDENT_MARKS;
Output :
STUDENT_ID STUDENT_NAME TELUGU HINDI ENGLISH MATHS PHYS CHEM MARKS PERCENTAGE
1 VISWANATH 87 84 74 97 74 74 490 81.6666666666667
2 VINITH 88 83 79 100 79 89 518 86.3333333333333
3 RAVI 87 52 59 100 98 68 464 77.3333333333333
4 RAGHU 88 79 85 89 69 96 506 84.3333333333333
4. Query CTE ( Common Table Expression )
WITH CTE AS
(
SELECT STUDENT_ID,
STUDENT_NAME,
SUBJECT_NAME,
SUB_MARKS
FROM STUDENT_MARKS
UNPIVOT
(SUB_MARKS -- unpivot_clause
FOR SUBJECT_NAME -- unpivot_for_clause
IN( -- unpivot_in_clause
TELUGU AS 'TELUGU',
HINDI AS 'HINDI',
ENGLISH AS 'ENGLISH',
MATHS AS 'MATHS',
PHYS AS 'PHYSICS',
CHEM AS 'CHEMISTRY'
)
)
)
SELECT X.STUDENT_ID,
X.STUDENT_NAME,
X.SUBJECT_NAME,
X.SUB_MARKS
FROM CTE X,
(
SELECT SUBJECT_NAME,
MAX(SUB_MARKS) SUB_MARKS
FROM CTE
GROUP BY SUBJECT_NAME
) Y
WHERE X.SUBJECT_NAME= Y.SUBJECT_NAME
AND X.SUB_MARKS=Y.SUB_MARKS
ORDER BY X.SUBJECT_NAME;
Output :
