SQL > SQL Commands > WHERE Clause

We can use the WHERE clause to filter the result set based on certain conditions. The syntax for using WHERE in the SELECT statement is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "condition";

"Condition" can include a single comparison clause (called simple condition) or multiple comparison clauses combined together using AND or OR operators (compound condition).

Example 1: WHERE Clause With Simple Condition

To select all stores with sales above $1,000 in Table Store_Information,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

we key in,

SELECT Store_Name
FROM Store_Information
WHERE Sales > 1000;

Result:

Store_Name
Los Angeles

Example 2: WHERE Clause With OR Operator

To view all data with sales greater than $1,000 or with transaction date of 'Jan-08-1999', we use the following SQL,

SELECT *
FROM Store_Information
WHERE Sales > 1000 OR Txn_Date = 'Jan-08-1999';

Result:

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

Using WHERE With UPDATE and DELETE

In addition to the SELECT statement, the WHERE clause can also be used with UPDATE and DELETE statements. Examples of how to use the WHERE clause with these two commands can be seen in the UPDATE and DELETE sections.

Exercises

For these exercises, assume we have a table called Users with the following data:

Table Users

First_NameLast_NameBirth_DateGenderJoin_Date
SophieLeeJan-05-1960FApr-05-2015
RichardBrownJan-07-1975MApr-05-2015
JamalSantoOct-08-1983MApr-09-2015
CaseyHealySep-20-1969MApr-09-2015
JillWilkesNov-20-1979FApr-15-2015

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT * FROM Users WHERE Gender = 'M';
b) SELECT * WHERE Gender = 'M' FROM Users;
c) SELECT Gender= 'M' FROM Users;
d) SELECT Gender FROM Users WHERE Last_Name = 'Wilkes';

2. What's the result of the following query?
SELECT * FROM User WHERE Join_Date = 'Apr-09-2015';

3. (True or False) The condition used in the WHERE clause must include a column that is part of the SELECT clause.

Next: SQL AND OR




Copyright © 2015   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact