The Structured Query Language

Introduction to Code

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

To open the editor: On the main menu, 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 open a new window and position it on the right side of the interface.

In our lessons, if you click File -> New -> Query With Current Connection on the main menu, or if you click the New Query button, or if you right-click the name of the server in the Object Explorer and click New Query, the window that displays will be called the Query Editor.

Saving Code

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.

Introduction to SQL

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. Although Microsoft SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems.

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

Practical Learning: Introducing Transact-SQL Code

1. Start Microsoft SQL Server using Microsoft SQL Server Management Studio
2. On the Connect to Server dialog box, click Connect

The SQL Interpreter

Executing a Statement

In the next sections and lessons, we will learn various techniques of creating SQL statements with code. By default, when a new Query Editor 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, the Query 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.

Accessories for SQL Code Writing

Code Colors

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 All Settings and Environment. Then click Fonts and Colors. You may be presented with a Fonts and Colors link:

If that happens, click the Fonts and Colors link:

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.

Code Templates

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 start a Query Editor. In the Template Explorer, expand the category you want. Then drag the desired option and drop it in the Query Editor. The Query Editor 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 the existing code. You can then edit the code any way you like.

Comments

Introduction

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.

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

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

A Multi-Line Comment

You can start a comment section with /* and end with */. You can write such a comment on one line. Here is an example:

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

As seen in our introduction to comments, if you want to write comments on many lines, you can starts each line with --. As an alternative, you can use the /* and */ combination to expand the comment on many lines, as if you are creatomg a paragraph. To proceed, 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:

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

Other Topics on SQL Code Writing

The End of a Statement

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.

Prolonging a Line of Code

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 \
SSection 2

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

Time to GO

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 window:

Introduction to SQL Operations

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

PRINT Something

Like every language, SQL has 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 (in future lessons, we will see many examples). 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 (in future lessons, we will see many examples).

SELECT

SELECT Anything

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. Some of the differences between PRINT and SELECT are:

• 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. SELECT displays its results in an organized window made of categories, 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:

Nesting a SELECT Statement

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 itself produces a result. To distinguish the SELECT sections, the second one should be included in parentheses. Here is an example: SELECT (SELECT 448.25); GO When one SELECT statement is created after another, the second is referred to as nested. Just as you can nest one SELECT statement inside of another, you can also nest one statement in another statement that itself is nested. Here is an example:

SELECT (SELECT (SELECT 1350.75));
GO

SELECT This AS That

In the above introductions, we used either PRINT or SELECT to display something in the Query Editor. 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:

There are three ways you can specify the item after AS. You can include the item:

• In single-quotes. Here is an example:

  SELECT 24.85 AS 'HourlySalary';

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

• Inside of double-quotes. Here is an example:

  SELECT 24.85 AS "HourlySalary";

Practical Learning: Closing Microsoft SQL Server

• To close Microsoft SQL Server, on the main menu, click File Exit.
  If you are asked whether you want to save something, click No