Write SQL queries for Sailors-Boats-Reserves database

Write SQL queries for Sailors-Boats-Reserves database:
 1. Find colors of boats reserved by Alber.
 2. Sailor IDs with rating ≥ 8 or who reserved boat 103.
 3. Sailor names who didn’t reserve boat containing “storm”.
 4. Sailor IDs over age 20 who didn’t reserve boats with “thunder”.

Answer:-

Assumed Schema:

Sailors(sid INT, sname VARCHAR, rating INT, age REAL)
Boats(bid INT, bname VARCHAR, color VARCHAR)
Reserves(sid INT, bid INT, day DATE)

1. Find colors of boats reserved by Alber

SELECT DISTINCT B.color
FROM Sailors S
JOIN Reserves R ON S.sid = R.sid
JOIN Boats B ON R.bid = B.bid
WHERE S.sname = 'Alber';

2. Find sailor IDs with rating ≥ 8 or who reserved boat 103

SELECT DISTINCT S.sid
FROM Sailors S
WHERE S.rating >= 8

UNION

SELECT DISTINCT R.sid
FROM Reserves R
WHERE R.bid = 103;

3. Find sailor names who didn’t reserve a boat containing “storm” in its name

SELECT DISTINCT S.sname
FROM Sailors S
WHERE S.sid NOT IN (
    SELECT DISTINCT R.sid
    FROM Reserves R
    JOIN Boats B ON R.bid = B.bid
    WHERE B.bname LIKE '%storm%'
);

4. Find sailor IDs over age 20 who didn’t reserve boats with “thunder” in the name

SELECT DISTINCT S.sid
FROM Sailors S
WHERE S.age > 20
AND S.sid NOT IN (
    SELECT DISTINCT R.sid
    FROM Reserves R
    JOIN Boats B ON R.bid = B.bid
    WHERE B.bname LIKE '%thunder%'
);