Question

Consider the Sailors$-$Boats$-$Reserves DB described in the text.

s $($sid$,$ sname, rating, age$)$

b $($bid$,$ bname, color$)$

r $($sid$,$ bid, date$)$

Write each of the following queries in SQL$.$

$1.$ Find the colors of boats reserved by Albert.

SELECT color

FROM s$,$ b$,$ r

WHERE r$.$sid$=$s$.$sid AND r$.$bid$=$b$.$bid AND

sname$=`$Albert$$

$2.$ Find all sailor id$$s of sailors who have a rating of at least $8$ or reserved boat $103.$

$($SELECT sid

FROM s

WHERE rating$>=8)$

UNION

$($SELECT sid

FROM r

WHERE bid$=103)$

$3.$ Find the names of sailors who have not reserved a boat whose name contains the string

$$storm$.$ Order the names in ascending order.

SELECT sname

FROM s s$1$

WHERE sid NOT IN

$($SELECT sid

FROM r$,$ s

WHERE r$.$sid$=$s$.$sid AND sname LIKE $`\%$storm$\%)$

ORDER BY s$1.$sname

$4.$ Find the sailor id$$s of sailors with age over $20$ who have not reserved a boat whose name

includes the string $$thunder$.$

SELECT sid

FROM s

WHERE age$>20$ AND sid NOT IN

$($SELECT sid

FROM r$,$ b

WHERE r$.$bid$=$b$.$bid AND bname LIKE $`\%$thunder$\%)$

$5.$ Find the names of sailors who have reserved at least two boats.

SELECT sname

FROM s$,$ r r$1,$ r r$2$

WHERE s$.$sid$=$r$1.$sid AND s$.$sid$=$r$2.$sid AND

r$1.$bid$<>$r$2.$bid

Note: If we want to eliminate duplicates, we SELECT DISTINCT sname. Alternatively, we

could simply change the condition in the WHERE clause from r$1.$bid$<>$r$2.$bid to$,$ say

r$1.$bid