Microsoft SQL Server - Lesson 19: Introduction to Data Filters

	Introduction to Data Filters

Introduction to Conditions

Overview

In the database world, a filter is a funel whose job is to select some records and exclude or ignore others. To indicate how that funnel should work, you formulate a condition using a Boolean operation. In data analysis, the condition is associated with a SELECT statement.

If you are working in a Query window or the Query Designer, you can visually create the whole SQL statement without writing a single line of code. Otherwise, you can create the statement using your knowledge of SQL.

Practical Learning: Introducing Restrictions in Data Selection

Start the computer and log in
Launch Microsoft SQL Server and click Connect
In the Object Explorer, expand Databases
Make sure you have the FunDS1 database.
In the Object Explorer, expand FunDS1 and expand Tables
Right-click Inventory.StoreItems and click Edit Top 200 Rows
Right-click in the Query Designer, posttion the mouse on Pane, and click Diagram
Right-click in Diagram pane -> Pane -> Criteria
Right-click in Diagram pane -> Pane -> SQL
In the Diagram pane, remove the check boxes of all fields
In the SQL pane, delete TOP (200)
In the Criteria pane, click the first combox box under Column and select ItemNumber (you may receive a message box; read it and click OK, then click the ItemNumber check box again)
Press Tab and, under Alias, type Item #
Click the first empty combox box under Column and select Manufacturer
Click the first empty combox box under Column and select ItemName
Press Tab and type Name/Description
Click the first empty combox box under Column and select UnitPrice
Press Tab and type Unit Price
Right-click in the Diagram pane and click Execute SQL

WHERE is the Filter?

When analyzing data, you can ask the database engine to apply one or more filters. To visually create a condition in a Query Designer, in the Criteria pane, click the box at the intersection of the Column's field and the Filter column:

Filter

In the SQL, the primary keyword used to create a condition is WHERE. A basic formula to follow is:

SELECT WhatField(s) FROM WhatObject WHERE Expression;

We are already familiar with the sections before WHERE. The Expression used in a condition is built using algebraic, logical, and string operators. The Expression is called a criterion. Although a group of expressions, making it plural is called criteria, the word criteria is sometimes used for a singular expression also.

The Expression of a WHERE statement is written using the formula:

ColumnName Operator Value

The ColumnName must be an existing column of a table. It is followed by an appropriate operator as we will see next. Value is the value that will set the condition.

To make the statement easier to read, you should include it in parentheses after WHERE.

Primary Operations on Filters

Checking the Nullity of a Field

Depending on how it was created, a field can have actual or null values. In the next sections we will see how to check the value of a field. To check whether a field is holding a null value, use the following formula for the WHERE statement:

WHERE ColumnName IS NULL

In this case, only the records that are NULL on the ColumnName will be considered in the result.

Practical Learning: Checking for Nullity

In the Criteria pane, click Manufacturer and press Tab six times
In the Filter box, type is null and press Enter
On the main menu, click Query Designer -> Execute SQL.
Notice that you get 19 records and only that have a null manufacturer

Checking the Nullity of a Field

Checking the Negative Nullity of a Field

Instead of considering the NULL records, the reverse is to get those that are not. To check whether a field doesn't hold a certain value, include the NOT operator in its expression. For example, to check the negativity of an IS NULL expression, use IS NOT NULL to find the records that are not null. In fact example, you can create a list of only records that don't have a null value on a certain column.

Practical Learning: Checking for Not Null

In the SQL pane, click the right side of IS, press the Space bar and type not
On the Query Designer toolbar, click the Execute SQL button .
Notice that you get only records that have a manufacturer
In the Filter column, click the first empty box, press the down arrow key and press Delete to remove IS NOT NULL

Filtering String-Based Fields

Introduction

As you should know already, the values of a certain column can be null or using a value of type char, varchar, or varchar(max), or their variants (nchar, nvarchar, or nvarchar(max)).

The primary Boolean operation you can perform on a field consists of checking its nullity. As mentiond above, this operation can be performed by using IS NULL in its expression. Here is an example:

