Topics on Filtering Records

Logical Conjunctions

Introduction

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.

Imagine 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.

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 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 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. 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 satisfy 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:

SELECT PropertyNumber,
       City,
       State,
       ZIPCode,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       Stories,
       YearBuilt,
       MarketValue
FROM   Properties
WHERE  PropertyType = 'Single Family' AND City = 'Silver Spring';

In your Windows application, you can assign this expression to the binding source. Each condition is 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:

SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM   Properties
WHERE  (PropertyType = 'Single Family') AND (City = 'Silver Spring');

When creating a logical conjunction, you can apply all the other features we have reviewed so far:

• Because SQL is not case sensitive, you can write AND in either uppercase or lowercase
• Because SQL is not case sensitive, you can write the conditions in uppercase, lowercase, or a combination of cases
• You can negate a condition by preceding it with the NOT operator
• You can create alias names for the columns. Here are examples:

  SELECT PropertyNumber AS [Prop #],
         PropertyType AS Type,
         YearBuilt AS [Year Built],
         City,
         State,
         ZIPCode AS [ZIP Code],
         Bedrooms AS Beds,
         Bathrooms AS Baths,
         MarketValue AS Value
  FROM Properties
  WHERE (PropertyType = 'Single Family') AND (City = 'Silver Spring');
  GO

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 spend over 500,000. To get this list, you would need two AND conditions. You would create the condition as follows:

SELECT PropertyNumber, PropertyType, City, Bedrooms, MarketValue
FROM   Properties
WHERE  PropertyType = 'Single Family' AND
       State = 'MD' AND MarketValue < 500000

To make the WHERE condition easier to read, each expression should be included in its own parentheses. Here is an example:

SELECT PropertyNumber,
       City,
       State,
       ZIPCode,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       Stories,
       YearBuilt,
       MarketValue
FROM   Properties
FROM   Properties
WHERE  (PropertyType = 'Single Family') AND
       (State = 'MD') AND
       (MarketValue < 500000)

If you are working in a Windows application, you can pass the expression to the binding source. Here is an example:

 bsProperties.Filter = "(PropertyType = 'Single Family') AND " +
		      "(State = 'MD') AND " +
		      "(MarketValue < 500000)"

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.

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

SELECT PropertyType,
       City,
       Bedrooms,
       Stories,
       MarketValue
FROM Properties
WHERE MarketValue BETWEEN 320000 AND 450000

To make the condition easy to ready, you should include the whole BETWEEN statement in parentheses. 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 spend more than 400,000. 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 20900 to 20999. To get the list, you would formulate your SELECT statement as follows:

SELECT PropertyNumber AS [Prop #],
       PropertyType AS Type,
       YearBuilt AS [Year Built],
       City,
       ZIPCode AS [ZIP Code],
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       MarketValue AS Value
FROM Properties
WHERE (PropertyType = 'Townhouse') AND
      (MarketValue < 400000) AND
      (ZIPCode = (SELECT ZIPCode
      WHERE ZIPCode BETWEEN '20500' AND '21000'));

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

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:

1. If the property is a single family, our statement is true and we do not need to check the second condition
2. 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.

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:

SELECT PropertyType,
       City,
       Bedrooms,
       Stories,
       MarketValue
FROM   Properties
WHERE  PropertyType = 'Single Family' OR PropertyType = 'Townhouse';

To make the disjunction easier to read, each expression should be included in parentheses. Here is an example:

SELECT PropertyType,
       City,
       Bedrooms,
       Stories,
       MarketValue
FROM   Properties
WHERE  (PropertyType = 'Single Family') OR (PropertyType = 'Townhouse');

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.

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 Expression1 OR Expression2 OR Expression_n, etc.

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 (the SQL code ROSH database is available):

SELECT FirstName, LastName, Gender City, State
FROM Registration.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. When creating your IN statement, you can combine any of the other features we have 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 PropertyNumber AS [Prop #],
       PropertyType AS Type,
       YearBuilt AS [Year Built],
       City,
       State,
       ZIPCode AS [ZIP Code],
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       MarketValue AS Value
FROM   Properties
WHERE (PropertyType IN ('Single Family', 'Townhouse')) AND
      (City = 'Rockville')

Filtering by 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, 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   Registration.Students
WHERE  State = N'VA';
GO

is equivalent to

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM   Registration.Students
WHERE  State LIKE N'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.

As done with all Boolean operations, there are ways you can negate a LIKE operation. Probably the easiest way to negate a LIKE operation is to precede the Expression with the NOT operator. Here is an example:

SELECT FirstName, LastName,
       Gender, State, ParentsNames
FROM   Registration.Students
WHERE NOT State LIKE N'VA';
GO

If you precede the expression with NOT, it is recommended that you put the section after NOT between parentheses. Here is an example:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE NOT (State LIKE N'VA');
GO

As an alternative, in most cases, you can also precede the LIKE keyword with NOT. This formula would produce the same result as the previous one.

You can also negate a negation. Here is an example:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT State NOT LIKE N'VA';
GO

This is the same as:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT (State NOT LIKE N'VA');
GO

If you negate a negation, you can get the result as you none of both negations was used.

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:

SELECT FirstName,
       LastName,
       Gender,
       City,
       State
FROM   Registration.Students
WHERE  (LastName LIKE 'S%')

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

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

This time, the result is the list of students whose last names do not 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, Gender, City, State
FROM   Registration.Students
WHERE  (LastName LIKE 'Ch%')

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, Gender, City, State
FROM   Registration.Students
WHERE  (LastName LIKE '%an%')

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

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, Gender, City, State
FROM   Registration.Students
WHERE  (LastName LIKE N'%[p-s]')

In the case, the result would be a list of students whose last names end with p, q, r, or 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, Gender, City, State
FROM   Registration.Students
WHERE  LastName LIKE N'%[^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 ^.

To refine your data analysis, you can use functions, whether functions you create yourself or the Transact-SQL built-in functions. The first candidates of functions you should try to use are the built-in functions.

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,
       Gender,
       DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
       City,
       State
FROM Registration.Students

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

SELECT FirstName, LastName, Gender, DateOfBirth, SPHome
FROM   Registration.Students
WHERE  (DateOfBirth BETWEEN CONVERT(DATETIME, '1995-01-01', 102) AND
			    CONVERT(DATETIME, '1999-12-31', 102))

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:

/* =============================================
   Author:      FunctionX
   Create date: Friday 26 February, 2012
   Description:	This function is used 
                to get the full name of a student
   =============================================*/
CREATE FUNCTION Registration.GetFullName
(
	@FName varchar(20),
	@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
	RETURN @LName + N', ' + @FName;
END;
GO
/* =============================================
   Author:	FunctionX
   Create date: Saturday 26 February, 2012
   Description:	This function is used 
                to display Yes or No
   ============================================= */
CREATE FUNCTION Registration.ShowYesOrNo
(
    @SPHomeStatus bit
)
RETURNS varchar(3)
AS
BEGIN
    DECLARE @Result varchar(3);

    IF @SPHomeStatus = 0
       SET @Result = N'No';
    ELSE
       SET @Result = N'Yes';

    RETURN @Result;
END;
GO

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 StudentNumber,
       Registration.GetFullName(FirstName, LastName) AS [Student's Name],
       Gender,
       Registration.ShowYesOrNo(SingleParentHome) AS [Live's in a Single Parent Home?],
       ParentsNames AS [Parents' Names]
FROM Registration.Students;