4. COLLEGE DATABASE VTU DBMS LAB | Direct SQL Code

VTU DBMS LAB Program 4


STUDENT (USN,SNAME,ADDRESS,PHONE,GENDER)

SEMSEC (SSID,SEM,SEC)

CLASS (USN,SSID)

SUBJECT (SUBCODE,TITLE,SEM,CREDITS)

IAMARKS (USN,SUBCODE,SSID,TEST1,TEST2,TEST3,FINALIA)


VTU DBMS LAB Create Table SQL Commands for COLLEGE

Create the tables for all of the above attributes


VTU DBMS LAB -Create Table for STUDENT

CREATE TABLE STUDENT(USN CHAR(10),
     SNAME VARCHAR(20),
    ADDRESS VARCHAR(25),
    PHONE NUMBER(10),
    GENDER CHAR,
    CONSTRAINT A PRIMARY KEY(USN)
     );

Table created

DESC STUDENT;
 Name                         Null?       Type
 ------------------------  --------       -----------------
 USN                       NOT NULL       CHAR(10)
 SNAME                                    VARCHAR2(20)
 ADDRESS                                  VARCHAR2(25)
 PHONE                                    NUMBER(10)
 GENDER                                   CHAR(1)

Create Tabel for SEMSEC

CREATE TABLE SEMSEC(SSID CHAR(2),
    SEM NUMBER(1),
    SEC CHAR,
   CONSTRAINT B PRIMARY KEY(SSID),
   CONSTRAINT C CHECK(SEM BETWEEN 1 AND 8)
   );

Table created

DESC SEMSEC;
 Name       Null?          Type
 --------   --------       -------------
 SSID       NOT NULL         CHAR(2)
 SEM                         NUMBER(1)
 SEC                         CHAR(1)

Create Tabel for CLASS

CREATE TABLE CLASS(USN CHAR(10),
      SSID CHAR(2),
      CONSTRAINT D PRIMARY KEY(USN,SSID),
      CONSTRAINT E FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE CASCADE,
      CONSTRAINT F FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE
      );

Table created

DESC CLASS;
Name                                      Null?               Type
 ----------------------------------------- -------- ----------------------------
 USN                                       NOT NULL       CHAR(10)
 SSID                                      NOT NULL        CHAR(2)

Create Table for SUBJECT

CREATE TABLE SUBJECT(SUBCODE VARCHAR(7),
    TITLE VARCHAR(20),
   SEM NUMBER(1),
   CREDITS NUMBER(1),
    CONSTRAINT G PRIMARY KEY(SUBCODE)
   );

Table created

DESC SUBJECT;
Name                                      Null?                Type
 -------------------------------------- -------- ----------------------------
 SUBCODE                                  NOT NULL        VARCHAR2(7)
 TITLE                                                    VARCHAR2(20)
 SEM                                                      NUMBER(1)
CREDITS                                                   NUMBER(1)

Create Table for IAMARKS

CREATE TABLE IAMARKS(USN CHAR(10),
     SUBCODE VARCHAR(7),
     SSID CHAR(2),
     TEST1 NUMBER(2),
     TEST2 NUMBER(2),
     TEST3 NUMBER(2),
     FINALIA NUMBER(2),
    CONSTRAINT H PRIMARY KEY(USN,SUBCODE,SSID),
    CONSTRAINT I FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE CASCADE,
    CONSTRAINT J FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE,         CONSTRAINT K FOREIGN KEY(SUBCODE) REFERENCES SUBJECT(SUBCODE) ON DELETE        CASCADE
   );

Table created

DESC  IAMARKS;
Name                         Null?         Type
 -------------             --------        ---------------
 USN                       NOT NULL        CHAR(10)
 SUBCODE                   NOT NULL        VARCHAR2(7)
 SSID                      NOT NULL        CHAR(2)
 TEST1                                     NUMBER(2)
 TEST2                                     NUMBER(2)
 TEST3                                     NUMBER(2)
 FINALIA                                   NUMBER(2)

VTU DBMS LAB -SQL Insertion Commands for COLLEGE

Enter ‘ / ‘ to continue to insert the values


VTU DBMS LAB -Insert Values into STUDENT

Insert Command

INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER');

Enter the input values

Enter value for usn: 1MV17CS001
Enter value for sname: AASHISH
Enter value for address: BANGALORE
Enter value for phone: 1020304050
Enter value for gender: M
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV17CS001','AASHISH','BANGALORE',1020304050,'M')

row-1 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV17CS060
Enter value for sname: NAELA
Enter value for address: MYSORE
Enter value for phone: 1122334455
Enter value for gender: F
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV17CS060','NAELA','MYSORE',1122334455,'F')

row-2 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV17CS130
Enter value for sname: MILIND
Enter value for address: JAMMU
Enter value for phone: 5060708090
Enter value for gender: M
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV17CS130','MILIND','JAMMU',5060708090,'M')

row-3 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS001
Enter value for sname: ABHIJITH
Enter value for address: PUNE
Enter value for phone: 9988776655
Enter value for gender: M
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV16CS001','ABHIJITH','PUNE',9988776655,'M')

row-4 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS060
Enter value for sname: NIKITHA
Enter value for address: HYDERABAD
Enter value for phone: 9080706050
Enter value for gender: F
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV16CS060','NIKITHA','HYDERABAD',9080706050,'F')

row-5 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS130
Enter value for sname: SANJANA
Enter value for address: GUWAHATTI
Enter value for phone: 1234567890
Enter value for gender: F
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV16CS130','SANJANA','GUWAHATTI',1234567890,'F')

row-6 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS001
Enter value for sname: ANSHUMAN
Enter value for address: PANAJI
Enter value for phone: 1112223334
Enter value for gender: M
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV15CS001','ANSHUMAN','PANAJI',1112223334,'M')

row-7 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS060
Enter value for sname: AMRUTHA
Enter value for address: BANGALORE
Enter value for phone: 1002003004
Enter value for gender: F
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV15CS060','AMRUTHA','BANGALORE',1002003004,'F')

row-8 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS130
Enter value for sname: BHUVANESH
Enter value for address: JAIPUR
Enter value for phone: 9008007006
Enter value for gender: M
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV15CS130','BHUVANESH','JAIPUR',9008007006,'M')

row-9 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS001
Enter value for sname: DEVAYANI
Enter value for address: BANGALORE
Enter value for phone: 10020030
Enter value for gender: F
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV14CS001','DEVAYANI','BANGALORE',10020030,'F')

row-10 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS060
Enter value for sname: DAVID
Enter value for address: KOCHI
Enter value for phone: 90080070
Enter value for gender: M
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV14CS060','DAVID','KOCHI',90080070,'M')

row-11 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS130
Enter value for sname: AISHWARYA
Enter value for address: MUMBAI
Enter value for phone: 1000020000
Enter value for gender: F
old   1: INSERT INTO STUDENT VALUES('&USN','&SNAME','&ADDRESS',&PHONE,'&GENDER')
new   1: INSERT INTO STUDENT VALUES('1MV14CS130','AISHWARYA','MUMBAI',1000020000,'F')

row-12 created

Command to select all 12 rows of STUDENT

SELECT * FROM STUDENT;
USN            SNAME            ADDRESS         PHONE        GENDER
----------    ----------------  ------------    ----------   ---------- -
1MV17CS001     AASHISH          BANGALORE       1020304050   M
1MV17CS060      NAELA           MYSORE          1122334455   F
1MV17CS130     MILIND           JAMMU           5060708090   M
1MV16CS001     ABHIJITH         PUNE            9988776655   M
1MV16CS060      NIKITHA         HYDERABAD       9080706050   F
1MV16CS130      SANJANA         GUWAHATTI       1234567890   F
1MV15CS001     ANSHUMAN         PANAJI          1112223334   M 
1MV15CS060      AMRUTHA         BANGALORE       1002003004   F
1MV15CS130      BHUVANESH       JAIPUR          9008007006   M
1MV14CS001      DEVAYANI        BANGALORE       10020030     F
1MV14CS060      DAVID           KOCHI           90080070     M
1MV14CS130      AISHWARYA       MUMBAI          1000020000   F

12-rows selected


Insert Values into SEMSEC

Insert Command

INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC');

Enter the input values

Enter value for ssid: 2A
Enter value for sem: 2
Enter value for sec: A
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('2A',2,'A')

row-1 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 2B
Enter value for sem: 2
Enter value for sec: B
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('2B',2,'B')

row-2 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 2C
Enter value for sem: 2
Enter value for sec: C
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('2C',2,'C')

row-3 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 4A
Enter value for sem: 4
Enter value for sec: A
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('4A',4,'A')

row-4 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 4B
Enter value for sem: 4
Enter value for sec: B
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('4B',4,'B')

row-5 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 4C
Enter value for sem: 4
Enter value for sec: C
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('4C',4,'C')

row-6 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 6A
Enter value for sem: 6
Enter value for sec: A
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('6A',6,'A')

row-7 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 6B
Enter value for sem: 6
Enter value for sec: B
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('6B',6,'B')

row-8 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 6C
Enter value for sem: 6
Enter value for sec: C
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('6C',6,'C')

row-9 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 8A
Enter value for sem: 8
Enter value for sec: A
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('8A',8,'A')

row-10 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 8B
Enter value for sem: 8
Enter value for sec: B
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('8B',8,'B')

row-11 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for ssid: 8C
Enter value for sem: 8
Enter value for sec: C
old   1: INSERT INTO SEMSEC VALUES('&SSID',&SEM,'&SEC')
new   1: INSERT INTO SEMSEC VALUES('8C',8,'C')

row-12 created

Command to select all 12 rows of SEMSEC

SELECT * FROM SEMSEC;
SSID        SEM          SEC
--         ----         ----- -
2A           2            A
2B           2            B
2C           2            C
4A           4            A
4B           4            B
4C           4            C 
6A          6             A
6B          6             B
6C          6             C
8A          8             A
8B          8             B
8C          8             C

12-rows selected


Insert Values into CLASS

Insert Command

INSERT INTO CLASS VALUES('&USN','&SSID');

Enter the input values

Enter value for usn: 1MV17CS001
Enter value for ssid: 2A
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV17CS001','2A')

row-1 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV17CS060
Enter value for ssid: 2B
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV17CS060','2B')

row-2 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV17CS130
Enter value for ssid: 2C
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV17CS130','2C')

row-3 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS001
Enter value for ssid: 4A
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV16CS001','4A')

row-4 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS060
Enter value for ssid: 4B
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV16CS060','4B')

row-5 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS130
Enter value for ssid: 4C
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV16CS130','4C')

row-6 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS001
Enter value for ssid: 6A
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV15CS001','6A')

row-7 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS060
Enter value for ssid: 6B
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV15CS060','6B')

row-8 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS130
Enter value for ssid: 6C
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV15CS130','6C')

row-9 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS001
Enter value for ssid: 8A
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV14CS001','8A')

row-10 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS060
Enter value for ssid: 8B
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV14CS060','8B')

row-11 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS130
Enter value for ssid: 8C
old   1: INSERT INTO CLASS VALUES('&USN','&SSID')
new   1: INSERT INTO CLASS VALUES('1MV14CS130','8C')

row-12 created

Command to select all 12 rows of CLASS

SELECT * FROM CLASS;
USN         SSID
-------     -----
1MV14CS001   8A
1MV14CS060   8B
1MV14CS130   8C
1MV15CS001   6A
1MV15CS060   6B
1MV15CS130   6C
1MV16CS001   4A
1MV16CS060   4B
1MV16CS130   4C
1MV17CS001   2A
1MV17CS060   2B
1MV17CS130   2C

12-rows selected


Insert Values into SUBJECT

Insert Command

INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS);

Enter the input values

Enter value for subcode: 15CS21
Enter value for title: M2
Enter value for sem: 2
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('15CS21','M2',2,4)

row-1 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 15PCD23
Enter value for title: PCD
Enter value for sem: 2
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('15PCD23','PCD',2,4)

row-2 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 15CS42
Enter value for title: SE
Enter value for sem: 4
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('15CS42','SE',4,4)

