In: Computer Science
Consider the following relational schema:
student(studID, studname, major, advisor)
department(deptname, major)
club(studID,clubname)
professor(profID, profname, building, deptname)
NOTE: KEY ATTRIBUTES ARE IN BOLD
where advisor takes values in the domain of professor names (profname) and
the underline attributes form the primary key of the corresponding relations.
Questions:
2.a. Find all students and their advisors.
2.b. Find all the students who are in any one of the clubs that Jamie Smith is in.
2.c. Find all of the advisors, their buildings and departments that advise students that
are in the same clubs that Jamie Smith participates into.
2.d. Find all professors names and their departments that have offices in the
buildings identified in query 2c.
2.e. Find all student names and their major(s) that participate in Computer Science
Association Club.
2.a. Find all students and their advisors.
select studname,advisor from student;
2.b. Find all the students who are in any one of the clubs that Jamie Smith is in.
select s.studname
from student s, club c
where s.studID = c.studID
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith');
2.c. Find all of the advisors, their buildings and
departments that advise
students that are in the same clubs that Jamie Smith participates
into.
select s.advisor, p.building, d.deptname
from student s, club c, department d , professor p
where s.studID = c.studID
and s.advisor = p.profname
and p.deptname = d.deptname
and s.major = d.major
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith');
2.d. Find all professors names and their departments that
have offices in the
buildings identified in query 2c.
select p.profname, p.deptname
from professor p where profID in
(select p.profID
from student s, club c, department d , professor p
where s.studID = c.studID
and s.advisor = p.profname
and p.deptname = d.deptname
and s.major = d.major
and c.clubname in (select c.clubname
from student s, club c
where s.studID = c.studID
and studname = 'Jamie Smith'));
2.e. Find all student names and their major(s) that
participate in Computer Science
Association Club.
select s.studname, s.major
from student s, club c
where s.studID = c.studID
and c.clubname = 'Computer Science Association Club';