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.

The SELECT operator can be used, among other things, to display a value. In this case, 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. 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. SELECT can be used with more than one value. The values must be separated by commas. Here is an example:

SELECT 'Hourly Salary', 24.85

Because we mentioned that the thing on the right side must produce a result, you can as well use another SELECT statement that 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

One of the characteristics of SELECT is that it can segment its result in different sections. SELECT presents each value in a section called a column. Each column is represented with text called a caption. By default, the caption is "(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;

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 commas, you can follow each with AS and a caption. Here is an example:

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

An alternative is:

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

Like C#, Transact-SQL supports the ability to declare and use variables. These are values that are stored in the computer memory.

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

DECLARE Options

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. Transact-SQL is extremely flexible with names. There are rules and suggestions you will use for the names:

• A name can 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

A name cannot be one of the following words reserved for Transact-SQL internal keywords:

To declare a variable, use the following formula:

DECLARE @VariableName DataType;

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

DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n DataType_n;

The name of each variable must be followed by its own data type.

After declaring a variable, to initialize it, 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 use its value.

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:

private void btnOperate_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command = new SqlCommand("DECLARE @IsMarried bit; " +
					"SET @IsMarried = 1; " +
				        "SELECT @IsMarried; ",
    					connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
	MessageBox.Show("Is Married? " + rdr[0].ToString());

    rdr.Close();
    connection.Close();
}

Like C#, the SQL supports integers. If a variable would hold natural numbers in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type. The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Remember that you can also declare and use more than one variable. Here is an example:

DECLARE @IsMarried bit, @EmplStatus int;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status];
GO

Here is an example that declares and uses a variable

private void btnOperate_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command = new SqlCommand("DECLARE @IsMarried bit; " +
					"SET @IsMarried = 1; " +
				        "SELECT @IsMarried AS [Married?]; ",
    connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
	MessageBox.Show("Is Married? " + rdr[0].ToString());

    rdr.Close();

    command = new SqlCommand("DECLARE @EmplStatus int; " +
		  	     "SET @EmplStatus = 2; " +
  			     "SELECT @EmplStatus; ",
			     connection);
    rdr = command.ExecuteReader();
    while (rdr.Read())
    {
	MessageBox.Show("Employment Status: " + rdr[0].ToString());
	cbxEmploymentsStatus.SelectedIndex = int.Parse(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
}

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.

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 that uses a smallint:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @NumberOfPages smallint; " +
                                                "SET @NumberOfPages = 748; " +
                                                "SELECT @NumberOfPages; ",
                                                connection);
            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Number of Pages: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

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.

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.

Like C#, Transact-SQL suports decimal number with single or double-precistion. If you want to use a variable whose values will be decimal, specify its data type as numeric or decimal. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @Distance decimal; " +
                                            "SET @Distance = 648.16; " +
                                            "SELECT @Distance; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
            MessageBox.Show("Distance: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

Transact-SQL supports decimal numbers whose precision is not particularly important. To declare such a variable, use the float or the real keyword. Here is an example:

void btnDatabaseClick(object sender, EventArgs e)
{
     using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @Radius float; " +
                                            "SET     @Radius = 52.49; " +
                                            "SELECT  @Radius; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while( rdr.Read() )
                MessageBox.Show("Radius: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
    }
}

A precision is the number of digits used to represent 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.

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: 

DECLARE @WeeklyTime Decimal(6,2);SET     @WeeklyTime = 36.50;
SELECT  @WeeklyTime AS [Time Worked];
GO

Here is an example that declares and uses a decimal variable:

private void btnOperate_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " +
					"SET @EmplStatus = 2; " +
					"SELECT @EmplStatus; ",
					connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	cbxEmploymentsStatus.SelectedIndex = int.Parse(rdr[0].ToString());
    }

    rdr.Close();

    command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " +
			     "SET @WeeklyHours = 36.50; " +
			     "SELECT @WeeklyHours; ",
			     connection);
    rdr = command.ExecuteReader();
    while (rdr.Read())
    {
	MessageBox.Show("Weekly Hours: " + rdr[0].ToString());
	txtWeeklyTime.Text = rdr[0].ToString();
    }

    rdr.Close();
    connection.Close();
}

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. 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.

Here is an example that uses currency variables:

private void btnOperate_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;");
    SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " +
					"SET @EmplStatus = 2; " +
					"SELECT @EmplStatus; ",
  					connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
 	cbxEmploymentsStatus.SelectedIndex = int.Parse(rdr[0].ToString());
    }

    rdr.Close();

    command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " +
			     "SET @WeeklyHours = 36.50; " +
			     "SELECT @WeeklyHours; ",
   			     connection);
    rdr = command.ExecuteReader();
    while (rdr.Read())
    {
	txtWeeklyTime.Text = rdr[0].ToString();
    }

    rdr.Close();

    command = new SqlCommand("DECLARE @HourlySalary SmallMoney; " +
			     "SET @HourlySalary = 15.72; " +
			     "SELECT @HourlySalary; ",
			     connection);
    rdr = command.ExecuteReader();
    while (rdr.Read())
    {
	MessageBox.Show("Hourly Salary: " + rdr[0].ToString());
	txtHourlySalary.Text = rdr[0].ToString();
    }

    rdr.Close();

    command = new SqlCommand("DECLARE @WeeklyHours Decimal(6, 2), " +
			     "	      @HourlySalary SmallMoney, " +
			     "	      @WeeklySalary SmallMoney; " +
			     "SET @WeeklyHours = 36.50; " +
			     "SET @HourlySalary = 15.72; " +
			     "SET @WeeklySalary = " +
			     "	  @WeeklyHours * @HourlySalary; " +
			     "SELECT @WeeklySalary; ",
			     connection);
    rdr = command.ExecuteReader();
    while (rdr.Read())
    {
	MessageBox.Show("Weekly Salary: " + rdr[0].ToString());
 	txtWeeklySalary.Text = rdr[0].ToString(); ;
    }

    rdr.Close();
   connection.Close();
}

A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a variable to hold a fixed number of characters, such as the book shelf numbers of a library, declare it with 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:

DECLARE @Gender char;
SET @GENDER = 'M';
SELECT @Gender AS Gender;
GO

By default, when you initialize a character variable, the interpreter reserves 8 bits of memory for the variable. This could be a problem if you want to store characters other than those used in US English. The alternative is to ask the interpreter to reserve 16 bits of space and follow Unicode rules. To do this, when initializing the variable, precede its value with N.

A string is a character or a combination of characters. 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.

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.

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.

When initializing the variable, to follow Unicode rules, precede its value with N follow by single-quotes:

1> DECLARE @FirstName    nchar(20),
2>         @LastName     nchar(20);
3> SET     @FirstName =  N'Philomène';
4> SET     @LastName  =  N'Açore';
5> SELECT  @FirstName As "Prénom",
6>         @LastName  As "Nom de Famille";
7> GO
Prénom               Nom de Famille
-------------------- --------------------
Philomène            Açore

(1 rows affected)
1>

In Powershell, if you are not using Unicode rules, the string must be included in double-quotes and if you are using Unicode, the string must be included in single-quotes.

If you have programmed in languages like C/C++, Pascal, or ADA, etc, you are probably familiar with the ability to give a friendly name to a known data type. Transact-SQL also gives you this option. A user-defined data type (UDT) is a technique of creating a data type based on an existing Transact-SQL data type.

Before creating a user-defined data type, you must be familiar with the existing types. If you want, you can create an alias name for one of these. To create a UDT in Transact-SQL, the basic formula to follow is:

CREATE TYPE AliasName FROM BaseType

You start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("CREATE TYPE NaturalNumber FROM int; ",
                                                connection);

            connection.Open();
            command.ExecuteNonQuery();
            
            MessageBox.Show("A user-defined data type has been created",
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
   {
        SqlCommand command = new SqlCommand("CREATE TYPE ShortString FROM nvarchar(20); " +
                                            "CREATE TYPE ItemCode FROM nchar(10); " +
                                            "CREATE TYPE LongString FROM nvarchar(80); " +
                                            "CREATE TYPE Salary FROM decimal(8, 2); " +
                                            "CREATE TYPE Boolean FROM bit;",
                                            connection);

        connection.Open();
        command.ExecuteNonQuery();
            
        MessageBox.Show("User-defined types have been created",
                        "Database Application",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

After creating a UDT, you can use it as you see fit. For example, you can declare a variable for it. Then, before using it, you must initialize it with the appropriate value. Here are examples:

DECLARE @EmployeeID NaturalNumber,
	@EmployeeNumber ItemCode,
        @FirstName ShortString,
        @LastName ShortString,
        @Address LongString,
        @HourlySalary Salary,
        @IsMarried Boolean;
SET     @EmployeeID = 1;
SET	@EmployeeNumber = N'28-380';
SET     @FirstName = N'Gertrude';
SET     @LastName = N'Monay';
SET     @Address = N'1044 Alicot Drive';
SET     @HourlySalary = 26.75;
SET     @IsMarried = 1;
SELECT  @EmployeeID AS [Empl ID],  @EmployeeNumber AS [Empl #],
        @FirstName AS [First Name], @LastName AS [Last Name],
        @Address, @HourlySalary AS [Hourly Salary],
        @IsMarried AS [Is Married ?];
GO

You can mix Transact-SQL data types and your own defined type in your code.