A pizza company is taking orders from customers, and each pizza ordered is added to their...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
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
Related Book For
Auditing A Practical Approach with Data Analytics
ISBN: 978-1119401742
1st edition
Authors: Raymond N. Johnson, Laura Davis Wiley, Robyn Moroney, Fiona Campbell, Jane Hamilton
Posted Date:
Students also viewed these accounting questions
-
A pizza delivery company promises to deliver all customer orders within one hour of receiving an order at one of its stores. Actual data for all pizza delivered during the past week showed that the...
-
An aerospace company has submitted bids on two separate federal government defense contracts. The company president believes that there is a 40% probability of winning the first contract. If they win...
-
A mail- order company has one department for taking customer orders and another for handling com-plaints. Currently, each department has a separate telephone number. Each has 7 telephone lines served...
-
Accounting for Contingencies and Provisions In accounting, contingencies and provisions play a crucial role in preparing financial statements and ensuring accurate representation of a company's...
-
The actual light sensor size of a digital camera is 14.9 mm 22.3 mm. You want to take a photo of your friend, who is 1.9 m tall. Your camera has a 55-mm-focal-length lens. How far from the camera...
-
Al Falfa Department Store is located near the Village Shopping Mall. At the end of the companys fiscal year on December 31, 2001, the following accounts appeared in its adjusted trial balance:...
-
AMPHIB Corporation processes input Leonardo into three outputs: Michaelangelo, Raphael, and Donatello. At the split-off point, Michaelangelo results in 60 percent of the net realizable value, Raphael...
-
Paudel Limited has been in the retail business for many years and has reached its goal in securing a strong financial position. The board of directors decided that it is time to adopt a policy of...
-
Feather Friends, Inc., distributes a high-quality wooden birdhouse that sells for $20 per unit. Variable expenses are $8 per unit, and fixed expenses total $180,000 per year. Its operating results...
-
The bar codes above represent locations in a warehouse. How many units would be left in each location? If you picked 250 units from location 00000113774360000 If you picked 250 units from location 00...
-
Exercise 3-4A (Algo) Determining variable cost from incomplete cost data LO 3-1 Gibson Corporation produced 205,000 watches that it sold for $21 each during year 2. The company determined that fixed...
-
How would applying the core values and concepts of the Baldrige Performance Excellence Framework improve healthcare quality?
-
1. How does kaizen differ from a kaizen event? 2. What is the Deming Cycle? Explain the activities that make up each step.
-
3. Implement F (X,Y,Z) = XY'ZYZ' by using a) Only one 8-to-1 MUX. b) Only one 4-to-1 MUX. 4. Realize (a,b,c,d) = m(0, 4, 5, 9, 13, 14, 15) with a 8-to-1 Mux. a) Consider a, b, c as selection lines...
-
1.Pink describes several "frames" including: The Less Frame, The Experience Frame, The Label Frame, The Blemished Frame, The Potential Frame. Which frame is your favorite? Why? Give a specific...
-
Rise of the hackers essay prompt question Do you think you might be vulnerable to the social engineering attack that hackers used against Matt in the video?
-
Amanda purchases a 7.5% annual coupon bond with a face value of 1,000 to yield a minimum interest rate of 8% effective. The bond is a callable corporate bond, with a call provision of 1050, (this...
-
An annual report of The Campbell Soup Company reported on its income statement $2.4 million as equity in earnings of affiliates. Journalize the entry that Campbell would have made to record this...
-
If an auditor identifies an internal control weakness for an assertion, how does it affect the audit strategy? If the auditor identifies an internal control strength for an assertion, how does it...
-
The key documents involved in recording a purchase adjustment involve: a. A purchase return authorization, a shipping report, and a debit memo. b. A vendors invoice, a receiving report, and a credit...
-
Auditors of publicly traded companies are required to perform a(an) ________ for their clients. a. Compliance audit b. Integrated audit c. Internal audit d. Operational audit
-
A company purchased $30,000 of 5% bonds for investment purposes on May AppendixLO1
-
On a balance sheet, what valuation must be reported for shortterm investments in trading securities? AppendixLO1
-
On a balance sheet, what valuation must be reported for debt securities classified as available-for-sale? AppendixLO1
![Mobile App Logo](https://dsd5zvtm8ll6.cloudfront.net/includes/images/mobile/finalLogo.png)
Study smarter with the SolutionInn App