ASPAlliance.com Sample Book Chapters

Hour 3: Using T-SQL: A Crash Course

ADO.NET enables you to connect to a data source and retrieve and manipulate data. However, ADO.NET doesn't actually gather the data itself. It simply sends a string to the data source with data processing instructions. The language used to communicate with the data source is known as T-SQL (Transact-SQL), which is a dialect of Structured Query Language (SQL).

Because you must provide ADO.NET with the proper T-SQL statements for data retrieval and manipulation, knowledge of T-SQL is an essential skill for any well-rounded developer. Hundreds of different kinds of T-SQL statements are available in a product such as Microsoft SQL Server. You can modify many aspects of the server itself, such as managing jobs, creating and maintaining databases, and other administrative tasks. This chapter provides a primer; you'll learn just enough about T-SQL to understand all the examples in this book.

In this chapter, you will learn how to do the following tasks:

• Retrieving data with the SELECT statement

• Adding data with the INSERT statement

• Modifying data with the UPDATE and DELETE statements

• Using some T-SQL built-in functions

Microsoft SQL Server and Microsoft Access both ship with a sample database called Northwind. This database will be used for the examples in this chapter. The Northwind access database is freely distributed. You can download it at http://www.intensitysoftware.com/ADO.NET/nwind.mdb. If you are using a default installation of Microsoft SQL Server, you'll see an entry in your program group for the Query Analyzer. You can launch this application, select Northwind as your database, and follow along with the examples in this chapter directly.

If you are using another data source, such as Oracle, you still should be able to follow along. Your database server probably ships with an application like Query Analyzer that you can use to enter database queries. Use that to enter the queries in the following sections.

Retrieving Data with SELECT

The SELECT statement is used to retrieve and filter data from your data source. Listing 3.1 shows the simplified syntax of the SELECT statement. Read from top to bottom, this statement says "select these columns from these tables where these search criteria are true." You can retrieve several column names from several tables, so long as you separate the column names by commas.

Listing 3.1 The Syntax of the SELECT SQL Statement

SELECT
   column_names
FROM
   table_names
WHERE
   search_conditions

For instance, to retrieve all records from the Employees table, enter the following code in the query manager and press F5 or click the green Play button to execute the query:

SELECT * FROM Employees

This will return every single row and column in the Employees table. The results of your query will look much like Figure 3.1.

Figure 3.1 The Query Analyzer has many uses, one of which is to see the results of your queries.

---

Note -

T-SQL is not case sensitive. SELECT * FROM Employees is syntactically identical to select * from employees. However, there is a convention to capitalize T-SQL keywords such as SELECT and FROM to distinguish them from table and column text.

---

Suppose you only want to return a single record; you want to return one employee based on his or her last name, for example. As you can see in Listing 3.1, the WHERE keyword enables you to filter the data based on any number of search criteria. The content of the search criteria itself is broad. However, most often, the values of various columns are checked. For instance, to return all employees from the database with the last name "King," you would use the following query:

SELECT * FROM Employees WHERE LastName = 'King'

Similarly, if you want to be even more specific and filter by the employee's first name as well, just add another condition to your query, as in the following SQL statement.

SELECT * FROM Employees WHERE LastName = 'King' and FirstName = 'Robert'

---

Note -

Strings in T-SQL are delimited by single quotation marks. If you attempt to use double quotation marks, an error will be returned by your data source. If you are filtering by a numerical field, there's no need for quotation marks at all.

---

Filtering by date is another common need. Let's say you want to return all employees hired after May 3, 1993. The query you build looks like this:

SELECT * FROM Employees WHERE HireDate between '5/3/1993' and getdate()

Notice that, like strings, dates in T-SQL are also delimited by single quotation marks. Getdate() is a built-in function that returns the current date and time in DateTime format.

Until now, we've used the wildcard "*" to select all columns for the table. This is fine for testing purposes, but not when building an application. Unless you are planning on using all the columns in the table, return only those columns that you plan to use in your application. You can do this by specifying the exact columns you need, separated by commas as shown in Listing 3.2.

Listing 3.2 Specifying Columns in SQL Statements

SELECT 
   FirstName, Lastname, Title 
FROM 
   Employees
WHERE 
   HireDate between '5/3/1993' and getdate()

