The SELECT Statement

The SELECT statement retrieves data from a database and returns it you in the form of query results. For simple queries, the English language request and the SQL SELECT statement are very similar. When the requests become more complex, more features of the select statement must be used to specify the query precisely.

List eastern region sales offices whose sales exceed their targets, sorted in alphabetical order by city.

SELECT CITY, TARGET, SALES

FROM OFFICES

WHERE REGION = ‘Eastern’

AND SALES > TARGET

ORDER BY CITY

CITY                 TARGET          SALES

-------------  -------------   --------------

Atlanta         $350,000.00          $367,911.00

New York        $575,000.00          $692,637.00

What are the average target and sales for Eastern region offices?

SELECT AVG (TARGET), AVG (SALES)

FROM OFFICES

WHERE REGION = ‘Eastern’

AVG (TARGET)      AVG (SALES)

--------------    -------------

$575,000.00       $598,530.00

 

The figure shows the full form of the SELECT statement, which consists of six clauses. The SELECT and FROM clauses of the statement are required. The remaining four clauses are optional. You include them in a SELECT statement only when you want to use the functions they provide. The following list summarizes the function of each clause:

·         The SELECT clause lists the data items to be retrieved by the SELECT statement. The items may be columns from the database, or columns to be calculated by SQL as it performs the query.

  The FROM clause lists the tables that contain the data to be retrieved by the query. Queries that draw        their data from a single table are described here.

·      

·         The WHERE clause tells the SQL to include only certain rows of data in the query results. A search condition is used to specify the desired rows.

·         The GROUP BY clause specifies a summary query. Instead of producing one row of query results for each row of query results for each row of data in the database, a summary query groups form from similar rows and then produces one summary row of query results for each group.

·         The HAVING clause tells SQL to include only certain groups produced by the GROUP BY clause in the query results. Like the WHERE clause, it uses a search condition to specify the desired groups.

·         The ORDER BY clause sorts the query results based on the data in one or more columns. If it is omitted, the query results are not sorted.

 The select clause:

 The select clause that begins each SELECT statement specifies the data items to be retrieved by the query. The items are usually specified by a select list, a list of select items separated by commas. Each select item in the list generates a single column of query results, in left-to-right order. A select item can be one of the following:
 

·         A column name, identifying a column from the table(s) named in the FROM clause. When a column name appears as a select item, SQL simply takes the value of that column from each row of the database table and places it in the corresponding row of query results.

·         A constant, specifying that the same constant value is to appear in every row of the query results.

·         A SQL expression, indicating that SQL must calculate the value to be placed into the query results, in the style specified by the expression.

 The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

 SELECT column_name(s)

 FROM table_name

The SQL statement below shows a simple usage of the SQL SELECT command:

 SELECT FirstName, LastName, DateOfBirth

 FROM Employees

The SELECT statement has many optional clauses:

WHERE          specifies which rows to retrieve.

GROUP BY  groups rows sharing a property so that an aggregate function can be applied to each group.

HAVING       selects among the groups defined by the GROUP BY clause.

ORDER BY     specifies an order in which to return the rows.