6,135 views
5 votes
5 votes

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)
  1. Pid of parts supplied by all except Sachin
  2. Pid of parts supplied only by Sachin
  3. Pid of parts available in catalog supplied by Sachin
  4. Pid of parts available in catalog supplied by all except Sachin

2 Answers

Best answer
12 votes
12 votes
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.
selected by
1 votes
1 votes

(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..

edited by
Answer:

Related questions

8 votes
8 votes
5 answers
1
makhdoom ghaya asked May 2, 2016
7,254 views
Embedded pointer providesA secondary access pathA physical record keyAn inverted indexA primary key
8 votes
8 votes
2 answers
2
makhdoom ghaya asked Apr 27, 2016
5,362 views
The physical location of a record determined by a formula that transforms a file key into a record location isHashed file$B$-Tree fileIndexed fileSequential file
8 votes
8 votes
2 answers
3
8 votes
8 votes
1 answer
4