21. Display the names of sailors who have sailed on green boats:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.color = 'green';
22. List boat names and the count of sailors for each boat, ordered by count:
SELECT b.bname, COUNT(s.sid) AS sailor_count
FROM Boats b
LEFT JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname
ORDER BY sailor_count DESC;
23. Find sailors who have the highest rating:
SELECT sname
FROM Sailors
WHERE rating = (SELECT MAX(rating) FROM Sailors);
24. Retrieve boat names and the average age of sailors who have sailed on them:
SELECT b.bname, AVG(s.age) AS avg_age
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;
25. List sailors who have sailed on boats shorter than the average boat length:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length < (SELECT AVG(length) FROM Boats);
26. Display the names of sailors who are either older than 30 or have a rating of 9 or higher:
SELECT sname FROM Sailors WHERE age > 30 OR rating >= 9;
27. Find the boat names that appear in the database more than once:
SELECT bname
FROM Boats
GROUP BY bname
HAVING COUNT(*) > 1;
28. Retrieve the names of sailors who have sailed on boats with the same color as their own:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE s.color = b.color;
29. List boat names and the number of sailors whose age is below the boat’s length:
SELECT b.bname, COUNT(s.sid) AS sailor_count
FROM Boats b
LEFT JOIN Sailors s ON b.bid = s.sid
WHERE s.age < b.length
GROUP BY b.bname;
30. Find sailors who have sailed on all boats of a specific color, say “blue”:
SELECT s.sname
FROM Sailors s
WHERE NOT EXISTS (
SELECT b.bid FROM Boats b
WHERE b.color = 'blue'
AND NOT EXISTS (
SELECT NULL FROM Sailors ss
WHERE ss.sid = s.sid AND ss.sid = b.bid
)
);
