dba2a

Query to create tables:

 Student Table –

CREATE TABLE STUDENT(
USN VARCHAR(20) PRIMARY KEY,
SNAME CHAR(20),
ADDRESS VARCHAR(50),
PHONE DECIMAL(10,0),
GENDER CHAR(10));

 SEMSEC Table –

CREATE TABLE SEMSEC(
SSID VARCHAR(20) PRIMARY KEY,
SEM INT,
SEC CHAR(2));

 Class Table –

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);

 Course Table –

CREATE TABLE COURSE(
SUBCODE VARCHAR(20) PRIMARY KEY,
TITLE VARCHAR(20),
SEM INT,
CREDITS INT);

 Course Table –

INSERT INTO COURSE VALUES(1,'DBMS',5,4);
INSERT INTO COURSE VALUES(2,'PHYSICS',8,3);
INSERT INTO COURSE VALUES(3,'CHEMISTRY',8,4);
INSERT INTO COURSE VALUES(4,'CNCS',4,2);
INSERT INTO COURSE VALUES(5,'PP',5,3);

 IAMarks Table –

CREATE TABLE IAMARKS(
USN VARCHAR(20),
SUBCODE VARCHAR(20),
SSID VARCHAR(20),
PRIMARY KEY(USN,SUBCODE,SSID),
FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE
CASCADE,
FOREIGN KEY(SUBCODE) REFERENCES COURSE(SUBCODE) ON
DELETE CASCADE,
FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE
CASCADE,
TEST1 INT,
TEST2 INT,
TEST3 INT,
FINALIA DECIMAL(5,2));

Query to insert values into the table:
 Student Table –
INSERT INTO STUDENT VALUES ('1DS19IS049','ARJUN','BANGALORE',
9535616756,'MALE');
INSERT INTO STUDENT VALUES ('1DS19IS022','ADITYA','BANGALORE',
7896716657,'MALE');
INSERT INTO STUDENT VALUES ('1DS19IS017','ANEESH','MATTUR',
7898676647,'MALE');
INSERT INTO STUDENT VALUES ('1DS19IS058','VARSHA','PUNE',
8978916756,'FEMALE');
INSERT INTO STUDENT VALUES ('1DS19IS007','RUPA','MANGALORE',
9535616756,'FEMALE');

 SEMSEC Table –
INSERT INTO SEMSEC VALUES('4A01',4,'A');
INSERT INTO SEMSEC VALUES('4C02',4,'C');
INSERT INTO SEMSEC VALUES('8A01',8,'A');
INSERT INTO SEMSEC VALUES('8C03',8,'C');
INSERT INTO SEMSEC VALUES('5A01',5,'A');

 Class Table –
INSERT INTO CLASS VALUES('1DS19IS049','8A01');
INSERT INTO CLASS VALUES('1DS19IS022','8A01');
INSERT INTO CLASS VALUES('1DS19IS017','4C02');
INSERT INTO CLASS VALUES('1DS19IS058','4C02');
INSERT INTO CLASS VALUES('1DS19IS007','5A01');

 IAMarks Table –
INSERT INTO IAMARKS VALUES('1DS19IS049',2,'8A01',19,18,20,0);
INSERT INTO IAMARKS VALUES('1DS19IS058',4,'4C02',18,12,13,0);
INSERT INTO IAMARKS VALUES('1DS19IS022',2,'8A01',14,16,18,0);
INSERT INTO IAMARKS VALUES('1DS19IS007',1,'5A01',18,16,7,0);
INSERT INTO IAMARKS VALUES('1DS19IS017',4,'4C02',2,4,3,0);

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;