3- Write a query to display the customer code, balance, and total purchases for each customer. Total purchase is calculated by summing the line subtotals (as in question 2) for each customer. Sort the results by customer code, and use aliases as shown below. CUS CODE CUS BALANCE| Tetal Purchases 444 00 153 05 422.77 34.97 TO 44 10011 10012 10014 600 345 86 O 00 10015 .00 10018 216 55 4- Modify the query in (question 3) 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.) The output values must match those shown in Figure below, sorted by customer code. CUS CODE CUS BALANCE Total Purchases | Number of Puurchasen 00 345 86 000 10011 10012 10014 10015 10018 444 00 163 85 422 77 3497 70 44 000 216 56 5- Use a query to compute the total of all purchases, the number of purchases, and the average purchase amount made by each customer. The output values must match those shown in Figure below. Sort the results by customer code. CUS CODE CUS BALANCE Total Purchases Number of Purchases Average Purchase Amount 74.00 51.28 70 46 17.48 10011 0.00 444.00 10012 345.86 153.85 10014 D.00 422.77 10015 0.00 216.55 34.97 2 10018 70.44 70.44
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
![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](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F5e1132c9-beab-4f38-876e-7cf6bf6331b8%2F6cdcdc62-a224-4f7c-88b9-ef785f972ad3%2Feyjcphq_processed.jpeg&w=3840&q=75)
![3- Write a query to display the customer code, balance, and total purchases for each customer. Total
purchase is calculated by summing the line subtotals (as in question 2) for each customer. Sort the
results by customer code, and use aliases as shown below.
CUS CODE CUS BALANCE | Tetal Purchases
O 00
345 86
0.00
O 00
216 55
10011
444.00
10012
10014
10015
10018
153.85
422.77
34 97
TO 44
4- Modify the query in (question 3) 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.) The output
values must match those shown in Figure below, sorted by customer code.
CUS CODE CUS BALANCE| Tot al Purchases Number of Penthases
10011
10012
10014
000
444 00
163 65
422.77
3497
70.44
345 86
000
10015
10018
216 56
5- Use a query to compute the total of all purchases, the number of purchases, and the average
purchase amount made by each customer. The output values must match those shown in Figure below.
Sort the results by customer code.
CUS_CODE CUS_BALANCE Total Purchases Number of Purchases
Average Purchase Amount
10011
D.00
444.00
74.00
10012
345.86
153.85
3
51.28
70. 46
17.48
10014
D.00
422.77
10015
D.00
34.97
2
10018
216.55
70.44
1
70.44](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F5e1132c9-beab-4f38-876e-7cf6bf6331b8%2F6cdcdc62-a224-4f7c-88b9-ef785f972ad3%2Fzlsl59_processed.jpeg&w=3840&q=75)
![](/static/compass_v2/shared-icons/check-mark.png)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![Oracle 12c: SQL](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781305627482/9781305627482_smallCoverImage.gif)
![Oracle 12c: SQL](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781305627482/9781305627482_smallCoverImage.gif)
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_smallCoverImage.gif)
![A Guide to SQL](https://www.bartleby.com/isbn_cover_images/9781111527273/9781111527273_smallCoverImage.gif)