Introduction to SQL Code Writing

In the previous lesson, we learned different ways of connecting to a server. 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 the SQL Query Analyzer application. In the previous lesson, we saw different ways to open it. When it comes up, by default, it displays a blank window you can use to write your code:

By default, when the SQL Query Analyzer comes up, it display a blank child window named Query. The name is followed by a dash, the name of the computer (the server). From now on, we will refer to the child window as the Query window. This is where you would be writing code when you are working in the SQL Query Analyzer:

The white area allows you to write code. Besides the usual title bar, the system buttons and the scroll bars, the Query window is equipped with a status bar divided in different sections. Each section has a specific and informing role that can assist you while you are working. To know what a section is displaying, you can position the mouse on it and a tool tip would come up:

As mentioned above, when the SQL Query Analyzer comes up, it displays a blank Query window made of only one white area. After writing code, you can execute it. To execute code, on the toolbar of SQL Query Analyzer, you can click the Execute Query button or you can press F5. After executing code, the Query window gets divided in two sections:

The top part allows you to type statements. The lower part is used to display a result, depending on the expression. The lower part is also made of two property pages labeled Grids and Messages. The Grids property page is used to show results that need to display on a sheet-like interface. Here is an example:

The Messages property page is used to show a result that appears as regular line-based and paragraphed text. Here is an example:

In the same way, the Messages section is used to display the errors resulting from interpreting your code. When this happens, indication of the error occurrence would appear in red and the name of the error would appear under it:

You can also use the top and the bottom parts to perform a test. For example, you can enter a test statement in the upper section and execute it to have the result displayed in the lower part.

An alternative to the SQL Query Analyzer is the SQL Server Enterprise manager. This application is more "visual" and is user-friendlier than SQL Query Analyzer. Still, we will learn that, in some cases, such as troubleshooting procedures or when performing data analysis, the SQL Server Enterprise Manager can offer some windows that allow you to write code.

Accessories for SQL Code Writing

The End of a Statement

In SQL, after writing a statement, you can end it with a semi-colon. Here is an example:

PRINT 'Microsoft SQL Server Database Development';

Many SQL environments require the semi-colon. If you want to execute many statements in the SQL Query Analyzer, you can write them once. Here is an example:

CREATE DATABASE CountriesStatistics
USE CountriesStatistics

This statement will not work:

The problem here is that, when the interpreter encounters these two lines, it would execute all of them at once and in fact would treat both lines as one expression. When the statements are executed, 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). Therefore, the above two statements can be performed separately but with one execution as follows:

CREATE DATABASE CountriesStatistics
GO
USE CountriesStatistics
GO

This time, when you execute the code, it would work fine

Comments

A comment is text that an 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:

A comment can also 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