An operation is an action performed on one or more
values either to modify the value held by one or both of the variables or
to produce a new value by combining values. Therefore, an operation is
performed by using at least one symbol and one value. The symbol used in an
operation is called an operator. A value involved in an
operation is called an operand.

Like every language, SQL ships with some words used to
carry its various operations. One of these words is PRINT. To display something in plain text as a result of a statement, type PRINT followed by what to display. Therefore, PRINT
uses the following formula:

PRINT WhatToPrint

The item to
display can be anything that is allowed and it is provided on the right
side of PRINT. If it is a regular constant number, simply type it on the right side of PRINT.
The item to display can also be an operation or the
result of an operation. You can also display an expression as a combination of number(s) and
sentences.

SELECT Something

The SELECT operator can be used, among other things, to display a value.
The SELECT keyword uses the following syntax:

SELECT What

Based on this, to use it, where it is needed, type SELECT
followed by a number, a word, a string, or an expression. The item to
display follows some of the same rules as PRINT. One of the
differences between PRINT and SELECT is that:

PRINT is mostly used for testing a simple value, a string, or an
expression. Therefore, it displays its results in a regular white window
under a tab labeled Messages. PRINT can be used with only one value

SELECT is the most regularly used SQL operator. We will see that it
is used to retrieve records from a table. For this reason, SELECT
displays its results in an organized window made of categories called
columns, under a tab labeled Results. SELECT can be used with more
than one value

As done for PRINT, to display a sentence using SELECT,
type it in single-quotes on the right side of this operator. Here is an executed
example:

When you create a SELECT statement, what is on the
right side of SELECT must be a value. Here is an example:

SELECT 226.75;

Based on this definition, instead of just being a value, the
thing on the right side of SELECT must be able to produce a value. As we
will see in the next sections, you can create algebraic operations on the right
side of SELECT.

As mentioned already, unlike PRINT, SELECT can be used to
display more than one value. The values must be separated by commas. Here is an
example:

Because we mentioned that the thing on the right side must
produce a result, you can as well use another SELECT statement that it itself
evaluates to a result. To distinguish the SELECT sections, the second one should
be included in parentheses. Here is an example:

SELECT (SELECT 448.25);
GO

When one SELECT statement is created after another,
the second is referred to as nested.

Just as you can nest one SELECT statement inside of another,
you can also nest one statement in another statement that itself is nested. Here
is an example:

SELECT (SELECT (SELECT 1350.75));
GO

SELECT This AS That

In the above introductions, we used either PRINT
or SELECT to display something in the query window. One of the
characteristics of SELECT is that it can segment its result in
different sections. SELECT represents each value in a section called a
column. Each column is represented with a name also called a caption. By
default, the caption displays as "(No column name)". If you want
to use your own caption, on the right side of an expression, type the AS
keyword followed by the desired caption. The item on the right side of the AS keyword must be
considered as one word. Here is an
example:

SELECT 24.85 AS HourlySalary;

This would produce:

You can also include the item on the right side of AS
in single-quotes. Here is an
example:

SELECT 24.85 AS 'HourlySalary';

If the item on the right side of AS is
in different words, you should include it in single-quotes or put them in inside of an opening square bracket
"[" and a closing square bracket "]". Here is an
example:

SELECT 24.85 AS 'Hourly Salary';

If you create different sections, separated by a
comma, you can follow each with AS and a caption. Here is an
example:

SELECT 'James Knight' As FullName, 20.48 AS Salary;

This would produce:

The above statement could also be written as follows:

SELECT 'James Knight' As [Full Name], 20.48 AS [Hourly Salary];

Math Operators

The Unary Positive and Negative Operators

A unary operator is an operator that performs its
operation on only one operand.

As you may know from C#, to express a positive number,
you can write + on its left side. As a mathematical convention, when a value is
positive, you do not need to express it with the + operator.

The - sign must be typed on the left side of a number to
make it negative.

Arithmetic Operators

To add two numbers, you use the addition operator. Here is an example:

PRINT 125 + 4088

In Transact-SQL, you can also perform the addition on
text. Here is an example:

PRINT 'Henry ' + 'Kono'

To subtract two numbers, you use the - operator. Here is an example:

PRINT 1240 - 608

Unlike the addition, the subtraction operation is not
associative. This means that a - b - c is not necessarily equal to c - b -
a. This is illustrated in the following statements:

PRINT 128 - 42 - 5
PRINT 5 - 42 - 128

This would produce:

81
-165

Notice that both operations of the addition convey the
same result.

To multiply two numbers, you use the * operator. Here is an example:

PRINT 128 * 42

This would produce 5376

To divide two numbers you use the / operator. Here is an example:

PRINT 128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by
zero (0). Make sure that you know the relationship(s) between the numbers
involved in the operation.

