December 22, 2014
Hot Topics:

Introductory Transact-SQL

  • May 7, 2003
  • By Addison Wesley
  • Send Email »
  • More Articles »

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 Tree2              Doe      Jane      123 Joshua Tree3              Citizen  John      57 RiversideSELECT * FROM ordersIn the following line, CustomerNumber should NOT be broken.
It was done in this case to fit the Developer.com web page.
CustomerOrderNumber OrderDate Number ItemNumber Amount----------- ----------------------- ------ ---------- --------101 1990-10-18 00:00:00.000 1 1001 123.45102 1992-02-27 00:00:00.000 2 1002 678.90103 1995-05-20 00:00:00.000 3 1003 86753.09104 1997-11-21 00:00:00.000 1 1002 678.90SELECT * FROM itemsItemNumber Description Price---------- ----------- --------1001 WIDGET A 123.451002 WIDGET B 678.901003 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 customersCustomerNumber LastName State-------------- -------- -----1              Doe      TX2              Doe      TX3              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), FirstNameFROM customers               FirstName-------------- ---------DOE            JohnDOE            JaneCITIZEN        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.





Page 4 of 10



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel