In previous sessions, you learned about the two sample databases supplied with Microsoft SQL Server 2000 — Pubs and Northwind. Now, it’s time to use the data that these databases contain.
Suppose you want to get a list of all authors from the Authors table in the Pubs database, as shown in Figure 8-1.
Note
Saturday Morning
Figure 8-1
View the Authors table from the Pubs database.
The T-SQL command you would use looks like this:
SELECT * FROM authors
Except for the asterisk (*) — which stands for “every single field in the
table” — this command looks a lot like a plain English statement. If you run this statement from the Query Analyzer window, you will see a result set containing all 23 rows in the Authors table and all fields in each row. The result set (sometimes
called the recordset) represents a virtual copy of the Authors table.
If you know the structure of the table you are querying, you can be more specific:
SELECT au_fname, au_fname, phone from Authors
The query will partition the underlying table vertically, returning a subset of the 23 Authors records — namely the author’s first name, last name, and phone number — and leaving out all other fields.
But while it is useful for producing lists of records, you will sometimes need the SELECT statement to be more precise, more specific. What if the table contains mil- lions of records? To filter through the records, to partition the table horizontally, you can use the WHERE clause.
The syntax of the WHERE clause is very intuitive. Suppose that you want a list of all the authors living in the state of California. The following is the statement that will return the first name, last name, and phone number of every author in the database who lives in California. This query returns only 15 records out of 23 present in the table, as shown in Figure 8-2.
SELECT au_fname, au_fname, phone, state from Authors WHERE state = ‘CA’
Session 8—Transact-SQL Programming Language 83
P art II — Satur day Morning Session 8
Figure 8-2
Records returned from the Authors table.
Let’s be even more selective. Here is the query that returns one record only:
SELECT au_fname, au_lname , phone, state from Authors WHERE state = ‘CA’
AND au_fname = ‘Cheryl’
Of course, if you have more than one Cheryl in your database, and all of them happen to live in California, T-SQL will return more than one record. As you can see, issuing very selective commands requires a thorough knowledge of the table structure.
If you happen to have a table that contains duplicate records, T-SQL enables you to filter them with a DISTINCT keyword: T-SQL will return only the first occur- rence of the record, ignoring the rest, as in the following example:
SELECT DISCTINCT au_fname, au_lname , phone, state from Authors
WHERE state = ‘CA’ AND au_fname = ‘Cheryl’
So far you’ve learned query basics: the SELECT statement, the FROM clause, the
WHERE clause, and the AND clause; you also learned that *means “all fields in the
table,” and that if you request specific fields, you must separate them from each other with commas. The following query will select information about all authors in the Pubs database living either in California or in Utah:
SELECT au_fname, au_lname , phone, state from Authors WHERE state = ‘CA’
OR state = ‘UT’
What if you do not know what state Cheryl Carson lives in? You can combine several modifiers, such as AND and OR, in the same query.
Saturday Morning
SELECT au_fname, au_lname , phone, state from Authors WHERE au_fname = ‘Cheryl’ AND au_lname =’ Carson’ AND (state = ‘CA’ OR
state = ‘UT’)
The uses of the SELECT statement can be much more complex than the samples given in this session. For full SELECT-statement syntax please refer to Books Online or a book specializing in T-SQL.
As you learn more about T-SQL, you will find more than one way to achieve the results you want. The important thing is to understand your selection criteria and the order in which they are applied.
With the last query, T-SQL will return one and only one record (assuming that you do not have more than one person named Cheryl Carson in your Authors table). The results are quite different if you remove the brackets: Instead of the record pertaining to Cheryl Carson only, you will also receive records for com- pletely irrelevant Utah residents. Why’s that? The query is supposed to return all the records for a Cheryl Carson living in the state of California — as well as all the records for Utah residents, regardless of what their names are. If you apply brack- ets, you instruct SQL Server to return the records for any Cheryl Carsons that hap- pen to live in California or Utah. The lesson here: Be careful what you query for.
You are in total control of the way the records appear in the final result set. If you want to combine one or more fields under a different name, this is the query you use:
SELECT au_fname + ‘,’ + au_lname AS FullName, phone, state from
Authors WHERE au_fname = ‘Cheryl’ AND au_lname =’ Carson’ AND (state = ‘CA’ OR state = ‘UT’)
The values of the two fields — au_fname and au_lname — will be concatenated under the new field name FullName. The only restriction on this kind of concate- nation is that the fields must be of compatible data types — meaning that you cannot concatenate character data and numeric data, for example, to produce one column.
T-SQL is a strongly typed language, which means that prior to using a variable in your code, you must declare it as being of a particular data type. The very basic data types are characters, numbers, and date/time values. SQL Server 2000 also introduces the new data type sql_variant, which can contain any of the basic types. Table 8-1 lists all supported data types.
Note
Session 8—Transact-SQL Programming Language 85
P art II — Satur day Morning Session 8
Table 8-1
SQL Server 2000 Supported Data Types
Numeric Dates Binary Data Text SQL server Data Types Types Data Types Special Data
Types
decimal, float, timestamp, binary, nchar, text, uniqueidentifier,
smallint, smalldatetime, image ntext, nvarchar, sql_variant,
tinyint, bit, datetime varbinary varchar, char sysname, NULL
int, real, money, bigint, smallmoney
Using the correct data type saves resources and helps ward off implicit conver- sion errors. (Assigning a numeric value to a variable declared as varchar, for exam- ple, will result in an error message.)
You may have noticed that I enclose values for the state and the names in single quotes. This is because T-SQL requires that all data assigned to a variable of character string type be so enclosed. Numbers — integers, doubles, and so on — do not require quotes.
Now let’s order the result set. To get a list of all Californian writers alphabetized by last name, issue the following request:
SELECT au_fname, au_lname , phone, state from Authors WHERE state = ‘CA’
ORDER BY au_lname
The result is a list in ascending alphabetical order (from A to Y); with the ORDER BY clause, ascending order is the default. For descending alphabetical order, simply add the following modifier:
SELECT au_fname, au_lname , phone, state from Authors WHERE state = ‘CA’
ORDER BY au_lname DESC
You have more options for arranging records in the result set, such as using the GROUP BY and HAVING clauses. These are more advanced options and require an understanding of aggregate functions, which I will explain in Session 10.
Note
Saturday Morning