To get the remainder of a division operation between
two numbers, you use the percent operator %. Here is an example:

PRINT 128 % 42

This would produce 2.

SQL Operators

Parentheses

Like most computer languages, Transact-SQL uses parentheses to
isolate a group of items that must be considered as belonging to one
entity. For example, as we will learn soon, parentheses allow a function
to delimit the list of its arguments.
Parentheses can also be used to isolate an operation or an expression with
regards to another operation or expression. For example, when studying the
algebraic operations, we saw that the subtraction is not associative and
can lead to unpredictable results. In the same way, if your operation
involves various operators such as a mix of addition(s) and subtraction(s), you can
use parentheses to specify how to proceed with the operations,
that is, what operation should (must) be performed first. Here is an
example:

PRINT (154 - 12) + 8
PRINT 154 - (12 + 8)

This would produce:

150
134

As you can see, using the parentheses controls how the
whole operation would proceed. This difference can be even more
accentuated if your operation includes 3 or more operators and 4 or more
operands. Here is another example of a nested SELECT statement that uses
parentheses:

SELECT
(SELECT 448.25 * 3) +
(SELECT 82.28 - 36.04);
GO

Bit Manipulations

When you use a value in your database or application, the
value must be stored somewhere in the computer memory using a certain amount of space.
A value occupies space that resembles a group of small boxes. In
our human understanding, it is not always easy to figure out how a letter
such as as B is stored in 7 seven small boxes when we know that B is only
one letter.

Bit manipulation or a bit related operation allows you to control how
values are stored in bits. This is not an operation you will need to
perform very often, especially not in the early stages of your database. Nevertheless, bit operations (and related overloaded operators)
are present in all or most programming environments, so much
that you should be aware of what they do or what they offer.

One of the operations you can perform on a bit
consists of reversing its value. That is, if a bit holds a value of 1, you
may want to change it to 0 and vice-versa. This operation can be taken
care of by the bitwise NOT operator that is represented with the tilde
symbol ~

The bitwise NOT is a unary operator that must be
placed on the left side of its operand as in

~Value

Here is an example:

PRINT ~158

To perform this operation, the Transact-SQL
interpreter considers each
bit that is part of the operand and inverts the value of each bit from 1
to 0 or from 0 to 1 depending on the value the bit is holding. This
operation can be resumed in the following table:

Bit

~Bit

1

0

0

1

Consider a number with a byte value such as 248. In
our study of numeric systems, we define how to convert numbers from one system to another. Based on this,
the binary value of decimal 248 is 1111 1000 (and its hexadecimal value is
0xF8). If you apply the bitwise NOT operator on it to reverse the values
of its bits, you would get the following result:

Value

1

1

1

1

1

0

0

0

~Value

0

0

0

0

0

1

1

1

The bitwise & is a binary operator that uses the
following syntax

Operand1 & Operand2

This operator considers two values and compares the
bit of each with the corresponding bit of the other value. If both
corresponding bits are 1, the comparison produces 1. Otherwise, that is,
if either bit is 0, the comparison produces 0. This comparison is resumed
as follows:

Bit1

Bit2

Bit1
& Bit2

0

0

0

1

0

0

0

1

0

1

1

1

Imagine you have two byte values represented as 187
and 242. The binary value of decimal 187 is 1011 1011
(and its hexadecimal value is 0xBB). The binary value of decimal 242 is
1111 0010 (and its hexadecimal value is 0xF2). Let’s compare these two
values bit by bit, using the bitwise AND operator:

Binary

Decimal

N1

1

0

1

1

1

0

1

1

187

N2

1

1

1

1

0

0

1

0

242

N1
& N2

1

0

1

1

0

0

1

0

178

Most of the times, you will want the interpreter to
perform this operation and use the result in your program. This means that
you can get the result of this operation and possibly display it to the
user. The above operation can be performed by the following program:

PRINT 187 & 242

This would produce 178

You can perform another type of comparison on bits
using the bitwise OR operator that is represented by |. Its syntax is:

Value1 | Value2

Once again, the interpreter compares the corresponding
bits of each operand. If at least one of the equivalent bits is 1, the
comparison produces 1. The comparison produces 0 only if both bits are 0.
This operation is resumed as follows:

Bit1

Bit2

Bit1
| Bit2

0

0

0

1

0

1

0

1

1

1

1

1

Once again, let’s consider decimals 187 and 242.
Their bitwise OR comparison would render the following result:

Binary

Decimal

N1

1

0

1

1

1

0

1

1

187

N2

1

1

1

1

0

0

1

0

242

N1 |
N2

1

1

1

1

1

0

1

1

251

You can also let the compiler perform the operation
and produce a result. Here is an example:

PRINT 187 | 242

This would produce 251

