SQL Queries on Sailors and Boat Schema | 100+ Queries

61. Retrieve sailors who have sailed on boats with a length that is a Fibonacci number:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length IN (1, 1, 2, 3, 5, 8, 13, 21, 34, 55);

62. List sailors who have sailed on boats that have been sailed on by sailors older than 25:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.bid IN (
    SELECT bid FROM Sailors WHERE age > 25
);

63. Display boat names and the number of sailors whose age is below the boat’s length, excluding boats shorter than 10 feet:

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 AND b.length >= 10
GROUP BY b.bname;

64. Find sailors who have sailed on boats with colors that have exactly three letters:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE LENGTH(b.color) = 3;

65. Retrieve sailors who have sailed on boats whose color is a palindrome:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.color = REVERSE(b.color);

66. List sailors who have sailed on boats with a length that is a triangular number:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length IN (1, 3, 6, 10, 15, 21, 28, 36, 45);

67. Display boat names and the count of sailors who are younger than the average sailor age and have a rating above 7, ordered by count:

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 > 7
GROUP BY b.bname
ORDER BY sailor_count;

68. Retrieve sailors who have sailed on boats with a length that is a power of two:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length IN (1, 2, 4, 8, 16, 32, 64);

69. List sailors who have sailed on boats that have been sailed on by sailors with a rating above 9:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.bid IN (
    SELECT bid FROM Sailors WHERE rating > 9
);

70. Display boat names and the total count of sailors who are younger than the average sailor age and have a rating above 8, ordered by count:

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 > 8
GROUP BY b.bname
ORDER BY sailor_count;

Pages: 1 2 3 4 5 6 7 8 9

Leave a Comment Cancel reply