dwcustomerCUS_CODE INT(11)CUS_LNAME VARCHAR(15)CUS_FNAME VARCHAR(15)CUS INITIAL CHAR (1)CUS_STATE CHAR(2)OREG_ID INT(11)IndexesdwdaysalesfactTM_ID INT (11)CUS_CODE INT(11)P_CODE VARCHAR(10)SALE UNITS INT (11)SALE PRICE DECIMAL (10,2)Indexes11dwregionREG_ID INT(11)OREG_NAME VARCHAR(10)IndexesdwtimeTM_ID INT (11)TM_YEAR INT(11)TM_MONTH INT (11)TM_DAY INT (11)TM_QTR INT(11)IndexesdwproductPP_CODE VARCHAR (10)OP_DESCRIPT VARCHAR(35)OP_CATEGORY VARCHAR(5)OV_CODE INT (11)IndexesdwvendorPV_CODE INT (11)OV NAME VARCHAR(35)OV_AREACODE CHAR(3)OV_STATE CHAR(2)Indexes Using Group By with Rollup, write & run the SQL command using MySQL workbench to list the total sales by customer, month and product, with subtotals by customer and by month and a grandtotal for all product sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.CUS_CODE TM_MONTH10018100181001810018100181001810019100191001999999HALLJ99ĆHALLP_CODE2238/QPD 38.9523109-HB 9.9554778-2T 9.98PVC23DRT 70.44129.32129.321546-QQ2 39.9539.9539.952252.06NULLHULLTotalSalesHALLMILL

The question states :Using Group By with Rollup, write & run the SQL command using MySQL…

Using Group By with Rollup, write & run the SQL command using MySQL workbench to list the total sales by customer, month and product, with subtotals by customer and by month and a grand total for all product sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.

Using Group By with Rollup, write & run the SQL command using MySQL workbench to list the total sales by customer, month and product, with subtotals by customer and by month and a grand total for all product sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: Find the title name of the latest published book, the name of its author, and the publisher's name.…

A: 5. SELECT T.title_name, A.au_fname, A.au_lname, P.pub_name FROM Titles T, Authors A, Publishers P,…

Q: I need help with the SQL query for the following question: 1. Get top 5 list of products liked in…

