Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each customer.
Question:
Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each customer. To do this, you can group the result set by the email_address and order_id columns. In addition, you must calculate the order total from the columns in the Order_Items table. Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s email address and the largest order for that customer. To do this, you can group the result set by the email_address. CUSTOMERS TABLE: CUSTOMER_ID, EMAIL_ADDRESS, PASSWORD, FIRST_NAME, LAST_NAME, SHIPPING_ADDRESS_ID, BILLING_ADDRESS_ID ORDER_ITEMS TABLE: ITEM_ID, ORDER_ID, PRODUCT_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUATITY 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.
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel