Introduction to SQL Code

To open the editor: 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.

After establishing a connection, you can take actions, such as creating a database and/or manipulating data. To provide the ability to create and manipulate a database, you use a data manipulation language (DML). The Structured Query Language, known as SQL, is a DML 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. Although Microsoft 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.

Throughout our lessons, we will use "SQL" and "Transact-SQL". Most of the time: SQL (used by itself) refers to a way the issue is used in most implementations of the language (Microsoft Access, MySQL, Oracle, DB2, etc) Transact-SQL may refer to a particular way the topic is used in Microsoft SQL Server, which means it may not work like that in other implementations

1. Server: Start the server
2. Server: Log in with the account you used to install Microsoft SQL Server
3. Server: To launch Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio
4. Server: On the Connect to Server dialog box, click Connect
5. pkatts: Start the computer and log in using your domain account
6. gmonay: Start the computer and log in using your domain account
7. rkouma: Start the computer and lo in using your domain account
8. operez: Start the computer and log in using your domain account

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, in the Object Explorer, right-click the name of the server and click New Query. In the same way, you can open as many instances of the New Query window as you want.

When the Query window comes up, it displays a blank child window in which you can write your code. 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, save it.

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:

• Press F5
• On the main menu, click Query -> Execute
• On the SQL Editor toolbar, click the Execute button
• 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 lines of error 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.

To make your code less boring, you can ask the Code Editor to apply colors to it. To do this, on the main menu, click tools -> Options... In the left tree, expand Environment and click Fonts and Colors:

To specify a color, in the Display Items list box, click an option, then change its colors in the Item Foreground and the Item Background combo boxes. Both check boxes are filled with colors. If you want to use a color that is not in the list, click the corresponding Custom button and create a color.

To assist you with writing code, Microsoft SQL Server provides many templates you can customize. These are available in the Template Explorer. To access it, on the main menu, click View -> Template Explorer.

The Template Explorer shows its item in a tree list. Like most windows in Microsoft SQL Server, it is floatable and dockable:

To use a code template, first create Query window. In the Template Explorer, expand the category you want. Then drag the desired option and drop it in the Query window. The Query window doesn't have to be empty.This means that, if it contains some code already, you can drag a node from the Template Explorer and drop it where it would add to the existing code. You can then edit the code any way you like. Comments 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.

Sometimes you will write a long line of code that may disappear on the right side of the Code Editor. To interrupt a line and continue the code on the next line, the formula to use is:

Section 1 \
Section 2

You start a section of code, add a backslash \, and continue the code on the next line.

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). The option to use GO is specified in the Batch Separator text box of the Query Execution section of the Options dialog box:

Besides the Template Explorer, Microsoft SQL Server provides the Properties window that can assist you with some issues. To get the Properties window if it is not visible:

• On the main menu, you can click View -> Properties Window
• Right-click the window on the right side and click Properties Window

The Properties window uses the common behaviors of other windows: it can auto-hide, it can be docked, or it can float.

The Properties window is divided in 5 sections:

The Properties window starts on top with a title bar, which displays the string Properties. If the window is docked somewhere, it displays the Window Position , the Auto-Hide , and the Close buttons on its right side. If the window is floating, it would display only the Close button .

Under the title bar, the Properties window displays a combo box. The content of the combo box depends on the contents of the main window and on what is going on. Under the combo box, the Properties window displays a toolbar with three buttons: Categorized button , Alphabetical , and Property Pages .

Under the toolbar, the Properties window displays a list of fields. This list depends on the contents of the main window and on what is going on. Here is an example when a Query window has been created:

On the right side, the list may be equipped with a vertical scroll bar. To rearrange the list, you can click the Alphabetical button .

Each field in the Properties window has two sections: the property's name and the property's value:

The box on the right side of each property name represents the value of the property. Some values can be changed and some others not. When a property can be changed, its name appears in normal characters:

When the name of a property appears disabled, it means the value cannot be changed.

Under the list of properties, there is a long bar that displays some messages. The area is actually a help section that displays a short description of the property that is selected in the main area of the Properties window.

In order to access something (such as a computer or a database), the user must be authenticated. The authentication is done using a username and a password. In the absence of this, a user would receive an error:

The user can also take advantage of the group he or she belongs to. For this reason, the ability to be authenticated in order to access the database or resource is called a login.

Before creating a login, the person for whom you want to create the account must have a user account on the network. In other words, the person must have an account that allows him or her to establish a connection to the domain.

You can create a login either visually or with code. To visually create a login in Microsoft SQL Server, in the Object Explorer, expand the Security node. Right-click Login and click New Login...

This would display the Login - New dialog box. The formula to programmatically create a login is:

CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

<option_list1> ::= 
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=  
    SID = sid
    | DEFAULT_DATABASE =database    
    | DEFAULT_LANGUAGE =language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL =credential_name <sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name<windows_options> ::=      
    DEFAULT_DATABASE =database
    | DEFAULT_LANGUAGE =language

To let Microsoft SQL Server generate skeleton code for you, on the Standard toolbar, click New Query to get a text editor. In the Template Explorer, expand the Login node, drag Create SQL Login Must Change Password and drop it in the empty text editor:

-- =================================================
-- Create SQL Login Must Change Password template
-- =================================================

CREATE LOGIN <SQL_login_name, sysname, login_name> 
	WITH PASSWORD = N'<password, sysname, Change_Password>' 
	MUST_CHANGE,
	CHECK_POLICY = <check_policy,ON or OFF, ON>;
GO

You start with the CREATE LOGIN expression, followed by the user name.

After creating a login, a user can use it to connect to the server. There are many options, as we will see in the next few sections.

After launching Microsoft SQL Server, in the Connect to Server dialog box, the user must select the type of server. This is done in the Server Type combo box:

In most cases, and for our lessons, we will use the Database Engine option. If the SQL Server Management Studio is already opened, to log in, the user can click Connect -> Database Engine...

The user must specify what computer has Microsoft SQL Server. If working from the Connect to Server dialog box, the user can select a computer in the Server Name. If the user wants to connect to another computer on the network, that is, a computer other than the one selected, he can type the computer name or select it in the Server Name combo box. Another option is to click the arrow of the Server Name combo box and select <Browse for more...>:

In this case, intermediate steps allow the user to select the computer. After selecting the computer, in the Authentication combo box, the user must specify the type of authentication to use. There are various choices.

If the user is working from PowerShell or from the Windows command prompt, to connect to a local computer, he can type SQLCMD -S followed by the name of the computer and press Enter. You can include space between -S and the name of the server. Here is an example:

You can also omit the space between -S and the name of the server. Here is an example:

To use Microsoft SQL Server, the user must provide a user name and a password. In reality, Microsoft SQL Server 2008 requires every user to log in but it provides various options. If the user already has a login, when he starts the computer an logs in to the operating system, when he launches Microsoft SQL Server, if the application recognizes his login account, he can log in without providing a user name and a password.

