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
