This is very similar to this question Gate 2009 Question.
Consider the table and values as below
CREATE TABLE if not exists reserves (
sid INT,
bid INT
);
INSERT INTO reserves (sid,bid) VALUES (1,1); -- sailor with id 1 borrowed Red boat
INSERT INTO reserves (sid,bid) VALUES (1,2); -- sailor with id 1 borrowed Blue boat
INSERT INTO reserves (sid,bid) VALUES (2,2); -- sailor with id 2 borrowed Blue boat
CREATE TABLE if not exists boat (
bid INT,
color varchar(10)
);
INSERT INTO boat (bid,color) VALUES (1,'Red');
INSERT INTO boat (bid,color) VALUES (2,'Blue');
The reason this will not work is because although:
SELECT B.bid from boat B where B.color = 'Red'
This innermost query returns the Bid of the boats that are not ‘Red”. But the query that encapsulates this query is querying on the borrows table wherein the sailor with id 1 may have borrowed the boat of Red colour but he also borrowed a different coloured boat as well (Blue in this case).
Hence the query:
select R.sid from reserves R where R.bid
NOT IN (SELECT B.bid from boat B where B.color = 'Red');
will get translated to select R.sid from reserves R where R.bid
NOT IN ({1});
so among the tuples of borrows (1,1) will be rejected as the BID is in 1, but the other two tuples will be part of the result. i.e. (1,2) (2,2)
Selecting SID from these two gives you 1 and 2. 1 being the ID of the sailor who borrowed both Red and blue boats and 2 being the ID of the sailor who Borrowed no Red boats.
Hence we can’t conclude whether of options A B or C
