Database Schema: Sailors(sid, sname, rating, age) Boats (bid, color) Reserve (si
ID: 3594822 • Letter: D
Question
Database Schema:
Sailors(sid, sname, rating, age)
Boats (bid, color)
Reserve (sid, bid, date)
1. What do we get if we replace UNION by EXCEPT ALL?
Find sid’s and names of sailors who’ve reserved a red or a green boat
(SELECT S.sid, name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’)
UNION
(SELECT S.sid, name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’)
2. Any duplicates in the result? If so, exactly how many duplicates for each qualified sid? How to fix the problem?
Find sid’s and names of sailors who’ve reserved both a red and a green boat:
SELECT S.sid, S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
AND S.sid IN (SELECT R2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid=R2.sid AND R2.bid=B2.bid
AND B2.color=‘green’)
Similarly, EXCEPT queries rewritten using NOT IN.
3. Any problem in the following?
Find sailors who’ve reserved all boats. A different ‘solution’
Select s.sname from sailors s
Where (select count(distinct bid) from reserves where sid=s.sid) = (select count(bid) from boats);
4. What do we get if we remove B.color= ‘red’ from the WHERE clause and add a HAVING clause with this condition? If there is a problem, how to fix it?
For each red boat, find the number of reservations for this boat.
SELECT B.bid, COUNT (*) AS scount
FROM Boats B, Reserves R
WHERE R.bid=B.bid AND B.color=‘red’
GROUP BY B.bid
Explanation / Answer
SELECT S.sid
FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)
SELECT S.sid
FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
UNION
SELECT S.sid
FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.