row-3 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 15CS44
Enter value for title: MP
Enter value for sem: 4
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('15CS44','MP',4,4)

row-4 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 15CS64
Enter value for title: CG
Enter value for sem: 6
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('15CS64','CG',6,4)

row-5 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 15CS62
Enter value for title: USP
Enter value for sem: 6
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('15CS62','USP',6,4)

row-6 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 10CS81
Enter value for title: SA
Enter value for sem: 8
Enter value for credits: 4
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('10CS81','SA',8,4)

row-7 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for subcode: 10CS842
Enter value for title: ST
Enter value for sem: 8
Enter value for credits: 3
old   1:  INSERT INTO SUBJECT VALUES('&SUBCODE','&TITLE',&SEM,&CREDITS)
new   1:  INSERT INTO SUBJECT VALUES('10CS842','ST',8,3)

row-8 created

Command to select all 8 rows of SUBJECT

SELECT * FROM SUBJECT;
SUBCODE      TITLE             SEM    CREDITS
------- -------------------- ---------- ----------
15CS21           M2            2          4
15PCD23        PCD             2          4
15CS42           SE            4          4
15CS44           MP            4          4
15CS64           CG            6           4
15CS62           USP           6           4
10CS81           SA            8          4
10CS842         ST             8           3

8-rows selected


Insert Values into IAMARKS

Insert Command

INSERT INTO
IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TEST1,
&TEST2,&TEST3);

Enter the input values

Enter value for usn: 1MV17CS001
Enter value for subcode: 15CS21
Enter value for ssid: 2A
Enter value for test1: 15
Enter value for test2: 14
Enter value for test3: 13
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV17CS001','15CS21','2A',15)

row-1 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV17CS060
Enter value for subcode: 15PCD23
Enter value for ssid: 2B
Enter value for test1: 15
Enter value for test2: 15
Enter value for test3: 14
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV17CS060','15PCD23','2B',1)

row-2 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV17CS130
Enter value for subcode: 15CS21
Enter value for ssid: 2C
Enter value for test1: 11
Enter value for test2: 12
Enter value for test3: 13
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV17CS130','15CS21','2C',11)

row-3 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS001
Enter value for subcode: 15CS42
Enter value for ssid: 4A
Enter value for test1: 19
Enter value for test2: 19
Enter value for test3: 18
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV16CS001','15CS42','4A',19)

row-4 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS060
Enter value for subcode: 15CS44
Enter value for ssid: 4B
Enter value for test1: 5
Enter value for test2: 8
Enter value for test3: 5
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV16CS060','15CS44','4B',5)

row-5 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV16CS130
Enter value for subcode: 15CS42
Enter value for ssid: 4C
Enter value for test1: 20
Enter value for test2: 20
Enter value for test3: 20
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV16CS130','15CS42','4C',20)

row-6 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS001
Enter value for subcode: 15CS64
Enter value for ssid: 6A
Enter value for test1: 12
Enter value for test2: 12
Enter value for test3: 12
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV15CS001','15CS64','6A',12)

row-7 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS060
Enter value for subcode: 15CS62
Enter value for ssid: 6B
Enter value for test1: 18
Enter value for test2: 19
Enter value for test3: 20
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV15CS060','15CS62','6B',18)

row-8 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV15CS130
Enter value for subcode: 15CS64
Enter value for ssid: 6C
Enter value for test1: 8
Enter value for test2: 12
Enter value for test3: 11
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV15CS130','15CS64','6C',8)

row-9 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS001
Enter value for subcode: 10CS81
Enter value for ssid: 8A
Enter value for test1: 3
Enter value for test2: 11
Enter value for test3: 12
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV14CS001','10CS81','8A',3)

row-10 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS060
Enter value for subcode: 10CS842
Enter value for ssid: 8B
Enter value for test1: 0
Enter value for test2: 0
Enter value for test3: 7
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE)
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV14CS060','10CS842','8B',0)

row-11 created

Command to continue insertion of the values into next row

/

Enter the input values

