CS 386: Quiz 3, 25 April 2006 Name:_____KEY__________
No books or notes. Work individually.
All three questions involve a database about student organizations, with two tables:
Clubs(name office phone) and Events(sponsor title date location length).
Give the result of each of the queries below using the following database instance.
Note: Two of the queries give the same result.
Clubs( club office phone )
sailing SH 120 5-8772
hiking NH 283 5-2313
ACM EB 118 5-8116
chess CH 442 5-1133
Events(sponsor title date location length
)
chess Speed chess 2 May SMSU 14 60
chess Opening moves 5 May SMSU 18 90
ACM Python intro 9 May SH 120 30
ACM Resume prep 4 June EB 276 90
Question 1 (2.5 points): Find events of maximum length (which is 90 minutes here)
SELECT E1.title title
FROM Events E1 Opening moves
WHERE E1.length >= ALL Resume prep
(SELECT E2.length
FROM Events E2)
Question 2 (2.5 points): Which clubs are not sponsors of events?
SELECT C.name name
FROM Clubs C sailing
WHERE C.name NOT IN hiking
(SELECT E.sponsor
FROM Events E)
Question 3 (2.5 points): Which clubs do not have any events they are sponsoring? (=Q2)
SELECT C.name same answer as above
FROM Clubs C
WHERE NOT EXISTS
(SELECT *
FROM Events E
WHERE E.sponsor = C.name)
Question 4 (2.5 points): Which club has an event in its office that another club is sponsoring?
SELECT C.name, C.office name office
FROM Clubs C sailing SH 120
WHERE C.office = SOME
(SELECT E.location
FROM Events E
WHERE E.sponsor <> C.Name)
