You want to know what vehicles are available from the ‘SPACE TRANSPORTS INC’ store that are below the store’s average price.

                    SELECT DESCRIPTION, PRICE
                      FROM INVENTORY_TBL
                    WHERE PRICE <
                               (SELECT AVG(PRICE)
                                  FROM INVENTORY_TBL
                                WHERE STOR_ID IN
                                           (SELECT STOR_ID
                                              FROM STORE_TBL
                                            WHERE STORE_NAME =
                                                        ‘SPACE TRANSPORTS INC’));

First, the innermost select finds the STOR_ID for the STORE_NAME = ‘SPACE TRANSPORTS INC’.

Result: ‘S001′

The next outer select finds the average price for vehicles in store ‘S001′.

                        960,400.00
                        128,200.00
                        569,500.00
                    + 417,000.00
                     2,075,100.00 / 4 = Result: 518,775.00

The outer select retrieves the DESCRIPTION and PRICE, where the price is below the average price of 518,775.00.

Result:

Most RDBMS systems have no limit on the number of subselect levels.