Program 4 (QUERIES)

Queries (Program 4)

1.List all the student details studying in fourth semester ‘C’ section.

SELECT *

FROM STUDENT

WHERE Usn IN(SELECT Usn

                                 FROM CLASS

                                 WHERE Ssid IN(SELECT Ssid

                                                                  FROM SEMSEC

                                                                  WHERE Sem=4 AND Sec='C'));

                                              OR

SELECT S.*,Sem,Sec 

FROM STUDENT S,CLASS C,SEMSEC SS

WHERE S.Usn=C.Usn AND C.Ssid=SS.Ssid AND Sem=4 AND Sec='C';

OR

SELECT S.*,Ssid

FROM STUDENT S,CLASS C

WHERE S.Usn=C.Usn AND Ssid='4c';

2.  Compute the total number of male and female students in each semester and in each section.

SELECT Sem,Sec,Gender,count(s.Gender) as COUNT

FROM STUDENT S,CLASS C,SEMSEC SS

WHERE S.Usn=C.Usn AND C.Ssid=SS.Ssid

GROUP BY Sem,Sec,Gender

ORDER BY Sem;

3.  Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects. (We have changed to 1KG13CS001).

CREATE VIEW STUD_TEST1_MARKS

AS SELECT Subcode,Test1

       FROM IAMARKS

       WHERE Usn='1KG13CS001';

4.  Calculate the FinalIA (average of best two test marks) and update the corresponding table for all students.

CREATE OR REPLACE PROCEDURE AVG_MARKS

IS

CURSOR C_IAMARKS IS

SELECT GREATEST(TEST1,TEST2), GREATEST(TEST1,TEST3), GREATEST(TEST2,TEST3)

FROM IAMARKS

WHERE FinalIA IS NULL

FOR UPDATE;

C_A number;

C_B number;

C_C number;

C_SM number;

C_AV number;

BEGIN

    OPEN C_IAMARKS;

LOOP

            FETCH C_IAMARKS INTO C_A,C_B,C_C;

            EXIT WHEN C_IAMARKS%NOTFOUND;

           

IF(C_A!=C_B) THEN

C_SM:=C_A+C_B;

ELSE

C_SM:=C_A+C_C;

END IF;

C_AV:=C_SM/2;

UPDATE IAMARKS SET FinalIA=C_AV WHERE CURRENT OF C_IAMARKS;

END LOOP;

     CLOSE C_IAMARKS;

END;

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 8^th semester A, B, and C section students.

SELECT S.Usn,Sname,IA.Subcode,

            (CASE

                        WHEN FinalIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'

                        WHEN FinalIA BETWEEN 12 AND 16 THEN 'AVERAGE'

                        ELSE 'WEAK'

            END) AS CATEGORY

FROM STUDENT S,IAMARKS IA,SUBJECT SUB,SEMSECSS

WHERE S.Usn=IA.Usn AND IA.Ssid=SS.Ssid AND IA.Subcode=SUB.Subcode AND SS.Sem=8;