SQL Server - Lesson 17: Restrictions on Data Selection

	Restrictions on Data Selection

Restrictions Fundamentals

Introduction

We know that we can analyze data from the table or using a Query window. Instead of selecting all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records.

Practical Learning: Introducing Restrictions

Start Microsoft SQL Server and connect.
If you didn't yet, create the RealEstate1 database
From the Object Explorer, expand Databases
Expand RealEstate1 and expand Tables
Right-click dbo.Properties and click Edit Top 200 Rows
On the Query Designer toolbar, click the Show Diagram Pane button , the Show Criteria Pane button , and the Show SQL Pane button

Selecting the TOP Records

If you have a long group of records, you can specify that you want to see only a certain number of records. To do this, after the SELECT operator, type TOP followed by an integral number. Continue the SELECT statement as you see fit. Here is an example:

USE Exercise;
GO

SELECT TOP 5 * FROM Employees;
GO

You can also include the number in parentheses. Here is an example:

USE Exercise;
GO

SELECT TOP(5) * FROM Employees;
GO

This statement asks the SQL interpreter to select the first 5 records from the Employees table. Just as done here, you can apply the TOP operator to any of the statements we will see for the rest of our lessons.

Practical Learning: Selecting the Top Records

In the SQL section, on the right side of SELECT, change Top(200) to Top(10)
Right-click somewhere in the window and click Execute SQL

WHEN a Field's Value Meets a Criterion

You can use a WHEN conditional statement to refine data selection. Consider the following table:

USE Exercise1;
GO

CREATE TABLE Persons(FirstName nvarchar(20), LastName nvarchar(20), GenderID int);
GO

INSERT INTO Persons VALUES(N'Gertrude', N'Monay', 2),
			  (N'Horace', N'Taylor', 1),
			  (N'Patricia', N'Katts', 2),
			  (N'Tiernan', N'Michael', 3),
			  (N'Paul', N'Yamo', 1),
			  (N'Mahty', N'Shaoul', 3),
			  (N'Helene', N'Mukoko', 2);
GO

Imagine you want to select the GenderID column:

Conditional Statements

If a column has values that are difficult to identify, you can use a CASE conditional statement to customize the result(s). Here is an example:

SELECT FirstName, LastName, Gender =
    CASE GenderID
	WHEN 1 THEN N'Male'
	WHEN 2 THEN N'Female'
	ELSE N'Unknown'
    END
FROM Persons

Conditional Statements

WHERE is the Condition

Introduction

When analyzing data or if you are creating a query using the Table window, you can type an expression that uses one or more logical operators we reviewed in Lesson 6. Here is an example of an expression

> '12/31/1993'

This means that the dates that occur after 1993 would be selected.

If you are writing your SELECT statement, to formulate a condition, you use the WHERE keyword with a basic formula as follows:

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor 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 is written using the formula:

ColumnName=Value

The ColumnName factor must be an existing column of a table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value.

Here is an example from a database of students, from a table named Students, to get a list of female students:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE Gender = N'Female';
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 DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE State = N'MD'
ORDER BY LastName;
GO

This would produce:

Practical Learning: Using WHERE

To see a list of only properties in DC, in the SQL section, change the statement as follows:

SELECT PropertyNumber AS [Prop #],
       Address,
       City,
       State,
       ZIPCode AS [Location],
       YearBuilt AS [Year Built],
       PropertyType AS [Type],
       MarketValue AS [Value]
FROM Properties
WHERE ZIPCode < 20500

On the main menu, click Query Designer -> Execute SQL

To get a list of only properties in Virginia, in the SQL section, change the statement as follows:

SELECT PropertyNumber AS [Prop #],
       Address,
       City,
       State,
       ZIPCode AS [Location],
       YearBuilt AS [Year Built],
       PropertyType AS [Type],
       MarketValue AS [Value]
FROM Properties
WHERE State = N'va'

On the main menu, click Query Designer -> Execute SQL

To get a list of only the newest properties built in or after 2000, in the SQL section, change the statement as follows:

SELECT Address,
       City,
       State,
       YearBuilt AS [Year Built],
       PropertyType AS [Type],
       MarketValue AS [Value]
FROM Properties
WHERE YearBuilt >= 2000

On the main menu, click Query Designer -> Execute SQL

To get a list of properties in Maryland arranged by ZIP Codes, change the statement as follows:

SELECT PropertyType AS Type,
       YearBuilt AS [Year Built],
       City,
       ZIPCode,
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       MarketValue AS Value
FROM Properties
WHERE State = N'md'
ORDER BY ZIPCode

On the main menu, click Query Designer -> Execute SQL

Hiding a Column

In our SELECT statements so far, we were selecting the columns we needed to display. When formulating such a statement, you can apply a condition to a column without including that column in the result. For example, consider the above query. It is used to display a list of female students. Since we know that the result would show only the girls, it becomes redundant to include the Gender column in the statement. In this case, you can hide that column in the result.

To hide a column from a query, omit that column in the SELECT statement but involve it in the WHERE condition. Here is an example:

SELECT DateOfBirth, LastName,
       FirstName, State, ParentsNames
FROM Students
WHERE Gender = N'Female';
GO

This would produce:

WHERE

Notice that the SELECT statement doesn't have the Gender column and the resulting query doesn't show the Gender column.

Practical Learning: Not Showing a Column

To see a list of only properties in Maryland, change the statement as follows:

SELECT PropertyType AS Type,
       YearBuilt AS [Year Built],
       City,
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       MarketValue AS Value
FROM Properties
WHERE State = N'md'

On the main menu, click Query Designer -> Execute SQL

Negating a Condition

In Lesson 5, we saw that you could use the NOT operator to negate the validity of a Boolean expression. Consider the following statement:

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM Students
WHERE Gender = N'Female';
GO

When this statement is executed, a list of female students would display. Instead of girls, to get a list of male students, you can negate this condition. To do this, type NOT before the condition. This would be done as follows:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT Gender = N'Female';
GO

To make this condition easier to read, you should include the positive expression in parentheses. This would be done as follows:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT (Gender = N'Female');
GO

This clearly indicates that it is the expression in the parentheses that is being negated. In the same way, you can use the IS NOT NULL to find the records that are not null. For example, you can create a list of only records that don't have a null value on a certain column. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM   Students
WHERE  State IS NOT NULL;
GO

When this statement is executed, the table would display only the records that include a state for each student.

Practical Learning: Negating a Value

To see a list of only properties that have been assigned a property number, change the 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 PropertyNumber IS NOT NULL

On the main menu, click Query Designer -> Execute SQL

Creating a Table

Consider the following Employees table:

CREATE DATABASE Corporation;
GO

USE Corporation;
GO
CREATE TABLE Employees
(
	EmployeeNumber int unique not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Department nvarchar(50) null,
	EmploymentStatus nvarchar(30),
	HourlySalary money
);
GO
INSERT INTO Employees
VALUES(84628, N'Anthony', N'Holms', N'Corporate', N'Full Time', 24.72),
      (40574, N'William', N'Wilson', N'Information Technology', N'Full Time', 21.18),
      (27462, N'Peter', N'Swanson', N'Corporate', N'Full Time', 22.84),
      (52835, N'Edward', N'Johansen', N'Information Technology', N'Consultant', 15.50),
      (93075, N'Sharon', N'Edson', N'Accounting', N'Full Time', 28.74),
      (82718, N'Yanuh', N'Hadhah', N'Corporate', N'Full Time', 14.86),
      (29174, N'Hamin', N'Souleyman', N'Public Relations', N'Consultant', 18.76);
GO

You can use all or some records from an existing table to create a new table that contains those records. To do this, you use the following formula:

SELECT Columns INTO NewTableName FROM ExistingTable [WHERE Condition]

To use all columns and all records, start with the SELECT operator, followed by *, followed by INTO, followed by a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example:

USE Corporation;
GO
SELECT * INTO CompanyRecipients FROM Employees;
GO

Instead of using all columns, you can specify only the desired columns after the SELECT keyword. Here is an example:

USE Corporation;
GO
SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus
INTO Salaried FROM Employees;
GO

Instead of using all records, you can use a condition by which the records would be selected and added to the new table you are creating. To set the condition, you can create a WHERE statement as the last in the whole expression. Here is an example:

USE Corporation;
GO
SELECT *
INTO FullTimeEmployees
FROM Employees
WHERE EmploymentStatus = N'Full Time';
GO

Pattern Operator: LIKE

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.

If you are visually creating the statement, in the Critera section, in the box corresponding to Filter for the column on which the condition would be applied, type the LIKE condition.

In Transact-SQL, 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 equality operator would be the same as LIKE. For example

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

is equivalent to

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

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

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 FirstName, LastName, Gender, SPHome
FROM   Students
WHERE  LastName LIKE N'S%'

This would produce:

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

SELECT FirstName, LastName, Gender, SPHome
FROM   Students
WHERE  NOT (LastName LIKE N'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 don't 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 FirstName, LastName, Gender, SPHome
FROM   Students
WHERE  LastName LIKE N'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 FirstName, LastName, Gender, SPHome
FROM   Students
WHERE  LastName LIKE '%an%'

This would produce:

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

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 FirstName, LastName, Gender, SPHome
FROM   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. This would produce:

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

Not Ending With a Range of Characters

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     FirstName, LastName, Gender, SPHome
FROM         Students
WHERE     (LastName LIKE '%[^p-r]')

The result would be a list of students whose last end 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 ^.


Previous	Copyright © 2007-2009 FunctionX, Inc.	Next