Scribd
Upload
33 views8 pages

Dbmslab (6th Program)

The document outlines the creation and manipulation of a student database schema named 'student1', detailing the structure, data types, and sample data entries for student records. It includes SQL commands for inserting data, updating total marks and GPA, querying for specific student information, and deleting records. Additionally, it demonstrates various SQL queries to extract insights such as maximum GPA by branch and filtering students based on specific criteria.

Uploaded by

malnadmanja2002
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
33 views8 pages

Dbmslab (6th Program)

The document outlines the creation and manipulation of a student database schema named 'student1', detailing the structure, data types, and sample data entries for student records. It includes SQL commands for inserting data, updating total marks and GPA, querying for specific student information, and deleting records. Additionally, it demonstrates various SQL queries to extract insights such as maximum GPA by branch and filtering students based on specific criteria.

Uploaded by

malnadmanja2002
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

6.

Consider the following schema:


STUDENT(USN, name, date_of_birth, branch, mark1, mark2, mark3, total, GPA)

create table student1(usn varchar(5) primary key,name varchar(10),dob date,branch


varchar(5), mark1 int,mark2 int, mark3 int, total int,gpa decimal(10,2));

desc student1;
Name Null? Type
----------------------------------------------------- --------
------------------------------------
USN NOT NULL VARCHAR2(5)
NAME VARCHAR2(10)
DOB DATE
BRANCH VARCHAR2(5)
MARK1 NUMBER(3)
MARK2 NUMBER(3)
MARK3 NUMBER(3)
TOTAL NUMBER(3)
GPA NUMBER(10,2)

Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)


values('USN01','MANJA',DATE'2002-05-02','MCA',99,98,100);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN02','RAMESH',DATE'2002-05-31','BCA',99,78,98);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN03','ANANYA',DATE'2002-10-23','BSC',67,56,87);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN04','MAYURA',DATE'2002-07-13','MBA',91,90,100);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN05','SACHIN',DATE'2002-11-20','BE',98,79,77);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN06','SUDHEER',DATE'2002-04-04','MCA',67,87,78);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN07','PRAVEEN',DATE'2002-07-07','MBA',45,78,66);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN08','SRAVESH',DATE'2002-12-12','BE',57,87,99);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN09','DARSHAN',DATE'2002-12-08','BCA',39,78,56);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN10','KRISHNA',DATE'2002-03-02','BSC',78,98,99);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN11','OMKAAR',DATE'2003-01-31','MCA',78,93,99);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('1001','KIRAN',DATE'2002-11-23','MBA',77,90,100);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN12','AVISHKAR',DATE'2003-08-11','BE',88,99,94);

select * from student1;

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN01 MANJA 02/MAY/02 MCA 99 98 100
USN02 RAMESH 31/MAY/02 BCA 99 78 98
USN03 ANANYA 23/OCT/02 BSC 67 56 87
USN04 MAYURA 13/JUL/02 MBA 91 90 100
USN05 SACHIN 20/NOV/02 BE 98 79 77
USN06 SUDHEER 04/APR/02 MCA 67 87 78
USN07 PRAVEEN 07/JUL/02 MBA 45 78 66
USN08 SRAVESH 12/DEC/02 BE 57 87 99
USN09 DARSHAN 08/DEC/02 BCA 39 78 56
USN10 KRISHNA 02/MAR/02 BSC 78 98 99
USN11 OMKAAR 31/JAN/03 MCA 78 93 99
1001 KIRAN 23/NOV/02 MBA 77 90 100
USN12 AVISHKAR 11/AUG/03 BE 88 99 94

13 rows selected.

Execute the following queries:

i. Update the column total by adding the columns mark1, mark2, mark3

update student1 set total=mark1+mark2+mark3;

13 rows updated.

SQL> select * from student1;

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN01 MANJA 02/MAY/02 MCA 99 98 100 297
USN02 RAMESH 31/MAY/02 BCA 99 78 98 275
USN03 ANANYA 23/OCT/02 BSC 67 56 87 210
USN04 MAYURA 13/JUL/02 MBA 91 90 100 281
USN05 SACHIN 20/NOV/02 BE 98 79 77 254
USN06 SUDHEER 04/APR/02 MCA 67 87 78 232
USN07 PRAVEEN 07/JUL/02 MBA 45 78 66 189
USN08 SRAVESH 12/DEC/02 BE 57 87 99 243
USN09 DARSHAN 08/DEC/02 BCA 39 78 56 173
USN10 KRISHNA 02/MAR/02 BSC 78 98 99 275
USN11 OMKAAR 31/JAN/03 MCA 78 93 99 270
1001 KIRAN 23/NOV/02 MBA 77 90 100 267
USN12 AVISHKAR 11/AUG/03 BE 88 99 94 281

13 rows selected.

ii. Find the GPA score of all the students.

update student1 set gpa=(total/300)*10;

13 rows updated.

SQL> select * from student1;

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN01 MANJA 02/MAY/02 MCA 99 98 100 297
9.9
USN02 RAMESH 31/MAY/02 BCA 99 78 98 275
9.17
USN03 ANANYA 23/OCT/02 BSC 67 56 87 210
7
USN04 MAYURA 13/JUL/02 MBA 91 90 100 281
9.37
USN05 SACHIN 20/NOV/02 BE 98 79 77 254
8.47
USN06 SUDHEER 04/APR/02 MCA 67 87 78 232
7.73
USN07 PRAVEEN 07/JUL/02 MBA 45 78 66 189
6.3
USN08 SRAVESH 12/DEC/02 BE 57 87 99 243
8.1
USN09 DARSHAN 08/DEC/02 BCA 39 78 56 173
5.77
USN10 KRISHNA 02/MAR/02 BSC 78 98 99 275
9.17
USN11 OMKAAR 31/JAN/03 MCA 78 93 99 270
9
1001 KIRAN 23/NOV/02 MBA 77 90 100 267
8.9
USN12 AVISHKAR 11/AUG/03 BE 88 99 94 281
9.37

13 rows selected.

iii. Find the students who born on a particular year of birth from the
date_of_birth
column.

select * from student1 where extract(year from dob)=2003;

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN12 AVISHKAR 11/AUG/03 BE 88 99 94 281
9.37

iv. List the students who are studying in a particular branch of study.

select * from student1 where branch='MCA';

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN06 SUDHEER 04/APR/02 MCA 67 87 78 232
7.73
USN11 OMKAAR 31/JAN/03 MCA 78 93 99 270
9
USN01 MANJA 02/MAY/02 MCA 99 98 100 297
9.9

v. Find the maximum GPA score of the student branch-wise.

select branch,max(gpa) as max_gpa from student1 group by branch order by branch;

BRANC MAX_GPA
----- ----------
BCA 9.17
BE 9.37
BSC 9.17
MBA 9.37
MCA 9.9

vi. Find the students whose name starts with the alphabet “S”.

select * from student1 where name like 'S%_';

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN05 SACHIN 20/NOV/02 BE 98 79 77 254
8.47
USN06 SUDHEER 04/APR/02 MCA 67 87 78 232
7.73
USN08 SRAVESH 12/DEC/02 BE 57 87 99 243
8.1

vii. Find the students whose name ends with the alphabets “AR”.

select * from student1 where name like '_%AR';

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN11 OMKAAR 31/JAN/03 MCA 78 93 99 270
9
USN12 AVISHKAR 11/AUG/03 BE 88 99 94 281
9.37

viii. Delete the student details whose USN is given as 1001

delete from student1 where usn='1001';

1 row deleted.

SQL> select * from student1;


USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL
GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN02 RAMESH 31/MAY/02 BCA 99 78 98 275
9.17
USN03 ANANYA 23/OCT/02 BSC 67 56 87 210
7
USN04 MAYURA 13/JUL/02 MBA 91 90 100 281
9.37
USN05 SACHIN 20/NOV/02 BE 98 79 77 254
8.47
USN06 SUDHEER 04/APR/02 MCA 67 87 78 232
7.73
USN07 PRAVEEN 07/JUL/02 MBA 45 78 66 189
6.3
USN08 SRAVESH 12/DEC/02 BE 57 87 99 243
8.1
USN09 DARSHAN 08/DEC/02 BCA 39 78 56 173
5.77
USN10 KRISHNA 02/MAR/02 BSC 78 98 99 275
9.17
USN11 OMKAAR 31/JAN/03 MCA 78 93 99 270
9
USN12 AVISHKAR 11/AUG/03 BE 88 99 94 281
9.37

USN NAME DOB BRANC MARK1 MARK2 MARK3 TOTAL


GPA
----- ---------- --------- ----- ---------- ---------- ---------- ----------
----------
USN01 MANJA 02/MAY/02 MCA 99 98 100 297
9.9

12 rows selected.

/////set linesize 1000;

group:example
studnet = {
usn, name, dob, branch, mark1, mark2, mark3, total, gpa
'usn1', 'manja', '02-05-2002', 'MCA', 99, 98, 100,297,9.9
'usn2', 'ramesh', '31-05-2003', 'BCA', 99, 78, 98,275,9.17
'usn3', 'ananya', '23-10-2000', 'BSC', 67, 56, 87,210,7
'usn4', 'mayura', '13-07-2002', 'MBA', 91, 90, 100,281,9.37
'usn5', 'sachin', '20-11-2002', 'Computer Science', 98, 79, 77,254,8.47
'usn6', 'sudheer', '04-04-2000', 'MCA', 67, 87, 78,232,7.73
'usn7', 'praveen', '07-07-2002', 'MBA', 45, 78, 66,189,6.3
'usn8', 'sarvesh', '12-12-2002', 'BE', 57, 87, 99,243,8.1
'usn9', 'darshan', '08-12-2000', 'BCA', 39, 78, 56,173,5.77
'usn10', 'krishna', '02-03-2003', 'Computer Science', 78, 98, 99,275,9.17
'usn11', 'omkaar', '31-01-2002', 'MCA', 78, 93, 99,270,9
'1001', 'kiran', '23-11-2003', 'MBA', 77, 90, 100,267,8.9
'usn12', 'avishkar', '11-08-2002', 'BE', 88, 99, 94,281,9.37
}

