Program - 1
Consider the following relations:
Student (snum: integer, sname: string, major: string,
level: string, age: integer)
Faculty (fid: integer, fname: string, deptid: integer)
Class (cname: string, meetsat: string, room: string, fid:
integer)
Enrolled (snum: integer, cname: string)
SQL> create table student
(snum number(4) primary key,
sname varchar2(10), major varchar2(5),
slevel varchar2(10), age number(3));
SQL> create table faculty
(fid number(4) primary key,
fname varchar2(10), deptid number(5));
SQL> create table class
(cname varchar2(4) primary key,
meets_at varchar2(10), room varchar2(10),
fid number(4) references faculty);
SQL> create table enrolled
(snum number(4) references student,
cname varchar2(4) references class,
primary key(snum, cname) );
SQL> insert into student values
(&snum, ’&sname’, ‘&major’, ‘&slevel’, &age);
Snum Sname Major Level Age
1 Adarsh CSE Jr 18
2 Akhil ISE Sr 22
3 Bhavya CSE Sr 21
4 Shwetha CSE Jr 19
5 Nagaraj ISE Sr 22
6 Hema CSE Jr 19
SQL> insert into faculty values
(&fid, ‘&fname’, &deptid);
Fid Fname Deptid
501 Harshith 201
502 Shobha 202
503 Chaitra 203
504 Pratap 204
505 Sushma 205
SQL> insert into class values
( ‘&cname’, ‘&meets_at’, ‘&room’, &fid);
Cname Meets_at Room Fid
C01 10am R128 501
C02 11am R124 501
C03 11am R125 502
C04 12pm R128 502
C05 2pm R128 503
C06 2pm R124 502
C07 3pm R125 501
C08 12pm R124 504
SQL> insert into enrolled values (&snum, ‘&cname’);
Snum Cname
1 C01
2 C03
3 C06
4 C07
2 C02
3 C01
4 C02
2 C01
5 C01
6 C01
1. Find Harshith’s fid
2. Find cname of harshith
3. Find the names of students whose name has the last letter ‘a’.
4. Find the names of students whose age is greater than 20 and
less than 22.
5. Display the number of students enrolled in each class.
6. Find the names of the faculty who are teaching senior students.
7. Find the names of the students who are enrolled in the class
‘C01’ and whose age is greater than 20.
8. Find the deptids of the faculty who are teaching 2 or more
classes.
9. Find the names of all classes (i.e cname) that meet in room
R128.
10. Find the names of all classes(i.e cname) that have five or more
students enrolled.
i. Find the names of all Juniors (level = JR) who are
enrolled in a class taught by Prof. Harshith.
Step 1: Find Harshith’s fid
(from faculty table)
Step 2: Find cname for the above fid
(from class table)
Step 3: Find snum for the above cname
(from the enrolled table)
Step 4: Print sname for the above snum
where slevel is ‘Jr’ (from student table)
SQL> select sname from student where
slevel =‘Jr’ and snum in
(select snum from enrolled where cname in
(select cname from class where fid in
(select fid from faculty where
fname = ‘Harshith’ ) ) );
(or)
SQL> select distinct sname from
student s, class c, enrolled e, faculty f
where [Link] = ‘Harshith and
[Link] = [Link] and [Link] = [Link] and
[Link] = [Link] and [Link] = ‘Jr’;
ii. Find the names of all classes that either meet in
room R128 or have five or more Students enrolled.
1. Find the names of all classes (i.e cname) that
meet in room R128.
(from class table).
2. Find the names of all classes(i.e cname) that
have five or more students enrolled.
(from enrolled table)
SQL> select cname from class where
room = ‘R128’ or cname in
(select cname from enrolled
group by cname
having count(*)>=5 );
Ex: Find the deptids of the faculty who are
teaching 2 or more classes.
Select deptid from faculty where fid in
(select fid from class
group by fid having count(*)>=2);
Ex: Find the names of the students who are
enrolled in the class ‘C01’ and whose age is
greater than 20.
Select sname from student
where age>20 and
snum in (select snum from enrolled
where cname=‘C01’);
Ex: Find the names of the faculty who are
teaching senior students.