The Gateway to Computer Science Excellence
+5 votes
2.6k views

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
in Databases by Boss (30.8k points) | 2.6k views
0

let me know if there is  any mistake-

2 Answers

+10 votes
Best answer
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.
by Veteran (119k points)
selected by
0
In the first Query it is written as Suppliers.sid = Catalog.pid . What does that mean? When we join two tables we take the attributes compared to be same. but here we are taking sid and pid. is there any significance or just a typing mistake?
+1
no just typing mistake
0

But As per the key provided by ISRO -2013

Answer is B

0

there is a typing mistake 

WHERE Suppliers.sid = Catalog.pid

instead of catalog.pid  there should be catalog .sid

+1
@srestha See options B and C carefully and also your explanation.
+1
ok done :)
0

it didnt make sense why option C was wrong.

First you said following about 2nd query:

In 2nd query we are doing same as 1st query ...i.e., this query returns pids for all parts which are being supplied by any supplier other than Sachin.

Then you said following about 2nd query:

If a part is supplied by Sachin and also by some other supplier, it will be selected by the second query 

Can you help me understand how these two are same?

0
ISRO likes to test verbal ability in their SQL questions.. its a general trend going on for a long time..
0
They just copy pasted the question 😊 Similar one was a previous year GATE question.
0

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.

+1 vote

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

by Loyal (6.6k points)
edited by
Answer:

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,324 answers
198,405 comments
105,169 users