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