Question

    Table Name: CUSTOMERS_1

CUST_NUM CUST_LNAME          CUST_FNAME          CUST_BAL

2001    James William           $2,999

2002    Crane Frasier             $983

    Table Name: CUSTOMERS_2

CUST_NUM

            CUST_LNAME          CUST_FNAME          CUST_BAL

1999    Anderson         Anne $510

2000    Bryant Juan     $21

2002    Crane Frasier             $983

2003    Dent    George            $1,790

    Table Name: CUST_INVOICES

INV_NUM     CUST_NUM INV_DATE    INV_AMOUNT

9000    2000    23-Mar-16       245

9001    2001    23-Mar-16       260

9002    2001    30-Mar-16       275

9003    1000    10-Apr-16       286

using this information, i need help formulating an sql query:

    Write a SQL code that will show CUST_NUM, CUST_LNAME, and CUST_FNAME for the one who has minimal INV_AMOUNT

Answer 1

Ans:- Here we will use UNION ALL to fetch all the results from CUSTOMER_1 and CUSTOMER_2.

The query will be

SELECT CUST_NUM,CUST_LNAME, CUST_FNAME FROM CUSTOMER WHERE CUST_NUM IN(SELECT cust_num FROM INVOICE WHERE INV_AMOUNT IN (SELECT min(INV_AMOUNT)FROM INVOICE ) ) UNION ALL
SELECT CUST_NUM,CUST_LNAME, CUST_FNAME FROM CUSTOMER_2 WHERE CUST_NUM IN(SELECT cust_num FROM INVOICE WHERE INV_AMOUNT IN (SELECT min(INV_AMOUNT)FROM INVOICE ) )