Enter value for usn: 1MV14CS130
Enter value for subcode: 10CS81
Enter value for ssid: 8C
Enter value for test1: 0
Enter value for test2: 0
Enter value for test3: 20
old   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('&USN','&SUBCODE','&SSID',&TE
new   1: INSERT INTO IAMARKS(USN,SUBCODE,SSID,TEST1,TEST2,TEST3)VALUES('1MV14CS130','10CS81','8C',0,

row-12 created

Command to select all 12 rows of IAMARKS

SELECT * FROM IAMARKS;
USN          SUBCODE   SSID   TEST1   TEST2     TEST3    FINALIA
----------   -------   --     -----   -----     ----------
1MV17CS001   15CS21    2A     15      14        13
1MV17CS060   15PCD23   2B     15      15        14
1MV17CS130   15CS21    2C     11      12        13
1MV16CS001   15CS42    4A     19      19        18
1MV16CS060   15CS44    4B     5       8         5
1MV16CS130   15CS42    4C     20      20        20
1MV15CS001   15CS64    6A     12      12        12
1MV15CS060   15CS62    6B     18      19        20
1MV15CS130   15CS64    6C     8       12        11
1MV14CS001   10CS81    8A     3       11      	12
1MV14CS060   10CS842   8B     0       0         7
1MV14CS130   10CS81    8C     0       0         20

12-rows selected

VTU DBMS LAB -SQL Queries for COLLEGE

1] Make a list of all students details studying in 4th sem c-sec

SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER
 FROM STUDENT S,CLASS C,SEM_SEC SS
 WHERE S.USN=C.USN AND  
                SS.SSID=C.SSID  AND
                SS.SEM=4 AND
                SS.SEC='C';

Output

USN          SNAME     ADDRESS    PHONE       GENDER
----------  ---------  ---------  ----------- -------
1MV16CS130   SANJANA   GUWATHI    1234567890  F

2] Compute the total no.of male and female students in each semester and in each sec

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;

Output

SEM  SEC  GENDER  COUNT(S.GENDER) 
---  ---- -- ---- -----------
4    B     F       1
2    C     M       1
6    A     M       1
8    B     M       1
2    A     M       1
8    A     F       1
2    B     F       1
4    A     M       1
4    C     F       1
6    B     F       1
6    C     M       1
8    C     F       1

12 rows selected

3] Create view of test1 marks of student 1MV15CS060 in all subjects

CREATE VIEW TEST1_MARKS AS
 SELECT USN,SUBCODE,TEST1
 FROM IAMARKS
 WHERE USN='1MV15CS060';

View created

Output

SELECT * FROM TEST1_MARKS;
USN           SUBCODE      TEST1
----------    -------     ----------
1MV15CS060    15CS62          18

Update Commands

UPDATE IAMARKS SET TEST1=19,TEST2=18,TEST3=17 WHERE USN='1MV14CS001';

1 row updated

UPDATE IAMARKS SET TEST1=11,TEST2=0,TEST3=14 WHERE USN='1MV14CS060';

1 row updated

UPDATE IAMARKS SET TEST1=10,TEST2=0,TEST3=7 WHERE USN='1MV14CS130';

1 row updated

SELECT * FROM IAMARKS;

Ouput

USN         SUBCODE   SSID  TEST1      TEST2      TEST3  FINALIA
----------  -------  --     ----  ------------   -------
1MV17CS001   15CS21    2A   15         14         13
1MV17CS060   15PCD23   2B   15         15         14
1MV17CS130   15CS21    2C   11         12         13
1MV16CS001   15CS42    4A   19         19         18
1MV16CS060   15CS44    4B   5          8          5
1MV16CS130   15CS42    4C   20         20         20
1MV15CS001   15CS64    6A   12         12         12
1MV15CS060   15CS62    6B   18         19         20
1MV15CS130   15CS64    6C   8          12         11
1MV14CS001   10CS81    8A   19         18         17	
1MV14CS060   10CS842   8B   11         0          14
1MV14CS130   10CS81    8C   10         0          7

12 rows selected

4] Calculate the final IA amrks and update the corresponding table for all students

