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
Explanation: Here [not unique] in nested query ensures that only for those suppliers it return True which supplies more than 1 item in which case supplier id in inner query will be repeated for that supplier.
Hence, answer is (D) supply two or more items.
Quiz of this Question