Please see the scheme below to answer this questions.
Posted: Fri Jul 08, 2022 6:16 am
Please see the scheme below to answer this questions.
4. Write a query that returns a result set containing the order ID, order date, and order total for each order. Instead of using the already existing order total column in the orders table, create a derived column that takes the sum of unit price multiplied by quantity for each line item in each order. If done properly, the derived column and the original order total column will contain the same value. Alias the derived column as "manual_order_total." Sort the result set by the derived column, descending. ORDER_ID 2385 ITSS 4300 Assessment 3A 101 5. Write a query that returns a result set containing the customer ID, order ID, and order date for each customer's first and last order. If a customer has only made one order, then there should only be one row for the customer, otherwise two rows. Assign the same integer value to ties in order date. Use consecutive integers (e.g., 1, 1, 2, 3, 4, 4, 5). Alias any derived columns as you see fit. Sort the result set by customer ID, ascending. CUSTOMER_ID ORDER_ID 101 102 08-DEC-07 11.34.11.331392 AM 295892 102 ORDER_DATE 2458 2447 2431 2397 ORDER_TOTAL MANUAL_ORDER_TOTAL ORDER DATE 16-AUG-07 03.34.12.234359 PM 295892 ORDER_DATE_SEQUENCE_ASC ORDER_DATE_SEQUENCE_DESC 1 27-JUL-es es.59.10.223344 AM 4 14-SEP-06 07.03.04.763452 AM 1 19-NOV-07 02.41.54.696211 PM 4 4 1 4 1
0 OE Ⓒ ORDER ITEMS order id line_item_id product_id unit_price quantity ORDERS order id order_date order_mode customer_id order_status order total sales_rep_id promotion_id PRODUCT INFORMATION product_id product_name product description category_id weight class warranty period supplier id product status list price min_price catalog url INVENTORIES product id warehouse id quantity_on_hand Description of "Figure 3-1 Sample Schemas HR and OE" (img_text/comsc008.htm) PRODUCT DESCRIPTIONS product_id language_id translated_name translated_description CUSTOMERS customer id cust_first_name cust last name cust address phone numbers nis_language nls territory credit limit cust_email account mgr id cust_geo_location date of birth marital status gender income level Figure 3-2 Sample Schemas OE and PM WAREHOUSES warehouse id warehouse spec warehouse_name location_id wh_geo_location ( ( Oe Categories_tab Gate Gory-Name Category-Description Category-10 Category Category_ID 1
- Please See The Scheme Below To Answer This Questions 1 (226.82 KiB) Viewed 46 times
0 OE Ⓒ ORDER ITEMS order id line_item_id product_id unit_price quantity ORDERS order id order_date order_mode customer_id order_status order total sales_rep_id promotion_id PRODUCT INFORMATION product_id product_name product description category_id weight class warranty period supplier id product status list price min_price catalog url INVENTORIES product id warehouse id quantity_on_hand Description of "Figure 3-1 Sample Schemas HR and OE" (img_text/comsc008.htm) PRODUCT DESCRIPTIONS product_id language_id translated_name translated_description CUSTOMERS customer id cust_first_name cust last name cust address phone numbers nis_language nls territory credit limit cust_email account mgr id cust_geo_location date of birth marital status gender income level Figure 3-2 Sample Schemas OE and PM WAREHOUSES warehouse id warehouse spec warehouse_name location_id wh_geo_location ( ( Oe Categories_tab Gate Gory-Name Category-Description Category-10 Category Category_ID 1