Note, there are more than one correct answer; this is just one set
of possibilities.
1. What distinct interests do the faculty members have?
Select distinct Interest
From Faculty
Interest
Algorithms
Architecture
Artificial Intelligence
Compilers
Complexity
Databases
Distributed/Parallel Computing
Graphics/Imaging
HCI
HW Systems/CAD
Networks
OS
Programming Languages
Software Engineering
2. How many students are in the database?
Select count(*)
From WhosWho
64
3. How many students went to Duke University?
SELECT count(*)
From WhosWho
Where Previous = "Duke University"
4
4. What's the userid of the student has been here the longest, and
when did he enter?
Select Year, Userid
From WhosWho
where Year = (
SELECT min(Year)
From WhosWho
Where Year is not null
)
volker 1892
5. Which universities does the department have more than two students
from, and how many are from each?
SELECT Previous, count(Previous)
From WhosWho
Where Previous is not null
Group by Previous
Having count(Previous) > 2
Brown University 4
Cornell University 7
Duke University 4
University of California-Berkeley 3
6. How many different schools do we have graduate students from:
save query1 as view
Select distinct Previous
From WhosWho
Select count(*)
From query1
44
7. Which students in the same office came from the same undergraduate
school:
SELECT o1.email, o2.email
FROM Offices o1, Offices o2, WhosWho w1, WhosWho w2
Where w1.userid = o1.email and
w2.userid = o2.email and
o1.email < o2.email and
o1.office = o2.office and
w1.previous = w2.previous
o1.email o2.email
gjb rap
cary tapan
8. Which school has the most students from it?
save as view:
SELECT Previous, count(Previous) AS num
FROM WhosWho
WHERE Previous is not null
GROUP BY Previous
HAVING count(Previous);
SELECT q1.Previous
From Query2 q1
Where q1.num = (
Select max(v.num)
From Query2 v
)
Cornell University
9. What are the userids of students who entered before 1993 and
are not advised by someone interested in OS
Select Userid
from WhosWho
where Year < 1993 and Userid not in(
Select Userid
from Advise
where Advisor in(
select Email
from Faculty
where Interest = 'OS'
)
)
Userid
brad
hinshaw
friedman
10. What is an interesting result (not listed) involving at least one
join?
sorry, you'll have to come up with your own... I already came up with
9 that I was at least moderately entertained with.