51. List sailors who have sailed on boats with a length greater 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);52. Display boat names and the average age of sailors who have sailed on them, excluding boats shorter than 20 feet:
SELECT b.bname, AVG(s.age) AS avg_age
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
WHERE b.length >= 20
GROUP BY b.bname;53. Find sailors who have sailed on at least three different boats:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
GROUP BY s.sname
HAVING COUNT(DISTINCT b.bid) >= 3;54. Retrieve boat names and the total rating of sailors who have sailed on them:
SELECT b.bname, SUM(s.rating) AS total_rating
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;55. List sailors who have sailed on boats with a length that is a prime number:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length IN (2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79, 83, 89, 97);56. Display boat names and the count of sailors whose age 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.age > b.length
GROUP BY b.bname;57. Retrieve sailors who have sailed on at least two boats with a color that matches their own:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE s.color = b.color
GROUP BY s.sname
HAVING COUNT(DISTINCT b.bid) >= 2;58. List sailors who have sailed on boats with a length that is a perfect square:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length IN (1, 4, 9, 16, 25, 36, 49, 64, 81);59. Find sailors who have sailed on boats whose color includes the letters of their own name:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.color LIKE '%' + s.sname + '%';60. Display boat names and the count of sailors who are older than the average sailor age and have a rating above 6:
SELECT b.bname, COUNT(s.sid) AS sailor_count
FROM Boats b
LEFT JOIN Sailors s ON b.bid = s.sid
WHERE s.age > (SELECT AVG(age) FROM Sailors) AND s.rating > 6
GROUP BY b.bname;