1. π usn,name,gpa(studnet)

2. π name,dob(σ dob='02-05-2002' (studnet))

3. π usn,name,branch(σ branch = 'Computer Science' (studnet))

4. γ branch;MAX(gpa) → Max_gpa(studnet)

5. π name(σ name like 's%' (studnet))

6. π name(σ name like '%ar' (studnet))

Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)


values('USN01','MANJA',DATE'2002-05-02','MCA',99,98,100);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN02','RAMESH',DATE'2002-05-31','BCA',99,78,98);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN03','MAYURA',DATE'2002-07-13','MBA',91,90,100);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN04','OMKAAR',DATE'2003-01-31','MCA',78,93,99);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('1001','KIRAN',DATE'2002-11-23','MBA',77,90,100);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN05','AVISHKAR',DATE'2003-08-11','BE',88,99,94);
Insert into student1 (usn,name,dob,branch,mark1,mark2,mark3)
values('USN06','SACHIN',DATE'2002-11-20','BE',98,79,77);

1. Update the column total by adding the columns mark1, mark2, mark3.

UPDATE STUDENT1 SET TOTAL=MARK1+MARK2+MARK3;

2. Find the students who born on a particular year of birth from the date_of_birth
column.

SELECT * FROM STUDENT1 WHERE EXTRACT(YEAR FROM DOB)=2003;

3. Find the maximum GPA score of the student branch-wise.


SELECT BRANCH,MAX(GPA) FROM STUDENT1 GROUP BY BRANCH;

4. Find the students whose name starts with the alphabet “S”.

SELECT * FROM STUDENT1 WHERE NAME LIKE 'S%_';

5. Find the students whose name ends with the alphabets “AR”.

SELECT * FROM STUDENT1 WHERE NAME LIKE '_%AR';

6. Delete the student details whose USN is given as 1001

delete from student1 where usn='1001';

7. List the student details who scored exactly the same marks in mark1 and mark2.

select * from student1 where mark1=mark2;

8. Find the students whose names start with the letter “A” and are studying in the
Computer Science branch.

SELECT * FROM STUDENT1 WHERE BRANCH='BSC' AND NAME LIKE 'A%_';

9. List the students who are studying in the Mechanical branch and have a GPA
greater than 8.

SELECT * FROM STUDENT1 WHERE BRANCH='BSC' AND GPA>7;

10. Find the students who were born before the year 2000.

SELECT * FROM STUDENT1 WHERE EXTRACT(YEAR FROM DOB)<2003;

11. List the students who have failed in any subject (assume passing marks as 35
in
each subject).

SELECT * FROM STUDENT1 WHERE mark1<35 or mark2<35 or mark3<35;

12. Find the students whose names have exactly 5 letters.

select * from student1 where length(name)=5;

13. Display the student details whose total marks are below the average total of
all
students.

select * from student1 where total<(select avg(total) from student1);


14. List the students who scored the lowest in mark2 and are studying in the Civil
branch.

select * from student1 where mark2=(select min(mark2) from student1 where


branch='MCA');

15. Find the students whose names contain exactly two vowels.

SELECT * from student1 where


length(name)-length(replace(lower(name),'a',''))+
length(name)-length(replace(lower(name),'e',''))+
length(name)-length(replace(lower(name),'i',''))+
length(name)-length(replace(lower(name),'o',''))+
length(name)-length(replace(lower(name),'u',''))=2;

select * from student1 where name like '%[aeiou]%[aeiou]%' and name not like '%
[aeiou][aeiou]%';

16. Calculate and update the GPA for students who have mark1 and mark2 greater
than 60.

update student1 set gpa=(total/300)*9 where mark1>60 and mark2>60;

17. Find the student who scored the maximum total in each branch and display their
details.

select * from student1 s where total=(select max(total) from student1 where


branch=s.branch);

18. Find the students whose names contain the sub string “AN” and are in the Civil
branch.

select * from student1 where name like '%AN%' and branch='BCA';

select usn,name,branch from student1 where branch in( select branch,max(gpa)


maximum_gpa from student1 group by branch order by maximum_gpa desc);

You might also like