Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
8- Write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all the customer purchase amounts. The output must match Figure below. Total Invoices Total Sales Minimum Customer Purchases Largest Customer Purchases Average Customer Purchases 8 1126.03 34.97 444.00 225.21

8- Write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all the customer purchase amounts. The output must match Figure below. Total Invoices Total Sales Minimum Customer Purchases Largest Customer Purchases Average Customer Purchases 8 1126.03 34.97 444.00 225.21

Database System Concepts
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
icon
Related questions
Question

SELECT COUNT(INV_NUMBER) AS 'Total Invoices', SUM(TOTAL_PRICE) AS 'Total Sales', MIN(TOTAL_PRICE) AS 'Minimum Customer Purchases', MAX(TOTAL_PRICE) AS 'Largest Customer Purchases', ROUND(AVG(TOTAL_PRICE), 2) AS 'Average Customer Purchases'
FROM (SELECT CUS_CODE, LINE.INV_NUMBER AS INV_NUMBER, ROUND(SUM(LINE.LINE_PRICE * LINE.LINE_UNITS), 2) AS TOTAL_PRICE
FROM INVOICE, LINE
WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
GROUP BY CUS_CODE, LINE.INV_NUMBER
ORDER BY CUS_CODE, LINE.INV_NUMBER)
INVOICELINE;

 

 

i use this code but it didn't show the same value as in the question

