How to Retrieve Data from a Single Table - SELECT Statement Examples
(Page 2 of 17 )
Figure 2 presents five SELECT statement examples. All of these statements retrieve data from the Invoices table. If you aren't already familiar with this table, you should use the Enterprise Manager as described in the last chapter to review its definition.
The first statement in this figure retrieves all of the rows and columns from the Invoices table. Here, an asterisk (*) is used as a shorthand to indicate that all of the columns should be retrieved, and the WHERE clause is omitted so that there are no conditions on the rows that are retrieved. Notice that this statement doesn't include an ORDER BY clause, so the rows are in primary key sequence. You can see the results following this statement as they're displayed by the Query Analyzer. Notice that both horizontal and vertical scroll bars are displayed, indicating that the result set contains more rows and columns than can be displayed on the screen at one time.
The second statement retrieves selected columns from the Invoices table. As you can see, the columns to be retrieved are listed in the SELECT clause. Like the first statement, this statement doesn't include a WHERE clause, so all the rows are retrieved. Then, the ORDER BY clause causes the rows to be sorted by the InvoiceTotal column in descending sequence.
The third statement also lists the columns to be retrieved. In this case, though, the last column is calculated from two columns in the base table, CreditTotal and PaymentTotal, and the resulting column is given the name TotalCredits. In addition, the WHERE clause specifies that only the invoice whose InvoiceID column has a value of 17 should be retrieved. The fourth SELECT statement includes a WHERE clause whose condition specifies a range of values. In this case, only invoices with invoice dates between 05/01/2002 and 05/31/2002 will be retrieved. In addition, the rows in the result set will be sorted by invoice date.
The last statement in this figure shows another variation of the WHERE clause. In this case, only those rows with invoice totals greater than 50,000 are retrieved. Notice that since none of the rows in the Invoices table satisfy this condition, the result set is empty.
Figure 2: A SELECT statement that retrieves all the data from the Invoices table
SELECT *
FROM Invoices
(114 rows)
A SELECT statement that retrieves three columns from each row, sorted in descending sequence by invoice total:
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices
ORDER BY InvoiceTotal DESC
(114 rows)
A SELECT statement that retrieves two columns and a calculated value for a specific invoice:
SELECT InvoiceID, InvoiceTotal, CreditTotal + PaymentTotal AS TotalCredits
FROM Invoices WHERE InvoiceID = 17
A SELECT statement that retrieves all invoices between given dates:
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices
WHERE InvoiceDate BETWEEN '2002-05-01' AND '2002-05-31'
ORDER BY InvoiceDate
(70 rows)
A SELECT statement that returns an empty result set:
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices
WHERE InvoiceTotal > 50000
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Code the SELECT Clause >>
More MS SQL Server Articles
More By Bryan Syverson
