GATE IT 2005 | Question: 69

Question

In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items.

Supply = (supplierid, itemcode)
Inventory = (itemcode, warehouse, stocklevel)

For a specific information required by the management, following SQL query has been written

Select distinct STMP.supplierid 
From Supply as STMP
Where not unique (Select ITMP.supplierid
                            From Inventory, Supply as ITMP
                            Where STMP.supplierid = ITMP.supplierid
                            And ITMP.itemcode = Inventory.itemcode
                            And Inventory.warehouse = 'Nagpur');

For the warehouse at Nagpur, this query will find all suppliers who

• A. do not supply any item
• B. supply exactly one item
• C. supply one or more items
• D. supply two or more items

Comments

Some one please tell me is what is ITMP table?

Is it cross product of Supply and Inventory table? Or only the Supply table is being renamed as ITMP and then being used?

@Bikram sir @Arjun sir

---

@gaurav_916

It is renamed.

---

https://www.geeksforgeeks.org/sql-correlated-subqueries/

Might be helpful to understand.

Answer 1

Answer is D) supply two or more items 
The whole query returns the distinct list of suppliers who supply two or more items.

Comments

a null value is an unknown value, two null values aren’t considered equal to each other; when the UNIQUE keyword is applied to a result table that contains only two null rows, the UNIQUE predicate evaluates to True

---

But what about the distinct in select? Then how 2 will be selected?

---

@Kiruthigakumar The distinct is at the outer query. It gives distinct supplier names and has no relation with number of items. 

Answer 2

Here most important part is "not unique" supplierid .

means supplier supplies more than one

So, from here we can say D) is the answer.

From the inner query  we get 1,3,3,4 as output.

And outer query takes only distinct nonunique item

i.e. 3 here

So, Ans D) supply two or more items

Comments

Nice explanation.

---

@ST Supplier 3 is supplying 2 or more items of Nagpur and It was asked in question ,Also the example was correct.

---

I think ITMP will contain itemcode from both the tables!

pls clear this!

Answer 3

Lets break query in 2 Parts:

(Select ITMP.supplierid

From Inventory, Supply as ITMP

Where STMP.supplierid = ITMP.supplierid And ITMP.itemcode = Inventory.itemcode And Inventory.warehouse = 'Nagpur') 

Returns suplier id who suplies atleast one item in nagpur city.

Select distinct STMP.supplierid

From Supply as STMP

Where not unique ( 1,2,3,1,4,5 )                                 

Will return only those value which are not unique i.e. atleast 2 time present. 

So Output will be distinct supllier id in nagpur who suplied at least 2 item.

So D is answer

Comments

Select distinct STMP.supplierid

From Supply as STMP

Where not unique ( 1,2,3,1,4,5 )  

'NOT UNIQUE' returns either TRUE or FALSE unlike what you've mentioned. The WHERE part will return either TRUE or FALSE.

Requesting you to rectify the answer.
 

---

@st Why are you not giving the answer?

---

nicely explained...

Answer 4

Answer is d,  nested query ensures that for only  those suppliers it returns true which supplies more than 1 item in which case supplier id in inner query will be repeated for that supplier.

Comments

Why not (c)

---

for one item, not unique is false