List all the student details studying in the fourth semester ‘C’ section.
SELECT S.*
FROM sstudent S, ssemsec SS, sclass C
WHERE SS.ssid = C.ssid
AND C.usn = S.usn
AND SS.sem = 4
AND SS.section = ‘c’
GROUP BY S.usn;
Compute the total number of male and female students in each semester and in each
section.
SELECT SS.sem, SS.section, S.gender, COUNT(*)
FROM ssemsec SS, sclass C,sstudent S
WHERE SS.ssid = C.ssid AND S.usn = C.usn
GROUP BY SS.sem ,SS.section , S.gender;
. Create a view of Test1 marks of student USN ‘1BI17CS101’ in all subjects.
CREATE VIEW STMARKS AS
(SELECT subcode, test1
FROM siamarks
WHERE siamarks.usn = ‘1SJ14CS81’);
SELECT * FROM STMARKS;
Calculate the FinalIA (average of best two test marks) and update the
corresponding table for all students.
CREATE VIEW finalia AS
(SELECT usn, subcode,
GREATEST(AVG(test1 + test2) / 2,AVG(test1 + test3) / 2,AVG(test2 + test3) / 2) AS finalia
FROM siamarks
GROUP BY usn , subcode);
UPDATE siamarks i set final=(SELECT finalia from finadlia f where i.usn=f.usn and
i.subcode=f.subcode);
5. Categorize students based on the following criterion: If FinalIA = 17 to 20 then CAT = ‘Outstanding’ If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT = ‘Weak’ Give these details only for 8th semester A, B, and C section students
SELECT i.usn, s.sem, s.section, i.subcode,
CASE
WHEN final >= 17 AND final <= 20 THEN ‘Outstanding’ WHEN final >= 12 AND final <= 16 THEN ‘Average’
ELSE ‘Weak’
END AS CAT
FROM siamarks i, sclass c,ssemsec s
WHERE i.usn = c.usn AND c.ssid = s.ssid AND s.sem = 8
AND s.section IN (‘A’ , ‘B’, ‘C’);