CREATE OR REPLACE PROCEDURE AVGMARKS
    IS
   CURSOR C_IAMARKS IS
   SELECT GREATEST(TEST1,TEST2) AS A,GREATEST(TEST1,TEST3) AS B,GREATEST(TEST2,TEST3) AS C
   FROM IAMARKS
   WHERE FINALIA IS NULL
    FOR UPDATE;
    C_A NUMBER;
    C_B NUMBER;
   C_C NUMBER;
   C_SUM NUMBER;
   C_AVG NUMBER;
   BEGIN
   OPEN C_IAMARKS;
   LOOP
   FETCH C_IAMARKS INTO C_A,C_B,C_C;
   EXIT WHEN C_IAMARKS%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(C_A||''||C_B||''||C_C);
   IF(C_A!=C_B) THEN
   C_SUM:=C_A+C_B;
   ELSE
   C_SUM:=C_A+C_C;
   END IF;
   C_AVG:=C_SUM/2;
   DBMS_OUTPUT.PUT_LINE('SUM='||C_SUM);
   DBMS_OUTPUT.PUT_LINE('AVERAGE='||C_AVG);
   UPDATE IAMARKS SET FINALIA=C_AVG
   WHERE CURRENT OF C_IAMARKS;
   END LOOP;
   CLOSE C_IAMARKS;
   END;
/

Procedure created

BEGIN AVGMARKS;
    END;
/

PL/SQL procedure successfully completed

Output

SELECT * FROM IAMARKS;
USN         SUBCODE   SSID  TEST1   TEST2   TEST3    FINALIA
----------  -------   --    -----   ------  -----   ----------
1MV17CS001   15CS21   2A    15       14      13     15
1MV17CS060   15PCD23  2B    15       15      14     15
1MV17CS130   15CS21   2C    11       12      13     13
1MV16CS001   15CS42   4A    19       19      18     19
1MV16CS060   15CS44   4B    5        8       5      7
1MV16CS130   15CS42   4C    20       20      20     20
1MV15CS001   15CS64   6A    12       12      12     12
1MV15CS060   15CS62   6B    18       19      20     20
1MV15CS130   15CS64   6C    8        12      11     12
1MV14CS001   10CS81   8A    19       18      17     19
1MV14CS060   10CS842  8B    11       0       14     13
1MV14CS130    10CS81  8C    10       0       7      9

12 rows selected

UPDATE IAMARKS SET FINALIA=NULL;

12 rows updated

SELECT * FROM IAMARKS;
USN         SUBCODE   SSID  TEST1      TEST2      TEST3 FINALIA
----------  -------   --    -----  ---------    -------
1MV17CS001   15CS21   2A    15         14         13
1MV17CS060   15PCD23  2B    15         15         14
1MV17CS130   15CS21   2C    11         12         13
1MV16CS001   15CS42   4A    19         19         18
1MV16CS060   15CS44   4B    5          8           5
1MV16CS130   15CS42   4C    20         20         20
1MV15CS001   15CS64   6A    12         12         12
1MV15CS060   15CS62   6B    18         19         20
1MV15CS130   15CS64   6C    8          12         11
1MV14CS001   10CS81   8A    19         18         17	
1MV14CS060   10CS842  8B    11          0         14
1MV14CS130   10CS81   8C    10          0          7

12 rows selected

5] Categorise students based on the following criteria

   If FINALIA=17 to 20 then CAT=’OUTSTANDING’

   If FINALIA=12 to 16 then CAT=’AVERAGE’

   If FINALIA=00 to 11 then CAT=’WEAK’

  Give these details only for 8th semester A, B and C section students

SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
    ( 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
   WHERE S.USN=IA.USN AND
   SS.SSID=IA.SSID AND
   SS.SEM=8;
USN           SNAME         ADDRESS        PHONE       GENDER  CAT
----------    -----------   --- ------     ---------   -----   -----------
1MV14CS001      DEVAYANI    BANGALORE      10020030    F       OUTSTANDING
1MV14CS060      DAVID       KOCHI          90080070    M       AVERAGE
1MV14CS130      AISHWARYA   MUMBAI         1000020000  F       WEAK

Leave a Reply

Your email address will not be published. Required fields are marked *

WhatsApp Icon Join For Job Alerts