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

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. 

---

By writing the option in the answer is selected as the best answer,how?

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

Answer 5

"Where not unique" clause will choose the tuples where the condition is not unique, ie, there's duplicity.
For 0 items, there can't be duplicity.
For exactly 1 item, it is unique! The clause literally says "where not unique"
For 2 or more items, there's duplicity.

Hence, Option D

Answer 6

Eg :

     SUPPLY                                         INVENTORY                           

sup_id      itemcode                        itemcode             warehouse

 S1              I1                                 I1                        NAGPUR

 S2              I1                                 I2                        NAGPUR

 S2              I2                                 I3                        MUMBAI

S3               I1

S3               I2

S3               I3

OUTER QUERY : Select distinct STMP.supplierid 
From Supply as STMP  -> Will select a Row of Supplier Table for which Inner Query will EXECUTE (for every Selected Row).

 

INNER QUERY : 

                            Select ITMP.supplierid
                            From Inventory, Supply as ITMP
                            Where STMP.supplierid = ITMP.supplierid
                            And ITMP.itemcode = Inventory.itemcode
                            And Inventory.warehouse = 'Nagpur'

STMP = OUTER SUPPLY TABLE .

ITMP = INNER SUPPLY TABLE.

Inventory, Supply -> CROSS PRODUCT of both tables .

Where STMP.supplierid = ITMP.supplierid -> THIS will match every sup_id from outer table STMP with respective sup_id in Inner Table ITMP.

Where ITMP.itemcode = After we got our supplier , we will match it with Every item he supplied .

And Inventory.warehouse = 'Nagpur' -> But only if location of warehouse is nagpur.

 

RESULT AFTER JOIN : 

inventory JOIN itmp

 

Sup_id .     Itemcode     Warehouse

   S1                I1             NAGPUR

   S2                I1             NAGPUR

   S2                I2             NAGPUR

   S3                I1             NAGPUR

   S3                I2             NAGPUR

   S3                I3             MUMBAI      will not be selected as location is not nagpur .

 

  For S1 NOT UNIQUE will return False as only 1 ROW is TRUE.

  FOR S2 NOT UNIQUE will return True as 2 duplicates of S2 Are Present which makes it not unique.

  FOR S3 NOT UNIQUE will return True as 2 duplicates of S3 Are Present which makes it not unique.

  So we can say that if location is NAGPUR and we find 2 or more items for a supplier , that sup_id will be returned.

So ANS = (D).