SQL Query to calculate grade of subjects based on marks.
CREATE TABLE STUDENT(ROLLNO NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(50),
C_LANG NUMBER(3) CHECK(C_LANG BETWEEN 0 AND 100),
JAVA NUMBER(3) CHECK(JAVA BETWEEN 0 AND 100),
ORACLE NUMBER(3) CHECK(ORACLE BETWEEN 0 AND 100));
INSERT INTO STUDENT
VALUES(101,
'PARAG SHUKLA',
56,
77,
87);
INSERT INTO STUDENT
VALUES(102,
'KAPIL SHUKLA',
87,
67,
54);
INSERT INTO STUDENT
VALUES(103,
'DEVEN PATEL',
76,
70,
68);
INSERT INTO STUDENT
VALUES(104,
'MUNO',
48,
78,
30);
INSERT INTO STUDENT
VALUES(105,
'CHAKO',
45,
75,
57);
SELECT ROLLNO,
CASE
WHEN C_LANG BETWEEN 85 AND 100 THEN 'AA'
WHEN C_LANG BETWEEN 75 AND 84 THEN 'AB'
WHEN C_LANG BETWEEN 65 AND 74 THEN 'BB'
WHEN C_LANG BETWEEN 55 AND 64 THEN 'BC'
WHEN C_LANG BETWEEN 50 AND 54 THEN 'CC'
WHEN C_LANG <50 THEN 'FF'
END AS C_LANG,
CASE
WHEN ORACLE BETWEEN 85 AND 100 THEN 'AA'
WHEN ORACLE BETWEEN 75 AND 84 THEN 'AB'
WHEN ORACLE BETWEEN 65 AND 74 THEN 'BB'
WHEN ORACLE BETWEEN 55 AND 64 THEN 'BC'
WHEN ORACLE BETWEEN 50 AND 54 THEN 'CC'
WHEN ORACLE <50 THEN 'FF'
END AS ORACLE,
CASE
WHEN JAVA BETWEEN 85 AND 100 THEN 'AA'
WHEN JAVA BETWEEN 75 AND 84 THEN 'AB'
WHEN JAVA BETWEEN 65 AND 74 THEN 'BB'
WHEN JAVA BETWEEN 55 AND 64 THEN 'BC'
WHEN JAVA BETWEEN 50 AND 54 THEN 'CC'
WHEN JAVA <50 THEN 'FF'
END AS JAVA,
NAME
FROM STUDENT;
