Saturday, November 08, 2014

TSQL Tutorial : Filtering Data

You use the SQL WHERE clause to qualify the data a SELECT statement returns. It can also be used to
Limit the rows affected by an UPDATE or DELETE statement. Here are some queries that use WHERE to
filter the data they return:
SELECT UPPER(LastName), FirstName
FROM customers
WHERE State='TX'
FirstName
--- ---------
DOE John
DOE Jane
The following code restricts the customers returned to those whose address contains the word "Joshua."
SELECT LastName, FirstName, StreetAddress FROM customers
WHERE StreetAddress LIKE '%Joshua%'
LastName FirstName StreetAddress
-------- --------- ---------------
Doe John 123 Joshua Tree
Doe Jane 123 Joshua Tree
Note the use of "%" as a wildcard. The SQL wildcard % (percent sign) matches zero or more instances of
any character, while _ (underscore) matches exactly one.
Here's a query that returns the orders exceeding $500:
SELECT OrderNumber, OrderDate, Amount
FROM orders
WHERE Amount > 500
OrderNumber OrderDate Amount
----------- ----------------------- --------
102 1992-02-27 00:00:00.000 678.90
103 1995-05-20 00:00:00.000 86753.09
104 1997-11-21 00:00:00.000 678.90
The following example uses the BETWEEN operator to return orders occurring between October1990 and
May1995, inclusively. I've included the time with the second of the two dates because, without it, the time
would default to midnight (SQL Server datetime columns always store both the date and time; an omitted
time defaults to midnight), making the query noninclusive. Without specification of the time portion, the
query would return only orders placed up through the first millisecond of May31.
SELECT OrderNumber, OrderDate, Amount FROM orders
WHERE OrderDate BETWEEN '10/01/90' AND '05/31/95 23:59:59.999'
OrderNumber OrderDate Amount
----------- ----------------------- --------
101 1990-10-18 00:00:00.000 123.45
102 1992-02-27 00:00:00.000 678.90

103 1995-05-20 00:00:00.000 86753.09

TSQL Tutorial..

No comments:

Post a Comment