Write a query that will return sales details of all customers and products. The query should...

   
 

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

Answer rating: 100% (QA)

SELECT CASE WHEN ccustomername IS NULL THEN NA ELSE ccustomername END AS customername iinvoiceid CAS... View the full answer