8- Write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all the customer purchase amounts. The output must match Figure below. Total Invoices Total Sales Minimum Customer Purchases Largest Customer Purchases Average Customer Purchases 8 1126.03 34.97 444.00 225.21
Transcribed Image Text:8- Write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all the customer purchase amounts. The output must match Figure below. Total Invoices Total Sales Minimum Customer Purchases Largest Customer Purchases Average Customer Purchases 8 1126.03 34.97 444.00 225.21
Given a small company database tables below. Create the database and tables for the company and answer all the questions below. LINE CUSTOMER VENDOR INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Cus CODE CUS_LNAME CUS_FNAME CUS_INITIAL cus AREACODE CUS_PHONE CUS_BALANCE V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER 1000 3 23100-HB 0.05 10010 Ramas Afred 015 844-2573 21225 Bryson, Ine. Smithson 015 223-3234 TN 1006 4 89-WRE-Q 256.00 10011 Dunne Leona 713 804-1238 21220 SuperLoo, Inc. Flushing 04 215-8006 FL N 1 13-Q2/P2 2 14.00 1007 10012 Smith Kathy 015 804-2205 345.88 21231 DSE Supply Singh 815 228-3246 TN 1007 254778-2T 4.00 10013 Olowski Paul 815 804-2100 530.75 21344 Gomez Bros. Ortega 815 889-2546 KY N 1008 1 PVC23DRT 5.87 10014 Orlando Myron le15 222-1872 22507 Dome Supply Smith 01 e78-1419 GA IN 1008 2 WR3/TT3 3 110.05 10015 OBrian Amy 713 442-3381 23110 Randsets Ltd. Anderson 001 078-3008 GA 1008 3 23100-HB 0.05 10010 Brown James 015 297-1228 221.19 24004 Brackman Bros. Browning 615 228-1410 TN IN 10017 Wiliams George 015 290-2556 788.9 24288 JORDVA, Inc. Hakford 815 e08-1234 TN PRODUCT 10018 Fariss Anne 713 382-7185 25443 BAK, Inc. 904 227-0003 FL 216.55 Smith P_CODE P_DESCRIPT P_INDATE P_QOHP_MIN P_PRICE P_DISCOUNT V_CODE 10019 Smith 015 207-3800 25501|Damal Supplies Smythe 015 e90-3529 TN IN Olette 11QER/31 Power painter, 15 psi, 3-nozzle 03-Nov-17 100.00 0.00 25505 25505 Rubicon Systems Orton 904 450-0002 FL 13-02/P2 7.25-in. pwr. saw blade 13-Dec-17 32 15 14.00 0.05 21344 INVOICE 14-Q1/L3 9.00-in. pwr. saw blade 13-Nov-17 18 12 17.40 0.00 21344 LINE INV_NUMBER CUS_CODE INV_DATE 1548-002 Hrd. cloth, 14-in. 2x50 0.00 23110 15-Jan-18 15 30.95 INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE 1001 10014 10-Jan-18 1558-QW1 Hrd. cloth. 1/2-in. 3x50 0.00 23119 15-Jan-18 23 43.00 1001 1 13-Q2/P2 14.00 1 1002 10011 10-Jan-18 2232/QTY BAD igsaw, 12-in, blade 30-Dec-17 100.02 8 0.05 24288 1001 2 23100-HB 1 9.05 1003 10012 16-Jan-18 2232/QWE BAD jgsaw, B-in. blade 24-Deo-17 00.87 0.05 24288 1002 154778-2T 4.00 1004 10011 17-Jan-18 2238/OPD B&D cordless drill, 1/2-in. 20-Jan-18 12 38.95 0.05 25505 1003 1 2238/QPD 38.05 1005 10018 17-Jan-18 23100-HB Ciaw hammer 20-Jan-18 23 10 9.05 0.10 21225 1003 2 1546-Q02 1 39.05 1000 10014 17-Jan-18 23114-AA Sledge hammer, 12 Ib. 02-Jan-18 8 14.4 0.05 1003 3 13-02/P2 5 14.90 1007 10015 17-Jan-18 54778-2T Rat-tail fie. 1/8-in. fine 15-Dec-17 4.90 1004 154778-2T 3. 4.00 43 20 0.00 21344 1008 10011 17-Jan-18 9.05 80-WRE-Q Hicut chain saw, 10 in. 07-Feb-18 1004 2 23109-HB 2 11 256.00 0.05 24288 1005 1 PVC23DRT 12 PVC23DRT PvC pipe. 3.5-in., 8-A 20-Feb-18 188 75 5.87 0.00 5.87 PRODUCT 1006 1 SM-18277 3 0.00 SM-18277 1.25-in. metal screw, 25 01-Mar-18 0.00 21225 172 75 6.00 P CODE P DESCRIPT PINDATE P_QOH P_MIN P_PRICEP_DISCOUNT V_CODE 1006 2 2232/QTY 1 109.92 sw-23116 2.5-in, wd. screw. 50 24-Feb-18 237 100 8.45 0.00 21231 WR3TT3 Steel matting. 4x8x1/8". 5" mesh 17-Jan-18 18 5 119.95 0.10 25606 25595
Transcribed Image Text:Given a small company database tables below. Create the database and tables for the company and answer all the questions below. LINE CUSTOMER VENDOR INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Cus CODE CUS_LNAME CUS_FNAME CUS_INITIAL cus AREACODE CUS_PHONE CUS_BALANCE V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER 1000 3 23100-HB 0.05 10010 Ramas Afred 015 844-2573 21225 Bryson, Ine. Smithson 015 223-3234 TN 1006 4 89-WRE-Q 256.00 10011 Dunne Leona 713 804-1238 21220 SuperLoo, Inc. Flushing 04 215-8006 FL N 1 13-Q2/P2 2 14.00 1007 10012 Smith Kathy 015 804-2205 345.88 21231 DSE Supply Singh 815 228-3246 TN 1007 254778-2T 4.00 10013 Olowski Paul 815 804-2100 530.75 21344 Gomez Bros. Ortega 815 889-2546 KY N 1008 1 PVC23DRT 5.87 10014 Orlando Myron le15 222-1872 22507 Dome Supply Smith 01 e78-1419 GA IN 1008 2 WR3/TT3 3 110.05 10015 OBrian Amy 713 442-3381 23110 Randsets Ltd. Anderson 001 078-3008 GA 1008 3 23100-HB 0.05 10010 Brown James 015 297-1228 221.19 24004 Brackman Bros. Browning 615 228-1410 TN IN 10017 Wiliams George 015 290-2556 788.9 24288 JORDVA, Inc. Hakford 815 e08-1234 TN PRODUCT 10018 Fariss Anne 713 382-7185 25443 BAK, Inc. 904 227-0003 FL 216.55 Smith P_CODE P_DESCRIPT P_INDATE P_QOHP_MIN P_PRICE P_DISCOUNT V_CODE 10019 Smith 015 207-3800 25501|Damal Supplies Smythe 015 e90-3529 TN IN Olette 11QER/31 Power painter, 15 psi, 3-nozzle 03-Nov-17 100.00 0.00 25505 25505 Rubicon Systems Orton 904 450-0002 FL 13-02/P2 7.25-in. pwr. saw blade 13-Dec-17 32 15 14.00 0.05 21344 INVOICE 14-Q1/L3 9.00-in. pwr. saw blade 13-Nov-17 18 12 17.40 0.00 21344 LINE INV_NUMBER CUS_CODE INV_DATE 1548-002 Hrd. cloth, 14-in. 2x50 0.00 23110 15-Jan-18 15 30.95 INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE 1001 10014 10-Jan-18 1558-QW1 Hrd. cloth. 1/2-in. 3x50 0.00 23119 15-Jan-18 23 43.00 1001 1 13-Q2/P2 14.00 1 1002 10011 10-Jan-18 2232/QTY BAD igsaw, 12-in, blade 30-Dec-17 100.02 8 0.05 24288 1001 2 23100-HB 1 9.05 1003 10012 16-Jan-18 2232/QWE BAD jgsaw, B-in. blade 24-Deo-17 00.87 0.05 24288 1002 154778-2T 4.00 1004 10011 17-Jan-18 2238/OPD B&D cordless drill, 1/2-in. 20-Jan-18 12 38.95 0.05 25505 1003 1 2238/QPD 38.05 1005 10018 17-Jan-18 23100-HB Ciaw hammer 20-Jan-18 23 10 9.05 0.10 21225 1003 2 1546-Q02 1 39.05 1000 10014 17-Jan-18 23114-AA Sledge hammer, 12 Ib. 02-Jan-18 8 14.4 0.05 1003 3 13-02/P2 5 14.90 1007 10015 17-Jan-18 54778-2T Rat-tail fie. 1/8-in. fine 15-Dec-17 4.90 1004 154778-2T 3. 4.00 43 20 0.00 21344 1008 10011 17-Jan-18 9.05 80-WRE-Q Hicut chain saw, 10 in. 07-Feb-18 1004 2 23109-HB 2 11 256.00 0.05 24288 1005 1 PVC23DRT 12 PVC23DRT PvC pipe. 3.5-in., 8-A 20-Feb-18 188 75 5.87 0.00 5.87 PRODUCT 1006 1 SM-18277 3 0.00 SM-18277 1.25-in. metal screw, 25 01-Mar-18 0.00 21225 172 75 6.00 P CODE P DESCRIPT PINDATE P_QOH P_MIN P_PRICEP_DISCOUNT V_CODE 1006 2 2232/QTY 1 109.92 sw-23116 2.5-in, wd. screw. 50 24-Feb-18 237 100 8.45 0.00 21231 WR3TT3 Steel matting. 4x8x1/8". 5" mesh 17-Jan-18 18 5 119.95 0.10 25606 25595
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 1 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Functions
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
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS