Sdílet prostřednictvím

  • Článek

Query Fundamentals

A query is a request for data that is stored in SQL Server. A query can be issued by 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 Server Management Studio or the osql utility can issue a SELECT statement.

  • A client or middle tier-based application, such as 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 Visual Studio Query Designer, 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 returns it 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 is made up of 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. The following 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 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.

The following SELECT statement finds the product ID, name, and list price of any products whose unit price exceeds $40:

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > $40
ORDER BY ListPrice ASC

The column names listed after the SELECT keyword (ProductID, Name, and ListPrice) form the select list. This list specifies that the result set has three columns, and each column has the name, data type, and size of the associated column in the Product 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 Product table as the one table from which the data is to be retrieved.

The WHERE clause specifies the condition that the only rows in the Product table that qualify for this SELECT statement are those rows in which the value of the ListPrice column is more than $40.

The ORDER BY clause specifies that the result set is to be sorted in ascending sequence (ASC) based on the value in the ListPrice column.