Query Fundamentals
A query is a request for data stored in Microsoft® SQL Server™ 2000. A query can be issued using several forms:
- An MS Query or Microsoft Access user can use a graphical user interface (GUI) to pick the data the user wants to see from one or more SQL Server tables.
- A user of SQL Query Analyzer or the osql utility can issue a SELECT statement.
- A Microsoft Visual Basic® application can map the data from a SQL Server table into a bound control, such as a grid.
Although queries have various ways of interacting with a user, they all accomplish the same task: They present the result set of a SELECT statement to the user. Even if the user never specifies a SELECT statement, as is usually the case with graphical tools such as MS Query, the client software transforms each user query into a SELECT statement that is sent to SQL Server.
The SELECT statement retrieves data from SQL Server and presents it back to the user in one or more result sets. A result set is a tabular arrangement of the data from the SELECT. Like an SQL table, the result set comprises columns and rows.
The full syntax of the SELECT statement is complex, but most SELECT statements describe four primary properties of a result set:
- The number and attributes of the columns in the result set. These attributes must be defined for each result set column:
- The data type of the column.
- The size of the column, and for numeric columns, the precision and scale.
- The source of the data values returned in the column.
- The data type of the column.
- The tables from which the result set data is retrieved, and any logical relationships between the tables.
- The conditions that the rows in the source tables must meet to qualify for the SELECT. Rows that do not meet the conditions are ignored.
- The sequence in which the rows of the result set are ordered.
This SELECT statement finds the product ID, name, and unit price of any products whose unit price exceeds $40:
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice > $40
ORDER BY UnitPrice ASC
The column names listed after the SELECT keyword (ProductID, ProductName, and UnitPrice) form the select list. This specifies that the result set has three columns, and each column has the name, data type, and size of the associated column in the Products table. Because the FROM clause specifies only one base table, all column names in the SELECT statement refer to columns in that table.
The FROM clause lists the single table, Products, from which the data is to be retrieved.
The WHERE clause specifies that the only rows in Products that qualify for this SELECT are those in which the value of the UnitPrice column exceeds $40.
The ORDER BY clause specifies that the result set is to be sorted in ascending sequence based on the value in the UnitPrice column.