A: QUERY: Creating table: CREATE TABLE Reviews( ProductModelName text, ProductCategory text,…

Q: Write the following SQL Statements: 1. Write a SELECT statement that returns one row for each…

A: 1.

Q: Use SQL commands to finish the questions. Please show BOTH SQL commands and results in your answers.…

A: Hi.. Check below for answer query. Here i give a correct query if you already have table and…

Q: Write the SQL statement that shows the count of invoices, the highest invoice total, the lowest…

A: Since schema of the table is not mentioned, I am assuming that table has invoices column. The query…

Q: Write a SELECT statement that returns two columns based on the Vendors table. The first column,…

Q: MarketPoint Sales currently has a credit limit of $5,000. Because MarketPoint Sales has an excellent…

A: Yes, the given statement is true.

Q: USE MICROSOFT SQL SERVER Using Adventureworks2017: Create a query listing the (productModel…

A: Answer is given below-

Q: Design a query of Stores with the following: Store Name, Store Size, Sum of order amounts, Percent…

A: JOIN Based on a common column between two or more tables, a JOIN clause is used to merge rows from…

Q: Please write the SQL to return the sale revenue (top line & bottom line) by Marketing channel for…

A: Here have to determine about sql query for given problem statements.

Q: help with sql class 2 questions 1. Report the names of all authors and the title_name of the…

A: Let's see the answer:

Q: Write the SQL command to find any tutors who have not submitted a report for July.

A: Here we need the two relation Match History and Tutor Report. We need to use Operator NOT IN to…

Q: The Driver Relationship team wants to arrange workshops and education materials to the drivers.…

A: The Driver Relationship team wants to arrange workshops and education materials to the drivers.…

Q: I need help with the SQL query for the following question: 1. Get the total number of reviews of…

A: The details about the table are given with table name and different entities. Here the table name is…

Q: The number of times the table structure has been altered by SQL queries. Which major changes need to…

A: Yes, by tracking modifications in the history table and using SQL commands, you can keep a record of…

Q: Here is some information about the size of two tables: Table 1: 10 columns 100,000 rows Table 2:…

A: - We need to give the maximum number of rows and columns from union of tge tables provided.

Q: How many different SQL queries do you think changed the table's structure? Which substantial…

A: The answer is given below step.

Q: Based on the picture please write an SQL theory for the following (DONT ANSWER THE ACTUAL QUESTION):…

A: The SQL queries for 3 questions are given below Join is being used...

Q: Display customer ID, customer name, and the order ID and the order date of all orders for customer…

A: 1- In this assignment requires, select, where clause, aggregate function, group by, and order by.…

Q: WEEK 9 SQL ASSIGNMENT Part I: Complete the following exercise: Create the following table with…

A: As per our guidelines, we are supposed to answer only 1st three parts. Kindly repost the remaining…

Q: Show the total number of reviews of most liked product per retailer in every city Here is the…

A: The details about the table are given with table name and different entities. Here the table name is…

Q: Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle,…

A: SELECT Statement:The query begins with the SELECT keyword, indicating that we want to retrieve…

Q: Write the SQL code to generate the total hours worked and the total charges made by all employees.…

A: See the explanation section for a complete answer.Explanation:To calculate the total hours worked…

Q: Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases,…

A: Here we have to write an SQL query to have the given table/list of data in the fig. P7.12, Key…

Q: Using the EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to…

A: The question is asking for an SQL command that will add two new columns to an existing table named…

Q: Write a SQL query. List names of all employees who have sold to customer Rachel Patterson (Note: You…

A: In this database problem, we are tasked with creating an SQL query that lists the names of all…

Q: Display the customer number, customer name, order number, and order date for each order. Sort the…

A: solution is given below

Concept explainers

Multiple Table

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.

Question

dwcustomer
CUS_CODE INT(11)
CUS_LNAME VARCHAR(15)
CUS_FNAME VARCHAR(15)
CUS INITIAL CHAR (1)
CUS_STATE CHAR(2)
OREG_ID INT(11)
Indexes
dwdaysalesfact
TM_ID INT (11)
CUS_CODE INT(11)
P_CODE VARCHAR(10)
SALE UNITS INT (11)
SALE PRICE DECIMAL (10,2)
Indexes
1
1
dwregion
REG_ID INT(11)
OREG_NAME VARCHAR(10)
Indexes
dwtime
TM_ID INT (11)
TM_YEAR INT(11)
TM_MONTH INT (11)
TM_DAY INT (11)
TM_QTR INT(11)
Indexes
dwproduct
PP_CODE VARCHAR (10)
OP_DESCRIPT VARCHAR(35)
OP_CATEGORY VARCHAR(5)
OV_CODE INT (11)
Indexes
dwvendor
PV_CODE INT (11)
OV NAME VARCHAR(35)
OV_AREACODE CHAR(3)
OV_STATE CHAR(2)
Indexes

Using Group By with Rollup, write & run the SQL command using MySQL workbench to list the total sales by customer, month and product, with subtotals by customer and by month and a grand
total for all product sales. Scroll the results to the bottom to show the GRAND TOTAL and the PREVIOUS SUB-TOTAL. Use the image below as a reference for a correct solution.
CUS_CODE TM_MONTH
10018
10018
10018
10018
10018
10018
10019
10019
10019
9
9
9
9
9
HALL
J
9
9
Ć
HALL
P_CODE
2238/QPD 38.95
23109-HB 9.95
54778-2T 9.98
PVC23DRT 70.44
129.32
129.32
1546-QQ2 39.95
39.95
39.95
2252.06
NULL
HULL
Total
Sales
HALL
MILL

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1: Explain the question and query

VIEW

Step 2: Define the query and its structures

VIEW

Solution

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

I need help with the SQL query for the following question: 1. Get a list of 10 product reviews that got review rating 5 and price more than thousand. Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours
Write a sample SQL query to look at data by week for the created_at date, where you're summing revenue by country and state.
Task 2: The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows: MASTER: more than 4 travels PRO: more than 2 travels ROOKIE: 2 or less travels In addition, run the function to verify it works as expected and send them back the driver levels. SQL Database Test: SELECT DRIVER_ID, DRIVER_STATUS(DRIVER_ID) FROM DRIVERS Create the DRIVER_STATUS function Expected Results: DRIVER_ID DRIVER_STATUS(DRIVER_ID) 2001 MASTER 2002 ROOKIE 2003 PRO 2004 ROOKIE 2005 ROOKIE
With SQL code, find the name of each employee with a manager along with their manager's name (use cartesian product and rename) Employee table: create table employee( ID varchar(6) not null,person_name varchar(10),street varchar(10),city varchar(10),primary key (ID)); Manages table: create table manages(ID varchar(6) not null,manager_id varchar(6),primary key (ID),foreign key (ID) references employee,foreign key (manager_id) references employee);
How do I make space on the Header on SQL? please see image.Thank you. SELECT BRAND_NAME, BRAND_TYPE, round(AVERAGE,2) AS 'Average Price', UnitsSoldFROM (LGBRAND INNER JOIN (SELECT BRAND_ID, AVG(PROD_PRICE) AS AVERAGE FROM LGPRODUCT GROUP BY BRAND_ID) AS SUB1 ON LGBRAND.BRAND_ID = SUB1.BRAND_ID) INNER JOIN (SELECT BRAND_ID, SUM(LINE_QTY) AS UNITSSOLD FROM LGPRODUCT INNER JOIN LGLINE ON LGPRODUCT.PROD_SKU = LGLINE.PROD_SKU GROUP BY BRAND_ID) AS SUB2 ON LGBRAND.BRAND_ID = SUB2.BRAND_IDORDER BY BRAND_NAME
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to retrieve the product name and price whose weight is between 15.0 (inclusive) and 20.0 (inclusive) and whose style is "Modern".
In Visual basics explain the meaning of the listed SQL commands : (a)ORDER BY (b)INNER JOIN (c)INSERT (d) ALTER TABLE
I need help with this SQL query: Write a MySQL statement to create a table named jobs including columns job_id, job_title, min_salary and max_salary, and check whether the min_salary amount is lower than the lower limit $15,000 and whether the max_salary amount exceeds the upper limit $50,000.
Write following SQL Queries:1. List the order number, order date, customer number, customer name (first and last), employee number, and employeename (first and last) of January 2017 orders placed by Colorado customers.2. List the customer number, name (first and last), order number, order date, employee number, employee name (first andlast), product number, product name, and order cost (OrdLine.Qty * ProdPrice) for products ordered on January 23,2017, in which the order cost exceeds $150.3. List the order number and total amount for orders placed on January 23, 2017. The total amount of an order is the sumof the quantity times the product price of each product on the order.4. List the order number, order date, customer name (first and last), and total amount for orders placed on January 23,2017. The total amount of an order is the sum of the quantity times the product price of each product on the order.
I need help with the SQL query for the following question: 1. Get a list of 5 reviews for shoppers in Chicago Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
Based on SQL information