When you install Microsoft SQL Server, it also installs 5 databases named master, model, msdb, and tempdb. These databases will be for internal use. This means that you should avoid directly using them, unless you know exactly what you are doing.

One of the databases installed with Microsoft SQL Server is named master. This database holds all the information about the server on which your Microsoft SQL Server is installed. For example, we saw earlier that, to perform any operation on the server, you must login. The master database identifies any person, called a user, who accesses the database, about when and how.

Besides identifying who accesses the system, the master database also keeps track of everything you do on the server, including creating and managing databases.

You should not play with the master database; otherwise you may corrupt the system. For example, if the master database is not functioning right, the system would not work.

As you should know from your learning C#, a namespace is a group of items that each has a unique name. For example, if you start creating many databases, there is a possibility that you may risk having various databases with the same name. If using a namespace, you can isolate the databases in various namespaces. In reality, to manage many other aspects of your database server, you use namespaces and you put objects, other than databases, within those namespaces. Therefore, a namespace and its content can be illustrated as follows:

Notice that there are various types of objects within a namespace.

Within a namespace, you can create objects as you wish. To further control and manage the objects inside of a namespace, you can put them in sub-groups called schemas. A schema is a group of objects within a namespace. This also means that, within a namespace, you can have as many schemas as you want:

To manage the schemas in a namespace, you need a way to identify each schema. Based on this, each schema must have a name. In our illustration, one schema is named Schema1. Another schema is named Schema2. Yet another schema is named Schema_n.

There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is called sys.

The sys schema contains a list of some of the objects that exist in your system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases object using the same name you gave it.

In the previous lesson, we saw that, before using a database, you must establish a connection with the server. You do this using a user account that can use the server. Once the connection exists, you can create a database. In Microsoft SQL Server, the user who creates a database is referred to as the database owner. To identify this user, when Microsoft SQL Server is installed, it also creates a special user account named dbo. This account is automatically granted various permissions on the databases of the server.

Because the dbo account has default access to all databases, to refer to an object of a database, you can qualify it by typing dbo, followed by the period operator, followed by the name of the object.

A schema is an object that contains other objects. To access the schemas of a database, in the Object Explorer, expand the Databases node, expand the database that will hold or own the schema, and expand the Security node.

To programmatically create a schema, the syntax to follow is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::=
    {
    schema_name
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name
    }

<schema_element> ::= 
    { 
        table_definition | view_definition | grant_statement |
        revoke_statement | deny_statement 
    }

The most important part is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

In this case, start with the CREATE SCHEMA expression and add a name to it. Here is an example:

1> CREATE SCHEMA PrivateListing;
2> GO
1>

The other parts deal with issues we have not studied yet.

Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:

Schema1.Something1

As mentioned already, when Microsoft SQL Server is installed, it creates a schema named dbo. This is probably the most common schema you will use. In fact, if you don't create a schema in a database, the dbo schema is the default and you can apply it to any object in your database.

SQL Selection

Introduction to SQL Operators and Operands

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.