Scribd
Upload
20 views5 pages

02 College DB

Uploaded by

M Krithika
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views5 pages

02 College DB

Uploaded by

M Krithika
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Consider the schema for College Database:

STUDENT(USN, SName, Address, Phone, Gender)


SEMSEC(SSID, Sem, Sec)
CLASS(USN, SSID)
COURSE(Subcode, Title, Sem, Credits)
IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)

CREATE TABLE STUDENT (


USN VARCHAR (20),
SNAME CHAR (15),
ADDRESS VARCHAR (20),
PHONE NUMBER (10),
GENDER CHAR (6),
PRIMARY KEY (USN));

CREATE TABLE SEMSEC (


SSID VARCHAR (20),
SEM NUMBER (3),
SEC CHAR (3),
PRIMARY KEY (SSID));

CREATE TABLE CLASS (


USN VARCHAR (20),
SSID VARCHAR (20),
PRIMARY KEY (USN, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN)
ON DELETE CASCADE,
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID)
ON DELETE CASCADE);

CREATE TABLE COURSE (


SUBCODE VARCHAR (15),
TITLE CHAR (20),
SEM INT,
CREDITS INT,
PRIMARY KEY (SUBCODE));

CREATE TABLE IAMARKS (


USN VARCHAR (20),
SUBCODE VARCHAR (15),
SSID VARCHAR (20),
TEST1 INT,
TEST2 INT,
TEST3 INT,
FINALIA INT,
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN)
ON DELETE CASCADE,
FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE)
ON DELETE CASCADE,
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID)
ON DELETE CASCADE);
SQL> Select * from Course
SQL> Select * from IAMARKS;

Write SQL queries to


1. List all the student details studying in fourth semester “C” section.

SELECT A.*, B.SEM, B.SEC


FROM STUDENTS A, SEMSEC B, CLASS C
WHERE A.USN=C.USN AND B.SSID=C.SSID AND B.SEM=4 AND B.SEC='C';

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

SELECT B.SEM, B.SEC, COUNT (CASE WHEN A.GENDER='MALE' THEN B.SSID


END) AS MALECOUNT, COUNT (CASE WHEN A.GENDER='FEMALE' THEN B.SSID
END) AS FEMALECOUNT
FROM STUDENT A, SEMSEC B, CLASS C
WHERE A.USN=C.USN AND B.SSID=C.SSID
GROUP BY B.SEM, B.SEC;

3. Create a view of Test1 marks of student USN “4SF20CD001” in all Courses.


CREATE VIEW TEST1_MARKS AS
SELECT SUBCODE, TEST1 FROM IAMARKS
WHERE USN='4SF20CD001';
View created.
SQL> SELECT * FROM TEST1_MARKS;
4. Calculate the FinalIA (average of three test marks) and update the corresponding table for all
students.

UPDATE IAMARKS SET FINALIA=(TEST1+TEST2+TEST3)/3;

5. Categorize students based on the following criterion:


If FinalIA = 45 to 50 then CAT = “Outstanding”
If FinalIA= 40 to 45 then CAT= “Good”
If FinalIA = 30 to 40 then CAT = “Average”
If FinalIA< 30 then CAT = “Weak”
Give these details only for 8th semester A, B, and C section students.
SELECT S.*, IA.FINALIA,
(CASE
WHEN IA.FINALIA BETWEEN 45 AND 50 THEN 'OUTSTANDING'
WHEN IA.FINALIA BETWEEN 40 AND 44 THEN 'GOOD'
WHEN IA.FINALIA BETWEEN 30 AND 39 THEN 'AVERAGE'
ELSE 'WEAK' END) AS CAT FROM STUDENT S, SEMSEC SS, IAMARKS IA
WHERE S.USN=IA.USN AND SS.SSID=IA.SSID AND SS.SEM=8;

You might also like