Knowledge of the T-SQL language is vital to your success as a SQL Server administrator or developer. You use the T-SQL language to manipulate the data in your database. Using T-SQL, you can select, insert, update, and delete data. In this lesson, you learn:
What T-SQL is
How to build a SELECT
statement
How to work with a WHERE
clause
How to order your output
T-SQL, or Transact-SQL, is the dialect of the Structured Query Language (SQL) incorporated in SQL Server. To work effectively as a SQL Server developer, you must have a strong grasp of T-SQL. Fortunately, T-SQL is easy to learn. When retrieving data, you simply build a SELECT
statement. SELECT
statements are composed of clauses that determine the specifics of how the data is selected. When they’re executed, SELECT
statements select rows of data and return them as a recordset.
Note
In the examples that follow, keywords appear in uppercase. Values that you supply appear italicized. Optional parts of the statement appear in square brackets. Curly braces, combined with vertical bars, indicate a choice. Finally, ellipses are used to indicate a repeating sequence.
The SELECT
statement is at the heart of the SQL language. You use the SELECT
statement to retrieve data from one or more tables. Its basic syntax is the following:
SELECT column-list FROM table-list WHERE where-clause
ORDER BY order-by-clause
The SELECT
clause specifies what columns you want to retrieve from the table that SQL Server returns to the result set. The basic syntax for a SELECT
clause is as follows:
SELECT column-list
The simplest SELECT
clause looks like this:
SELECT * FROM Person.Person
This SELECT
clause, combined with the FROM
clause covered in the next section, retrieves all columns from a table.
You will not always want to retrieve all fields from a table. In fact, most often you need to retrieve data only from specific fields.
The example that follows retrieves only the BusinessEntityID and FirstName columns from a table:
SELECT BusinessEntityID, FirstName FROM Person.Person
Not only can you include columns that exist in your table, but you also can include expressions in a SELECT
clause. Here’s an example:
SELECT BusinessEntityID, Title, FirstName + ' ' + MiddleName + ' ' +
LastName AS FullName FROM Person.Person
This SELECT
clause retrieves the BusinessEntityID column, the Title column, and an alias called FullName, which includes an expression that concatenates the FirstName, MiddleName, and LastName columns (see Figure 6.1).
Note
The examples throughout this book are based on the AdventureWorks sample database. A backup of this database is available at http://msftdbprodsamples.codeplex.com/releases/view/125550. After you download the file, you must restore the database so that you can follow along with the examples. Lesson 22, “Maintaining the Databases You Build,” covers the process of restoring a database.
After installing the AdventureWorks database, you will probably notice that all the table names appear in the format schema-name.table-name. All objects in databases are contained in schemas. There are many advantages to placing database objects in schemas. Throughout the book, you will see examples such as Person.Address, with Person being the schema name and Address being the table name. When an object is associated with a schema, you must refer to the object along with its schema name.
The FROM
clause specifies the tables or views from which the records should be selected. It can include an alias that you use to refer to the table. The FROM
clause looks like this:
FROM table-list [AS alias]
Here’s an example of a basic FROM
clause:
FROM Person.Address
In this case, the name of the table is Address. If you combine the SELECT
clause with the FROM
clause, the SQL statement looks like this:
SELECT AddressID, AddressLine1 FROM Person.Address
This SELECT
statement retrieves the AddressID and AddressLine1 columns from the Address table, which is part of Person schema.
Just as you can alias the fields included in a SELECT
clause, you can also alias the tables included in the FROM
clause. The alias is used to shorten the name and to simplify a cryptic name, as well as for a variety of other reasons. Here’s an example:
SELECT BusinessEntityID, FirstName FROM Person.Person AS Clients
The WHERE
clause limits the records retrieved by the SELECT
statement. A WHERE
clause can include columns combined by the keywords AND
and OR
. The syntax for a WHERE
clause looks like this:
WHERE expression1 [{AND|OR} expression2 [...]]
A simple WHERE
clause looks like this:
WHERE PersonType = 'EM'
Using an AND
to further limit the criteria, the WHERE
clause looks like this:
WHERE PersonType = 'EM' AND LastName Like 'Ma%'
This WHERE
clause limits the records returned to those in which the PersonType is equal to EM
and the LastName begins with Ma
. Notice that T-SQL uses the percent (%
) sign as a wildcard. Using an OR
, the SELECT
statement looks like this:
WHERE PersonType = 'EM' OR PersonType = 'SC'
This WHERE
clause returns all records in which the PersonType is equal to either EM
or SC
. Compare that with the following example:
WHERE PersonType = 'EM' OR LastName Like 'Ma%'
This WHERE
clause returns all records in which the PersonType is equal to EM
or the LastName begins with Ma
. For example, the people with EM
as their PersonType are returned from this WHERE
clause, as are the people whose LastName begins with Ma
. The WHERE
clause combined with the SELECT
and FROM
clauses looks like this (see Figure 6.2):
SELECT BusinessEntityID, PersonType, LastName FROM Person.Person
WHERE PersonType = 'EM' OR LastName Like 'MA%'
Notice in Figure 6.2 that records with a PersonType of ‘MA’
appear regardless of their last name. Records of people whose last names begin with ‘MA’
appear regardless of their PersonType.
Contrast this example to the following example:
SELECT BusinessEntityID, PersonType, LastName FROM Person.Person
WHERE PersonType = 'EM' AND LastName Like 'MA%'
The results appear in Figure 6.3.
If you look at the records returned in Figure 6.3, you see that all records that appear in the result have both a PersonType of ‘EM’
and a last name that begins with ‘MA’
.
You must follow several rules when building a WHERE
clause. You must enclose the text strings for which you are searching in apostrophes. You must also surround dates with apostrophes. Unlike text strings and dates, you do not delimit numeric fields. Finally, you must include the keyword LIKE
when utilizing wildcard characters. Remember that T-SQL uses the percent symbol as the wildcard for zero or more characters. The underscore (_
) is the wildcard for a single character. Let’s take a look at some examples:
SELECT BusinessEntityID, PersonType, LastName FROM Person.Person
WHERE BusinessEntityID >= 200
In this example, because BusinessEntityID is a numeric field with the int
data type, the criteria for BusinessEntityID contains no apostrophes. With character (string) data, the process is different:
SELECT BusinessEntityID, PersonType, LastName FROM Person.Person
WHERE LastName = 'Martin'
Notice that the text string we are looking for is enclosed in apostrophes. You might be surprised that when specifying criteria for dates, you also use apostrophes:
SELECT BusinessEntityID, PersonType, LastName, ModifiedDate FROM Person.Person
WHERE ModifiedDate >= '1/1/2013'
The T-SQL language is rich with date and time functions you can use when specifying criteria. The text that follows covers many of these powerful functions.
The GetDate()
function returns the current date and time. Take a look at the simplest example:
SELECT GetDate()
This example returns one column containing the current date and time. A more complex example of using the GetDate()
function looks like this:
SELECT ModifiedDate, CONVERT(int. ModifiedDate – GetDate())
FROM Person.Person
The T-SQL in this example returns the ModifiedDate from the Person table, along with the number of days between the current date and the ModifiedDate. The results appear in Figure 6.4.
Another T-SQL date function is DAY. Its counterparts are the MONTH and YEAR functions. These functions return the day, month, and year associated with a date. They look like this:
SELECT DAY(GetDate()), MONTH(GetDate()), YEAR(GetDate())
The results of executing this statement appear in Figure 6.5.
The DATEPART
function extracts a part of a date. You use it to determine the day of the month, month of the year, year of a date, day of the year, day of the week, week of the year, and other parts of a date. An example of several uses of the DATEPART
function appear in Figure 6.6.
With the DATEADD
function, you add or subtract a specified period of time from a date. For example, you can add a quarter to a date. Examples of the uses of the DATEADD function appear in Figure 6.7.
Another powerful date-related T-SQL function is DATEDIFF. It allows you to determine the number of days, months, years, or other time increments between two dates. Figure 6.8 illustrates some examples of the DATEDIFF function.
The IN
and NOT
keywords facilitate the process of determining what rows appear in the result of your SELECT
statement. The IN
keyword allows you to select a series of values that will appear in the result. The NOT
keyword allows you to exclude what appears in the result. The following is an example of the use of the IN
keyword:
SELECT BusinessEntityID, PersonType, LastName, ModifiedDate FROM Person.Person
WHERE PersonType IN('EM','IN','VC')
This SELECT
statement returns the designated fields from the Person table for all records where the PersonType is ‘EM’
, ‘IN’
, or ‘VC’
. If you want to retrieve all rows where the PersonType is not ‘EM’
, ‘IN’
, or ‘VC’
, you simply add the keyword NOT
to the statement:
SELECT BusinessEntityID, PersonType, LastName, ModifiedDate FROM Person.Person
WHERE PersonType NOT IN('EM','IN','VC')
Another example of the use of the NOT
keyword is where you want to return all rows from the Person table where the ModifiedDate is not between 1/1/2013 and 12/31/14. It looks like this:
SELECT BusinessEntityID, PersonType, LastName, ModifiedDate FROM Person.Person
WHERE ModifiedDate NOT BETWEEN '1/1/2013' AND '12/31/2014'
Another keyword you might find useful is the NULL
keyword. The NULL
keyword allows you to designate that you want to retrieve only those rows where the designated field does not contain data or only those rows where the designated field does contain data. Here’s how it works:
SELECT BusinessEntityID, PersonType, LastName, ModifiedDate FROM Person.Person
WHERE Suffix IS NULL
This SELECT
statement returns all rows where the Suffix field does not contain data. By adding the keyword NOT
, you can return only those rows where the Suffix field contains data.
SELECT BusinessEntityID, PersonType, LastName, ModifiedDate FROM Person.Person
WHERE Suffix IS NOT NULL
As you can see, by combining everything you have learned in this section, you can retrieve only the data you want and need.
The ORDER BY
clause determines the order in which SQL Server sorts the returned rows. It’s an optional clause and looks like this:
ORDER BY column1 [{ASC|DESC}], column2 [{ASC|DESC}] [,...]]
Here’s an example:
ORDER BY BusinessEntityID
The ORDER BY
clause can include more than one field:
ORDER BY LastName, FirstName
When you specify more than one field, SQL Server uses the leftmost field as the primary level of sort. Any additional fields are the lower sort levels. Combined with the rest of the SELECT
statement, the ORDER BY
clause looks like this:
SELECT BusinessEntityID, FirstName, LastName, ModifiedDate FROM Person.Person
WHERE ModifiedDate Between '1/1/2013' AND '12/31/2014'
ORDER BY ModifiedDate
The results appear in order by ModifiedDate (see Figure 6.9).
The ORDER BY
clause enables you to determine whether the sorted output appears in ascending or descending order. By default, output appears in ascending order. To switch to descending order, use the optional keyword DESC
. Here’s an example:
SELECT BusinessEntityID, FirstName, LastName, ModifiedDate FROM Person.Person
WHERE ModifiedDate Between '1/1/2013' AND '12/31/2014'
ORDER BY ModifiedDate DESC
This example selects the BusinessEntityID, FirstName, LastName, and ModifiedDate fields from the Person table, ordering the output in descending order by the ModifiedDate field (see Figure 6.10).
The T-SQL language is the foundation for most of what you do in SQL Server. It is therefore necessary to have a strong understanding of the T-SQL language constructs. This lesson covered many of the basics of the T-SQL language. You learned about the SELECT
statement, the FROM
keyword, the WHERE
clause, and the ORDER BY
clause. We cover T-SQL in additional detail throughout the remainder of the book.
Q. Why do you use a SELECT
statement?
A. To retrieve data from one or more tables.
Q. Name the wildcard characters you can use when searching and explain the differences between them.
A. The two wildcard characters are the percent (%
) sign and the underscore (_
). T-SQL uses the percent symbol (%
) as the wildcard for zero or more characters. The underscore (_
) is the wildcard for a single character.
1. Which keyword do you use to designate the tables you will include in the query?
2. You use an alias to permanently rename a field in a table (true/false).
3. What is the keyword you use if you want the data to appear in descending order?
1. You use FROM
to designate the tables you will include in the query.
2. False. ALIAS
provides only an alias for the field in the query output.
3. If you want the query output to appear in descending order, use the DESC
keyword.
Build a simple SELECT
statement based on the Person.Address table in the AdventureWorks2014 sample database. Include the AddressID, AddressLine1, AddressLine2, City, StateProvinceID, and PostalCode fields. Add a WHERE
clause to limit the records returned in the result to those where the City is Seattle, Redmond, or Newport Hills. Order the data in descending order by City.
3.133.133.233