LAB ASSIGNMENT –3-(i)

Subject: Database Management System Lab Subject code: BCS-552/BCS552 H

Faculty Name: Session: 2024-25
Section:
Submission Date………………………………………..
Writing SQL statements Using ORACLE /MYSQL:
a) Writing basic SQL SELECT statements. b) Restricting and sorting data.
c)Displaying data from multiple tables. d)Aggregating data using group function.
e)Manipulating data. f)Creating and managing tables.

I. Create following three tables college,Apply an Student and add the data as
given below.

College Apply Student

sID sName GPA sizeHS DoB sID cName major decision
cName state enroll 123 Stanford CS Y
ment 123 Amy 3.9 1000 26-JUN-96
123 Stanford EE N
Stanford CA 1500 234 Bob 3.6 1500 7-Apr-95
0 123 Berkeley CS Y
345 Craig 3.5 500 4-Feb-95
Berkeley CA 3600 123 Cornell EE Y
0 456 Doris 3.9 1000 24-Jul-97 234 Berkeley biology N
MIT MA 1000 567 Edward 2.9 2000 21-Dec-96 345 MIT bioengineering Y
0
345 Cornell bioengineering N
678 Fay 3.8 200 27-Aug-96
Cornell NY 2100
345 Cornell CS Y
0 789 Gary 3.4 800 8-Oct-96
345 Cornell EE N
Harvard MA 5004
987 Helen 3.7 800 27-Mar-97 678 Stanford history Y
0
876 Irene 3.9 400 7-Mar-96 987 Stanford CS Y
987 Berkeley CS Y
765 Jay 2.9 1500 8-Aug-98
876 Stanford CS N
654 Amy 3.9 1000 26-May-96
876 MIT biology Y
543 Craig 3.4 2000 27-Aug-98 876 MIT marine N
biology
765 Stanford history Y
765 Cornell history N
765 Cornell psychology Y
543 MIT CS N
II. Perform the following query based on above relation
1) List the name of student scoring more than 3.7 in GPA.
2) List the name of student whose High School size is atleast 1000 and
born after 1996. [Hint: check DoB greater than 31st December, 1996]
3) List the name of student who are scoring GPA in between 2.9 and 3.9
4) List all the details of colleges who situated in MA.
5) List the students who are scored more than 2.0 but less than 3.5.
6) List the students who have born after 1st Jul 96 in the order of the
Date of Birth.
7) List the sID, cName, decision of applications that are accepted.
8) List the sID, cName of applications which are filled at Stanford.
9) List the colleges that that has enrollment greater than 10001.
10) List the colleges not in California.
11) List names of all student who came from high school having size
greater than 17000 and scored GPA less than 3.8.
12) Display the description of the Student table.
13) Display the details of all students.
14) Display unique majors.
15) List the student names those are having three characters in their
Names.
16) List the student names those are starting with ‘H’ and with five
characters.
17) List the student names those are having third character and fifth char.
must be ‘e’.
18) List the student names ending with ‘y’.
19) List the Students in the order of their GPA.
20) List the details of the students in order of the ascending of GPA and
descending of DoB.
21) List the sIDs of student who apply in either ‘Stanford’, ‘Cornell’ or ‘MIT’
college.
22) Delete all applications filled at Stanford (Choose table wisely)
23) Delete the college Stanford from college table.
24) Modify the GPA of all students by giving 10% raise in their GPA.
25) Increment the GPA of the students by 1.5 whose GPA is less than 3.5
and belong to High School having size greater than 1500.
26) Delete the students who have scored less than 3.2 GPA.
27) Count the total number of Students.
28) Calculate the average GPA of all Student.
29) Determine the minimum and maximum GPA. Rename the titles as
‘max_GPA’ and ‘min_GPA’ respectively.
30) Count the number of students having GPA greater than or equal to 3.7.
31) Find Maximum, Average, Minimum, total GPA of all student.
32) Find how many different majors student had applied in.
33) Find average of all distinct GPA.
34) Display the total number of application accepted.
35) Find number of students having GPA>3.4 and coming from high school
having size>1000.
36) Find how many student applied to ‘marine biology’.
37) Find how many applications were rejected and accepted by the
colleges.
38) Find number of applications received by particular college.
39) Find the name of students that are duplicate.
40) Provide name of student who have not applied to any college.
41) Find how many student have their name started from A, B or C.
42) IDs and names of students who have applied to major in CS at some
college.
43) Find ID and name of student having same high school size as Jay.
44) Find ID and name of student having same high school size as Jay but
result should not include Jay.
45) Find the name of student with their GPA and Sid whose GPA not equal
to GPA of Irene?
46) Find college where any student having their name started from J have
applied?
47) Find all different major where Irene has applied?
48) Find IDs of student and major who applied in any of major Irene had
applied to?
49) Find IDs of student and major who applied in any of major Irene had
applied to? But this time exclude Irene sID from the list.
50) Give the number of colleges Jay applied to? (Remember count each
college once no matter if he applied to same college twice with different
major)
51) Find sID of student who applied to more or same number of college
where Jay has applied?
52) Find details of Students who applied to major CS but not applied to
major EE? (sID 987, 876, 543 should only be include in result)
53) All colleges such that some other college is in same state. (Cornell
should not be part of result as no other college in New York Hint: use
exists)
54) Find the college with highest enrollment.
55) Find name of student having lowest GPA.
56) Find the most popular major.
57) Find sID, sName, sizeHS of all students NOT from smallest HS
 LAB ASSIGNMENT –3-(ii)
