ISRO-2013-24

Question

Consider the following relational schema:

• Suppliers (sid:integer, sname:string, saddress:string)
• Parts (pid:integer, pname:string, pcolor:string)
• Catalog (sid:integer, pid:integer, pcost:real)

What is the result of the following query?

(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sid = Catalog.sid)
MINUS
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sname <> 'Sachin' and Suppliers.sid = Catalog.sid)

• A. Pid of parts supplied by all except Sachin
• B. Pid of parts supplied only by Sachin
• C. Pid of parts available in catalog supplied by Sachin
• D. Pid of parts available in catalog supplied by all except Sachin

Comments

let me know if there is  any mistake-

Answer 1

Answer is (B)

Here in 1st query we are joining Catalog and Supplier and getting pids for all parts which are being supplied.

In 2nd query we are doing same as 1st query but just removing all those rows where Supplier name is not Sachin. i.e., this query returns pids for all parts which are being supplied by any supplier other than Sachin.

In the resulting table we are subtracting 2nd table rows from 1st  table and getting pids for those part which are supplied only by Sachin.

If a part is supplied by Sachin and also by some other supplier, it will be selected by the second query and due to "minus" will be missing in the final output. Thus option C is wrong and B is correct.

Comments

They just copy pasted the question 😊 Similar one was a previous year GATE question.

---

If a part is supplied by Sachin and also by some other supplier, it will be selected by the second query and due to "minus" will be missing in the final output. Thus option C is wrong and B is correct.

At first I didn't understand this. Now I think I know this. There are two facts

1) Multiple people can supply same part
2) SELECT will be a set - no duplicate values

Which implies that the second part of the query will output
 ALL parts (containing no duplicates) MINUS parts which supplied by Sachin.

Ultimately we are removing all the part tuples where sachin suplied even if some other guy also supplied the same.

---

isro making such good level question haha what a joke
if in isro a question is coming  then high chances that it was asked already in gate

Answer 2

(SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sid = Catalog.sid) MINUS (SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sname <> 'Sachin' and Suppliers.sid = Catalog.sid)

 

First sub-query selects all the parts.
Second sub-query selects all the "non-Sachin" parts.
First query - Second Query = All parts - Non-Sachin parts = Only Sachin parts.

Options A and D eliminated straight.

 

Now, if Sachin supplies, say  blue gloves, and Sunil also supplies blue gloves then blue gloves would be selected by the second sub-query, and hence eliminated from the overall result.
Option C eliminated

 

So, Option B..