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.

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"
    -- Subtracting the minimum TaxRate value from the maximum TaxRate value
    MAX(TaxRate) - MIN(TaxRate) AS "Tax Rate Difference"  
-- Selecting data from the SalesTaxRate table
-- Filtering records where StateProvinceID is not NULL
    StateProvinceID IS NOT NULL;


  • 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|

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Follow us on Facebook and Twitter for latest update.