31. Display boat names and the names of the oldest sailor who has sailed on each boat:
SELECT b.bname, MAX(s.age) AS oldest_age
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;
32. List sailors who have sailed on boats shorter than the average boat length and have a rating above 5:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length < (SELECT AVG(length) FROM Boats)
AND s.rating > 5;
33. Retrieve sailors whose age is equal to their rating:
SELECT sname
FROM Sailors
WHERE age = rating;
34. Find the boat names that have been sailed on by sailors under the age of 25:
SELECT DISTINCT b.bname
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
WHERE s.age < 25;
35. List sailors who have sailed on boats with a color different from their own:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE s.color <> b.color;
36. Display the names of sailors who have the highest rating within their age group:
SELECT s.sname
FROM Sailors s
WHERE rating = (
SELECT MAX(rating)
FROM Sailors
WHERE age = s.age
);
37. Retrieve boat names and the count of sailors for each boat, excluding those with no sailors:
SELECT b.bname, COUNT(s.sid) AS sailor_count
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname
HAVING COUNT(s.sid) > 0;
38. List sailors who have sailed on both green and blue boats:
SELECT s.sname
FROM Sailors s
WHERE EXISTS (
SELECT NULL FROM Boats b
WHERE (s.sid = b.bid AND b.color = 'green')
AND EXISTS (
SELECT NULL FROM Boats bb
WHERE (s.sid = bb.bid AND bb.color = 'blue')
)
);
39. Find sailors who have sailed on the longest boat:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length = (SELECT MAX(length) FROM Boats);
40. Display boat names and the number of sailors whose rating is above 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.rating > b.length
GROUP BY b.bname;
