SQL Queries on Sailors and Boat Schema. In this seaworthy article, we’ll delve into the depths of databases and set sail on a quest to uncover insights about sailors and the vessels they command. We’ll start with a brief overview of our schema, then navigate through a treasure trove of SQL queries, complete with answers and explanations. So hoist the anchor and let’s embark on this SQL adventure!
Schema Overview: Our maritime database comprises two primary tables: Sailors and Boats. The Sailors the table holds information about intrepid sailors, while the Boats table contains details about the vessels that brave the open sea. Here’s a glimpse of our schema:
SQL Queries on Sailors and Boat Schema Definition
Sailors Table:
sid (Sailor ID, Primary Key)
sname (Sailor Name)
rating (Rating of the Sailor)
age (Age of the Sailor)Boat Table:
bid (Boat ID, Primary Key)
bname (Boat Name)
color (Color of the Boat)
length (Length of the Boat)SQL Queries Sailors and Boat Schema:
Table of Contents | SQL Queries List:
- SQL Queries on Sailors and Boat Schema Definition
- SQL Queries Sailors and Boat Schema:
- 1. Retrieve all sailor names:
- 2. List the names of sailors aged 25 or younger:
- 3. Find sailors with a rating above 8:
- 4. Display boat names and their colors:
- 5. List sailors and their ratings in ascending order of age:
- 6. Count the number of sailors:
- 7. Find the length of the longest boat:
- 8. Retrieve the average age of sailors:
- 9. Find boats that are not green:
- 10. List sailors who are either 18 years old or have a rating of 10:
- 11. Display boat names and the names of sailors who have sailed on them:
- 12. Count the number of distinct boat colors:
- 13. Retrieve sailors and the average length of boats they’ve sailed on:
- 14. Find sailors who have sailed on boats longer than 30 feet:
- 15. List boat names along with the count of sailors who have sailed on them:
- 16. Retrieve sailors who are either 20 years old or have a rating below 5:
- 17. Display boat names and the youngest sailor who has sailed on each boat:
- 18. Find the average rating of sailors under the age of 30:
- 19. List boat names and the total combined age of sailors who have sailed on them:
- 20. Retrieve sailors who have sailed on all boats:
- 21. Display the names of sailors who have sailed on green boats:
- 22. List boat names and the count of sailors for each boat, ordered by count:
- 23. Find sailors who have the highest rating:
- 24. Retrieve boat names and the average age of sailors who have sailed on them:
- 25. List sailors who have sailed on boats shorter than the average boat length:
- 26. Display the names of sailors who are either older than 30 or have a rating of 9 or higher:
- 27. Find the boat names that appear in the database more than once:
- 28. Retrieve the names of sailors who have sailed on boats with the same color as their own:
- 29. List boat names and the number of sailors whose age is below the boat’s length:
- 30. Find sailors who have sailed on all boats of a specific color, say “blue”:
- 31. Display boat names and the names of the oldest sailor who has sailed on each boat:
- 32. List sailors who have sailed on boats shorter than the average boat length and have a rating above 5:
- 33. Retrieve sailors whose age is equal to their rating:
- 34. Find the boat names that have been sailed on by sailors under the age of 25:
- 35. List sailors who have sailed on boats with a color different from their own:
- 36. Display the names of sailors who have the highest rating within their age group:
- 37. Retrieve boat names and the count of sailors for each boat, excluding those with no sailors:
- 38. List sailors who have sailed on both green and blue boats:
- 39. Find sailors who have sailed on the longest boat:
- 40. Display boat names and the number of sailors whose rating is above the boat’s length:
- 41. Retrieve sailors who have sailed on boats with a color similar to their own (first letter matches):
- 42. List boat names and the total count of sailors for each boat, ordered by count:
- 43. Find sailors who have the highest rating within their respective age group and are 30 years old or younger:
- 44. Retrieve boat names and the average rating of sailors who have sailed on them:
- 45. List sailors who have sailed on the same boat as sailors named “John”:
- 46. Display boat names and the count of sailors who are older than the average sailor age:
- 47. Retrieve sailors who have sailed on boats with a length greater than their age:
- 48. List boat names and the average age of sailors who have sailed on them, ordered by age:
- 49. Find sailors who have sailed on boats with a color that starts with the same letter as their name:
- 50. Retrieve boat names and the maximum rating of sailors who have sailed on them:
- 51. List sailors who have sailed on boats with a length greater than the average boat length:
- 52. Display boat names and the average age of sailors who have sailed on them, excluding boats shorter than 20 feet:
- 53. Find sailors who have sailed on at least three different boats:
- 54. Retrieve boat names and the total rating of sailors who have sailed on them:
- 55. List sailors who have sailed on boats with a length that is a prime number:
- 56. Display boat names and the count of sailors whose age is above the boat’s length:
- 57. Retrieve sailors who have sailed on at least two boats with a color that matches their own:
- 58. List sailors who have sailed on boats with a length that is a perfect square:
- 59. Find sailors who have sailed on boats whose color includes the letters of their own name:
- 60. Display boat names and the count of sailors who are older than the average sailor age and have a rating above 6:
- 61. Retrieve sailors who have sailed on boats with a length that is a Fibonacci number:
- 62. List sailors who have sailed on boats that have been sailed on by sailors older than 25:
- 63. Display boat names and the number of sailors whose age is below the boat’s length, excluding boats shorter than 10 feet:
- 64. Find sailors who have sailed on boats with colors that have exactly three letters:
- 65. Retrieve sailors who have sailed on boats whose color is a palindrome:
- 66. List sailors who have sailed on boats with a length that is a triangular number:
- 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:
- 68. Retrieve sailors who have sailed on boats with a length that is a power of two:
- 69. List sailors who have sailed on boats that have been sailed on by sailors with a rating above 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:
- 71. Retrieve sailors who have sailed on boats with colors that have more vowels than consonants:
- 72. List sailors who have sailed on boats that have been sailed on by sailors with a rating above 8 and age above 30:
- 73. Display boat names and the total count of sailors who are older than the average sailor age and have a rating above 7, ordered by count:
- 74. Retrieve sailors who have sailed on boats that have been sailed on by sailors with an age below 20 and rating above 6:
- 75. List sailors who have sailed on boats that have been sailed on by sailors with a name starting with the letter “A”:
- 76. Display boat names and the total count of sailors who are older than the average sailor age and have a rating above 6, ordered by count:
- 77. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name ending with the letter “y”:
- 78. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “an”:
- 79. Display boat names and the total count of sailors who are younger than the average sailor age and have a rating above 5, ordered by count:
- 80. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “tho”:
- 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:
- 82. Display boat names and the total count of sailors who have the same age as the average sailor age, ordered by 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:
- 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:
- 85. Display boat names and the total count of sailors who have the same rating as the average sailor rating, ordered by 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:
- 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:
- 88. Display boat names and the total count of sailors who have the same rating as the maximum sailor rating, ordered by 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:
- 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:
- 91. Display boat names and the total count of sailors who have the same age as the minimum sailor age, ordered by 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:
- 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:
- 94. Display boat names and the total count of sailors who have the same rating as the minimum sailor rating, ordered by 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:
- 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:
- 97. Display boat names and the total count of sailors who have the same age as the maximum sailor age, ordered by 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:
- 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:
- 100. Display boat names and the total count of sailors who have the same rating as the maximum sailor rating, ordered by count:
1. Retrieve all sailor names:
SELECT sname FROM Sailors;2. List the names of sailors aged 25 or younger:
SELECT sname FROM Sailors WHERE age <= 25;3. Find sailors with a rating above 8:
SELECT sname FROM Sailors WHERE rating > 8;4. Display boat names and their colors:
SELECT bname, color FROM Boats;5. List sailors and their ratings in ascending order of age:
SELECT sname, rating FROM Sailors ORDER BY age ASC;6. Count the number of sailors:
SELECT COUNT(*) FROM Sailors;7. Find the length of the longest boat:
SELECT MAX(length) FROM Boats;8. Retrieve the average age of sailors:
SELECT AVG(age) FROM Sailors;9. Find boats that are not green:
SELECT bname FROM Boats WHERE color <> 'green';10. List sailors who are either 18 years old or have a rating of 10:
SELECT sname FROM Sailors WHERE age = 18 OR rating = 10;