If you are visually creating a login and if you want the Microsoft Windows operating system to take care of logging, in which case the user will be able to connect to Microsoft SQL Server without providing a user name and a password, click the Windows Authentication radio button (it is the default and should be selected already:

If you are creating the login with code, add the FROM WINDOWS flag:

CREATE LOGIN See Below
FROM WINDOWS

When Windows Authentication is used or if you add the FROM WINDOWS flag, when the Connect to Server dialog box comes up, the user can select Windows authentication and click Connect.

Instead of connecting as the user who is currently logged, the user may want to use a different account. In this case, in the Authentication combo box, the user can select SQL Server Authentication:

If working from the Connect to Server dialog box, if the user selects SQL Server Authentication, he would have to provide a login name. Of course, the login must have been created.

If you are visually creating a login, in the Login Name text box, type the name of the domain, followed by \, and followed by the user name as it exists in the domain. Here is an example:

Of course, the user's account must have been created already in the server (domain). If you provide a user name that cannot be found in the system, when trying to finalize, you would receive an error:

If you don't remember the user account, or to help you find the user name, click the Search button. This would open the Select User or Group dialog box. In the bottom text box, type the user name of the account and click Check Names. You can also search for more than one name at the same time. To do this, type each user name and separate them with semicolons. Then click Check Names. Once the name(s) has (have) been found, click OK.

If you are creating the login using code, after CREATE LOGIN, type the DomainName\Username combination between [ and ]. Here is an example:

USE master;
GO
CREATE LOGIN [functionx\mwatts]
FROM WINDOWS;
GO

When the user decides to log in, after selecting SQL Server Authentication in the Authentication combo box, the user would type the same login name combination in the Login combo box. If the user had previously successfully connected with that account, it would be listed in the combo box where he can simply select it.

If the user is connecting from PowerShell or from the command prompt, he must use -U followed by the login name and press Enter. Here is an example:

If the user decides to use SQL Server Authentication, after specifying a login name, he must also provide a password, which must be associated with the login. If you are visually creating the login, to provide a password, first click the SQL Server Authentication radio button. Then click the Password text box and type the desired password. Click the Confirm Password text box and type the same password.

If you are programmatically creating the login, to specify the password, after the login name, type WITH PASSWORD = and provide the password in single-quotes. Here is an example:

USE master;
GO
CREATE LOGIN [functionx\mwatts]
WITH PASSWORD = N'Password1';
GO

When creating a login, the password you provide doesn't have to be the same used in the domain. In fact, the first time the user logs in, you can make him change the default password you specified when you created his account. To set this option, if you are visually creating the login, click the User Must Change Password At Next Login check box (this is the default if you click the SQL Server Authentication radio button):

If you are programmatically creating the login, to set this option, add a flag as MUST_CHANGE.

After visually creating the login, click OK. If you are creating it with code in a Query window, execute it.

Here is an example of creating a login with various options:

CREATE LOGIN [FUNCTIONX\pmukoko]
WITH PASSWORD = N'Password1'
MUST_CHANGE,
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON;
GO

Remember that when Microsoft SQL Server is installed, it creates a default account named sa. You can also use it as a login and provide its password:

Microsoft SQL Server allows a user to connect with many login account. After connecting, to make another connection, the user can click Connect -> Database Engine, and provide the necessary pieces of information as we had seen in this section. In the same way, you can create as many connections as possible, using valid login accounts.

1. Server: In the Object Explorer, expand the server name if necessary and expand Security
2. Server: Right-click Login and click New Login...
3. Server: Click Search
4. Server: In the bottom text box of the Select User or Group, type operez
   
5. Server: Click Search Names
    
   
6. Server: Click OK
7. Server: Click OK
8. Server: On the Standard toolbar, click the New Query button
9. Server: To programmatically create a login, type the following (replace the name of the domain accordinagly):

   CREATE LOGIN [FUNCTIONX\rkouma]
   FROM WINDOWS;
   GO
   CREATE LOGIN [FUNCTIONX\pkatts]
   FROM WINDOWS;
   GO
   CREATE LOGIN [FUNCTIONX\gmonay]
   FROM WINDOWS;
   GO

10. Server: On the Standard toolbar, click the Execute button
    
11. Server: In the Object Explorer, expand Login under Security if necessary. To refresh it, right-click Logins and click Refresh
     
    

For the rest of our lessons, we will consider that you have five accounts on the computer where Microsoft SQL Server is installed. The accounts are: Administrator: This is just an account that has administrative rights in the computer where Microsoft SQL Server is installed. In our lessons, the steps performed by this account will start with Server: pkatts: This account will always have all rights on the computer and on Microsoft SQL Server. In our lessons, the steps performed by this account will start with pkatts: (you can use another user name of your choice and substitute it with this one) gmonay:(you can use another user name of your choice and substitute it with this one): This account will usually have all rights on the computer and on Microsoft SQL Server. In our lessons, the steps performed by this account will start with gmonay: (you can use another user name of your choice and substitute it with this one) rkouma:(you can use another user name of your choice and substitute it with this one): This account will sometimes have all rights on the computer and on Microsoft SQL Server. In our lessons, the steps performed by this account will start with rkouma: (you can use another user name of your choice and substitute it with this one) operez: (you can use another user name of your choice and substitute it with this one): This account will be used as a regular user with limited rights. It is used to test things. In our lessons, the steps performed by this account will start with operez: (you can use another user name of your choice and substitute it with this one)  We will strive to always indicate the types of rights an account needs for what the lesson wants you to do.

1. pkatts: To start Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio. Make sure Windows Authentication is selected, that your login name is specified, and click Connect
2. gmonay: Click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio. Click Connect
3. rkouma: To start Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio. Click Connect
4. operez: To start Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio. Click Connect

After using a server or to take a break (such as stepping out from the computer), the person can disconnect from it. To do this: On the main menu, click File -> Disconnect Object Explorer In the Object Explorer, right-click the connection node and click Disconnect Disconnecting neither closes Microsoft SQL Server nor shuts down the server. It simply closes the connection to the server. If a persons wants to use the server again, she must connect.

The Object Explorer displays a list of items as a tree. One of the most regularly 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 servers. 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:

If you are connected to more than one server, each is represented by its own node:

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. When many connections have been made, each connection is represented in the Object Explorer by its own node and each connection has its own objects (sub-nodes):

To close a connection, you can right-click it and click Disconnect:

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 named Administrator is currently logged in to a server named Central:

We saw that, by default, the right area of Microsoft SQL Server Management Studio displays an empty gray window. When you select something in the Object Explorer, you can use that right area to display more detailed information about the selected item. To do this, on the main menu, click View -> Object Explorer Details. The main area on the right side would then be filled with information:

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 connected to. 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. For example, to start PowerShell, you can right-click  the Databases node or the server name and click PowerShell:

When the PowerShell comes up, what it displays depends on what you had right-clicked.

• All Computers: To close Microsoft SQL Server, on the main menu, click File Exit

1. What is wrong with the following code?

   CREATE TABLE StaffMembers
   (
       StaffCode nchar(2),
       DateHired date,
       [First Name] nvarchar(20),
       LastName nvarchar(20),
       HourlySalary money
   );
   GO
   INSERT StaffMembers
   VALUES(N'DF', #12-10-2012#, N'James', N'Simpson', 20.05);
   GO 

   1. Nothing
   2. You cannot use space inside the name of a column
   3. The value of a date cannot be included inside # signs
   4. The INTO keyword is missing after the INSERT keyword
   5. The list of columns is missing after INSERT StaffMembers

1. Answers
   1. Wrong: There is something wrong with the code
   2. Wrong: Yes, you can use space inside the name of a column as long as you follow some rules
   3. Right: The value of the date must be included in single-quotes
   4. Wrong: The INTO keyword is optional after the INSERT keyword
   5. Wrong: If the data entry expression contains values for all columns and provides values in the exact order the columns exist in the table, the list of columns becomes optional