Introductory Transact-SQL (part 4)

By Addison Wesley

Querying Data

The SELECT command is used to query tables and views for data. You specify what you want via a SELECT statement, and the server "serves" it to you via a result set—a collection of rows containing the data you requested. SELECT is the Swiss Army knife of basic SQL. It can join tables, retrieve data you request, assign local variables, and even create other tables. It's a fair guess that you'll use the SELECT statement more than any other single command in Transact-SQL.

We'll begin exploring SELECT by listing the contents of the tables you just built. Execute

SELECT * FROM tablename

in Query Analyzer, replacing tablename with the name of each of the three tables. You should find that the CUSTOMER and items tables have three rows each, while orders has four.

SELECT * FROM customers

(Results abridged)

CustomerNumber LastName FirstName StreetAddress
-------------- -------- --------- -------------
1 Doe John 123 Joshua Tree
2 Doe Jane 123 Joshua Tree
3 Citizen John 57 Riverside

SELECT * FROM orders

In the following line, CustomerNumber should NOT be broken.
It was done in this case to fit the Developer.com web page.

Customer
OrderNumber OrderDate Number ItemNumber Amount
----------- ----------------------- ------ ---------- --------
101 1990-10-18 00:00:00.000 1 1001 123.45
102 1992-02-27 00:00:00.000 2 1002 678.90
103 1995-05-20 00:00:00.000 3 1003 86753.09
104 1997-11-21 00:00:00.000 1 1002 678.90

SELECT * FROM items

ItemNumber Description Price
---------- ----------- --------
1001 WIDGET A 123.45
1002 WIDGET B 678.90
1003 WIDGET C 86753.09

Column Lists

SELECT * returns all the columns in a table. To return a subset of a table's columns, use a comma-delimited field list, like so:

SELECT CustomerNumber, LastName, State FROM customers

CustomerNumber LastName State
-------------- -------- -----
1 Doe TX
2 Doe TX
3 Citizen CA

A SELECT's column can include column references, local variables, absolute values, functions, and expressions involving any combinations of these elements.
SELECTing Variables and Expressions

Unlike most SQL dialects, the FROM clause is optional in Transact-SQL when not querying database objects. You can issue SELECT statements that return variables (automatic or local), functions, constants, and computations without using a FROM clause. For example,

SELECT GETDATE()

returns the system date on the computer hosting SQL Server, and

SELECT CAST(10+1 AS CHAR(2))+'/'+CAST(POWER(2,5)-5
AS CHAR(2))+'/19'+CAST(30+31
AS CHAR(2))

returns a simple string. Unlike Oracle and many other DBMSs, SQL Server doesn't force the inclusion of a FROM clause if it makes no sense to do so. Here's an example that returns an automatic variable:

SELECT @@VERSION

And here's one that returns the current user name:

SELECT SUSER_SNAME()

@@VERSION is an automatic variable that's predefined by SQL Server and read-only. The SQL Server Books Online now refers to these variables as functions, but they aren't functions in the true sense of the word—they're predefined constants or automatic variables (e.g., they can be used as parameters to stored procedures, but true functions cannot). I like variable better than constant because the values they return can change throughout a session—they aren't really constant, they're just read-only as far as the user is concerned. You'll see the term automatic variable used throughout this book.
Functions
Functions can be used to modify a column value in transit. Transact-SQL provides a bevy of functions that can be roughly divided into six major groups: string functions, numeric functions, date functions, aggregate function, system functions, and meta-data functions. Here's a Transact-SQL function in action:

SELECT UPPER(LastName), FirstName
FROM customers

FirstName
-------------- ---------
DOE John
DOE Jane
CITIZEN John

Here, the UPPER() function is used to uppercase the LastName column as it's returned in the result set. This affects only the result set—the underlying data is unchanged.