The Microsoft SQL Server Management Studio

There are many tools you will use in Microsoft SQL server. One of them is called Microsoft SQL Server Management Studio. To access it, you can click Start -> (All) Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio. A dialog box would come up but you can click Cancel on it:

The top section of the SQL Server Management Studio displays the classic title bar of a regular window, with an icon on the left, followed by the title of the application, and the system buttons on the right side. Under the title bar, the menu bar displays categories of menus that you will use to perform the various necessary operations. A toolbar displays under the main menu.

The left side of the interface displays the Object Explorer window, with its title bar labeled Object Explorer. On the right side of the Object Explorer title, there are three buttons. If you click the first button that points down, a menu would appear, which allows you to specify whether you want the window to be floated, docked, or tabbed.

This window is dockable, meaning you can move it from the left side to another side on the interface. To do this, you can click and drag its title bar to a location of your choice. The window is also floatable, which means you can place it somewhere in the middle of the interface:

To place the window back to its previous position, you can double-click its title bar. The window can also be tabbed. This means that the window can be positioned either vertically or horizontally.

The right side of the window is equipped by a window whose tab is labeled Summary. This area will be used to display either the contents of what is selected in the Object Explorer, or to show a result of some operation. As you will see later on, many other windows will occupy the right section but they will share the same area. To make each known it will be represented with a tab and the tab shows the name (or caption) of a window.

After installing Microsoft SQL Server, you can use it to create an manage databases. To assist you with this, you can use Microsoft SQL Server Management Studio.

Microsoft SQL Server Management Studio is a window you will use to create and manage databases. To assist you with this, it comes equipped with various tools, some are graphically-based and some others required code writing for you. To perform some operations, you will use the main menu. Some other operations will be available from clicking one of the buttons on the toolbar. Many other operations will start from the Object Explorer.

The Object Explorer displays a list of items as a tree-style. One of the most regular used items will be the name of the server you are using. If you are just starting to learn database development or you are a junior database developer, you may use or see only one server. In some cases, you may be dealing with many. Regardless, you should always know what server you are currently connecting to. This is easy to check  with the first node of the Object Explorer. In the following example, the server is named Central:

The name of the server is followed by parentheses.

In the previous section, we saw that, to establish a connection to a server, you must authenticate yourself. In some cases you may use the same account over and over again. In some other cases you may have different accounts that you use for different scenarios, such as one account for database development, one account for database management, and/or one account for database testing. Some operations cannot be performed by some accounts. When performing some operations, you should always know what account you are using. You can check this in the parentheses of the server name. In the following connection, an account called Administrator is currently connecting to a server named Central:

When the server name is selected in the Object Explorer, the whole caption of its node displays in the Summary window.

Probably the most regular node you will be interested in, is labeled Databases. This node holds the names of databases on the server you are connecting. Also, from that node, you can perform almost any necessary operation of a database. To see most of the regularly available actions, you can expand the Databases node and some of its children. You can then right-click either Databases or one of its child nodes.

Although you will perform many of your database operations visually, some other operations will require that you write code. To assist with with this, Microsoft SQL Server provides a code editor and various code templates.

To open the editor, on the Standard toolbar, you can:

• Press Ctrl + N
• On the main menu, you can click File -> New -> Query With Current Connection
• On the Standard toolbar, click the New Query button
• In the Object Explorer, right-click the name of the server and click New Query

This would create a new window and position it on the right side of the interface. Whether you have already written code or not, you can save the document of the code editor at any time. To save it:

• You can press Ctrl + S
• On the main menu, you can click File -> Save SQLQueryX.sql...
• On the Standard toolbar, you can click the Save button

You will be required to provide a name for the file. After saving the file, its name would appear on the tab of the document.

Besides the SQL Server Management Studio, you can also work on Microsoft SQL Server from the DOS command prompt. This is done using an application or command named SQLCMD.EXE. To use, open the Command Prompt, type SQLCMD (case-insensitive) and press Enter.

After establishing a connection, you can take actions, such as creating a database and/or manipulating data.

The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard.

The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.

As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database.

As we will learn in this and the other remaining lessons of this site, you use SQL by writing statements. To help you with this, Microsoft SQL Server provides a window, also referred to as the Query Window, that you can use to write your SQL code. To access it, on the left side of the window, you can right-click the name of the server and click New Query. In the same way, you can open as many instances as the New Query as you want.