Bits Comparison: The Bitwise-Exclusive XOR Operator ^

Like the previous two operators, the bitwise-exclusive
OR operator performs a bit comparison of two values. It syntax is:

Value1 ^ Value2

The compiler compares the bit of one value to the
corresponding bit of the other value. If one of the bits is 0 and the
other is 1, the comparison produces 1. In the other two cases, that is, if
both bits have the same value, the comparison produces 0. This operation
is resumed as follows:

Bit1

Bit2

Bit1
^ Bit2

0

0

0

1

0

1

0

1

1

1

1

0

We will again consider decimals 187 and 242. Their
bitwise-exclusive XOR comparison would render the following result:

Binary

Decimal

N1

1

0

1

1

1

0

1

1

187

N2

1

1

1

1

0

0

1

0

242

N1 ^
N2

0

1

0

0

1

0

0

1

73

If the interpreter performs this operation, it can
produce a result as in the following example:

PRINT 187 ^ 242;

This would produce 73.

Variables Fundamentals

Introduction

In the previous sections, we used some values such as
242 or 'James Knight'. These are
constant values because we certainly know them before using them and we
did not change them
in our statements. If you intend to use a certain category of value over
and over again, you can declare a variable for it. Like C#, the SQL
supports variables.

Declaring Variables

To
declare a variable, use the DECLARE keyword using the following
formula:

DECLARE Options

The DECLARE keyword lets the interpreter know
that you are declaring a variable. The DECLARE keyword is followed
by a name for the variable. In Transact-SQL, the name of a variable starts
with the @ sign. The name of a variable allows you to identify the area of
memory where the value of the variable is stored. While other languages
like C/C++, Pascal, Java, C#, etc, impose strict rules to names,
Transact-SQL is extremely flexible. A name can be made of digits only.
Here is an example:

DECLARE @264

Such a name made of digits can create confusion
with a normal number. A name can also be made of one or more words.

To
avoid confusion, to name our variable, here are the rules we will follow:

A name will start with either an underscore or a letter. Examples
are @_n, @act, or @Second

After the first character as an underscore or a letter, the name
will have combinations of underscores, letters, and digits. Examples
are @_n24 or @act_52_t

A name will not include special characters such as !, @, #, $, %, ^,
&, or *

If the name is a combination of words, each word will start in
uppercase. Examples are @DateHired, @_RealSport, or @DriversLicenseNumber

To declare a variable, as we will see in the next
sections, after giving a name to a variable, you must also specify the
amount of memory that the variable would need. The amount of memory is
also called a data type. Therefore, the declaration of a variable uses the
following formula:

DECLARE @VariableNameDataType;

You can also declare more than one variable. To do
that, separate them with a comma. The formula would be:

Unlike many other languages like C/C++, C#, Java, or Pascal, if
you declare many variables that use the same data type, the name of each
variable must be followed by its own data type.

Initializing a Variable

After declaring a variable, the
interpreter reserves a memory for it but the space does not
necessarily hold a recognizable value. This means that, at this time, the
variable is null. One way you can change this is to give a value to the
variable. This is referred to as initializing the variable.

Remember
that a variable's name starts with @ and whenever you need to refer to the
variable, you must make sure you include the @ sign. To initialize a
variable, in the necessary section, type the SELECT or the SET
keyword followed by the name of the variable, followed by the assignment
operator "=",
followed by an appropriate value. The formula used is:

SELECT @VariableName = DesiredValue

or

SET @VariableName = DesiredValue

Once a variable has been initialized, you can make its
value available or display it. This time, you can type the
name of the variable to the right side of PRINT or SELECT.

Data Types

Introduction

After setting the name of a variable, you must specify
the amount of memory that the variable will need to store its value. Since there
are various kinds of information a database can deal with, the SQL
provides a set of data types.

Boolean Variables

Like C#, the SQL supports Boolean values. To declare a Boolean variable, you use
the BIT or bit keyword. Here is an example:

DECLARE @IsOrganDonor bit;

After declaring a Boolean variable, you can initialize
it with 0 or another integral value. If the variable is initialized with 0, it
receives the Boolean value of False. If it is initialized with any
other number, it receives a True value. Here is an example of using a Boolean
variable:

Practical
Learning: Using a Boolean Variable

To declare and use a Boolean variable, change the code of the Operate
button as follows:

If you want to use very small
numbers such as student's ages, or the number of pages of a brochure or
newspaper, apply the tinyint data type to such a field. A variable with the tinyint data
type can hold positive numbers that range
from 0 to 255. Here is an example:

The smallint data type follows the
same rules and principles as the C#'s short data type to store
numbers that range between -32,768 and 32,767.
Here is an example:

The bigint data type follows the
same rules and principles as the C#'s long data type and can hold numbers from -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807. Here is an example:

