Any help would be appreciated(i am using livesql.oracle

chapter 4, exercise 2

1. SELECT

CUST_LAST_NAME,

CUST_EMAIL,

ORDER_ID,

SUM(QUANTITY) AS QTY

FROM

(SELECT

CUST_LAST_NAME,

CUST_EMAIL,

ORDERS.ORDER_ID,

PRODUCT_ID,

QUANTITY

FROM

OE.CUSTOMERS INNER JOIN OE.ORDERS

ON OE.CUSTOMERS.CUSTOMER_ID = OE.ORDERS.CUSTOMER_ID

INNER JOIN OE.ORDER_ITEMS

ON OE.ORDERS.ORDER_ID = OE.ORDER_ITEMS.ORDER_ID

ORDER BY

ORDER_ID ASC) CTE

4. Back in Chapter 4, exercise 2 you wrote an SQL statement to get some information on customer orders. Convert that query into a common table expression (CTE) named customer_information. Use your newly created CTE as your source table and return the following fields:

• CUST_LAST_NAME

• CUST_EMAIL

• ORDER_ID

• SUM(QUANTITY) as QTY

  Remember to place any fields not part of an aggregate function within the GROUP BY clause.

5. Using the CTE named customer_information that you created for the previous problem (problem 4). Add a correlated subquery that also shows the total value of the order. Using an alias, name this calculated field OrderTotal. Because this is a corelated subquery, it falls into the category of an aggregate and does not need to be included in the GROUP BY clause. (Hint: You will have to correlate between oe.orderse.order_id and customer_information.orderid to get order_total).