A pizza company is taking orders from customers, and each pizza ordered is added to their...

 

Transcribed Image Text:

A pizza company is taking orders from customers, and each pizza ordered is added to their database as a separate order. Each order has an associated status, "CREATED or SUBMITTED or DELIVERED". An order's Final Status is calculated based on status as follows: 1. When all orders for a customer have a status of DELIVERED, that customer's order has a Final Status of COMPLETED. 2. If a customer has some orders that are not DELIVERED and some orders that are DELIVERED, the Final Status is IN PROGRESS. 3. If all of a customer's orders are SUBMITTED, the Final Status is AWAITING PROGRESS. 4. Otherwise, the Final Status is AWAITING SUBMISSION. Write a query to report the customer_name and Final Status of each customer's order. Order the results by customer name. Table definitions and a data sample are given below. Schema Table: Customer_Order column name column type customer_name varchar2(50) order_id varchar2(10) status varchar2(50) Sample Data Tables Table: Customer_Order Customer_name order_id status John J1 DELIVERED John J2 DELIVERED David D1 SUBMITTED David D3 CREATED Smith S1 SUBMITTED KRISH K1 CREATED When all orders are in DELIVERED status then the Final Status is "COMPLETED". When one or more orders for a customer are DELIVERED and one or more orders are CREATED or SUBMITTED, then the Final Status is "IN PROGRESS". When one or more orders for a customer are SUBMITTED and none are DELIVERED, then the Final Status is "AWAITING PROGRESS". Otherwise, the Final Status is "AWAITING SUBMISSION". Order priority is DELIVERED, SUBMITTED, CREATED. The results are: customer_name Final Status David IN PROGRESS John COMPLETED KRISH AWAITING SUBMISSION Smith AWAITING PROGRESS A pizza company is taking orders from customers, and each pizza ordered is added to their database as a separate order. Each order has an associated status, "CREATED or SUBMITTED or DELIVERED". An order's Final Status is calculated based on status as follows: 1. When all orders for a customer have a status of DELIVERED, that customer's order has a Final Status of COMPLETED. 2. If a customer has some orders that are not DELIVERED and some orders that are DELIVERED, the Final Status is IN PROGRESS. 3. If all of a customer's orders are SUBMITTED, the Final Status is AWAITING PROGRESS. 4. Otherwise, the Final Status is AWAITING SUBMISSION. Write a query to report the customer_name and Final Status of each customer's order. Order the results by customer name. Table definitions and a data sample are given below. Schema Table: Customer_Order column name column type customer_name varchar2(50) order_id varchar2(10) status varchar2(50) Sample Data Tables Table: Customer_Order Customer_name order_id status John J1 DELIVERED John J2 DELIVERED David D1 SUBMITTED David D3 CREATED Smith S1 SUBMITTED KRISH K1 CREATED When all orders are in DELIVERED status then the Final Status is "COMPLETED". When one or more orders for a customer are DELIVERED and one or more orders are CREATED or SUBMITTED, then the Final Status is "IN PROGRESS". When one or more orders for a customer are SUBMITTED and none are DELIVERED, then the Final Status is "AWAITING PROGRESS". Otherwise, the Final Status is "AWAITING SUBMISSION". Order priority is DELIVERED, SUBMITTED, CREATED. The results are: customer_name Final Status David IN PROGRESS John COMPLETED KRISH AWAITING SUBMISSION Smith AWAITING PROGRESS

Answer rating: 100% (QA)

Below I have added the SQL query which uses case statements to provide solution for above problem st... View the full answer