query.java

CREATE TABLE STUDENT1(
USN VARCHAR2(20), 
SNAME VARCHAR2(10),
ADDRESS VARCHAR2(10),
PHONE NUMBER(10), 
GENDER VARCHAR2(10), 
PRIMARY KEY(USN));

INSERT INTO STUDENT1 VALUES('4SF20CS089','AJAY','MANGALORE',987654067,'MALE');
INSERT INTO STUDENT1 VALUES('4SF20IS109','VARSHINI','BANTWAL',987611234,'FEMALE');
INSERT INTO STUDENT1 VALUES('4SF20CS098','NAVISH','UDUPI',8765406711,'MALE');
INSERT INTO STUDENT1 VALUES('4SF20CD001','SAHANA','SURATKA',9876544567,'FEMALE');
INSERT INTO STUDENT1 VALUES('4SF20CS088','KAVYA','MANGALORE',876514067,'FEMALE');


SELECT * FROM STUDENT1;


USN                  SNAME      ADDRESS         PHONE GENDER
-------------------- ---------- ---------- ---------- ----------
4SF20CS089           AJAY       MANGALORE   987654067 MALE
4SF20IS109           VARSHINI   BANTWAL     987611234 FEMALE
4SF20CS098           NAVISH     UDUPI      8765406711 MALE
4SF20CD001           SAHANA     SURATKA    9876544567 FEMALE
4SF20CS088           KAVYA      MANGALORE   876514067 FEMALE
-------------------------------------------------------------------------------------------------------------------
CREATE TABLE SEMSEC (
SSID NUMBER(5), 
SEM NUMBER(2),
 SECTION VARCHAR2(1),
 PRIMARY KEY(SSID));

INSERT INTO SEMSEC VALUES(1,4,'A');
INSERT INTO SEMSEC VALUES(2,4,'C');
INSERT INTO SEMSEC VALUES(3,8,'A');
INSERT INTO SEMSEC VALUES(4,8,'B');
INSERT INTO SEMSEC VALUES(5,8,'C');
INSERT INTO SEMSEC VALUES(6,5,'A');

SELECT * FROM SEMSEC;

      SSID        SEM S
---------- ---------- -
         1          4 A
         2          4 C
         3          8 A
         4          8 B
         5          8 C
         6          5 A

----------------------------------------------------------------------------------------------------------------
CREATE TABLE CLASS (
SSID NUMBER(5),
USN VARCHAR2(20), 
PRIMARY KEY(USN), 
FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID), 
FOREIGN KEY(USN) REFERENCES STUDENT1(USN));

INSERT INTO CLASS VALUES(1,'4SF20CD001');
INSERT INTO CLASS VALUES(2,'4SF20CS088');
INSERT INTO CLASS VALUES(3,'4SF20CS089');
INSERT INTO CLASS VALUES(4,'4SF20IS109');
INSERT INTO CLASS VALUES(5,'4SF20CS098');


SELECT * FROM CLASS;

      SSID USN
---------- --------------------
         1 4SF20CD001
         2 4SF20CS088
         3 4SF20CS089
         4 4SF20IS109
         5 4SF20CS098

-------------------------------------------------------------------------------------------------------------
CREATE TABLE SUBJECT (
SUBCODE VARCHAR2(7) PRIMARY KEY, 
TITLE VARCHAR2(20), 
SEM NUMBER(4), 
CREDIT NUMBER(2));


 INSERT INTO SUBJECT VALUES('20CS31','DATA STRUCTURE',4,4);
INSERT INTO SUBJECT VALUES('20CS32','UNIX',4,3);
INSERT INTO SUBJECT VALUES('20CS33','DBMS',5,4);
INSERT INTO SUBJECT VALUES('20CS34','DCN',5,3);
INSERT INTO SUBJECT VALUES('20CS35','AIML',8,4);

SELECT * FROM SUBJECT;

SUBCODE TITLE                       SEM     CREDIT
------- -------------------- ---------- ----------
20CS31  DATA STRUCTURE                4          4
20CS32  UNIX                          4          3
20CS33  DBMS                          5          4
20CS34  DCN                           5          3
20CS35  AIML                          8          4
------------------------------------------------------------------------------------------------------------
CREATE TABLE IAMARKS(
USN VARCHAR2(20),
SUBCODE VARCHAR2(7),
SSID NUMBER(5),
TESTI NUMBER(3),
TEST2 NUMBER(3),
TEST3 NUMBER(3),
FINALIA NUMBER(3),
PRIMARY KEY(USN,SUBCODE,SSID),
FOREIGN KEY(USN) REFERENCES STUDENT1(USN),
FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE),
FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID));


INSERT INTO IAMARKS VALUES('4SF20CD001','20CS31',1,38,35,32,0);
INSERT INTO IAMARKS VALUES('4SF20CD001','20CS32',1,28,26,29,0);
INSERT INTO IAMARKS VALUES('4SF20CS088','20CS31',2,38,42,32,0);
INSERT INTO IAMARKS VALUES('4SF20CS089','20CS33',3,42,42,41,0);
INSERT INTO IAMARKS VALUES('4SF20CS098','20CS34',4,48,46,50,0);
INSERT INTO IAMARKS VALUES('4SF20IS109','20CS35',5,28,26,29,0);


SELECT * FROM IAMARKS;


USN                  SUBCODE       SSID      TESTI      TEST2      TEST3    FINALIA
-------------------- ------- ---------- ---------- ---------- ---------- ----------
4SF20CD001           20CS31           1         38         35         32          0
4SF20CD001           20CS32           1         28         26         29          0
4SF20CS088           20CS31           2         38         42         32          0
4SF20CS089           20CS33           3         42         42         41          0
4SF20CS098           20CS34           4         48         46         50          0
4SF20IS109           20CS35           5         28         26         29          0
--------------------------------------------------------------------------------------------------------

SELECT A.*,B.SEM,B.SECTION FROM STUDENT1 A, SEMSEC B, CLASS C 
WHERE  A.USN=C.USN AND B.SSID=C.SSID
AND B.SEM=4 AND B.SECTION ='C';

SELECT SEM,SECTION,GENDER,COUNT(*) FROM STUDENT1 S,SEMSEC SL,CLASS C 
WHERE S.USN =C.USN AND SL.SSID=C.SSID GROUP BY (GENDER,SEM,SECTION)ORDER BY (SEM);

CREATE VIEW TEST_MARKS AS SELECT SUBCODE,TESTI FROM IAMARKS WHERE USN='4SF21CD001';
 UPDATE IAMARKS SET finalia=GREATEST((testI+test2),(testI+test3),(test3+test2))/2;