SQL Server Magazine

This month, I'll show you how to write simple SQL queries. You can run the queries that this article describes from the ISQL/W window or the Query window in the Enterprise Manager. Both windows have the same features. Select the Query tab at the top of the Query window before you type in SQL statements. You use the Query window to write select statements, or database queries, when you want to retrieve information from the database. You can also use the Query window for data modification statements, such as insert, update, and delete instructions. And you can use the Query window to administer the database, create tables and columns, and so on.

The Select Statement
You use an SQL query to retrieve the data you want from the database. A query has three parts: SELECT, FROM, and WHERE. SELECT tells SQL Server which columns you want to see as the result of the query. FROM specifies which table or tables you want to use in the query. WHERE limits which rows you want to see. A select statement contains three elements: select, from, and where. The syntax isn't case sensitive, but I'll use uppercase and lowercase to distinguish the commands from the object names. A typical query looks like this:

   SELECT col1, col2, col3......
   FROM  table1
   WHERE  col1  = search condition

Consider this simple query:

USE  pubs
GO
SELECT * FROM authors

The phrase "USE pubs" ensures that your statement queries the pubs database. You could instead select the database in the DB window on the Toolbar. GO ensures that SQL Server has completed the previous command before the next one starts. This query will return all columns and rows in the authors table. You use the asterisk to specify all the columns in the table. You use FROM authors to retrieve data from the authors table.

But one of the three elements—the WHERE clause—is missing from this statement. So if you use this statement, SQL Server will return all the rows. A query without a WHERE clause can return a large amount of data, increasing network traffic. And an update or delete statement without a WHERE clause to restrict the affected rows could be disastrous.

Because the authors table in the pubs sample database is small, you can run this query without worry. Click on the green arrow or press CTRL-E. Screen 1 shows the results. You need to scroll the window to see all the columns and all the rows.

Selecting Columns
If you want to see only certain columns, you can restrict the output by listing the names of the columns you want. You can run a stored procedure that SQL Server provides to determine the column names. From the Query window, type and run:

sp_help  authors  

You can type this query in the same window as the previous query, then highlight it to run the line on its own. Or you can use the cursor or mouse to highlight the word authors, then press ALT-F1. This action runs the sp_help stored procedure on the highlighted table name. Both methods will yield the same output—a list of columns and their datatypes and length. Another way you can identify the column names is to add a line to the query:

SELECT * FROM authors
WHERE  0  =  1

Because the WHERE condition is never met for this query (zero can't equal one), the query won't produce output, as Screen 2 shows. But the query will return a page header that includes column names, with the order and spacing in which they'll appear on the final query. Now you can choose which columns you want to see by listing them in the SELECT clause:

SELECT au_id, au_lname, au_fname, phone
   FROM authors

Note that commas separate the column names. For clarity, FROM is now on the line below SELECT. I also indented it, again for clarity, but you can type it on the same line. If you run this query, you'll see that the output consists of only the four requested columns.

   Prev. page   [1] 2 3 4     next page