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...
-
Case Development began operations in December 2011. When property is sold on an installment basis, Case recognizes installment income for financial reporting purposes in the year of the sale. For tax...
-
Why did many companies oppose FIN 48 when it was first proposed?
-
Distinguish between syntactics, semantics and pragmatics. Are these perspectives independent of each other or are they interwoven?
-
Sabina Company had the following two transactions related to its delivery truck. 1. Paid $38 for an oil change. 2. Paid $400 to install special shelving units, which increase the operating efficiency...
-
Journalize Payroll Tax The payroll register of Patel Engineering Co. indicates $906 of social security withheld and $226.50 of Medicare tax withheld on total salaries of $15,100 for the period....
-
Julio and Milania are owners of Falcons Corporation, an S corporation. They each own 50 percent of Falcons Corporation. In year 1, Julio and Milani received distributions of $20,000 and $10,000,...
-
What is the total present value of $1,000 received at the end of year 1, $1,200 received at the end of year 2, and $1,300 received at the end of year 3, assuming an opportunity cost of 7 percent?
-
You will write a report (4-5 pages) on any ONE of the following topics: a. Summarize the corporate social responsibility of any company of your choice. b. Evaluate three programs offering training...
-
Choose any alcohol substance discussed in this section on Substance Abuse. Consider the following scenario: You suspect that one of your client may be abusing this drug. Your goal is to educate...
-
During 2024, a company sells 20 units of inventory. The company has the following inventory purchase transactions for 2024: Date January 1 Transaction Beginning inventory September 8 Purchase Number...
-
Explain how do you cite the Donatos case study found in the textbook ?
-
Oslo Company prepared the following contribution format income statement based on a sales volume of 1,000 units (the relevant range of production is 500 units to 1,500 units): Sales Variable expenses...
-
When you utilize supply chain management techniques in construction, what is LEAST likely to happen? and why ?Explain
-
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.
-
What statutory and mandatory professional pronouncements require a review to be published?
-
List two items that may be found in the operating section and two items that may be found in the financial review section of an OFR.
-
What statutory and mandatory professional requirements require directors to submit a report to shareholders?
Study smarter with the SolutionInn App