In an inventory management system implemented at a trading corporation, there…
2005
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
Attempted by 64 students.
Show answer & explanation
Correct answer: D
Conclusion: The query returns suppliers who supply two or more items that are stocked in the Nagpur warehouse.
Subquery behavior: For a given supplier, the subquery joins Supply (aliased as ITMP) with Inventory and selects ITMP.supplierid for each Inventory row where the itemcode matches and the warehouse is 'Nagpur'. Each matching item produces one row in the subquery.
Meaning of NOT UNIQUE: UNIQUE(subquery) is true when the subquery result has no duplicate rows. Because the subquery selects the same supplierid value for every matching item of that supplier, duplicates occur when the supplier has two or more matching items in Nagpur. NOT UNIQUE therefore holds exactly when there are at least two such items.
Outer query effect: The outer SELECT DISTINCT returns each supplierid for which the NOT UNIQUE test is true, i.e., suppliers that supply at least two items stocked in the Nagpur warehouse.
Why other interpretations fail: Suppliers supplying zero items yield no rows (NOT UNIQUE false). Suppliers supplying exactly one item yield a single row (NOT UNIQUE false).