The binary data type is used for a variable that would hold hexadecimal numbers. Examples of hexadecimal
numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all
values of the variable would have the exact same length (or quantity).

If you anticipate that some entries would be different than others, then
use the alternative varbinary data type. The varbinary type
also
is used for hexadecimal numbers but allows dissimilar entries, as long as
all entries are hexadecimals.

Practical
Learning: Using an Integer Variable

To declare and use an integer variable, change the code of the Operate
button as follows:

To support decimal numbers, the SQL provides various data
types. To declare a variable that can hold decimal numbers, you can use the numeric or
the decimal
data types (either decimal or numeric would produce the same effect in
SQL Server). Here is an example:

To declare a variable that would hold decimal numbers
with single precision, you can use the float or the real
data type. Here is an
example:

A precision is the number of digits
used to display a numeric value. For example, the number 42005 has a
precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the int and its
variants) or a floating-point number (float and real), the precision is
fixed by the database and you can just accept the value set by the
Microsoft SQL Server interpreter.
For a decimal number (decimal or numeric data types),
Microsoft SQL Server allows
you to specify the amount of precision you want. The value must be an
integer between 1 and 38 (28 if you are using SQL Server 7).

The scale of a number
if the number of digits on the right side of the period (or the character
set as the separator for decimal numbers for your language, as specified in Control Panel).
The scale is used only for numbers that have a decimal part, which
includes currency (money and smallmoney) and decimals (numeric
and
decimal). If a variable is declared with the decimal or numeric data type, you can specify the amount of
scale you want. The value must be an integer between 0 and 18. Here is an
example:

Remember that you can declare an use various variables. Here
is an example:

If a variable would hold monetary values, you can
declare it with the money data type.
A variable with a money data type can hold positive or negative values from
-922,337,203,685,477.5808 to +922,337,203,685,477.5807. Here is an
example:

While the money data type
can be used for a variable that would hold large quantities of currency
values, the smallmoney data type can be applied for a variable whose
value cannot be lower than -214,748.3648 nor higher than 214,748.3647.

The precision and scale of a money or smallmoney
variable are fixed by Microsoft SQL Server. The scale is fixed to 4.

Once again, remember that you can declare and use
various variables. Here is an example:

A DATETIME data type is used for a column whose data would consist of date and/or
time values, just like the DateTime structure of the .NET Framework. The entries must be valid date or time values but Microsoft SQL Server
allows a lot of flexibility, even to display a date in a non-traditional
format. The date value of a datetime field can be comprised between
January 1st, 1753 and December 31, 9999.

To initialize a DATETIME variable, include its
value between single-quote. If the value is a date, separate the
components of the value with the symbol recognized in Control Panel as the
Date Separator:

Here is an example:

If the value is a time period, still include it in
single-quotes. Inside of the quotes, follows the rules and formats
specified in the Control Panel:

The smalldatetime data type is an
alternative to datetime. It follows the same rules
and principles as the datetime data type except that a date value
must be comprised between January 1st, 1900 and June 6, 2079.

Practical
Learning: Using Date and Time Variables

To use a date value, change the code of the Operate
button as follows:

Like C#, Transact-SQL supports character variables. To
declare such a variable, use the char data type. Here is
an example:

DECLARE @Gender char;

By default, the char data type can be applied to a
variable that would hold one character at a time. After declaring the
variable, when initializing it, include its value in single-quotes. Here
is an example:

If a
variable will hold strings of different lengths, declare it with the varchar data
type. The maximum length of text that a field of varchar type
can hold is equivalent to 8 kilobytes.

In some circumstances, you will need to change or
specify the number of characters used in a string variable. Although a First Name and a Book
Title variables should use the varchar type, both variables would not
have the same length of entries. As it happens, people hardly have a first
name that is beyond 20 characters and many book titles go beyond 32
characters. In this case, both variables would use the same data type but
different lengths.

To specify the maximum number of characters that can be
stored in a string variable, on the right side of char or varchar, type an
opening and a closing parentheses. Inside of the parentheses, type the
desired number.

To initialize the variable, if you are using the Command
Prompt (SQLCMD.EXE), include its value between double-quotes. Here is an example:

If you are using a query window, don't include the string
value in double-quotes; otherwise, you would receive an error:

Therefore, if using the query window, include the
string in single-quotes:

The text data type can be used on a variable whose data would consist of ASCII characters. As opposed to a varchar
type of field, a text type of field can hold text that is longer than 8
kilobytes.

The nchar, nvarchar, and ntext types follow the same rules as the char, varchar, and text
respectively, except that they can be applied to variables that would hold
international characters, that is, characters of languages other than US
English. This is done following the rules of Unicode formats.

Practical
Learning: Using String Variables

To declare and use some string variables, change the code of the Operate
button as follows: