AdventureWorks Database: Tax difference between highest and lowest-taxed state or province

Last update on December 20 2024 11:37:33 (UTC/GMT +8 hours)

139. From the following table write a query in SQL to calculate the tax difference between the highest and lowest tax-rate state or province.

Sample table: Sales.SalesTaxRate

salestaxrateid|stateprovinceid|taxtype|taxrate|name                                 |rowguid                             |modifieddate           |
--------------+---------------+-------+-------+-------------------------------------+------------------------------------+-----------------------+
             1|              1|      1|     14|Canadian GST + Alberta Provincial Tax|683de5dd-521a-47d4-a573-06a3cdb1bc5d|2008-04-30 00:00:00.000|
             2|             57|      1|  14.25|Canadian GST + Ontario Provincial Tax|05c4ffdb-4f84-4cdf-abe5-fdf3216ea74e|2008-04-30 00:00:00.000|
             3|             63|      1|  14.25|Canadian GST + Quebec Provincial Tax |d4edb557-56d7-403c-b538-4df5e7302588|2008-04-30 00:00:00.000|
             4|              1|      2|      7|Canadian GST                         |f0d76907-b433-453f-b95e-16fce73b807a|2008-04-30 00:00:00.000|
             5|             57|      2|      7|Canadian GST                         |7e0e97a2-878b-476f-a648-05a3dd4450ed|2008-04-30 00:00:00.000|
             6|             63|      2|      7|Canadian GST                         |1e285d2c-8af7-47aa-b06a-762cf4d93acd|2008-04-30 00:00:00.000|
             7|              7|      3|      7|Canadian GST                         |590ccb14-cb20-49bf-8fee-e0c3abc4c2b1|2008-04-30 00:00:00.000|
             8|             29|      3|      7|Canadian GST                         |a8365f30-78b7-4dbe-8985-f8260560126b|2008-04-30 00:00:00.000|
             9|             31|      3|      7|Canadian GST                         |f4fde24b-7a53-4340-9d10-173e9424864a|2008-04-30 00:00:00.000|
            10|             41|      3|      7|Canadian GST                         |383d465b-e1d1-492a-83f3-ab3e9cbf3282|2008-04-30 00:00:00.000|
            11|             45|      3|      7|Canadian GST                         |8451909f-8a8d-4789-9f87-16a335d28053|2008-04-30 00:00:00.000|
            12|             49|      3|      7|Canadian GST                         |fb7607ee-8f12-41ea-b461-10885c6f1533|2008-04-30 00:00:00.000|
-- more --

Click to view Full table

Sample Solution:

-- Calculating the difference between the maximum and minimum TaxRate values and labeling it as "Tax Rate Difference"
SELECT 
    -- Subtracting the minimum TaxRate value from the maximum TaxRate value
    MAX(TaxRate) - MIN(TaxRate) AS "Tax Rate Difference"  
-- Selecting data from the SalesTaxRate table
FROM 
    Sales.SalesTaxRate  
-- Filtering records where StateProvinceID is not NULL
WHERE 
    StateProvinceID IS NOT NULL;

Explanation:

This SQL code calculates the difference between the maximum and minimum TaxRate values from the SalesTaxRate table and labels it as "Tax Rate Difference".
The SELECT statement specifies the calculation to be performed, subtracting the minimum TaxRate value from the maximum TaxRate value.
The FROM clause specifies the table from which data will be retrieved, in this case, the SalesTaxRate table.
The WHERE clause filters records to include only those where the StateProvinceID is not NULL, ensuring that only valid tax rates are considered.
The result will be a single value representing the difference between the maximum and minimum tax rates in the table.

Sample Output:

Tax Rate Difference|
-------------------+
               14.6|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Add vacation and sick time to find total hours away from work.
Next: Calculate sales targets per month for salespeople.