• Sailors (Sid, Sname, Rating, Age)
• Reserves (Sid, Bid , Day)
• Boats(Bid, Bname, Color)
Sailors, Reserves, Boats
• Find the names of sailors who have reserved boat 103.
SELECT [Link]
FROM Sailors S, Reserves R
WHERE [Link] = [Link] and [Link]=103
SELECT [Link]
FROM Sailors S
WHERE [Link] IN(SELECT [Link]
FROM Reserves R
WHERE [Link] = 103 )
• Find the names of sailors who have reserved a red boat.
SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]='red' AND [Link]=[Link] AND [Link] = [Link]
SELECT [Link]
FROM Sailors S
WHERE [Link] IN ( SELECT [Link]
FROM Reserves R
WHERE [Link] IN(SELECT [Link]
FROM Boats B
WHERE [Link] = ‘red’ )
• Find the names of sailors who have reserved a red or a green boat.
SELECT [Link]
FROM Sailors S, Reserves R, Boats B
WHERE [Link] = [Link] AND [Link] = [Link]
AND ([Link] = ‘red’ OR [Link] = ‘green’)
SELECT [Link]
FROM Sailors S, Reserves R, Boats B
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = ‘red’
UNION
SELECT [Link]
FROM Sailors S2, Boats B2, Reserves R2
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = ‘green’
• Find the sids of sailors with age over 20 who have not reserved a red
boat.
SELECT [Link]
FROM Sailors S, Reserves R, Boats B
WHERE [Link] != 'red' and [Link] = [Link] and [Link] = [Link] and [Link]> 20
• Find the ages of sailors whose name begins and ends with B and has
at least three characters.
• SELECT [Link]
FROM Sailors S
WHERE [Link] LIKE' B_%B'
• Find all sids of sailors who have a rating of 10 or have reserved boat
104.
SELECT [Link]
FROM Sailors S
WHERE [Link] = 10
UNION
SELECT [Link]
FROM Reserves R
WHERE [Link] = 104
• Find sailors whose rating is better than some sailor called Horatio.
SELECT [Link]
FROM Sailors S
WHERE [Link] > ANY ( SELECT [Link]
FROM Sailors S2
WHERE [Link] = ‘Horatio’ )
• Find the sailors with the highest rating.
• SELECT [Link]
• FROM Sailors S
• WHERE [Link]>=ALL(SELECT [Link]
• FROM Sailors S2 )
• Find the name and age of the oldest sailor.
• SELECT [Link], [Link]
• FROM Sailors S
• WHERE [Link]=(SELECT MAX([Link])
• FROM Sailors S2 )
• Find the names of sailors who are older than the oldest sailor with a
rating of 10.
• SELECT [Link]
• From Sailors S
• WHERE [Link]> (SELECT MAX([Link])
• FROM Sailor S2
• WHERE [Link]=10)
• Find the age of the youngest sailor who is eligible to vote (i.e., is at
least 18 years old) for each rating level with at least two such sailors.
SELECT [Link], MIN([Link]) AS minage
FROM Sailors S
WHERE [Link]>18
GROUP BY [Link]
HAVING COUNT(*) > 1
• For each red boat, find the number of reservations for this boat.
SELECT [Link], COUNT (*) AS sailorcount
FROM Boats B, Reserves R
WHERE [Link] = [Link] AND [Link] = ‘red’
GROUP BY [Link]
• Find the average age of sailors for each rating level that has at least
two sailors.
SELECT [Link], AVG([Link]) AS avgage
FROM Sailors S
GROUP BY [Link]
HAVING COUNT(*) > 1