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...
-
Suppose in its income statement for the year ended June 30, 2014, The Clorox Company reported the following condensed data (dollars in millions). Instructions (a) Prepare a multiple-step income...
-
A heat engine that operates on a Carnot cycle uses a lowtemperature reservoir at \(25^{\circ} \mathrm{C}\) and runs at an efficiency of 0. 350 . The working substance is nitrogen gas, and the gas...
-
Examine the collaborative filtering mechanism used by an online shopping system. How effective do you think it is? Might there be more effective methods to achieve the same goal? What kinds of...
-
For a recent 2-year period, the balance sheet of Santana Dotson Company showed the following stockholders equity data at December 31 in millions. Instructions (a) Answer the following questions. (1)...
-
Media and advertising companies still use the same old demographics to understand audiences, but they're becoming increasingly harder to track online, says media researcher Johanna Blakley. As social...
-
Car Armour sells car wash cleaners. Car Armour uses a perpetual inventory system and made purchases and sales of a particular product in 2023 as follows: Required 1. Calculate the total goods...
-
Computation Theory ExplainShow that the function giving the next state of a register machine in terms of the current state is primitive recursive. (You may assume the existence of primitive recursive...
-
When a two-degree-of-freedom system is subjected to a harmonic force, the system vibrates at the a. frequency of applied force b. smaller natural frequency c. larger natural frequency
-
The fundamental natural frequency of a system is a. the largest value b. the smallest value c. any value
-
A_______________ is useful when the data consist of values measured at different points in time. In Exercises 36, fill in each blank with the appropriate word or phrase.
-
In a back-to-back stem-and-leaf plot, each of the two data sets plotted must have the same _______________. In Exercises 36, fill in each blank with the appropriate word or phrase.
-
True or False. The characteristics of a two-degree-of-freedom system are used in the design of dynamic vibration absorber.
-
Three charges are arranged in straight line as in the figure below. The left most charge is +10.0 nC. The distance between the left and middle charge is 2.00 cm. The third charge has an unknown...
-
Wholesalers Ltd. deals in the sale of foodstuffs to retailers. Owing to economic depression, the firm intends to relax its credit policy to boost productivity and sales. The firms current credit...
-
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 are the two key financial objectives in the management of a company? How can a focus on these objectives create ethical dilemmas?
-
Briefly explain the difference between accounting, finance, and engineering economics. Try to put the concepts in your own (or your team's) words and compare the concepts where appropriate.
-
Among your colleagues in class, identify a term or phrase italicized in this chapter that you think is the most significant from your reading. Absent team consensus, then just provide your...
Study smarter with the SolutionInn App