Table: Customers
| CustID |
Name |
Address |
City |
Country |
PostalCode |
Telephone |
Fax |
| CUST001 |
Maria Anders |
Obere Str. 57 |
Berlin |
Germany |
12209 |
030-0074321 |
030-0076545 |
| CUST002 |
Ernst Handel |
Kirchgasse 6 |
Graz |
Austria |
8010 |
7675-3425 |
7675-3426 |
| CUST003 |
Helen Bennett |
Garden House Crowther Way |
Cowes |
UK |
PO31 7PJ |
(198) 555-8888 |
(198) 555-8889 |
| CUST004 |
Paula Wilson |
2817 Milton Dr. |
Albuquerque |
USA |
87110 |
(505) 555-5939 |
(505) 555-5940 |
| CUST005 |
Liu Wong |
55 Grizzly Peak Rd. |
Butte |
USA |
59801 |
(406) 555-5834 |
(406) 555-8083 |
SELECT * FROM Customers WHERE Country = 'USA'
The above SQL statement will return customers from USA only, as shown in
Resultset1 below.
Resultset1
| CustID |
Name |
Address |
City |
Country |
PostalCode |
Telephone |
Fax |
| CUST004 |
Paula Wilson |
2817 Milton Dr. |
Albuquerque |
USA |
87110 |
(505) 555-5939 |
(505) 555-5940 |
| CUST005 |
Liu Wong |
55 Grizzly Peak Rd. |
Butte |
USA |
59801 |
(406) 555-5834 |
(406) 555-8083 |
Customer CUST004(Helen Bennett) and CUST005 (Liu Wong) are from USA. Therefore,
the above SQL command only returns 2 records.
* Please note that the SELECT and WHERE keywords are not case
sensitive but the string 'USA' is case-sensitive. This has been tested on SQL
Server 2005 Express.
The WHERE conditions can be simple or complex. The above example is the
simplest form of WHERE conditions. Besides the '=' operator, you can also use
other relational operators such as <, <=, >, >=, <>. You can
also use logical operator such as OR, AND and NOT.
T-SQL in SQL Server has introduced a few more operators:
!= --> Not equal to
!< --> Not less than
!> --> Not greated than
Let's say, you want to retrieve customers from Germany and USA only. Then, you
can make use of the OR operator in the condition of the SELECT
statement.
SELECT * FROM Customers WHERE Country= 'USA' OR Country
='Germany'
Resultset2
| CustID |
Name |
Address |
City |
Country |
PostalCode |
Telephone |
Fax |
| CUST001 |
Maria Anders |
Obere Str. 57 |
Berlin |
Germany |
12209 |
030-0074321 |
030-0076545 |
| CUST004 |
Paula Wilson |
2817 Milton Dr. |
Albuquerque |
USA |
87110 |
(505) 555-5939 |
(505) 555-5940 |
| CUST005 |
Liu Wong |
55 Grizzly Peak Rd. |
Butte |
USA |
59801 |
(406) 555-5834 |
(406) 555-8083 |
