<sub>2025-05-24</sub> <sub>#data-visualization #data-management #sql #hmp669</sub>
<sup>[[maps-of-content|🌐 Maps of Content — All Notes]] </sup>
<sup>Series: [[hmp669|Data Management and Visualization]]</sup>
# SQL Practice — Sale Co Database
![[sale-co-db-exercise-1748068098714.webp]]
### 1. Write a query to count the number of invoices.
> [!check]-
> ```sql
> SELECT
> COUNT(INV_NUMBER) AS [Number of Invoices]
> FROM INVOICE;
> ```
>
> ![[sale-co-db-exercise-1748068393587.webp]]
### 2. Write a query to count the number of customers with a customer balance over $500.
> [!check]-
> ```sql
> SELECT
> COUNT(CUSTOMER.CUS_CODE) AS [No of High balance Customers]
> FROM
> CUSTOMER
> WHERE
> CUSTOMER.CUS_BALANCE > 500;
> ```
>
> ![[sale-co-db-exercise-1748068408758.webp]]
### 3. Generate a listing of all purchases made by the customers, using the output shown below as your guide.
> [!check]-
> ```sql
> SELECT
> C.CUS_CODE AS [Customer],
> I.INV_NUMBER AS [Invoice Number],
> I.INV_DATE AS [Invoice Date],
> P.P_DESCRIPT AS [Product Description],
> L.LINE_UNITS AS [Line Units],
> L.LINE_PRICE AS [Line Price]
> FROM ((CUSTOMER AS C
> INNER JOIN INVOICE AS I ON C.CUS_CODE = I.CUS_CODE)
> INNER JOIN LINE AS L ON I.INV_NUMBER = L.INV_NUMBER)
> INNER JOIN PRODUCT AS P ON L.P_CODE = P.P_CODE
> ORDER BY
> C.CUS_CODE,
> I.INV_NUMBER;
> ```
>
> ![[sale-co-db-exercise-1748075581917.webp]]
>
### 4. Using the output shown below as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers.
> [!check]-
> ```sql
> SELECT
> CUSTOMER.CUS_CODE AS [Customer],
> INVOICE.INV_NUMBER AS [Invoice Number],
> PRODUCT.P_DESCRIPT AS [Product Description],
> LINE.LINE_UNITS AS [Units Bought],
> LINE.LINE_PRICE AS [Unit Price],
> ROUND([Units Bought] * [Unit Price], 2) AS [Subtotal]
> FROM ((CUSTOMER
> INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE)
> INNER JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER)
> INNER JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE
> ORDER BY
> CUSTOMER.CUS_CODE,
> INVOICE.INV_NUMBER;
> ```
>
>
> ![[sale-co-db-exercise-1748075825100.webp]]
>
>
### 5. Modify the query used in Problem 4 to produce the Customer purchase summary shown below.
> [!check]-
> ```sql
> SELECT
> CUSTOMER.CUS_CODE AS [Customer],
> CUSTOMER.CUS_BALANCE AS [Customer Balance],
> SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) AS [Total Purchases]
> FROM ((CUSTOMER
> INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE)
> INNER JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER)
> INNER JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE
> GROUP BY
> CUSTOMER.CUS_CODE,
> CUSTOMER.CUS_BALANCE
> ORDER BY
> CUSTOMER.CUS_CODE;
> ```
>
> ![[sale-co-db-exercise-1748076078103.webp]]
>
> #### Alternatively: Source Query4 table
> ![[sale-co-db-exercise-1748075825100.webp]]
> ```sql
> SELECT
> C.CUS_CODE AS [Customer],
> C.CUS_BALANCE AS [Customer Balance],
> SUM(QUERY4.Subtotal) AS [Total Purchase]
> FROM
> CUSTOMER AS C INNER JOIN QUERY4 ON C.CUS_CODE = QUERY4.[Customer]
> GROUP BY
> C.CUS_CODE,
> C.CUS_BALANCE;
> ```
>
> ![[sale-co-db-exercise-1748076296256.webp]]
>
### 6. Modify the query in Problem 5 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 below.
> [!check]-
> ```sql
> SELECT
> C.CUS_CODE AS [Customer],
> C.CUS_BALANCE AS [Customer Balance],
> SUM(QUERY4.Subtotal) AS [Total Purchases],
> COUNT(query4.[Customer]) AS [Number of Purchases]
> FROM CUSTOMER AS C
> INNER JOIN QUERY4 ON C.CUS_CODE = QUERY4.[Customer]
> GROUP BY
> C.CUS_CODE,
> C.CUS_BALANCE;
> ```
>
> ![[sale-co-db-exercise-1748076455211.webp]]
>
>
### 7. Use a query to compute the average purchase amount per product made by each customer. (Hint: Use the results of Problem 6 as the basis for this query.) Your output values must match those shown below. Note that the average purchase amount is equal to the total purchases divided by the number of purchases
![[sale-co-db-exercise-1748069287889.webp]]
> [!check]-
> **Option 1: Direct from Query6**
> This option assumes Query6 has already aggregated total purchases and purchase counts per customer. The average is computed row by row.
> ```sql
> SELECT
> Query6.[Customer],
> Query6.[Customer Balance],
> Query6.[Total Purchases],
> Query6.[Number of Purchases],
> ROUND(Query6.[Total Purchases] / Query6.[Number of Purchases], 2) AS [Average Purchase Amount]
> FROM
> Query6
> ORDER BY
> Query6.[Customer];
> ```
>
> **Option 2: Full Query from scratch**
> This query builds everything directly from the base tables
>
> ```sql
> SELECT
> CUSTOMER.CUS_CODE AS [Customer],
> CUSTOMER.CUS_BALANCE AS [Customer Balance],
> ROUND(SUM(LINE.LINE_UNITS * LINE.LINE_PRICE), 2) AS [Total Purchases],
> COUNT(CUSTOMER.CUS_CODE) AS [Number of Purchases],
> ROUND(SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) / COUNT(CUSTOMER.CUS_CODE), 2) AS [Average Purchase Amount]
> FROM
> ((CUSTOMER
> INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE)
> INNER JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER)
> INNER JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE
> GROUP BY
> CUSTOMER.CUS_CODE, CUSTOMER.CUS_BALANCE;
> ```
>
> ![[sale-co-db-exercise-1748071205348.webp]]
>
>
### 8. Create a query to produce the total purchase per invoice, generating the results shown below. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE.
![[sale-co-db-exercise-1748069298661.webp]]
> [!check]-
> ```sql
> SELECT
> INVOICE.INV_NUMBER AS [Invoice Number],
> ROUND(SUM(LINE.LINE_UNITS * LINE.LINE_PRICE), 2) AS [Invoice Total]
> FROM INVOICE
> LEFT JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
> GROUP BY
> INVOICE.INV_NUMBER
> ORDER BY
> INVOICE.INV_NUMBER;
> ```
>
> ![[sale-co-db-exercise-1748073466324.webp]]
>
>
### 9. Use a query to show the invoices and invoice totals as shown below.
![[sale-co-db-exercise-1748069310196.webp]]
> [!check]-
> **Option 1: Using Query8**
> ```sql
> SELECT
> CUSTOMER.CUS_CODE AS [Customer],
> Query8.[Invoice Number],
> Query8.[Invoice Total]
> FROM (CUSTOMER
> INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE)
> INNER JOIN Query8 ON INVOICE.INV_NUMBER = Query8.[Invoice Number]
> GROUP BY
> CUSTOMER.CUS_CODE,
> Query8.[Invoice Number],
> Query8.[Invoice Total]
> ORDER BY
> CUSTOMER.CUS_CODE;
> ```
> **Option 2: Full from scratch**
> ```sql
> SELECT
> CUSTOMER.CUS_CODE AS [Customer],
> INVOICE.INV_NUMBER AS [Invoice Number],
> ROUND(SUM(LINE.LINE_UNITS * LINE.LINE_PRICE), 2) AS [Invoice Total]
> FROM (CUSTOMER
> INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE)
> INNER JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
> GROUP BY
> CUSTOMER.CUS_CODE,
> INVOICE.INV_NUMBER
> ORDER BY
> CUSTOMER.CUS_CODE,
> INVOICE.INV_NUMBER;
> ```
>
> ![[sale-co-db-exercise-1748074111605.webp]]
>
>
### 10. Using the query results in Problem 9 as your basis, write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest invoice amount, the largest invoice amount, and the average of all of the invoices. Your output must match as shown below.
![[sale-co-db-exercise-1748069320175.webp]]
> [!check]-
> ```sql
> SELECT
> DISTINCT Query9.[Customer],
> COUNT(Query9.[Invoice Number]) AS [Number of Invoices],
> SUM(Query9.[Invoice Total]) AS [Total Customer Purchases]
> FROM Query9
> GROUP BY
> Query9.[Customer]
> ORDER BY
> Query9.[Customer];
> ```
>
> ![[sale-co-db-exercise-1748074912260.webp]]
>
>
> ```sql
> SELECT
> DISTINCT Query9.[Customer],
> COUNT(Query9.[Invoice Number]) AS [Number of Invoices],
> SUM(Query9.[Invoice Total]) AS [Total Customer Purchases],
> MIN(Query9.[Invoice Total]) AS [Smallest Invoice Amount],
> MAX(Query9.[Invoice Total]) AS [Largest Invoice Amount],
> AVG(Query9.[Invoice Total]) AS [Average Invoice Amount]
> FROM Query9
> GROUP BY
> Query9.[Customer]
> ORDER BY
> Query9.[Customer];
> ```
>
> ![[sale-co-db-exercise-1748075107262.webp]]
>
>