So far, we have stated the conditions one at a
time. This made their interpretation easier. Sometimes, you will need to test
one
condition that depends on another. Boolean algebra allows you to combine two
conditions and use the result, or to test two conditions but consider if either
is true.

For a real estate company, suppose you have a customer
who is considering purchasing a single family house around Silver Spring,
Maryland,
you would check the listing of the properties and find out what you have. To respond to this request, you must examine two
conditions for each property:

The property must be a single family

The property must be located in Silver Spring

When preparing your listing prior to seeing the
customer, you can start by building a query that lists only the single
family houses:

The other condition requires that the house be located
in Silver Spring:

From
these two results, notice that there is no relationship between the fact
that a property is a single family that it is located in Silver Spring.

AND a Logical Conjunction

A logical conjunction is a Boolean operation that
combines at least two conditions. For example our customer wants to purchase
a property only if two conditions are
met: The property must be a single family AND the property must be located in Silver
Spring. This type of condition is referred to as
logical conjunction.

To create a logical conjunction,
if you are working on a table in Microsoft SQL Server Management Studio or in
Microsoft Visual Studio, under Filter, type each condition as seen above. Here
is an example:

If you are working in an ADO.NET graphical application, you
can access the Properties window for the binding source and, in the Filter
field, type the expression. Alternatively, you can access the variable of the
binding source in your code and assign it the AND expression.
In both cases, the expression must follow the Transact-SQL rule,

To create a logical conjunction in
SQL, you use the AND operator.
To write the statement, you use the the following formula:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2

The WhatColumn(s) and the WhatObject
factors are the same we have used in previous WHERE conditions so far. The AND keyword is the
operator that joins the conditions.
Each condition is written as a SQL operation using the
formula:

Column operator Value

In this case, the WHERE operator resembles the
C#'s
if conditional statement. The Condition1 is the first that would be
examined. If the first
condition is false, the whole statement is false and there is no
reason to examine the second condition. If the first condition is true,
then the second condition would be examined.

Based on this, to get a list of properties that
satisfies our customer, that is, to get a list that includes the single
family properties in Silver Spring, we would create a SQL
statement as follows:

We stated that each condition was separately
evaluated. For this reason, to make the conjunction statement easier to
read, each condition should be included in parentheses. Therefore, the above SQL
statement can be written as follows:

Instead of one logical conjunction, you can create as many
conjunctions as you want. To do this, use as many combinations of AND operators
as necessary. For example, suppose a customer wants to purchase a single family in
MD but she is not willing to sp} over 500000. To get this list, you would need two
AND
conditions. You would create the condition as follows:

If you have a logical range of values and you want to
know if a certain value is contained in that range, you can use add a BETWEEN
operator to a WHERE statement. The BETWEEN operator is combined with
AND to get a list of records between two values. The
basic formula of this operator is:

WHERE Expression BETWEEN Start AND End

The Expression placeholder is
usually the name of the column whose values you want to examine. The Start
factor is the starting value of the range to consider. the end factor is the highest value to consider in the range. After this condition
is executed, it produces the list of values between Start and End.

To create a BETWEEN expression in the Table
window, under the Filter column that
corresponds to the field on which you want to set the condition, type the
BETWEEN expression. Here is an example that specifies the price range
a customer wants to consider for a house:

As stated already, in SQL, you use the BETWEEN operator to
create this type of condition. Here is an example:

To make the condition easy to ready, you should include the whole BETWEEN
statement in parentheses. Here is an example:

When creating your BETWEEN statement, you can use any of the
SQL features we have studied so far. To create a complex expression, you can
combine many (or all) of the conditions we have reviewed so far. For example, you
may have a customer who is considering purchasing a
townhouse around Silver Spring but he does not want to sp} more than
400000. Because the customer wants the townhouse in the Silver Spring area, you
will not consider the City column. You remember that Silver Spring contains many ZIP Codes
from 20800 to 20999. To get the list, you would formula your SELECT statement as
follows:

If you are working in a Windows application, you can assign the expression to
the Filter property of the binding
source.

Logical Disjunctions

Introduction

Suppose a customer is considering purchasing either a
single family house or a townhouse. To prepare the list or properties, you must create a
list that
includes only these two options. Before building the query, you can state
the following:

The property is a single-family

The property is a townhouse

When creating the list, you would want to include a property
only if it is either a single family or a townhouse:

If the property is a single family, our statement is true and we don't
need to check the second condition

If the property is not a townhouse, then we consider the second condition.
If the property is a townhouse, our statement becomes true

This type of statement is referred to as logical
disjunction.

OR a Logical Disjunction

To create a logical disjunction, if you are working on a
table:

Under the first Or column, click the box that corresponds to one of the
columns that will be involved in the OR condition, and type the necessary
value or expression

Under the second Or column, access the box that corresponds to the same
column as previously and type the appropriate value or expression

Here is an example:

If you are working in a Windows application, you can assign the
logical disjunction to
the Filter property of the binding
source.

To create a logical disjunction in SQL, you use the OR operator.
To do this, after the WHERE operator, type two conditions separated by an
OR operator. The formula to use would be:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 OR Condition2

Each condition must be complete; that is, it can
consist of a Column=Value expression. Here is an
example:

When creating a disjunction, you can apply any of the other
SQL features we have reviewed so far.

Instead of two disjunctions, you can create three or more if
you want. To add one more disjunction if you are working on a table, in the
third Or column, click the box that corresponding to the same column used for
the previous Or condition, and type the desired value or expression.

IN a Selected Series

If you have a series of records and want to find a
record or a group of records among them, you can use the IN
operator by adding it to a WHERE statement. The IN operator is a type of various OR operators. It follows this
formula:

IN(Expression1, Expression2, Expression_n)

Each Expression factor can be one of the
values of a column. This is equivalent to Expression1ORExpression2ORExpression_n, etc.

To create an IN expression in the Table
window, select the desired columns. Under the Filter column that
corresponds to the field on which you want to set the condition, type the IN
expression. Here is an example:

To create an IN expression in SQL,
after the WHERE operator, type the name of the column
that holds the values to be considered, followed by the IN expression. From our list of students, imagine that you want to
get a list of students who live either in Silver Spring, in Rockville, or
in Chevy Chase. You can write an IN expression as follows:

SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE City IN ('Silver Spring', 'Rockville', 'Chevy Chase')

To make the expression easier to read, you should include the whole
IN statement in
its own parentheses. Here is an example:

When creating your IN statement, you can
combine any of the other features we have so studied so far. For example, to get
a list of single family and townhouses located in Rockville, you would create a
statement as follows:

