Question

Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns: email_address, order_id, and order_date.
CUSTOMERS TABLE: CUSTOMER_ID, EMAIL_ADDRESS, PASSWORD, FIRST_NAME, LAST_NAME, SHIPPING_ADDRESS_ID, BILLING_ADDRESS_ID

ORDERS TABLE: ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_AMOUNT, TAX_AMOUNT, SHIP_DATE_SHIP_ADDRESS_INFO, CARD_TYPE, CARD_NUMBER, CARD_EXPIRES, BILLING_ADDRESS_ID

Answer 1

Answer:

The correlated subquery for the given scenario is given below.

SELECT EMAIL_ADDRESS, ORDER_ID, ORDER_DATE

FROM CUSTOMERS JOIN ORDERS O ON CUSTOMERS.CUSTOMER_ID = O.CUSTOMER_ID

WHERE ORDER_DATE = ( SELECT MIN(ORDERS.ORDER_DATE)

                   FROM ORDERS JOIN CUSTOMERS

                   ON ORDERS.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID

                   GROUP BY CUSTOMERS.CUSTOMER_ID ) ;

Explanation:

The steps in the formation of the outer query are explained below.

1. We need three columns in the output – email address, order id and date of order.

2. The above mentioned columns are taken in SELECT clause.

3. The email address belongs to the customers table. While order id and date of order belongs to the orders table.

4. Both the orders and the customers tables are joined using JOIN keyword on the common column, CUSTOMER_ID.

5. The tables are joined in the FROM clause.

6. The oldest order for each customer is required using sub query. Hence, in the WHERE clause, the ORDER_DATE is equated to the result of the subquery.

7. After ORDER_DATE, equal sign is used and IN keyword is not used since there can be only one oldest order or earliest order date for each customer.

8. The outer query is written as shown.

SELECT EMAIL_ADDRESS, ORDER_ID, ORDER_DATE

FROM CUSTOMERS JOIN ORDERS O ON CUSTOMERS.CUSTOMER_ID = O.CUSTOMER_ID

WHERE ORDER_DATE =

The steps in the formation of the subquery are explained below.

1. We need to find the oldest order for each customer.

2. The oldest order or the earliest ORDER_DATE can be found out by using an aggregate function MIN().

3. The function, MIN(ORDER_DATE), gives the oldest order for each customer.

4. Since aggregate function is used, we use GROUP BY clause since this function is not used in an overall scenario.

5. GROUP BY CUSTOMER_ID indicates for each customer.

6. The customers table is the same table as that of the outer query.  

7. Hence, the sub query created is as follows.

SELECT MIN(ORDERS.ORDER_DATE)

                   FROM ORDERS JOIN CUSTOMERS

                   ON ORDERS.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID

                   GROUP BY CUSTOMERS.CUSTOMER_ID