Subject: Database Management System Lab Subject code: BCS-552/BCS552 H
Faculty Name: Session: 2024-25
Section:
Submission Date………………………………………..
Writing SQL statements Using ORACLE /MYSQL:
a) Writing basic SQL SELECT statements. b) Restricting and sorting data.
c)Displaying data from multiple tables. d)Aggregating data using group function.
e)Manipulating data. f)Creating and managing tables.
By using assignment 1 relation solve the following query
1. IDs and names of students who have applied to major in CS at some college.
2. Find ID and name of student having same high school size as Jay.
3. Find ID and name of student having same high school size as Jay but result should not
include Jay.
4. Find the name of student with their GPA and Sid whose GPA not equal to GPA of Irene?
5. Find college where any student having their name started from J have applied?
6. Find all different major where Irene has applied?
7. Find IDs of student and major who applied in any of major Irene had applied to?
8. Find IDs of student and major who applied in any of major Irene had applied to? But this
time exclude Irene sID from the list.
9. Give the number of colleges Jay applied to? (Remember count each college once no matter
if he applied to same college twice with different major)
10. Find sID of student who applied to more or same number of college where Jay has applied?
11. Find details of Students who applied to major CS but not applied to major EE? (sID 987,
876, 543 should only be include in result)
12. All colleges such that some other college is in same state. (Cornell should not be part of
result as no other college in New York Hint: use exists)
13. Find the college with highest enrollment.
14. Find name of student having lowest GPA.
15. Find the most popular major.
16. Find sID, sName, sizeHS of all students NOT from smallest HS
17. Find the name of student who applies to all the colleges where sID 987 has applied? (Hint:
see Query Find IDs of student applied to all colleges)

Run the following SQL script before next query

insert into Apply
select [Link], 'Berkeley', 'CSE', 'Y'
from student s1
where [Link] IN (select [Link] from student s
MINUS
select [Link] from apply a where [Link] = 'Berkeley');
18. Find college where all the student have applied.

For next three queries you are expected to solve using without joins and IN
operators
19. Find sid of student who have not applied to Stanford.
20. Find sid of Student that applied to both Stanford and Berkeley.
21. Give list of all names including all names of colleges and students.
22. Create a table ApplicationInfo having columns sID: int, sName: varchar2(10)
and number_of_applications: number(2) they filed?
Populate this table with appropriate data using insert command.
23. Create table ApplicationData and load with ID, name and college where they applied
with state of college (remember to include details of ALL students that have applied or not
applied) on runtime using single query.
24. Stanford decide not to take any student who have also applied to its rival Berkeley turn their
application decision to N.
25. Delete applications that are filed to city ‘New York’.
 LAB ASSIGNMENT –3-(iii)
Subject: Database Management System Lab Subject code: BCS-552/BCS552 H
Faculty Name: Session: 2024-25
Section:
Submission Date………………………………………..
Writing SQL statements Using ORACLE /MYSQL:
a) Writing basic SQL SELECT statements. b) Restricting and sorting data.
c)Displaying data from multiple tables. d)Aggregating data using group function.
e)Manipulating data. f)Creating and managing tables.

Create the following tables and specify constraints at the time of creation.

Department
Column Data Type Size Constraint
Name
Deptno number 3 primary key
Dname varchar2 20 Unique
Location varchar2 20 not null, department are located in Delhi, Pune,
Agra

Employee
Column Data Type Size Constraint
Name
Empno varchar2 5 primary key, should start with ‘E’
Ename varchar2 20 Unique
Designation varchar2 20 not null
Salary number 10 default 25000, must lie between 15000 and 50000

DOB date not null

Dno number 3 foreign key (references department)

Candidate
Column Name Data type Size Constraints
Candidate_ID Number 6 Primary key of the table
Candidate_Name Varchar2 20 Not Null
Candidate_Email Varchar2 30 Unique, Must have ‘@’ followed by ‘.’ in
between the email
Candidate_Dept Number 2 Default ‘HR’
Manager_ID Varchar2 30 It can take only those values which are present
in Candidate_ID column
1. Create the schemas as specified above without specifying any constraints.

College (cName: varchar2(10), state: varchar2(10), enrollment: int)

Student (sID: int, sName: varchar2(10), GPA: number(2,1),sizeHS:int)
Apply (sID: int, cName: varchar2(10), major: varchar2(20))

(i) Add cName as Primary key in College.

(ii) Add sID as Primarykey in Student.
(iii) Add sID, cName, major as Primarykey in Apply.
(iv) Make sID in Applyforeignkey referring table student and cName referring table
college.
(v) Increase data type size of majorfrom 20 to 25.
(vi) Add a new column decision in the Apply table keeping a constraint of not null for this
column with data type varchar2(3).
(vii) Change data type of decision in Apply to char(1).
(viii) Drop foreign key on column name cName from Apply table.
(ix) Remove column sizeHS from Student table.
(x) Drop primary key from College
(xi) Make cName, majorunique pairwise such as Stanford CS, Stanford EE.
(xii) Add cName as Foreign Key in Apply table referring table College usingon delete
cascade.
(xiii) Modify foreign key on sID in Apply table to foreign key on delete set null.
Rename column enrollment to enroll in College Table.