This greatly reduces the amount of data returned by the data source to your application. Because the bottleneck in many applications is the database server, any way to make your queries perform more efficiently is likely to make your application perform better.

---

Note -

In Microsoft SQL Server, all extra "white space" is ignored and does not affect processing. "White space" is defined as any character that does not generate a character on the screen. For instance, spaces, tabs, and newline characters are considered "white space." This enables you to format the appearance of queries however you want. Listing 3.2 separates the T-SQL commands from the actual table objects they use. Though the code takes up several more lines, it is easier to understand quickly.

---

This section only scratches the surface of what is possible with the SELECT statement. Microsoft SQL Server version 7.0 and higher ships with a terrific reference named SQL Server Books Online. This can be found in your SQL Server program group.

The online books are used on a daily basis by professionals everywhere (some might not admit to it), but new users might find it too terse to be very useful. In that case, there's certainly no lack of great books and Web sites devoted to the topic.

Adding New Data with INSERT

You can enter new data into the database by using the INSERT SQL statement. The syntax of the command is fairly simple. However, before building the query to add new data, you must know the schema of the table. Figure 3.2 shows the schema of the Categories table in the Northwind database.

Figure 3.2 The Microsoft SQL Enterprise Manager can display the schema of your database to help you build queries.

Notice that the table consists of four fields, including the CategoryID column. This field contains an integer that is automatically incremented for each new record added; we will not need to add a value with our query. Additionally, notice that the only required field in the table is CategoryName. The Description and Picture columns can both be left null. When executed, the statement in Listing 3.3 adds a new record into the category table:

INSERT INTO Categories
(
 CategoryName,
 Description
)
VALUES
(
 'Spam',
 'Spam and other canned-meat products'
)

The first line uses the keywords INSERT INTO to specify that we are inserting the data into the Categories table. Then the first parenthesized section of code specifies the fields into which we're putting our data. The VALUES keyword and the next parenthesized section enter the actual values in the same column order as the first section.

Modifying Data with UPDATE and DELETE

So far, you have seen how to retrieve and add data to the database. However, suppose you would like to modify existing database rows. To modify data, you would use the UPDATE SQL statement. A simplified version of the syntax of the UPDATE statement looks like the code in Listing 3.3.

Listing 3.3 The Syntax of the UPDATE SQL Statement

UPDATE 
   table_name
SET 
   column_name = expression
WHERE 
   search_conditions

The specific example in Listing 3.4 explains the syntax quite well. After the statement in Listing 3.4 is executed against the data source, any employee with last name of "Peacock" and first name of "Margaret" as specified by the WHERE clause will be changed to "Hogue" as specified by the SET clause of the statement. Figure 3.3 shows the change.

Listing 3.4 Using the SQL UPDATE Statement to Change an Employee's Last Name

UPDATE 
   employees
SET 
   LastName = 'Hogue'
WHERE 
   LastName = 'Peacock' and
   FirstName = 'Margaret'

Figure 3.3 The value in the LastName column changes for the selected employee.

---

Note -

Be careful when using the UPDATE statement, particularly when working with live data. Remember that every row meeting the conditions of the WHERE clause in the statement will be updated. In fact, if you inadvertently do not include the WHERE clause in the statement, your query will affect every single row in the table!

---

It's also possible to update several fields at once. You only need to place commas between each segment as in Listing 3.5.

Listing 3.5 Updating Multiple Columns in a Single UPDATE Statement

UPDATE 
   employees
SET 
   LastName = 'Hogue',
   Address = '11 Longfellow St.'
WHERE 
   LastName = 'Peacock' and
   FirstName = 'Margaret'

Compared to updating database rows, deleting database rows is easy. Listing 3.6 shows the syntax of the DELETE SQL statement. It is the simplest query you have seen thus far. All you need to specify is the name of the table and the search conditions.

Listing 3.6 Deleting Rows from the Employee Table

DELETE FROM 
   table_name
WHERE 
   search_conditions

To delete the employee with EmployeeID of 7, you use the query in Listing 3.7. Remember that if you are deleting only a single row, your search conditions must single out that row. Normally, the purpose of an ID field in a database table is to guarantee this uniqueness.

Listing 3.7 Deleting Rows from the Employee Table

DELETE FROM 
   employees
WHERE 
   EmployeeID = 7

Using the Built-in SQL Functions

