Write a query that will return sales details of all customers and products. The query should...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Write a query that will return sales details of all customers and products. The query should return all customers, even customers without invoices and also all products, even those products that were not sold. Print "N/A" for a null customer or product name, and 0 for a null quantity. For each row return customer name, product name, and the quantity of the product sold. Order the result ascending by customer id, product id and invoice item id. Table definitions and a data sample are given below. ▼ Schema Table: customer column name id customer_name city_id customer_address contact person email phone Table: product id column name sku product_name current_price column type quantity in stock int varchar(255) int int product_description. text varchar(255) varchar(255) N varchar(128) varchar(128) varchar(32) varchar(128) column type key/NULL key/NULL decimal(8,2) PK int FK PK Table: invoice column name id invoice_number varchar(255) customer_id Table: invoice_item column type user_account_id int column name int id invoice_id product_id quantity price int total_price decimal(8,2) time_issued varchar(255) N time_due varchar(255) N time_paid varchar(255) N time_canceled varchar(255) N time_refunded varchar(255) N invoice.customer_id references customer.id int key / NULL int PK column type key / NULL int FK PK FK FK decimal(8,2) decimal(8,2) line_total_price decimal(8,2) invoice_item.invoice_id references invoice.id invoice_item.product_id references product.id Sample Data Tables Table: customer id 1 Drogerie Wien 2 4 3 Kosmetikstudio 5 6 7 8 9 id 1 2 3 4 5 6 Table: product 7 customer_name 8 9 Cosmetics Store 10 Neue Kosmetik Bio Kosmetik K-Wien Natural Cosmetics Kosmetik Plus New Line Cosmetics sku 330120 330121 330122 330124 330125 330126 330127 city_id 330128 1 4 3 1 2 1 4 2 4 Advanced Night Repair - ESTEE LAUDER Rose Deep Hydration - FRESH 330123 Pore-Perfecting Moisturizer - TATCHA customer_address Deckergasse 15A Watling Street 347 Rothenbaumchaussee 53 Karlsplatz 2 Capture Youth - DIOR Motzstraße 23 Kärntner Straße 204 Clerkenwell Road 14B product_name 330129 Silk Pillowcase - SLIP Unter den Linden 1 Devonshire Street 92 Game Of Thrones - URBAN DECAY Slice of Glow - GLOW RECIPE Healthy Skin - KIEHLS SINCE 1851 Power Pair! - IT COSMETICS Dewy Skin Mist - TATCHA contact person Emil Steinbach Jeremy Corbyn Willy Brandt NULL Clara Zetkin Maria Rauch-Kallat Glenda Jackson Angela Merkel Oliver Cromwell Slice of Glow Set email emil@drogeriewien.com jeremy@c-store.org willy@kosmetikstudio.com info@neuekosmetik.com clara@biokosmetik.org maria@kwien.org glena.j@natural-cosmetics.com angela@k-plus.com oliver@nic.org product_description Game Of Thrones Eyeshadow Palette Advanced Night Repair Synchronized Recovery Complex II Rose Deep Hydration Facial Toner Pore-Perfecting Moisturizer & Cleanser Duo Capture Youth Serum Collection Silk Pillowcase Duo + Scrunchies Kit phone 094234234 093923923 0941562222 094109253 093825825 093427002 093555123 094727727 093202404 Healthy Skin Squad IT is Your Skincare Power Pair! Best-Selling Moisturizer & Eye Cream Duo Limited Edition Dewy Skin Mist Mini current_price quantity_in_stock 65 98 45 25 95 45 68 80 20 170 122 51 34 393 74 40 154 0 281 0 Table: invoice id 1 2 8fba0000fd456b27502b9f81e9d52481 3 3b6638118246b6bcfd3dfcd9be487599 4 dfe7f0a01a682196cac0120a9adbb550 5 2a24cc2ad4440d698878a0a1a71f70fa 6 cbd304872ca6257716bcab8fc43204d7 id Table: invoice_item 1 2 3 4 5 in 25181b07ba800c8d2fc967fe991807d9 6 7 00 8 9 1 invoice_number 1 1 3 4 4 4 invoice_id product_id quantity price 5 6 1 7 5 10 1 2 5 10 4 20 2 10 2 5 10 4 100 6 The first line of the result should be: customer_name product_name quantity N/A Rose Deep Hydration - FRESH O 65 9 68 customer_id user_account_id: total_price 3 65 7 95 5 6 100 4 180 25 100 95 1300 136 1000 360 325 line_total_price 950 400 9500 4 150 2 2 2 2 2 1436 1000 360 1675 9500 150 time_issued 7/20/2019 3:05:07 PM 7/20/2019 3:07:11 PM 7/20/2019 3:06:15 PM 7/20/2019 3:06:34 PM 7/20/2019 3:06:42 PM 7/20/2019 3:08:15 PM time due time_paid time_canceled 7/27/2019 7/25/2019 NULL 3:05:07 PM 9:24:12 AM 7/27/2019 3:07:11 PM 7/27/2019 3:06:15 PM 7/27/2019 3:06:34 PM 7/20/2019 NULL 3:10:32 PM 7/31/2019 9:22:11 PM NULL 7/27/2019 NULL 3:06:42 PM 7/27/2019 7/27/2019 3:08:15 1:42:45 PM PM NULL NULL 7/22/2019 11:17:02 AM NULL time refund NULL NULL NULL NULL NULL 7/27/2019 2:11:20 PM Write a query that will return sales details of all customers and products. The query should return all customers, even customers without invoices and also all products, even those products that were not sold. Print "N/A" for a null customer or product name, and 0 for a null quantity. For each row return customer name, product name, and the quantity of the product sold. Order the result ascending by customer id, product id and invoice item id. Table definitions and a data sample are given below. ▼ Schema Table: customer column name id customer_name city_id customer_address contact person email phone Table: product id column name sku product_name current_price column type quantity in stock int varchar(255) int int product_description. text varchar(255) varchar(255) N varchar(128) varchar(128) varchar(32) varchar(128) column type key/NULL key/NULL decimal(8,2) PK int FK PK Table: invoice column name id invoice_number varchar(255) customer_id Table: invoice_item column type user_account_id int column name int id invoice_id product_id quantity price int total_price decimal(8,2) time_issued varchar(255) N time_due varchar(255) N time_paid varchar(255) N time_canceled varchar(255) N time_refunded varchar(255) N invoice.customer_id references customer.id int key / NULL int PK column type key / NULL int FK PK FK FK decimal(8,2) decimal(8,2) line_total_price decimal(8,2) invoice_item.invoice_id references invoice.id invoice_item.product_id references product.id Sample Data Tables Table: customer id 1 Drogerie Wien 2 4 3 Kosmetikstudio 5 6 7 8 9 id 1 2 3 4 5 6 Table: product 7 customer_name 8 9 Cosmetics Store 10 Neue Kosmetik Bio Kosmetik K-Wien Natural Cosmetics Kosmetik Plus New Line Cosmetics sku 330120 330121 330122 330124 330125 330126 330127 city_id 330128 1 4 3 1 2 1 4 2 4 Advanced Night Repair - ESTEE LAUDER Rose Deep Hydration - FRESH 330123 Pore-Perfecting Moisturizer - TATCHA customer_address Deckergasse 15A Watling Street 347 Rothenbaumchaussee 53 Karlsplatz 2 Capture Youth - DIOR Motzstraße 23 Kärntner Straße 204 Clerkenwell Road 14B product_name 330129 Silk Pillowcase - SLIP Unter den Linden 1 Devonshire Street 92 Game Of Thrones - URBAN DECAY Slice of Glow - GLOW RECIPE Healthy Skin - KIEHLS SINCE 1851 Power Pair! - IT COSMETICS Dewy Skin Mist - TATCHA contact person Emil Steinbach Jeremy Corbyn Willy Brandt NULL Clara Zetkin Maria Rauch-Kallat Glenda Jackson Angela Merkel Oliver Cromwell Slice of Glow Set email emil@drogeriewien.com jeremy@c-store.org willy@kosmetikstudio.com info@neuekosmetik.com clara@biokosmetik.org maria@kwien.org glena.j@natural-cosmetics.com angela@k-plus.com oliver@nic.org product_description Game Of Thrones Eyeshadow Palette Advanced Night Repair Synchronized Recovery Complex II Rose Deep Hydration Facial Toner Pore-Perfecting Moisturizer & Cleanser Duo Capture Youth Serum Collection Silk Pillowcase Duo + Scrunchies Kit phone 094234234 093923923 0941562222 094109253 093825825 093427002 093555123 094727727 093202404 Healthy Skin Squad IT is Your Skincare Power Pair! Best-Selling Moisturizer & Eye Cream Duo Limited Edition Dewy Skin Mist Mini current_price quantity_in_stock 65 98 45 25 95 45 68 80 20 170 122 51 34 393 74 40 154 0 281 0 Table: invoice id 1 2 8fba0000fd456b27502b9f81e9d52481 3 3b6638118246b6bcfd3dfcd9be487599 4 dfe7f0a01a682196cac0120a9adbb550 5 2a24cc2ad4440d698878a0a1a71f70fa 6 cbd304872ca6257716bcab8fc43204d7 id Table: invoice_item 1 2 3 4 5 in 25181b07ba800c8d2fc967fe991807d9 6 7 00 8 9 1 invoice_number 1 1 3 4 4 4 invoice_id product_id quantity price 5 6 1 7 5 10 1 2 5 10 4 20 2 10 2 5 10 4 100 6 The first line of the result should be: customer_name product_name quantity N/A Rose Deep Hydration - FRESH O 65 9 68 customer_id user_account_id: total_price 3 65 7 95 5 6 100 4 180 25 100 95 1300 136 1000 360 325 line_total_price 950 400 9500 4 150 2 2 2 2 2 1436 1000 360 1675 9500 150 time_issued 7/20/2019 3:05:07 PM 7/20/2019 3:07:11 PM 7/20/2019 3:06:15 PM 7/20/2019 3:06:34 PM 7/20/2019 3:06:42 PM 7/20/2019 3:08:15 PM time due time_paid time_canceled 7/27/2019 7/25/2019 NULL 3:05:07 PM 9:24:12 AM 7/27/2019 3:07:11 PM 7/27/2019 3:06:15 PM 7/27/2019 3:06:34 PM 7/20/2019 NULL 3:10:32 PM 7/31/2019 9:22:11 PM NULL 7/27/2019 NULL 3:06:42 PM 7/27/2019 7/27/2019 3:08:15 1:42:45 PM PM NULL NULL 7/22/2019 11:17:02 AM NULL time refund NULL NULL NULL NULL NULL 7/27/2019 2:11:20 PM
Expert Answer:
Answer rating: 100% (QA)
SELECT CASE WHEN ccustomername IS NULL THEN NA ELSE ccustomername END AS customername iinvoiceid CAS... View the full answer
Related Book For
Database Processing Fundamentals, Design, and Implementation
ISBN: 978-0133876703
14th edition
Authors: David M. Kroenke, David J. Auer
Posted Date:
Students also viewed these databases questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
List three specific parts of the Case Guide, Objectives and Strategy Section (See below) that you had the most difficulty understanding. Describe your current understanding of these parts. Provide...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
You are looking at buying a piece of real estate and you intend to borrow as much as you possibly can from a bank to buy the property. The bank you are dealing with has a requirement that the LVR for...
-
If the minimum wage raises the market wage, w, but hours worked, L, fall as a result, total wage payments, wL, may rise or fall. Use supply and demand curves to show that either outcome is possible...
-
Two cards are drawn in succession from a deck of 52 cards (without replacement). Let \(D_{1}=\{\) diamond is drawn on the first draw\} and \(D_{2}=\) \{diamond is drawn on the second draw\}. Find the...
-
Consider a random walk \(\left\{y_{t} ight\}\) as the partial sum of a white noise process \(\left\{c_{t} ight\}\). a. Show that the \(l\)-step forecast error is...
-
In addition to the basic consumer rights expressed in the consumers Magna Carta, what other expectations do you think consumer stakeholders have of business?
-
Did Wendy's Twitter campaign increase the company's sales and profits? Use the customer journey to speculate about why and how it might or might not have an effect. (660 words max)
-
Alice has invented a new card game to play with Bob. Alice made a deck of cards with random values between 1 and 52. Bob picks 5 cards. Then, he has to rearrange the cards so that by utilizing the...
-
In order to complete each sentence, click and drag each word or phrase from the left into the appropriate blank on the right. (Some labels may not be used.) Then click and drag each sentence...
-
address the following: Which of your peer's recommendations did you find most compelling, surprising, or innovative? One important theme of this course is the need for most companies to innovate on a...
-
Describe and scope your map Explain the scope of the topic or knowledge domain you have chosen to map. Consider what's included and what's not included in your mind map scope (i.e. put a boundary...
-
An acid indicator may exist in two forms: acidic form (HIn) and basic form (In). Absorptivity values measured for these two forms are listed below. To determine its dissociation constant, K, a...
-
Initiating and implementing social determinants of health data collection in community health centers Links to an external site.. Population Health Management , https://doi.org/10.1089/pop.2019.0205...
-
who well can diffusion of innovation be for a company that is trying to succeed in this day and time? What is diffusion so important? How can it improve a patients experience. Provide in-text...
-
Unix System Administration Shell Programming Modifying & Deleting Users Given the input file active_cs.txt with the following layout generate an output file, modified_users.txt, that will contain the...
-
Using Gauss-Jordan elimination, invert this matrix ONLY 0 0 0 0 1
-
Suppose that two columns in two different tables have the same column name. What convention is used to give each a unique name?
-
Explain the three different sets of terms used to describe tables, columns, and rows.
-
Explain how views can be used to improve data security.
-
Differentiate between tax compliance and tax planning.
-
Differentiate between an open transaction and a closed transaction.
-
What form(s) is (are) used to file an individuals income tax return?
Study smarter with the SolutionInn App