The SELECT Statement

The SELECT statement is the most frequently used SQL command and is the fundamental way to query data. The SQL syntax for the SELECT statement is intuitive—at least in its simplest forms—and resembles how you might state a request in English. As its name implies, it is also structured and precise. However, a SELECT statement can be obscure or even tricky. Brainteasers about writing a SELECT statement to perform some tricky task often appear in database publications. As long as you write a query that's syntactically correct, you get a result. However, it might not be the result you really want—you might get the answer to a different question than the one you thought you posed! Queries with incorrect semantics commonly cause bugs in applications, so you should understand proper query formulation.

The basic form of SELECT, which uses brackets ([]) to identify optional items, is shown here.

 SELECT [DISTINCT][TOP n] <columns to be chosen, optionally eliminating duplicate rows from result set or limiting number of rows to be returned> [FROM] <table names> [WHERE] <criteria that must be true for a row to be chosen> [GROUP BY] <columns for grouping aggregate functions> [HAVING] <criteria that must be met for aggregate functions> [ORDER BY] <optional specification of how the results should be sorted> 

Notice that the only clause that must always be present is the verb SELECT; the other clauses are optional. For example, if the entire table is needed, you don't need to restrict data using certain criteria, so you can omit the WHERE clause.

Here's a simple query that retrieves all columns of all rows from the authors table in the pubs sample database:

 SELECT * FROM authors 

Figure 7-1 shows the output.

Using tools such as OSQL and SQL Query Analyzer, you can issue queries interactively. You can also, of course, build queries into applications. The calling application determines the formatting of the data output. SQL Server returns the data back to the calling application, which then works with the data. In the case of an ad hoc query tool, it displays the output on a monitor.

click to view at full size.

Figure 7-1. The authors table in the pubs database.

NOTE


In general, this book concentrates on topics specific to Microsoft SQL Server, but it wouldn't be complete without some discussion of the SQL language. Because the treatment of generic SQL in this book is, by design, far from complete, please see Appendix D for suggestions on further reading, including books on ANSI Standard SQL-92 and SQL-99 as well as books on the nuances of Microsoft Transact-SQL.

The power of the SQL language begins to reveal itself when you limit the information to be returned to specified ranges. For example, you can specify that a query from one table return only certain columns or rows that meet your stated criteria. In the select list, you specify the exact columns you want, and then in the WHERE clause, you specify the criteria that determine whether a row should be included in the answer set. Still using the pubs sample database, suppose we want to find the first name and the city, state, and zip code of residence for authors whose last name is Ringer:

 SELECT au_lname, au_fname, city, state, zip FROM authors WHERE au_lname='Ringer' 

Here's the output:

 au_lname au_fname city state zip -------- -------- -------------- ----- ----- Ringer Albert Salt Lake City UT 84152 Ringer Anne Salt Lake City UT 84152 

The results of this query tell us that two authors are named Ringer. But we're interested only in Anne. Retrieving only Anne Ringer's data requires an additional expression that is combined (AND'ed) with the original. In addition, we'd like the output to have more intuitive names for some columns, so we respecify the query:

 SELECT 'Last Name'=au_lname, 'First'=au_fname, city, state, zip FROM authors WHERE au_lname='Ringer' AND au_fname='Anne' 

Here's the output, just as we want it:

 Last Name First city state zip --------- ----- -------------- ----- ----- Ringer Anne Salt Lake City UT 84152 



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net