81. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “el” and are younger 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 sname LIKE '%el%' AND age < 25
);82. Display boat names and the total count of sailors who have the same age as the average sailor age, 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)
GROUP BY b.bname
ORDER BY sailor_count;83. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “eth” and are older than 30:
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 LIKE '%eth%' AND age > 30
);84. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “ia” and have a rating above 6:
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 LIKE '%ia%' AND rating > 6
);85. Display boat names and the total count of sailors who have the same rating as the average sailor rating, 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.rating = (SELECT AVG(rating) FROM Sailors)
GROUP BY b.bname
ORDER BY sailor_count;86. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “el” and are 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 sname LIKE '%el%' AND age > 25
);87. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “li” and have a rating above 5, ordered by name:
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 LIKE '%li%' AND rating > 5
)
ORDER BY s.sname;88. Display boat names and the total count of sailors who have the same rating as the maximum sailor rating, 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.rating = (SELECT MAX(rating) FROM Sailors)
GROUP BY b.bname
ORDER BY sailor_count;89. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “an” and are older than 30:
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 LIKE '%an%' AND age > 30
);90. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “th” and have a rating above 4, ordered by name:
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 LIKE '%th%' AND rating > 4
)
ORDER BY s.sname;91. Display boat names and the total count of sailors who have the same age as the minimum sailor age, 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 MIN(age) FROM Sailors)
GROUP BY b.bname
ORDER BY sailor_count;92. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “on” and are 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 sname LIKE '%on%' AND age > 25
);93. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “ri” and have a rating above 3, ordered by name:
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 LIKE '%ri%' AND rating > 3
)
ORDER BY s.sname;94. Display boat names and the total count of sailors who have the same rating as the minimum sailor rating, 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.rating = (SELECT MIN(rating) FROM Sailors)
GROUP BY b.bname
ORDER BY sailor_count;95. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “ma” and are younger than 30:
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 LIKE '%ma%' AND age < 30
);96. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “ar” and have a rating above 2, ordered by name:
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 LIKE '%ar%' AND rating > 2
)
ORDER BY s.sname;97. Display boat names and the total count of sailors who have the same age as the maximum sailor age, 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 MAX(age) FROM Sailors)
GROUP BY b.bname
ORDER BY sailor_count;98. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “li” and are younger than 25:
SELECT s.sname
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.bid IN (
SELECT bid FROM Sailors WHERE sname LIKE '%li%' AND age < 25
);99. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “th” and have a rating above 1, ordered by name:
SELECT s.sname
INNER JOIN Boats b ON s.sid = b.bid
WHERE b.bid IN (
SELECT bid FROM Sailors WHERE sname LIKE '%th%' AND rating > 1
)
ORDER BY s.sname;100. Display boat names and the total count of sailors who have the same rating as the maximum sailor rating, ordered by count:
sql SELECT b.bname, COUNT(s.sid) AS sailor_count
FROM Boats b LEFT JOIN Sailors s ON b.bid = s.sid
WHERE s.rating = (SELECT MAX(rating)
FROM Sailors)
GROUP BY b.bname
ORDER BY sailor_count;SQL Queries on Sailors and Boat Schema | Conclusion
In this comprehensive article, we have explored a wide range of SQL queries involving sailors and boats, using various JOINs, subqueries, conditional clauses, and aggregation functions. Through these examples, we’ve demonstrated how SQL can be used to extract meaningful insights and information from a relational database containing data about sailors, boats, and their attributes.
By practicing and understanding these queries, you’ll be better equipped to manipulate and retrieve data from similar database structures in your own projects. SQL is a powerful tool for data analysis and management, and mastering it can greatly enhance your ability to work with databases effectively.