Home

The Structured Query Language

 

Introduction

 

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.

 
Author Note 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.

The SQL Interpreter

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:

Microsoft SQL Server Management Studio

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

If you are working in a Windows Application, you can write the exact same code you would in a query window. Once your code is ready, you can pass it to a SqlCommand object you would have created as we saw already. This would be done as follows:

void InitializeComponent()
{
    SqlConnection connection =
        new SqlConnection("Data Source=(local);Integrated Security=yes");
    SqlCommand command = new SqlCommand(SQL Code, connection);
}

In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.

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 window appears in which you will 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, the query window becomes divided into two horizontal sections:

Microsoft SQL Server Management Studio

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:

Microsoft SQL Server Management Studio: An Error

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

If you are working in a Windows Application, after passing the SQL code to a command, to execute it, as we saw in the previous lesson, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows:

void InitializeComponent()
{
    SqlConnection connection =
        new SqlConnection("Data Source=(local);Integrated Security=yes");
    SqlCommand command = new SqlCommand(SQL Code, connection);

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}
 

Published on Monday 24 December 2007

 

Previous Copyright © 2007 FunctionX, Inc. Next