SELECT house.PropertyNumber AS [Prop #],
house.PropertyType AS Type,
house.YearBuilt AS [Year Built],
house.City,
house.State,
house.ZIPCode AS [ZIP Code],
house.Bedrooms AS Beds,
house.Bathrooms AS Baths,
house.MarketValue AS Value
FROM Properties house
WHERE (house.PropertyType IN ('Single Family', 'Townhouse')) AND
(house.City = 'Rockville')

If you are working in a Windows application, you can assign the
IN expression to
the Filter property of the binding
source.

LIKE a Pattern

Introduction

Most or all of the criteria we have specified with the
WHERE keyword had to exactly match the specified criterion. In some cases,
you may not remember the exact value of records but you want to
specify some type of approximation. To do this, you use the LIKE operator.

To create a LIKE operator, if you are visually creating the statement, in the
Table window, in the Criteria section, under the Filter column, click the box
that corresponds to the column on
which the condition would be applied and type.

In a SQL statement, the LIKE
operator is used in a formula as follows:

Expression LIKE pattern

The Expression factor is the expression that
will be evaluated. This must be a clear and valid expression.

The pattern factor can be a value to be
found in Expression. For example, it can be the same type of value
used in a WHERE statement. In this case, the equal operator would
be the same as LIKE. For example

SELECT DateOfBirth, LastName, FirstName,
Gender, State, ParentsNames
FROM Students
WHERE State LIKE 'VA';
GO

If you are working in a Windows application, you can assign the
LIKE expression to
the Filter property of the binding
source.

The idea of using a LIKE operator is to give
an approximation of the type of result you want. The available wildcards
to use with the LIKE operator are:

LIKE Any Character %

If you want to match any character, in any
combination, for any length, use the % wildcard. If you precede it with a
letter, as in S%, the condition would consist of finding any string that
starts with S. Imagine that you want to get a list of students whose
last names start with S. You would type the condition as LIKE 'S%'. To do this visually, in the Criteria
section, under the Filter column, type the condition. Here is an example:

The SQL statement is this query is:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE (LastName LIKE 'S%')

This would produce:

You can negate this condition by preceding it with
NOT. Here is an example:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE (NOT (LastName LIKE 'S%'))

This would produce:

This time, the result is the list of students whose last names don't start with S.

When you precede the % character with a letter, only
that letter would be considered. Alternatively, you can specify a group of
characters that would precede the % symbol. For example, if you have some
first names that start with Ch in a list but you do not remember the end of the name you are looking for, to create the list, you can specify that
the first name would start with Ch and end with whatever. In this case,
you would use Ch% as follows:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE (LastName LIKE 'Ch%')

This would produce:

Instead of ending a letter or a group of letters with
%, you can begin the LIKE statement with %. An example would be LIKE "%son".
In this case, all strings that end with son, such as Johnson or Colson,
would be considered.

If you remember neither the beginning nor the end of a
string you want to search for, but you know a sub-string that is probably
included in the type of string you are looking for, you can precede it
with % and end it with %. An example would be LIKE "%an%".
In this case, all strings that include "an" anywhere inside would be considered.
Here is an example:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE (LastName LIKE '%an%')

This would produce:

Like the other SQL statements, you can also negate
this one.

LIKE a Range of Characters []

The % wildcard is used to precede or succeed a
specific character or a group of characters, that is, any character. If
you want to consider only a range of characters from the alphabet, you can
include the range in square brackets. To do this, type [, followed by the
lowest character of the range, followed by -, followed by the highest
character of the range, followed by ]. For example, to consider the range
of letters between p and s, you would use '[p-s]'. Then, either to the
left, to the right, or to both sides of this expression, type % to specify
whether to include any character or combination of characters before or after
the expression. Here is an
example:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE (LastName LIKE '%[p-s]')

In the case, the result would be a list of students
whose last names } with p, q, r, or s. This would produce:

Notice that the list includes only the students whose
last names } with a letter between p and s.

As opposed to considering the characters that are in a
specific range, to specify a character or a range of characters that must
NOT be considered, use the ^ character inside the square brackets but
before the desired range. Here is an example:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE (LastName LIKE '%[^p-r]')

The result would be a list of students whose last }
with a letter other than p, q, r, or s.

Once again, remember that you can negate this
expression by preceding it with NOT. Note that if you negate an
expression that include ^, you would get the same result as not using ^.

Functions and Data
Selection

Using a Built-In Function

To refine your data analysis, you can use functions,
whether functions you create yourself or the Transact-SQL built-in
functions. As mentioned previously, the first candidates of functions you should
try to use are the built-in functions, some of which we reviewed in Lesson
23.

To use a built-in function, in the placeholder of the
column, type the name of the function, followed by its parentheses. If the
function takes some parameters, remember to follow the rules of calling a
parameterized function. Here is an example that uses some date-based
built-in functions to display the ages of the students:

SELECT FirstName,
LastName,
Sex,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
City,
State
FROM Students

This would produce:

You can also include a function in any of the operators
we have reviewed so far. Here is an example:

If you are working on a Windows application, you can assign
the condition to the Filter property of the binding source.

Using a User-Defined Function

If none of the built-in functions satisfies your needs, you
can create your own and use it during data analysis. Obviously, you should first
create the function. Here is an example of two functions created in the ROSH
database:

Once a function is ready, in the placeholder of your SQL
statement, type dbo., followed by the name of the function, its parentheses, and
its paremeter(s), if any, inside of the parentheses. Here is an example:

SELECT StudentID,
dbo.GetFullName(FirstName, LastName) AS [Student's Name],
Gender,
dbo.ShowYesOrNo(SPHome) AS [Live's in a Single Parent Home?],
ParentsNames AS [Parents' Names]
FROM Students;
GO