Operations on Data Analysis
|
Operations on Data Analysis |
|
Logical Conjunction and Disjunction |
So far, we have stated the conditions one at a time. This made their interpretation easier. Sometimes, you will need to test a 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:
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. This means that the customer would purchase the property only if BOTH conditions are true: The property is a single family AND the property is 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 we have used 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 If conditional statement. The Condition1 is the first that would be examined. If the first condition is false, the whole statement is rendered 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, suppose we want to get a list of female students who live in Maryland. The SQL statement used to get this list can be written as follows: SELECT FirstName, LastName, Gender, City, State FROM Students WHERE Gender = 'female' AND State = 'md'; 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: SELECT FirstName, LastName, Gender, City, State FROM Students WHERE (Gender = 'female') AND (State = 'md') This would produce:
You can also negate a condition by preceding it with the NOT operator.
Suppose a customer is considering purchasing either a single family house or a townhouse. To prepare the list or properties, you must create a query that considers only these two options. Before building the query, you can state the following:
When creating the list, you would want to include a property only if it is either a single family or a townhouse:
This type of statement is referred to as logical disjunction. The logical disjunction is expressed in SQL with the OR operator.
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 of 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, select the desired columns. 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 produces a list of students who live in southern Maryland where the ZIP Code is from 20500 to 21000 (excluded): SELECT FirstName, LastName, Gender, City, State, ZIPCode, SPHome FROM Students WHERE ZIPCode BETWEEN '20500' AND '21000' It is usually a habit to include the whole BETWEEN statement in parentheses. The above statement would produce:
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 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. 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, Gender, City, State, ZIPCode, SPHome
FROM Students
WHERE City IN ('silver spring', 'rockville', 'chevy chase');
It is usually a habit to include the whole IN statement in its own parentheses. The above statement would produce:
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 Table window, click the Criteria box corresponding 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 = 'VA';
GO
is equivalent to SELECT DateOfBirth, LastName, FirstName,
Gender, State, ParentsNames
FROM Students
WHERE State LIKE 'VA';
GO
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:
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 Fiter 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 '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 '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 '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.
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 '%[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. 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 ^.
To refine your data analysis, you can use functions, whether functions you create yourself or the Transact-SQL built-in functions. As always mentioned, the first candidates of functions you should try to use are the built-in functions, some of which we reviewed in Lesson 7. 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 to call 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
FROM Students;
GO
This would produce:
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 Students WHERE (DateOfBirth BETWEEN CONVERT(DATETIME, '1995-01-01', 102) AND CONVERT(DATETIME, '1999-12-31', 102)) You can also involve a built-in function in an expression.
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 6 April, 2007
Description: This function is used
to get the full name of a student
=============================================*/
CREATE FUNCTION GetFullName
(
@FName varchar(20),
@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
RETURN @LName + ', ' + @FName;
END;
GO
/* =============================================
Author: FunctionX
Create date: Saturday 7 April, 2007
Description: This function is used
to display Yes or No
============================================= */
CREATE FUNCTION ShowYesOrNo
(
@SPHomeStatus bit
)
RETURNS varchar(3)
AS
BEGIN
DECLARE @Result varchar(3);
IF @SPHomeStatus = 0
SET @Result = 'No';
ELSE
SET @Result = '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 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
This would produce:
|
|
|
||
| Previous | Copyright © 2007 Yevol | Next |
|
|
||
Practical Learning: Using Conditions With Data Analysis
