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; SUM function: The “SUM” function is used to add the value and then return the sum of the expression in a “SELECT” statement. Syntax: SELECT SUM (exp) FROM table_Name [WHERE condition]; 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;

Question

Modify the query in Problem 13 to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) Your output values must match those shown in Figure P7.14, sorted by customer code.

Answer 1

Question

Chapter 7, Problem 30P

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;

SUM function:

The “SUM” function is used to add the value and then return the sum of the expression in a “SELECT” statement.

Syntax:

SELECT SUM (exp) FROM table_Name [WHERE condition];

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;

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Students have asked these similar questions

Modify the query in Problem 13 to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) Your output values must match those shown in Figure P7.14, sorted by customer code.

(A) Write a query which shows the employee IDs that are unique to the employee table. Order the employee IDs in descending order. An employee ID Is the same in both tables if the integer value of the ID matches. (B) We want to add a new column to the employee table. We want to provide a new column with a more complete phone number. Right now the PHONENO column only shows the last 4 digits. We want a new column which is called PHONE and consists of ###-###-####. The last 4-digits are already in the PHONENO column. The first three digits should be 416 and the next three should be 123. To improve clarity in the table, we also want to rename the PHONENO column to PHONEEXT. Show all the commands used to accomplish this, then, select all data for employees who have the last name of 'smith' (case insensitive). (C) Show a list of employee id, names, department, years and job of any employee in the staff table who makes a total amount more than their manager or has more years of service than…

All the queries should be executed in MySQL and Screen Shot has to be attached for each question.