SQL Queries on Sailors and Boat Schema | 100+ Queries

71. Retrieve sailors who have sailed on boats with colors that have more vowels than consonants:

SELECT s.sname
FROM Sailors s
INNER JOIN Boats b ON s.sid = b.bid
WHERE (
    LENGTH(b.color) - LENGTH(REPLACE(b.color, 'a', '')) +
    LENGTH(b.color) - LENGTH(REPLACE(b.color, 'e', '')) +
    LENGTH(b.color) - LENGTH(REPLACE(b.color, 'i', '')) +
    LENGTH(b.color) - LENGTH(REPLACE(b.color, 'o', '')) +
    LENGTH(b.color) - LENGTH(REPLACE(b.color, 'u', ''))
) > LENGTH(b.color) / 2;

72. List sailors who have sailed on boats that have been sailed on by sailors with a rating above 8 and age above 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 rating > 8 AND age > 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:

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;

74. Retrieve sailors who have sailed on boats that have been sailed on by sailors with an age below 20 and 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 age < 20 AND rating > 6
);

75. List sailors who have sailed on boats that have been sailed on by sailors with a name starting with the letter “A”:

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 '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:

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

77. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name ending with the letter “y”:

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 '%y'
);

78. List sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “an”:

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%'
);

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:

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

80. Retrieve sailors who have sailed on boats that have been sailed on by sailors with a name containing the substring “tho”:

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 '%tho%'
);

Pages: 1 2 3 4 5 6 7 8 9

Leave a Comment Cancel reply