# Using the output shown in Figure P7.29 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. ( Hint: Modify the query format used to produce the list of customer purchases in Problem 28, delete the INV_DATE column, and add the derived attribute LINE_UNITS * LINE_PRICE to calculate the subtotals.

### Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482

Chapter
Section

### Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482
Chapter 7, Problem 29P
Textbook Problem
314 views

## Using the output shown in Figure P7.29 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. (Hint: Modify the query format used to produce the list of customer purchases in Problem 28, delete the INV_DATE column, and add the derived attribute LINE_UNITS * LINE_PRICE to calculate the subtotals.

Program Plan Intro

DISTINCTROW Keyword:

The “DISTINCTROW” keyword omits data based entire duplicate records, not like just duplicate fields. The syntax for “DISTINCT” keyword is as follows:

Syntax:

SELECT DISTINCTROWcol_Name FROM table_Name1 INNER JOIN table_Name2 ON table_Name1.col_Name = table_Name2.col_Name ORDER BY table_Name1;

GROUP BY Clause:

The GROUPBY clause is used to group the result of a SELECT statement done on a table where the tuple values are similar for more than one column

Syntax:

SELECT expression1, expression2, expression_n, aggregate_function(expression)FROM table_name WHERE conditions GROUP BY expression1, expression2, expression_n;

INNER JOIN keyword:

“INNER JOIN” keyword is used to select all the matching records of both the table.

Syntax:

SELECT col_Name FROM table_Name1 INNER JOIN table_Name2 ON table_Name1.col_Name = table_Name2.col_Name;

### Explanation of Solution

SQL code:

The SQL code to generate a list of customer purchases that includes the subtotals for every invoice line number is given below:

SELECT DISTINCTROW INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT, LINE.LINE_UNITS, LINE.LINE_PRICE, [LINE]![LINE_UNITS]*[LINE]![LINE_PRICE] AS Expr1 FROM CUSTOMER INNER JOIN (INVOICE INNER JOIN (PRODUCT INNER JOIN LINE ON PRODUCT.P_CODE = LINE.P_CODE) ON INVOICE.INV_NUMBER = LINE.INV_NUMBER) ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE GROUP BY INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT, LINE.LINE_UNITS, LINE...

### Still sussing out bartleby?

Check out a sample textbook solution.

See a sample solution

#### The Solution to Your Study Problems

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

Get Started

Find more solutions based on key concepts
What are the basic characteristics of a NoSQL database?

Database Systems: Design, Implementation, & Management

What type of toolholder might he selected for mounting a slitting saw?

Precision Machining Technology (MindTap Course List)

For Problem 19.27, determine the probability (assuming normal distribution) that a car would need engine mainte...

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

Describe the seven basic steps for logical diagnosis.

Automotive Technology: A Systems Approach (MindTap Course List)

Identify and briefly describe five common fact-finding methods.

Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)

How might you know if you are addicted to computers or suffer from technology overload?

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)

How can discontinuities in the root face be removed?

Welding: Principles and Applications (MindTap Course List)