This tutorial will explain the basic Transact-SQL statements.

Basic T-SQL Statements

In the previous lesson, we learned about the main components of a SQL Server database. We learned what tables, fields and records represent. You've probably already started to grasp the power of this system. Today, we'll show you how to retrieve the data stored inside a SQL server database.

What T-SQL Is For

T-SQL is a language that deals with data sets. A data set is a collection of information that can be modified or grouped in many different ways and dealt with as single records or as a group. T-SQL is a powerful language that helps you tell programs things like, "Get me all the records from 2013."

There are many different flavors of SQL languages. The examples here use MS T-SQL, which is the language used when programming in Microsoft's SQL Server program. If you are using a different system you'll find most of the commands are the same and your result set will not look radically different.

What SQL is Not For

T-SQL is not a linear, or procedural, language. Its not made to execute a series of sequential steps like other programming languages such as C, Visual Basic and others. Although a few limited tools are available for this functionality, T-SQL's real purpose is to bring back sets of data to be utilized by other programming languages, reports or tools.

Your First SQL Statement

Today, you're going to learn the SELECT statement, the most basic of T-SQL statements and three clauses that are applied to a SELECT statement. The scripts shown here are simply examples. You can use T-SQL on a variety of platforms and programs, so the actual interface will vary slightly depending on what you use.

The SELECT Statement

The SELECT statement in SQL says, "Get me this and that from the database." As we learned in the previous lesson, data in SQL is stored in fields, which are grouped into tables. The most basic SELECT statement tells SQL to get you everything from a specific table:

Syntax:
SELECT [list of fields] FROM [table name]
Example:
SELECT AccNum, FirstName, LastName FROM ACCOUNTS

This statement starts with SELECT, which tells SQL we want to retrieve information. Then we list the fields we want, separated by commas. The FROM clause tells SQL server we're done with our column list and now we're going to tell it what table to look at. You can also put a * in place of the field list to tell SQL to get all the fields on the current table. Take a look at what the following SQL statement would return:

SELECT * FROM ACCOUNTS
Database Result

As you can see, we get back everything on the Account table. Using a "SELECT *" statement is a great way to take a quick look at what is inside a table before we set to work on it. Let's take a look at what our previous statement would return:

SELECT AccNum, FirstName, LastName FROM ACCOUNTS
Database Result

You might be asking yourself, "Where did the columns like BirthDate and Limit go?" An important thing to grasp about a SELECT statement is that we're only retrieving data. Nothing we do with a SELECT statement is going to change the information stored in the database.

The reason for only retrieving certain fields is to make the system as efficient as possible. Some SQL statements involve millions of rows of data being transferred across the Web. Even one unneeded column can cost tons of bandwidth to send across. For this reason, it's a good habit to start selecting only what you need when writing real queries.

The WHERE Clause

The next part of the SELECT well add is the WHERE clause. T-SQL can be used to return subsets of data from a larger set of data. For example, let's say we want to get everyone in the database that has the last name Doe. The WHERE clause comes right after the table name in our statement:

Syntax:
SELECT [list of fields] FROM [table name] WHERE [column] [operator =,>,<,<>] [value]
Example:
SELECT FirstName, LastName, AccNum FROM ACCOUNTS WHERE LastName = ‘Doe'

This statement will return the following:

Database Result

This time, the data about cardholders who aren't named Doe is gone. You'll also notice that the columns came back in a new order. SQL server is very flexible. It doesn't care what order or what data you ask it to bring back. Now you can see how SQL answers important reporting questions like, "Show me everyone in the accounting department" or "get me all the records with a value in the Urgent field."

You can also use mathematic operators to bring back data. Notice that the single quotes are used to denote a string value, whereas numbers are simply typed out. Let's say we want credit card that has a credit limit over $100:

SELECT AccNum, Limit FROM ACCOUNTS WHERE Limit > 100
Database Result

As you can see, all the accounts with a $100 limit are now removed from the results. But maybe you'd also like to have the limits ordered from least to greatest? Having things in a specific order is incredibly useful in database scenarios such as reports. Fortunately, SQL makes it easy to order and sort our results with just one simple clause.

The ORDER BY Clause

The ORDER BY clause allows you to tell SQL how to sort out the data it returns to you. The ORDER BY clause comes right after the WHERE clause. The ORDER BY clause looks like this:

Syntax:
SELECT [list of fields] FROM [table name] WHERE [column] [operator =,>,<,<>] [value]
ORDER BY [list of columns] [ASC or DESC]
Example:
SELECT AccNum, Limit FROM ACCOUNTS WHERE Limit > 100 ORDER BY Limit ASC

Running the statement above returns the following:

Database Result

The "ORDER BY Limit" code tells it to arrange the rows based on the value of Limit. The "ASC" stands for ascending and says start with the lowest value and go up. You can also put "DESC" to reverse the order of the results. You can even use two or more columns in the list to have a secondary ordering:

SELECT * FROM ACCOUNTS WHERE Limit < 400 ORDER BY Limit, BirthDate ASC
Database Result

Now, you can see that the data set is ordered by the Limit. However, if you look at the yellow section, you'll see that the three $100 limit cards have also been sorted by birthdate. With these statements you're well prepared to take advantage of all that SQL has to offer! Our next lesson will teach you how to retrieve data in this same way from more than one table at a time!



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories