SELECT S.*, SS.SEM, SS.SEC
 FROM STUDENT S, SEMSEC SS, CLASS C
 WHERE S.USN = C.USN AND
 SS.SSID = C.SSID AND
 SS.SEM = 4 AND
 SS.SEC='C';


SELECT SS.SEM, SS.SEC, S.GENDER, COUNT (S.GENDER) AS COUNT
FROM STUDENT S, SEMSEC SS, CLASS C
WHERE S.USN = C.USN AND
SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER
ORDER BY SEM;


CREATE VIEW STU_TEST1_MARKS_VIEW
 AS
 SELECT TEST1, SUBCODE
 FROM IAMARKS
 WHERE USN = '1BI15CS101';


UPDATE IAMARKS SET FINALIA=((TEST1+TEST2+TEST3)-
LEAST(TEST1,TEST2,TEST3))/2;


SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,IA.SUBCODE,
 (CASE
 WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
 WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
 ELSE 'WEAK'
 END) AS CAT
 FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
 WHERE S.USN = IA.USN AND
 SS.SSID = IA.SSID AND
 SUB.SUBCODE = IA.SUBCODE AND