When the Query window comes up, it display a blank child window in which you can write your code. The code you write is a document and it can be saved as a file. The file would have the extension .sql. Every time you open a new query, it is represented with a tab. To switch from one code part to another, you can click its tab. To dismiss an instance of the query, first access it (by clicking its tab), then, on the right side, click the close button . If you had written code in the query window, when you close it, you would be asked to save your code. If you want to preserve your code, then save it. If you had already executed the code in the window (we will learn how to write and execute SQL code), you don't have to save the contents of the window.

In the next sections and lessons, we will learn various techniques of creating SQL statements with code. By default, when a new query window appears, it is made of a wide white area where you write your statements:

After writing a statement, you can execute it, either to make it active or simply to test it. To execute a statement:

• You can press F5
• On the main menu, you can click Query -> Execute
• On the SQL Editor toolbar, you can click the Execute button
• You can right-click somewhere in the code editor and click Execute

When you execute code, code editor becomes divided into two horizontal sections:

Also, when you execute code, the interpreter would first analyze it. If there is an error, it would display one or more red lines of text in its bottom section. Here is an example:

If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement.

A comment is text that the SQL interpreter would not consider as code. As such, a comment is written any way you like. What ever it is made of would not be read. Transact-SQL supports two types of comments. The style of comment that starts with /* and ends with */ can be used. To apply it, start a line with /*, include any kind of text you like, on as many lines as you want. To close the commented section, type */. Here is an example of a line of comment:

/* First find out if the database we want to create exists already */

A comment can also be spread on more than one line, like a paragraph. Here is an example:

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */

Transact-SQL also supports the double-dash comment. This comment applies to only one line of text. To use it, start the line with --. Anything on the right side of -- is part of a comment and would not be considered as code. Here is an example:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */


-- Now that the database is not in the system, create it

In SQL, after writing a statement, you can end it with a semi-colon. In fact, if you plan to use many statements in one block, you should end each with a semi-colon. When many statements are used, some of them must come after others.

To separate statements, that is, to indicate when a statement ends, you can use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be required, but the Microsoft SQL Server interpreter accepts GO as the end of a statement).

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

The item to display can also be an operation or the result of an operation. If you want to display a character, a word, or a sentence, include it between single-quotes. If you want to include a single-quote in your statement, double it; that is, write it twice. Here is an example:

You can also display an expression as a combination of number(s) and sentences as we will learn later.

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:

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:

SELECT 'Hourly Salary', 24.85

This would produce:

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 operation on the right side of SELECT. 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

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];

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

Algebra uses a type of ruler to classify numbers. This ruler has a middle position of zero. The numbers on the left side of the 0 are referred to as negative while the numbers on the right side of the rulers are considered positive:

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand.

The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side.

As a mathematical convention, when a value is positive, you do not need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned as we learned in the previous lesson.

To express a variable as positive or unsigned, you can just type it. here is an example:

PRINT +1250

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left.

Here is an example that uses two variables. One has a positive value while the other has a negative value:

SELECT -1250

An operator is referred to as binary if it operates on two operands.

The addition, also called the sum, is an operation used to add one item to another. The addition is performed using the + sign. To get the addition of two values, you type + between them, as in Value1 to Value2. After the addition has been performed, you get a new value that you can make available or display to the user. You can perform the addition on two numbers. 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'

You can also add more than two values, like a + b + c. The order you use to add two or more values doesn't matter. This means Value1 + Value2 is the same as Value2 + Value1. In the same way a + b + c is the same as a + c + b the same as b + a + c and the same as c + b + a.

The subtraction operation, sometimes called the difference, is used to take out or subtract one value from another value. It is essentially the opposite of the addition. The subtraction is performed with the - sign. 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. In the subtraction section, the numbers follow the same order but a different operation; and the last two operations render different results.

The multiplication allows adding one value to itself a certain number of times, set by a second value. As an example, instead of adding a value to itself in this manner: a + a + a + a, since the variable a is repeated over and over again, you could simply find out how many times a is added to itself, then multiply a by that number which, is this case, is 4. This would mean adding a to itself 4 times, and you would get the same result.

The multiplication is performed with the * sign. Just like the addition, the multiplication is associative: a * b * c = c * b * a. Here is an example:

PRINT 128 * 42

This would produce 5376

The division operation is similar to cutting an item in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. 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.

In the above division, 128/42, the result is 3. When you multiply 42 by 3, as in 42*3, you get 126. In some cases, you may be interested in knowing the amount that was left out after the operation. The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the percent sign (%). Here is an example:

PRINT 128 % 42

This would produce 2.

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

Thi

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:

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:

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:

Imagine you have two byte values represented as 187 and 242. Based on our study of numeric systems, 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:

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:

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

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

PRINT 187 | 242

This would produce 251

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:

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

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

PRINT 187 ^ 242;

This would produce 73.