Simple Queries

The purpose of the SELECT statement is to retrieve and display data from one or more
database tables. It is an extremely powerful command capable of performing the equivalent
of the relational algebra’s Selection, Projection, and Join operations in a single statement
(see Section 4.1). SELECT is the most frequently used SQL command and has the
following general form:
SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [, . . . ]}
FROM TableName [alias] [, . . . ]
[WHERE condition]
[GROUP BY columnList] [HAVING condition]
[ORDER BY columnList]
columnExpression represents a column name or an expression, TableName is the name of an
existing database table or view that you have access to, and alias is an optional abbreviation
for TableName. The sequence of processing in a SELECT statement is:
FROM specifies the table or tables to be used
WHERE filters the rows subject to some condition
GROUP BY forms groups of rows with the same column value
HAVING filters the groups subject to some condition
SELECT specifies which columns are to appear in the output
ORDER BY specifies the order of the output
The order of the clauses in the SELECT statement cannot be changed. The only two
mandatory clauses are the first two: SELECT and FROM; the remainder are optional.
The SELECT operation is closed: the result of a query on a table is another table (see
Section 4.1). There are many variations of this statement, as we now illustrate.
Retrieve all rows
Example 5.1 Retrieve all columns, all rows
List full details of all staff.
Since there are no restrictions specified in this query, the WHERE clause is unnecessary
and all columns are required. We write this query as:
SELECT staffNo, fName, lName, position, sex, DOB, salary, branchNo
FROM Staff;
Since many SQL retrievals require all columns of a table, there is a quick way of expressing
‘all columns’ in SQL, using an asterisk (*) in place of the column names. The following
statement is an equivalent and shorter way of expressing this query:
SELECT *
FROM Staff;
The result table in either case is shown in Table 5.1.
Table 5.1 Result table for Example 5.1.
staffNo fName lName position sex DOB salary branchNo
SL21 John White Manager M 1-Oct-45 30000.00 B005
SG37 Ann Beech Assistant F 10-Nov-60 12000.00 B003
SG14 David Ford Supervisor M 24-Mar-58 18000.00 B003
SA9 Mary Howe Assistant F 19-Feb-70 9000.00 B007
SG5 Susan Brand Manager F 3-Jun-40 24000.00 B003
SL41 Julie Lee Assistant F 13-Jun-65 9000.00 B005
Example 5.2 Retrieve specific columns, all rows
Produce a list of salaries for all staff, showing only the staff number, the first and last
names, and the salary details.
SELECT staffNo, fName, lName, salary
FROM Staff;
In this example a new table is created from Staff containing only the designated columns
staffNo, fName, lName, and salary, in the specified order. The result of this operation is shown
in Table 5.2. Note that, unless specified, the rows in the result table may not be sorted.
Some DBMSs do sort the result table based on one or more columns (for example,
Microsoft Office Access would sort this result table based on the primary key staffNo). We
describe how to sort the rows of a result table in the next section.
Table 5.2 Result table for Example 5.2.
staffNo fName lName salary
SL21 John White 30000.00
SG37 Ann Beech 12000.00
SG14 David Ford 18000.00
SA9 Mary Howe 9000.00
SG5 Susan Brand 24000.00
SL41 Julie Lee 9000.00
Example 5.3 Use of DISTINCT
List the property numbers of all properties that have been viewed.
SELECT propertyNo
FROM Viewing;
The result table is shown in Table 5.3(a). Notice that there are several duplicates because,
unlike the relational algebra Projection operation (see Section 4.1.1), SELECT does not
eliminate duplicates when it projects over one or more columns. To eliminate the duplicates,
we use the DISTINCT keyword. Rewriting the query as:
SELECT DISTINCT propertyNo
FROM Viewing;
we get the result table shown in Table 5.3(b) with the duplicates eliminated.
Example 5.4 Calculated fields
Produce a list of monthly salaries for all staff, showing the staff number, the first and last
names, and the salary details.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
This query is almost identical to Example 5.2, with the exception that monthly salaries are
required. In this case, the desired result can be obtained by simply dividing the salary by
12, giving the result table shown in Table 5.4.
This is an example of the use of a calculated field (sometimes called a computed or
derived field). In general, to use a calculated field you specify an SQL expression in the
SELECT list. An SQL expression can involve addition, subtraction, multiplication, and
division, and parentheses can be used to build complex expressions. More than one table
column can be used in a calculated column; however, the columns referenced in an arithmetic
expression must have a numeric type.
The fourth column of this result table has been output as col4. Normally, a column in
the result table takes its name from the corresponding column of the database table from
which it has been retrieved. However, in this case, SQL does not know how to label the
column. Some dialects give the column a name corresponding to its position in the table
(for example, col4); some may leave the column name blank or use the expression entered
in the SELECT list. The ISO standard allows the column to be named using an AS clause.
In the previous example, we could have written:
SELECT staffNo, fName, lName, salary/12 AS monthlySalary
FROM Staff;
In this case the column heading of the result table would be monthlySalary rather than col4.
Row selection (WHERE clause)
The above examples show the use of the SELECT statement to retrieve all rows from a
table. However, we often need to restrict the rows that are retrieved. This can be achieved
with the WHERE clause, which consists of the keyword WHERE followed by a search
condition that specifies the rows to be retrieved. The five basic search conditions (or predicates
using the ISO terminology) are as follows:
n Comparison Compare the value of one expression to the value of another expression.
n Range Test whether the value of an expression falls within a specified range of values.
n Set membership Test whether the value of an expression equals one of a set of values.
n Pattern match Test whether a string matches a specified pattern.
n Null Test whether a column has a null (unknown) value.
The WHERE clause is equivalent to the relational algebra Selection operation discussed
in Section 4.1.1. We now present examples of each of these types of search conditions.
Example 5.5 Comparison search condition
List all staff with a salary greater than £10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
Here, the table is Staff and the predicate is salary > 10000. The selection creates a new table
containing only those Staff rows with a salary greater than £10,000. The result of this
operation is shown in Table 5.5.