In the last section we composed our first SQL query to get us closer to getting the names of all persons who live in NY.

We have got a list of people in our results, but now we narrow this list down to only the people who live in NY.


enter The WHERE Clause

Criteria with Text

SELECT 
  firstName,
  residence AS [Home City], 
  age
FROM
  person
WHERE
  firstName = 'Candyce'
ORDER BY 
  firstName

 

In order to add criteria to a query you need to include a WHERE clause.  The WHERE clause always comes after the FROM but before the ORDER BY.  In the example above the WHERE clause is added to return all persons who live in New York.  The = sign is referred to as an operator and there are several different operators that can be used.

=    Equal To
>    Greater Than
<    Less Than
>=  Greater Than or Equal To
<=  Less Than or Equal To
<>  Not Equal To
 

Criteria with Numbers

SELECT 
 firstName,
 residence AS [Home City],
 age 
FROM
 person
WHERE
age = 19
ORDER BY firstName

Another useful Operator in SQL Server is the BETWEEN operator.  It returns a range of values. 

SELECT
 firstName,
 residence AS [Home City], 
 age  FROM person
WHERE
 age BETWEEN 15 AND 18
ORDER BY age

 

Yet another useful operator is the IN operator which is used to find a list of precise values.  The values are separated by a comma and wrapped in brackets as shown below.

SELECT 
firstName,
residence AS [Home City],
 age
FROM 
 person
WHERE 
 age IN (10, 15, 30, 40)
ORDER BY age 

 

Using wildcards with text

If we were to look for anyone whose name begins with "C" the criteria would need to be changed to accommodate this type of search.  First we would replace the = sign with the keyword LIKE.  Next we would also introduce what is called a wildcard character also called the percentage symbol.  A wildcard symbol represents any number of any characters.  As used in the example below the query will search for any person whose name begins with "C" followed by anything else. 

SELECT  
 firstName, 
 residence AS [Home City],  
 age 
FROM 
 person 
WHERE 
 firstName LIKE 'C%' 
ORDER BY firstName

 

Adding another percent symbol before the "C" would now make the query search for any person who has a C in their name.

SELECT 
 firstName,
 residence AS [Home City], 
 age 
FROM
 person 
WHERE
 firstName LIKE '%C%' 
ORDER BY firstName

 

The LIKE operator can also be used to exclude results that match criteria specified.  By placing the NOT keyword in front of LIKE, NOT LIKE an be used to exclude records from your query result.

SELECT 
 firstName, 
 residence AS [Home City], 
 age 
FROM
 person 
WHERE
 firstName NOT LIKE '%C%' 
ORDER BY firstName

9.) Find all people who do not have "an" anywhere within their name.

Criteria with Dates

SELECT 
start_city,
dest_city,
FlightDate
FROM
flight
WHERE
flightDate = '2014-01-27'
ORDER BY flightDate

When querying for dates it is important to take a look at how the date is stored within the table you are querying.  In our sample database dates are stored as yyyy-mm-dd.  Dates are also surrounded in single quotes just as text is in SQL Server.  Querying with dates also use the same operators that numbers do i.e. ><BETWEEN, etc.

 

In SQL Server there are a number of date functions that can be used.  The MONTH() function can be used to find a particular month from your data.

SELECT 
 start_city,
 dest_city,
 FlightDate
FROM
 flight
WHERE
 MONTH(flightDate) = 6   
ORDER BY flightDate

The example above uses the MONTH function to return all flights that occurred during the month of June.

 

Using the AND and OR operators

Criteria can be combined by using the AND and the OR operators.  The query below uses the AND together with the LIKE operator to find all people whose name begins with "C" and are below 30 years old.

SELECT
 firstName,
 residence AS [Home City], 
 age
FROM
 person
WHERE
 firstName LIKE 'C%' AND age < 30
ORDER BY firstName

You can add additional AND operators to search for additional criteria.  The AND operator allows you to find the single records that match all of the criteria you ask for.

 

The OR operator allows you to find the single records that match any of the criteria you ask for.  The query below searches for all people whose first name starts with "P" or starts with "D".

SELECT 
 firstName,
 residence AS [Home City], 
 age 
FROM
 person 
WHERE
 firstName LIKE 'P%' OR firstName LIKE 'D%'
ORDER BY firstName

The use of brackets play a significant role in the result that are returned for your query when using the AND and OR operators.  When the query below is executed it returns results for persons whose name begin with "P" and are older than 40 years old.  It also returns records for all people whose name begins with "D".  It treats age > 40 AND firstName LIKE 'P%' separately from firstName LIKE 'D%' and returns the results for both.

SELECT
 firstName,
 residence AS [Home City], 
 age
FROM
 person
WHERE
 age > 40 AND
 firstName LIKE 'P%' OR firstName LIKE 'D%'
ORDER BY firstName

When brackets are added as shown in the example below.  It treats (firstName LIKE 'P%' OR firstName LIKE 'D%') as a single criteria.  In this query it returns all people whose name starts with "P" or 'D' AND are also over 40 years old.  It

SELECT 
 firstName,
 residence AS [Home City], 
 age 
FROM
 person 
WHERE
 age > 40 AND
 (firstName LIKE 'P%' OR firstName LIKE 'D%')
ORDER BY firstName

 

Now lets get the names of all persons who live in NY.

 

CONGRATULATIONS

YOU HAVE JUST FOUND ALL PEOPLE WHO RESIDE IN NEW YORK