SQL Queries on Sailors and Boat Schema | 100+ Queries

11. Display boat names and the names of sailors who have sailed on them:

SELECT b.bname, s.sname
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid;

12. Count the number of distinct boat colors:

SELECT COUNT(DISTINCT color) FROM Boats;

13. Retrieve sailors and the average length of boats they’ve sailed on:

SELECT s.sname, AVG(b.length) AS avg_length
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
GROUP BY s.sname;

Explanation: This query uses an INNER JOIN to associate sailors with the boats they’ve sailed on, then calculates and displays the average boat length for each sailor using GROUP BY.

14. Find sailors who have sailed on boats longer than 30 feet:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.length > 30;

15. List boat names along with the count of sailors who have sailed on them:

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;

16. Retrieve sailors who are either 20 years old or have a rating below 5:

SELECT sname FROM Sailors WHERE age = 20 OR rating < 5;

17. Display boat names and the youngest sailor who has sailed on each boat:

SELECT b.bname, MIN(s.age) AS youngest_age
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;

18. Find the average rating of sailors under the age of 30:

SELECT AVG(rating) AS avg_rating
FROM Sailors
WHERE age < 30;

19. List boat names and the total combined age of sailors who have sailed on them:

SELECT b.bname, SUM(s.age) AS total_age
FROM Boats b
INNER JOIN Sailors s ON b.bid = s.sid
GROUP BY b.bname;

Explanation: Through an INNER JOIN and GROUP BY, this query retrieves boat names and calculates the total combined age of sailors who have sailed on each boat.

20. Retrieve sailors who have sailed on all boats:

SELECT s.sname
FROM Sailors s
WHERE NOT EXISTS (
    SELECT b.bid FROM Boats b
    WHERE NOT EXISTS (
        SELECT NULL FROM Sailors ss
        WHERE ss.sid = s.sid AND ss.sid = b.bid
    )
);