USE master;
GO
CREATE DATABASE Exercise;
GO
USE Exercise;
GO
CREATE TABLE Videos
(
	[Shelf #] nchar(7) null,
	Title nvarchar(50) not null,
	Director nvarchar(50),
	[Length] int,
	Rating nchar(10),
	[Year] int
);
GO

INSERT INTO Videos
VALUES(N'DHE-927', N'Two for the Money', N'D.J. Caruso', 123, N'R', 2008),
      (N'CGM-683', N'Her Alibi', N'Bruce Beresford', 94, N'PG-13', 1998),
      (N'FQT-973', N'Memoirs of a Geisha', N'Rob Marshall', 145, N'PG-13', 2006),
      (N'DBT-395', N'Wall Street', N'Oliver Stone', 126, N'R', 2000);
      
GO
INSERT INTO Videos(Title, Director, [Length], Rating)
VALUES(N'Stealing Harvard', N'Bruce McCulloch', 85, N'PG-13');
GO

INSERT INTO Videos([Shelf #], Title, Director, [Length], [Year])
VALUES(N'TPH-973', N'A Few Good Men', N'Rob Reiner', 138, 1992);
GO

INSERT INTO Videos(Title, Director, [Year], [Length])
VALUES(N'The Silence of the Lambs', N'Jonathan Demme', 1991, 118);
GO

INSERT INTO Videos([Shelf #], Title, Director, Rating, [Length])
VALUES(N'DZV-737', N'The Lady Killers', N'Joel Coen & Ethan Coen', N'R', 104);
GO

INSERT INTO Videos(Title, Director, [Length],  Rating, [Year])
VALUES(N'Sneakers', N'Phil Alden Robinson', 126, N'PG-13', 1992),
      (N'Annie', N'John Huston', 126, N'G', 1982),
      (N'Dave', N'Ivan Reitman', 110, N'PG-13', 1993);
GO

INSERT INTO Videos
VALUES(N'ADR-737', N'Incredibles (The)', N'Brad Bird', 133, N'PG', 2004);
GO

SELECT ALL * FROM Videos
WHERE  [Shelf #] IS NULL;
GO

This would produce:

Videos

As mentioned already, to make the condition easier to read, you should include the expression in parentheses. This would be done as follows:

SELECT ALL * FROM Videos
WHERE ([Shelf #] IS NULL);
GO

When the statement executes, the table would display only the records that don't have a value for the state. On the other hand, to get the records that are not null, you would use IS NOT NULL. Here is an example:

SELECT ALL * FROM Videos
WHERE  [Shelf #] IS NOT NULL;
GO

This would produce:

Videos

Checking for String Equality

Another common operation performed on a field consists of finding out whether it holds a specific value. This is done using the equality "=" operator. Therefore, to find out whether a field holds a certain value, compare it with that value. You must include the value in single-quotes. Here is an example:

SELECT ALL * FROM Videos
WHERE  Rating = N'R';
GO

This would produce:

WHERE

In a WHERE statement, you can also use the ORDER BY expression to sort a list of records based on a column of your choice. Here is an example:

SELECT ALL * FROM Videos
WHERE  Rating = N'R'
ORDER BY Director;
GO

This would produce:

WHERE Condition

Practical Learning: Checking for String Equality

To see a list of items made by a certain manufacturer, in the Filter box corresponding to Manufacturer, type Giorgio Armani
On the main menu, click Query Designer -> Execute SQL

Checking for String Inequality

To check whether a field doesn't hold a certain value, you can use the <> operator. Here is an example:

SELECT ALL * FROM Videos
WHERE  Rating <> N'R';
GO

Not Equal

Remember (from Lesson 08) that, besides <>, Transact-SQL also supports the != operator used to perform a comparison for inequality. Therefore, the above statement can also be written as:

SELECT ALL * FROM Videos
WHERE  Rating != N'R';
GO

As an alternative, instead of <> or !=, use the equality operator but precede the expression with NOT. Here is an example:

SELECT ALL * FROM Videos
WHERE NOT Rating = N'R';
GO

Not Equal

Notice that the result is the same as if only the comparison for equality was used. Of course, you can precede the <> operation with NOT. Here is an example:

SELECT StudentNumber, FirstName, LastName, Gender, ParentsNames
FROM Registration.Students
WHERE NOT (Gender <> 'Male');
GO

In this case, the result would include not the records that are not equal to the value, which would be equivalent to using = and not NOT.

Practical Learning: Filtering String-Based Fields

To see a list of items not made by a certain manufacturer, in the Criteria pane and in the Filter column, delete =N'Giorgio Armani' and type
!= Kenneth Cole
On the main menu, click Query Designer -> Execute SQL

Filtering Numeric Fields

Introduction

As done for strings, if a field holds both numeric and null values, to find out whether a field holds a null value, apply the IS NULL expression to its condition. Here is an example:

SELECT [Shelf #], Title, [Year]
FROM Videos
WHERE [Year] IS NULL;
GO

This would produce:

Not Equal

Unlike strings, number-based fields use all Boolean operators supported both by ISO SQL anbd Transact-SQL. They are:

Operation	Used to find out whether
=	A field holds a certain numeric value
<> or !=	A field doesn't hold a certain numeric value or a field has a value different from a certain numeric value
<	A field's value is lower than a certain numeric value
<= or !>	A field's value is lower than or is equal to a certain numeric value or a field's value is not greater than a certain numeric value
>	A field's value is greater than a certain numeric value
>= or !<	A field's value is greater than or is equal to a certain numeric value or a field's value is greater than or is equal to a certain numeric value

Here is an example:

SELECT *
FROM Videos
WHERE [Length] > 125;
GO

This would produce:

Boolean Operations on Numeric Fields

Practical Learning: Filtering Numeric Fields

In the Criteria pane, delete the condition in the Filter column
To get a list of items that cost 18.00 or less, click the box at the intersection of UnitPrice and Filter. Type <= 18.00
On the main menu, click Query Designer -> Execute SQL
In the Criteria pane, delete the condition under Filter

The Negativity or Opposite of a Numeric Comparison

There are various ways you can find the negation of a number-based comparison. As seen previously, to negate a comparison, you can precede the expression with the NOT operator. Otherwise, by definition, each Boolean operator has an opposite. They are:

Operation		Opposite
Primary	Also	Primary	Also
=		<>	!=
<>	!=	=
<	!<	>=
<=		>	!>
>	!>	<=
>=		<	!<

Based on this, to find the negativity of a comparision, you can use the opposite operator.

Practical Learning: Filtering the Opposite of a Boolean Operator

To get a list of items that don't cost less than 300.00, in the Criteria pane, under Filter, replace < 10.01 and !< 300
On the main menu, click Query Designer -> Execute SQL
In the Filter column, delete the condition

Filtering Boolean Fields

As you may know already, a Boolean field is one whose type is BIT. A Boolean field can hold only one of two values: 0 or 1 (representing False or True, No or Yes, On or Off).

As seen for the other types, a Boolean field can hold a null value if it didn't receive an actual value during data entry. This means that you can compare its value to IS NULL. Here is an example:

SELECT StudentNumber, FirstName, LastName,
       Gender, City, SingleParentHome
FROM   Registration.Students
WHERE  SingleParentHome IS NULL;
GO

This would produce:

Filtering Boolean Fields

In the same way, you can use IS NOT NULL, exactly as done for the other data type, to negate IS NULL.

In Microsoft SQL Server, a Boolean or bit value is treated as a small integer. That is, it behaves as if it can use a value from 0 up. In this case, 0 means that the value of the field is false and 1 means the value of the field is true. When filtering Boolean records, you can use the 0 or the 1 value applied to a column with the equality operator. If you use any other number, or although you can also use any integer, you may get get only empty records.

Filtering Date/Time Fields

Introduction

Transact-SQL provides various data types to support dates, times, and combinations of dates and times. Like a string, the value of a date or time is provided or initialized in single-quotes. Unlike a string, the value of a date or time must follow strict rules inside the single-quotes, otherwise the value would be invalid. When performing comparisons on date/time-based fields, you must keep those rules in mind.

Because a string-based field can contain anything, its comparison allows only equality or inequality. Unlike strings but like numbers, date and time values support all Boolean operators.

Comparing Date/Time Values

Boolean operators used in date/time fields work as follows:

Operation	The value of the field
=	Exactly matches the indicated date/time value
<> or !=	Doesn't match the indicated date/time value or is different from the indicated date/time value
<	Occurs before the indicated date/time value
<= or !>	Occurs before the indicated date/time value or on the same day or time, or doesn't occur after the indicated date/time value
>= or !<	Occurs after the indicated date/time value or on the same day or time or doesn't occur before the indicated date/time value
>	Occurs after the indicated date/time value

Here is an example:

SELECT StudentNumber, FirstName, LastName, DateOfBirth,
       Gender, City, SingleParentHome
FROM   Registration.Students
WHERE  DateOfBirth >= '1995-01-01';
GO

Practical Learning: Filtering Date-Based Fields

In the Diagram pane, click the check box of DateEntered
In the Criteria pane, click DateEntered and press Tab
In the Alias box, type Date Entered
In the Criteria pane, move the DateEntered row and position it below ItemNumber
To run the query, right-click somewhere in the window and click Execute SQL
In the Results pane, locate the record with the item # as 749374. Click its Date Entered box and press Ctrl + C
In the box at the intersection of DateEntered and Filter, click and press Ctrl + V (or type that date)
To see the result, on the Query Designer toolbar, click the Execute SQL button . Here is an example:

Negating Date/Time Comparisons

When it comes to comparisons, date and time values follow the same approach as numbers, but date and time values add some logical rules. To negate a date/time comparison, you can precede it with the NOT operator. If you negate an equality comparison, the expression would mean that the date or time doesn't occur on the indicated value, but it doesn't indicate whether the date/time occurs before or after that date. Therefore, in most cases, to negate a date/time comparison, you should use the opposite of the operator. Because date/time values support all Boolean operators, the opposites of those operators apply in the same logic we reviewed for numeric values:

Operation	Opposite
=	<> or !=
<> or !=	=
<	>= or !<
<= or !>	>
>	<= or !>
>= or !<	<

Practical Learning: Filtering the Negativity of a Date-Based Field

In the Criteria pane, in the Filter column, replace = with !<
To see the result, on the Query Designer toolbar, click the Execute SQL button
Close the Query Designer
Close Microsoft SQL Server

Exercises

Lesson Summary Questions

What is the basic formula to apply a filter when selecting records?

WITH WhatObject SELECT WhatField(s) WHERE Expression;

CREATE FILTER(Expression) SELECT WhatField(s) FROM WhatObject;

SELECT WhatField(s) FROM WhatObject WHERE Expression;

SELECT(WhatField(s)) FROM WhatObject WHERE(Expression);

EXECUTE FILTER WITH SELECT WhatField(s) FROM WhatObject WHERE Expression;

What is the basic formula of a WHERE expression?
1. ColumnName Operator Value
2. WITH ColumnName Operator Value
3. ColumnName Operator Value SELECT ALL
4. SET ColumnName Operator Value
5. EXECUTE FILTER(ColumnName Operator Value)
How do you use a WHERE expression to select NULL values?
1. WHERE ColumnName = 0
2. ColumnName Operator SET NULL
3. WHERE EXISTS(ColumnName) = NULL
4. WITH ColumnName WHERE NULL
5. WHERE ColumnName IS NULL
If you are comparing numeric or date/time values, what two operators are the same?
1. <>
2. !<
3. !>
4. !=
5. ==
If you are comparing numeric or date/time values, what two operators are the same?
1. <>
2. >=
3. !<
4. !>
5. ==
If you are comparing numeric or date/time values, what two operators are the same?
1. <
2. >
3. !>
4. <=
5. ==

Answers

Answers
1. Wrong Answer
2. Wrong Answer
3. Right Answer
4. Wrong Answer
5. Wrong Answer
Answers
1. Right Answer
2. Wrong Answer
3. Wrong Answer
4. Wrong Answer
5. Wrong Answer
Answers
1. Wrong Answer
2. Wrong Answer
3. Wrong Answer
4. Wrong Answer
5. Right Answer
Answers
1. Right Answer
2. Wrong Answer
3. Wrong Answer
4. Right Answer
5. Wrong Answer
Answers
1. Wrong Answer
2. Right Answer
3. Right Answer
4. Wrong Answer
5. Wrong Answer
Answers
1. Wrong Answer
2. Wrong Answer
3. Right Answer
4. Right Answer
5. Wrong Answer