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.

1. Start Microsoft SQL Server and the SQL Server Management Studio.
   If you didn't yet, create the RealEstate1 database
2. In the Object Explorer, right-click Databases and click New Query

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. Here is an example:

The other condition requires that the house be located in Silver Spring. You could set it as follows:

From these two results, notice that there is no relationship between the fact that a property is a single family and that it is located in Silver Spring. But our 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 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 = N'female' AND State = N'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 = N'female') AND (State = N'md')

This would produce:

You can also negate a condition by preceding it with the NOT operator.

1. To see a list of single family houses located in Silver Spring, type the following statement:
    

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

2. Press F5 to execute the statement
3. Suppose a prospective buyer is considering purchasing a townhouse but cannot spend more than $400,000.
   To get a listing of  town homes that cost less than $400,000, change the statement as follows:
    

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

4. Press F5 to execute the statement
    
   
5. Suppose the above customer would prefer that the house be in Rockville.
   To get a list of town homes in Rockville Spring that cost less than $400,000, change the statement as follows:
    

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

6. Press F5 to execute the statement

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:

• 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 don't 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. The logical disjunction is expressed in SQL with the OR operator.

1. To see a list of single family and townhouses, change the statement as follows:
    

   SELECT h.PropertyNumber AS [Prop #], h.PropertyType AS Type, h.YearBuilt AS [Year Built], h.City, h.ZIPCode AS [ZIP Code], h.Bedrooms AS Beds, h.Bathrooms AS Baths, h.MarketValue AS Value FROM Properties h WHERE (h.PropertyType = N'single family') OR (h.PropertyType = N'Townhouse'); GO

2. Press F5 to execute the statement

If you have a logical range of values and you want to know if a certain value is contained in that range, you can 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, 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 N'20500' AND N'21000'

It is usually a habit to include the whole BETWEEN statement in parentheses.

The above statement would produce:

1. To get a list of properties whose prices are in the range of $350,000 and $450,000, change the statement as follows:
    

   SELECT h.PropertyNumber AS [Prop #], h.PropertyType AS Type, h.YearBuilt AS [Year Built], h.City, h.ZIPCode AS [ZIP Code], h.Bedrooms AS Beds, h.Bathrooms AS Baths, h.MarketValue AS Value FROM Properties h WHERE (h.MarketValue BETWEEN 350000 AND 450000); GO

2. Press F5 to execute the statement
3. After running the statement, you find out that you don't have anything like that in Silver Spring. So you suggest that the customer consider other townhouses around Silver Spring. To create the statement, you would need townhouses whose ZIP Codes are between 20800 and 20999 (Montgomery County, Maryland).
   To get a list of town homes for this customer, change the statement as follows:
    

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

4. Press F5 to execute the statement

If you have a series of records and you 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 (N'silver spring', N'rockville', N'chevy chase');

It is usually a habit to include the whole IN statement in its own parentheses.

The above statement would produce:

1. To get a list of single family and townhouses, change the 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 (N'Single Family', N'Townhouse'); GO

2. Press F5 to execute the statement
3. To get a list of  single family and townhouses located in Rockville, change the 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 (N'Single Family', N'Townhouse')) AND (house.City = N'Rockville'); GO

4. Press F5 to execute the statement

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

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(datetime2, N'1995-01-01', 102) AND
			    CONVERT(datetime2, N'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:

USE ROSH;
GO

/* =============================================
   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 + ', N' + @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 = 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 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: