How do I use the SQL WHERE clause with numbers?
In the last section we talked about how to use the SQL WHERE clause to filter rows of data based on text criteria. Now, we will look at how to filter rows of data based on numbers. Remember, we are using the AdventureWorks sample database that Microsoft provides for all examples in our SQL lesson. Please navigate to our introduction to SQL post that will provide information about how to get your environment set up.
The process is nearly identical in the WHERE clause; the only difference is that we will not need the single apostrophes around our filtering criteria. For example, to find all rows with a TotalDue value in Sales.SalesOrderHeader greater than 10,000 we would type:
WHERE TotalDue > 10000
We do not place any commas in the numbers to specify a thousands separator. The number is not enclosed by single apostrophes like we are required to do with text values. All of the comparison operators discussed in the last section can be used when filtering numbers in the WHERE clause. For example, to find all rows in Production.Product with a SafetyStockLevel value of 1000:
WHERE SafetyStockLevel = 1000
We can also use the “AND” and “OR” operators to use multiple filtering conditions in the same WHERE clause. To find all rows in Production.Product with a ListPrice greater than $2,000 and less than $3,000, we can use the query:
WHERE ListPrice > 2000 AND ListPrice < 3000
The BETWEEN operator can complete the previous query with a little less typing involved. We will show you how to use the BETWEEN operator and others in a later post!
Using some of the techniques learned in this lesson, try to complete the practice problems below.
- Find the rows in Production.Product with a ListPrice less than $250. Return only the Name and ListPrice columns.
- Find all rows in Sales.SalesOrderHeader where the TotalDue column is at least $15,000. Return only the SalesOrderNumber and TotalDue columns.
- Find all rows in HumanResources.Employee with a value greater than 50 in the VacationHours column.