Hundreds of timesaving functions are built into Microsoft SQL Server. These functions enable you to perform all sorts of tasks such as working with dates and strings and performing mathematical calculations. Some of the most commonly used functions are described in this section. However, you can locate a list of all built-in functions by searching Microsoft SQL Server Books Online for "functions."

Working with Strings

Microsoft SQL Server ships with a number of functions that enable you to manipulate strings. For the most part, these string functions are similar to the ones used in Microsoft Visual Basic.

For instance, the Left() and Right()functions are nearly identical to their counterparts. They enable you to return part of a character string, from either the left or right end of the string, respectively. They have the following function definitions:

Left( string, value )
Right( string, value )

By calling the Left() function, and passing in 'She sells sea shells' as the string and 6 as the value, Left() returns 'She se'. Likewise, Right() with the same arguments returns 'shells'.

Sometimes, when working with strings, you need to convert the entire string to either uppercase or lowercase to compare two strings or to ensure that data is entered into a certain field in a standard way. The upper() and lower() methods perform exactly these tasks. The two methods accept the string to convert as a single argument.

Table 3.1 contains a list of some SQL string functions and their return values for a given string.

Table 3.1 String Functions at a Glance

---

Note -

Keep in mind that you can use string functions on other string functions that return strings. In other words, this is a perfectly legal set of calls that returns the length of a left and right trimmed string:

Len( LTrim( RTrim( string ) ) )

---

Working with Dates

In addition to the string functions, there are several invaluable date manipulation functions as well.

The DateAdd( datepart, number, date ) function can be used to add a chosen unit of time to a particular date. The first argument, datepart, controls the part of the date you are adding. For a complete list of values for the datepart argument, see Table 3.2. Number is the amount of the chosen datepart you're adding to the date. For instance, in order to add two months to the current date, you can use the following:

DateAdd( m, 2, getdate() )

Table 3.2 Common Codes for Special Symbols and Syntax

The functions Month(), Day(), and Year() are used to return the corresponding piece of a given date. For instance, Month('12/7/1952') returns 12, Day('12/7/1952') returns 7, and Year('12/7/1952') returns 1952. These functions can save hours of needless parsing of dates by hand.

One last function that is indispensable when working with dates is Datediff( datepart, startdate, enddate ). This function returns the difference of two dates in units determined by the datepart argument. Fortunately, it also uses the codes shown in Table 3.2 for the values in its first argument.

Mathematical Functions

SQL Server contains a number of methods for working with numbers. You probably will never use most of them in a query (when was the last time you needed to compute the arctangent of a value as part of a query?). However, when you do need one of these methods, they are quite handy. Table 3.3 shows some math functions and their return values. For a complete list, please see Microsoft SQL Server Books Online.

Table 3.3 SQL Server Math Functions

Summary

In this hour, you've seen the four most often used SQL queries: SELECT, INSERT, UPDATE, and DELETE. You also saw how some of these queries run against the Northwind database on Microsoft SQL Server. Lastly, you saw some built-in SQL Server methods that make working with strings, dates, and numbers much easier.

Q&A

Q Where can I learn more about writing SQL queries?

A A great book for learning SQL syntax is Sams Teach Yourself SQL in 10 Minutes Second Edition. This book focuses on the queries themselves and avoids delving deep into database theory and database design. If you are interested in database theory as well, Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days might be more appropriate for you.

Q If strings are delimited by the ' (single quote) character in SQL, how do you enter the single quote character into a database field programmatically?

A This is referred to as "escaping" the special character. Simply enter two single quotes instead of one. For instance, SQL Server will recognize the text "it''s" as "it's".

Workshop

These quiz questions are designed to test your knowledge of the material covered in this chapter.

Quiz

1. Which of the following SQL commands enables you to create new entries in a database table?

1. INSERT

2. ADD

3. CREATE NEW RECORD

4. UPDATE

2. Which portion of a SQL query is used to filter the number of records returned?.

Quiz Answers

1. a. The INSERT command enables you to add new records to a database table.

2. The WHERE portion of a SQL query uses an expression to filter the records returned by the query.

Exercise

Write SELECT, INSERT, UPDATE, and DELETE queries for the Customers table in the Northwind database. Verify that your queries work by running them in the Query Analyzer application (SQL Server only).

© Copyright Pearson Education. All rights reserved.