41. Retrieve sailors who have sailed on boats with a color similar to their own (first letter matches):
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE LEFT(s.color, 1) = LEFT(b.color, 1);
42. List boat names and the total 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;
43. Find sailors who have the highest rating within their respective age group and are 30 years old or younger:
SELECT s.sname
FROM Sailors s
WHERE age <= 30 AND rating = (
SELECT MAX(rating)
FROM Sailors
WHERE age = s.age
);
44. Retrieve boat names and the average rating of sailors who have sailed on them:
SELECT b.bname, AVG(s.rating) AS avg_rating
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;
45. List sailors who have sailed on the same boat as sailors named “John”:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.bid IN (
SELECT bid FROM Sailors WHERE sname = 'John'
);
46. Display boat names and the count of sailors who are older than the average sailor age:
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)
GROUP BY b.bname;
47. Retrieve sailors who have sailed on boats with a length greater than their age:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length > s.age;
48. List boat names and the average age of sailors who have sailed on them, ordered by age:
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
ORDER BY avg_age;
49. Find sailors who have sailed on boats with a color that starts with the same letter as their name:
SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE LEFT(b.color, 1) = LEFT(s.sname, 1);
50. Retrieve boat names and the maximum rating of sailors who have sailed on them:
SELECT b.bname, MAX(s.rating) AS max_rating
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;
