edited by
14,096 views
55 votes
55 votes

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

  1. do not supply any item
  2. supply exactly one item
  3. supply one or more items
  4. supply two or more items
edited by

7 Answers

2 votes
2 votes

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

 

1 votes
1 votes

"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

0 votes
0 votes

Inner query returns 1 or more items

But as It should not be unique, This means outer query Should have 1+ 1 or more items

Example 

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');

 

Supply Table STMP

Supplier

ID

Item Code
1 I1
I2
3 I3
4 I4
5 I5
6 I6
7 I6
8 I6
9 I6

 

Supply Table ITMP

Supplier

ID

Item Code
1 I1
I2
3 I2
4 I2
5 I5
6 I6
7 I7
8 I8
9 I9

Inventory Table

Item Code Warehouse

Stock Level

I1 DELHI 432
I2 NAGPUR 12
I3 JAMMU 876
I4 LUCKNOW 82
I5 KANPUR 25
I6 NAGPUR 152
I7 MIZORAM 765
I8 NAGPUR 99872
I9 NAGPUR 33

SUPPLIER ID’S 2,6,8,9 WILL GET SELECTED

BUT NOT UNIQUE SAYS WHICH IS MORE THAN ONCE

AS WE CAN SEE, I2 IS MORE THAN ONE 

 

BUT OUTER QUERY SAYS DISTINCT, THAT MEANS, SELECT THOSE WHICH ARE MORE THAN TWO.

Answer:

Related questions

54 votes
54 votes
4 answers
1