Home

Topics on Conjunctions and Disjunctions
 

BETWEEN a Starting and Ending Value
 

Introduction

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.
 

Practical LearningPractical Learning: Introducing BETWEEN Operations
  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. In the Object Explorer, expand Databases. Make sure you have the FunDS1 database (if you don't have it, create it by opening the FunDS1.sql file from the Resources that accompany these lessons (or open the FunDS1.txt) file and execute it)
  4. Expand FunDS1 and expand its Tables node
  5. Right-click Inventory.StoreItems and click Edit Top 200 Rows
  6. Right-click the table in the middle window, position the mouse on Pane, and click Diagram
  7. Right-click anywhere in the windows -> Pane -> Criteria
  8. Right-click anywhere in the windows -> Pane -> SQL
  9. In the Criteria pane, click the first box under Alias and type Item #
  10. Click the second box under Alias and type Date Entered
  11. Click the fourth box under Alias and type Name - Description
  12. Click the fifth box under Alias and type Unit Price
  13. Click the sixth box under Alias and type Discount Rate
 Topics on Conjunctions and Disjunctions

Creating a BETWEEN Operation

To visually create a BETWEEN expression, in the Criteria pane, click the box of the column on which the operation will be performed and type the expression. The statement is in the form:

BETWEEN Start AND End

In the SQL, the BETWEEN operator is used in a WHERE condition and combines with an AND conjunction. The basic formula to use a BETWEEN operator in SQL is:

WHERE Expression BETWEEN Start AND End

The Expression placeholder is usually the name of the column whose values you want to examine. Start is the starting value of the range to consider. End is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End.

The values used in a BETWEEN operation must be of the same type and the SQL interpreter must be able to identify the range of values:

  • If the column Expression is character-based (of data type char or nchar), the SQL interpreter will first internally sort the values of the column, then it will get the values in the specified range of alphabetic characters. Here is an example: 
    SELECT * FROM Administration.Students
WHERE Grade BETWEEN N'a' AND N'c'
    BETWEEN
  • If the column Expression is string-based (of data type varchar or nvarchar), the SQL interpreter will first internally sort the strings of the column, then consider the specified range in alphabetical order. Here is an example: 
    SELECT * FROM Students
WHERE LastName  BETWEEN N'Jeffers' AND N'Nyat';
    BETWEEN
  • If the column Expression is number-based (integer, real, and their variants), the SQL interpreter will consider the range of values from begining to end. Here is an example: 
    SELECT * FROM Students
WHERE StudentNumber  BETWEEN 1003 AND 1006;
    BETWEEN
  • If the column is date/time-based, the BETWEEN operator would consider the values from the starting date/time to the end date/time

Of course, when creating a BETWEEN operation, you should be able to perform the operation on only similar values.

Practical LearningPractical Learning: Creating a BETWEEN Operation
  1. To see the first 10 records, in the SQL pane, change 200 to 10
  2. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
     
    Creating a BETWEEN Operation
  3. In the SQL pane, delete TOP (10)
  4. In the Criteria pane, click the box at the intersection of ItemNumber and Filter
  5. Instead of the top 10 records, to see the records from 100000 to 300000, type BETWEEN 100000 AND 300000
  6. To see the result, right-click any of the windows and click Execute SQL

    Creating a BETWEEN Operation
  7. In the Criteria pane, click ItemNumber, press Tab six times and type 770240
  8. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
  9. In the Result pane, write down the indicated value of Date Entered. Get the same date of the previous month from that date. For example, if the record was created on 02/02/2011, one month from it would be 01/01/2011. If the previous month doesn't have the same day (for example February doesn't have 30 and April doesn't have 31), use the highest day of that month. Write down the resulting date
  10. In the Criteria pane, click ItemNumber, press Tab six times and press Delete
  11. In the Criteria pane, click the box at the intersection of DateEntered and Filter, type BETWEEN N', followed by the date you found from the above step, followed by ' AND N', followed by the date of the 770240 record, followed by '. Here is an example:
     
    Creating a BETWEEN Operation
  12. To see the result, right-click any of the windows and click Execute SQL

    Creating a BETWEEN Operation
  13. In the Criteria pane, click DateEntered, press Tab six times, and press Delete

Negating a BETWEEN Operation

To negate a BETWEEN operation, you can precede it with NOT. Here is an example:

SELECT * FROM Students
WHERE not Grade BETWEEN N'a' AND N'c';

This would produce:

BETWEEN

To make the statement easier to read, you should include in parentheses the section after NOT. Here is an example:

SELECT * FROM Students
WHERE NOT (Grade BETWEEN N'a' AND N'c');

An alternative is to use the ranges outside the desired one, such as BETWEEN N'd' AND N'z'.

Combining BETWEEN Operations

You can combine two or more BETWEEN operation to consider more than one range of values. If you want to combine two BETWEEN operation, you can use the following formula:

BETWEEN Start1 AND End1 OR Start2 AND End2

To make the statement easier to read, you should include each operation in parentheses:

BETWEEN (Start1 AND End1) OR (Start2 AND End2)

Each operation is evaluated individually. The first range is considered, followed by the second. Then the records resulting from each operation is combined to the other. Here is an example:

SELECT * FROM Students
WHERE (StudentNumber BETWEEN 1002 AND 1004)
      OR
      (StudentNumber BETWEEN 1008 AND 1010);

This would produce:

Logical Conjunction

In the above statement, examine the ranges from a single column. You can create one BETWEEN operation from one column and another operation from another column, then combine them. Here is an example:

SELECT * FROM Students
WHERE (StudentNumber BETWEEN 1002 AND 1004)
      OR
      (Grade BETWEEN 'A' AND N'B');

This would produce:

Logical Conjunction
 
 
 

IN a Selected Series
 

Introduction

Imagine you have a field that holds certain repeating values. Here is an example we used:

USE Exercise;
GO
CREATE TABLE Employees([Empl #] nchar(10), [First Name] nvarchar(20),
	[Last Name] nvarchar(20), Salary money, [Full Time?] bit);
GO
INSERT INTO Employees
VALUES(N'29730', N'Philippe', N'Addy', 20.05, 1);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'28084', N'Joan', N'Shepherd', 12.72);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'79272', N'Joshua', N'Anderson', 18.26);
GO
INSERT INTO Employees
VALUES(N'22803', N'Gregory', N'Swanson', 15.95, 0);
GO
INSERT INTO Employees([Empl #], [Last Name], Salary, [Full Time?])
VALUES(N'28084', N'Shepherd', 12.72, 1),
      (N'39742', N'Anders', 8.88, 0);
GO
INSERT INTO Employees
VALUES(N'83084', N'Josephine', N'Anderson', 20.02, 1);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'79272', N'James', N'Anders', 18.26),
      (N'27924', N'Gregory', N'Hope', 12.85),
      (N'39742', N'John', N'Anderson', 8.88);
GO
SELECT * FROM Employees;
GO

This would produce:

Sorting Records

Notice that some names (such as Sheperd, Anders, and Anderson). We already know how to fnd records that share a certain value. For example, the following code gives us the list of employees whose last name is Anderson:

SELECT * FROM Employees
WHERE [Last Name] = N'Anderson';

This would produce:

IN

Imagine you want to add another value to your seach. We already know how to do that with an OR operator. Here is an example:

SELECT * FROM Employees
WHERE ([Last Name] = N'Anderson') OR ([Last Name] = N'Shepherd');

This would produce:

IN a Selected Series

In the same way, you can add as many OR expressions as you see fit. As seen previously, you can create a logical disjunction that deals with either only one or more than one column. Here is an example that acts on two columns:

SELECT * FROM Employees
WHERE ([Last Name] = N'Anderson') OR ([Full Time?] IS NOT NULL);

This would produce:

IN a Selected Series

If your logical disjunction involves only one column, the SQL provides an alternative operator named IN.

Creating an IN Operation

The IN operation behaves like an OR applied to onw column but there are different ways you created each. You can create the expression visually or with code. First, the formula of an IN operation is:

IN(Expression1, Expression2, Expression_n)

You start with the IN keyword and add some parentheses to it. In the parentheses, put the list of values separated by commas.

To visually create an IN operation, open the table in the Query Designer. In the Filter box that corresponds to the column, type the expression.

In the SQL, the IN operator is associated with a WHERE statement. The formula to follow is: 

WHERE ColumnName IN(Expression1, Expression2, Expression_n)

Start with WHERE followed by the name of the column that holds the values to look for. In the parentheses of IN, add the desired values separated by commas. Each Expression can be one of the values of the ColumnName. This is equivalent to Expression1 OR Expression2 OR Expression_n, etc.

Practical LearningPractical Learning: Stepping IN
  1. In the Criteria pane, click the box at the intersection of Manufacturer and Filter
  2. To get a list of items made by Coach or Michael Kors, type IN(Coach, Michael Kors)
  3. Press F5 to execute the statement
     
    Stepping IN
  4. To see the result, right-click any of the windows and click Execute SQL

    Creating a BETWEEN Operation

Negating an IN Operation

As seen for the other operators, there are various ways you can negate an IN operator. Consider the following example:

SELECT * FROM Employees
WHERE [Last Name] IN (N'Anderson', N'Shepherd');

Logical Conjunction

The easiest way is to precede the operation with NOT. One solution is to put NOT before the name of the column. Here is an example:

SELECT * FROM Employees
WHERE NOT [Last Name] IN (N'Anderson', N'Shepherd');

Logical Conjunction

Another solution is to precede the IN keyword with NOT. This can be done as follows:

SELECT * FROM Employees
WHERE [Last Name] NOT IN (N'Anderson', N'Shepherd');

If the number of values in the column is short, to negate an IN operation, instead of the desired values, you can give the other values.

Functions and Data Selection
 

Built-In Functions and Data Selection

To refine your data analysis, you can use functions, whether functions you create yourself or Transact-SQL built-in functions. As always mentioned, the first candidates of functions you should 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 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   Registration.Students

This would produce:

Function

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, SingleParentHome
FROM   Registration.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.

 User-Defined Functions and Data Selection

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 Registration.GetFullName
(
	@FName nvarchar(20),
	@LName nvarchar(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 Registration.ShowYesOrNo
(
    @SingleParentHomeStatus bit
)
RETURNS nvarchar(3)
AS
BEGIN
    DECLARE @Result nvarchar(3);

    IF @SingleParentHomeStatus = 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 the name of the schema, followed by a period, 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,
       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;
GO

This would produce:

Function

Practical LearningPractical Learning: Closing the Lesson
  1. Close the Query Designer
  2. Close Microsoft SQL Server

Exercises
   

Lesson Summary Questions
  1. What's the basic formula to create a between expresion?
    1. BETWEEN Start AND End WHERE Expression
    2. WHERE Expression FROM Start TO End
    3. WHERE Expression BETWEEN Start AND End
    4. BETWEEN Start TO End WHERE Expression
    5. LIKE BETWEEN Start AND End WHERE Expression
  2. What's the basic formula to negate a between expresion?
    1. WHERE Expression NOT BETWEEN Start AND End
    2. NOT BETWEEN Start AND End WHERE Expression
    3. WHERE Expression FROM Start TO End SET NULL
    4. BETWEEN Start TO End WHERE Expression NOT ON
    5. NOT LIKE BETWEEN Start AND End WHERE Expression
  3. What's the formula to create an IN statement?
    1. IN(Expression_1, Expression_2, Expression_n) WHERE ColumnName
    2. WITH ColumnName IN(Expression_1, Expression_2, Expression_n)
    3. IN(Expression_1, Expression_2, Expression_n) SET ColumnName = TRUE
    4. WHERE ColumnName IN(Expression_1, Expression_2, Expression_n)
    5. INSERT IN(Expression_1, Expression_2, Expression_n) WHERE ColumnName

Answers
  1. Answers
    1. Wrong Answer: The WHERE condition must be stated first
    2. Wrong Answer: There is no valid FROM ... TO clause in a BETWEEN statement
    3. Right Answer: That's the right formula
    4. Wrong Answer: A BETWEEN statement doesn't use TO
    5. Wrong Answer: The presence of LIKE messes up already
  2. Answers
    1. Right Answer: That's the right formula
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  3. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: That's the right formula
    5. Wrong Answer

 
 
 
   
 
Previous Copyright © 2008-2016, FunctionX, Inc., Inc. Next