SQL subqueries ques :

Question

I have these 3 tables :

1)Sailors (sid:INT, sname:VARCHAR(30), rating:INT, age:INT)

2)Boats (bid:INT, bname:VARCHAR(30), color:VARCHAR(10))

3)Reserves (bid:INT, sid:INT, day:DATE)

I don't know how to build a query that displays : The names of the sailors who have reserved at least 2 boats in different colors!

Answer 1

You can create a group for each sailor, and then demand that there are at least 2 distinct colors in that group:

select  s.sid
,       s.sname
,       count(distinct b.color) as NumberOfBoatColorsReserved
from    Reserves r
join    Sailors s
on      s.sid = r.sid
join    Boats b
on      b.bid = r.bid
group by
        s.sid
,       s.sname
having  count(distinct b.color) >= 2

Answer 2

There is probably a more efficient way to do this, but here is a quick and dry solution:

SELECT sname FROM Sailors 
JOIN Reserves USING (sid) 
JOIN Boats USING (bid) 
GROUP BY sname, color 
HAVING COUNT(sname) >= 2;

Added Selecting Unique BID:

SELECT bid FROM Sailors 
JOIN Reserves USING (sid) 
JOIN Boats USING (bid)
HAVING COUNT(bid) >= 3;

Where 3 represents the total number of sailors that are in the database. This can be queried separately and quite simply using COUNT