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

SQL Query - AdventureWorks: Exercise-139 with Solution

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.

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.