tw4

*-------------------TW4-----------------------------* CREATE TABLE STUDENT( USN VARCHAR (10) PRIMARY KEY, SNAME VARCHAR(25), ADDRESS VARCHAR(25), PHONE BIGINT(10), GENDER CHAR(1)); CREATE TABLE SEMSEC( SSID VARCHAR(5) PRIMARY KEY, SEM INT (5), SEC CHAR (1)); CREATE TABLE CLASS( USN VARCHAR (10), SSID VARCHAR (5), PRIMARY KEY(USN, SSID), FOREIGN KEY(USN) REFERENCES STUDENT(USN), FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID)); CREATE TABLE SUBJECT( SUBCODE VARCHAR(10) PRIMARY KEY, TITLE VARCHAR(5), SEM INT, CREDITS INT); CREATE TABLE IAMARKS ( USN VARCHAR(10), SUBCODE VARCHAR(6), SSID VARCHAR(5), TEST1 INT(2), TEST2 INT(2), TEST3 INT(2), FINALIA INT(3), PRIMARY KEY(USN, SUBCODE, SSID), FOREIGN KEY(USN) REFERENCES STUDENT(USN), FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE), FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID)); INSERT INTO STUDENT VALUES('2KL20CS400','ADINATH','BELAGAVI',9821456230,'M'); INSERT INTO STUDENT VALUES('2KL20CS401','AYAZ','BELAGAVI',6320487791,'M'); INSERT INTO STUDENT VALUES('2KL20CS402','ASHITOSH','HUBLI',8196301236,'M'); INSERT INTO STUDENT VALUES('2KL20CS403','DHANU','BELAGAVI',8147988110,'F'); INSERT INTO STUDENT VALUES('2KL20CS404','DARSHAN','BELAGAVI',7800526997,'M'); INSERT INTO STUDENT VALUES('2KL20CS405','DURGA','BELAGAVI',9912365478,'F'); INSERT INTO STUDENT VALUES('2KL20CS407','SANTOSHI','BELAGAVI',6312850019,'F'); INSERT INTO STUDENT VALUES('2KL20CS408','SHIFA','BELAGAVI',7019500321,'F'); INSERT INTO STUDENT VALUES('2KL20CS409','SWARAJ','BELAGAVI',7022998877,'M'); INSERT INTO SEMSEC VALUES('CS4A',4,'A'); INSERT INTO SEMSEC VALUES('CS4B',4,'B'); INSERT INTO SEMSEC VALUES('CS5A',5,'A'); INSERT INTO SEMSEC VALUES('CS5B',5,'B'); INSERT INTO SEMSEC VALUES('CS6A',6,'A'); INSERT INTO SEMSEC VALUES('CS6B',6,'B'); INSERT INTO SEMSEC VALUES('CS7A',7,'A'); INSERT INTO SEMSEC VALUES('CS7B',7,'B'); INSERT INTO CLASS VALUES('2KL20CS400','CS5A'); INSERT INTO CLASS VALUES('2KL20CS401','CS5A'); INSERT INTO CLASS VALUES('2KL20CS402','CS4A'); INSERT INTO CLASS VALUES('2KL20CS403','CS6A'); INSERT INTO CLASS VALUES('2KL20CS404','CS5B'); INSERT INTO CLASS VALUES('2KL20CS405','CS5B'); INSERT INTO CLASS VALUES('2KL20CS407','CS6B'); INSERT INTO CLASS VALUES('2KL20CS408','CS4B'); INSERT INTO CLASS VALUES('2KL20CS409','CS7A'); INSERT INTO SUBJECT VALUES ('18CS50','ME',5,4); INSERT INTO SUBJECT VALUES ('18CS51','CNS',5,3); INSERT INTO SUBJECT VALUES ('18CS52','DBMS',5,4); INSERT INTO SUBJECT VALUES ('18CS53','ATC',5,3); INSERT INTO SUBJECT VALUES ('18CS54','ADP',5,5); INSERT INTO SUBJECT VALUES ('18CS55','UP',5,5); INSERT INTO SUBJECT VALUES ('18CS41','OS',4,4); INSERT INTO SUBJECT VALUES ('18CS42','DAA',4,3); INSERT INTO SUBJECT VALUES ('18CS43','OOC',4,3); INSERT INTO IAMARKS VALUES ('2KL20CS400','18CS50','CS5A',25,40,45,0); INSERT INTO IAMARKS VALUES ('2KL20CS400','18CS51','CS5A',40,44,49,0); INSERT INTO IAMARKS VALUES ('2KL20CS400','18CS52','CS5A',39,50,37,0); INSERT INTO IAMARKS VALUES ('2KL20CS400','18CS53','CS5A',35,47,43,0); INSERT INTO IAMARKS VALUES ('2KL20CS400','18CS54','CS5A',44,44,42,0); INSERT INTO IAMARKS VALUES ('2KL20CS400','18CS55','CS5A',38,40,33,0); INSERT INTO IAMARKS VALUES ('2KL20CS404','18CS50','CS5B',40,38,41,0); INSERT INTO IAMARKS VALUES ('2KL20CS404','18CS51','CS5B',38,44,39,0); INSERT INTO IAMARKS VALUES ('2KL20CS404','18CS52','CS5B',43,50,35,0); INSERT INTO IAMARKS VALUES ('2KL20CS404','18CS53','CS5B',37,33,47,0); INSERT INTO IAMARKS VALUES ('2KL20CS404','18CS54','CS5B',40,39,50,0); INSERT INTO IAMARKS VALUES ('2KL20CS404','18CS55','CS5B',27,30,48,0); 1) SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,SS.SEM,SS.SEC FROM STUDENT S,SEMSEC SS,CLASS C WHERE S.USN=C.USN AND SS.SSID=C.SSID AND SS.SEM=4 AND SS.SEC='B'; 2) select ss.sem,ss.sec,s.gender,count(s.gender) from student s,semsec ss,class c where s.usn=c.usn and ss.ssid=c.ssid group by ss.sem,ss.sec,s.gender order by ss.sem;