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