Consider a database consisting of the following tables. SAILORS(sid, sname, rati

Question

Consider a database consisting of the following tables.

SAILORS(sid, sname, rating, age)
BOATS(bid, bname, color)
RESERVED(sid, bid, day)

Write a SQL statement for each of the following queries.

1. Find the bids of the boats whose bname contains the string "storm".

2. Find the colors of boats reserved by Smith.

3. Find the snames of sailors who have reserved a red or green boat.

4. Find the snames of sailors who have reserved a red boat and a green boat.

5. Find the snames of sailors who have reserved a green boat but not a red boat.

6. Find the snames of sailors with age over 20 who have not reserved a red boat.

7. Find the snames of sailors who have reserved at least three boats.

8. Find the snames of sailors who have reserved all boats.

9. Calculate the average age of all sailors.

10. Find the name and the age of the youngest sailor.

11. For each boat, find the boat id and the average age of sailors who reserved it.

12. Find the sids of sailors with the highest rating.

Explanation / Answer

Consider a database consisting of the following tables.
SAILORS(sid, sname, rating, age)
BOATS(bid, bname, color)
RESERVED(sid, bid, day)
Write a SQL statement for each of the following queries.

1. Find the bids of the boats whose bname contains the string "storm".
SELECT bid FROM Boats WHERE bname = '%storm%'; will list all the bids of boats whose bnames contain storm in it.

2. Find the colors of boats reserved by Smith.
SELECT DISTINCT(bcolor) FROM Boats B, Reserved R, Sailors S
WHERE B.bid = R.bid
AND S.sid = R.sid
AND S.sname = "Smith"

3. Find the snames of sailors who have reserved a red or green boat.
SELECT sname FROM Sailors S, Boats B, Reserved R
WHERE B.bid = R.bid
AND S.sid = R.sid
AND (B.color = 'red' OR B.color = 'green')

4. Find the snames of sailors who have reserved a red boat and a green boat.
SELECT sname FROM Sailors S, Boats B, Reserved R
WHERE B.bid = R.bid
AND S.sid = R.sid
AND B.color = 'red'
INTERSECT
SELECT sname FROM Sailors S, Boats B, Reserved R
WHERE B.bid = R.bid
AND S.sid = R.sid
AND B.color = 'green'

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.