dba2b

Query for given questions:

1) SELECT S1.* FROM STUDENT S1, SEMSEC S2, CLASS C
WHERE S1.USN = C.USN AND C.SSID = S2.SSID
AND S2.SEM = 4 AND S2.SEC = 'C';

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

3) CREATE VIEW IAMARKS_1 AS
SELECT SUBCODE , TEST1
FROM IAMARKS WHERE USN = '1DS19IS049';
SELECT * FROM IAMARKS_1;

4) UPDATE IAMARKS
SET FINALIA = (GREATEST(TEST1, TEST2, TEST3) +
((TEST1+TEST2+TEST3) - GREATEST(TEST1, TEST2, TEST3) -
LEAST(TEST1, TEST2, TEST3)))/2;
SELECT * FROM IAMARKS;

5) SELECT SS.SEC,S.*,
(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, COURSE SUB
WHERE S.USN = IA.USN AND SS.SSID = IA.SSID AND
SUB.SUBCODE = IA.SUBCODE AND SUB.SEM = 8;