8.3.2 Selecting, Filtering and Sorting Data

8.3.2 Selecting, Filtering and Sorting Data

Once tables contain data, a query can retrieve only the information needed for a particular task. In this section, you will build one-table SQL queries using SELECT, FROM, WHERE and ORDER BY.

The Example Table

The examples continue the independently created EcoTrack database from Section 8.3.1. The Sensor table contains one tuple for each monitoring sensor.

Anatomy of a Simple Query

SELECT SensorID, SensorType
FROM Sensor;

The attributes after SELECT become the result columns. The table after FROM supplies the candidate tuples.

Selecting the Required Columns

More than one attribute can be selected. Attribute names are separated by commas and are not enclosed in parentheses.

SELECT SensorID, InstalledOn, SampleInterval
FROM Sensor;

Use * only when every attribute is required:

SELECT *
FROM Sensor;

Filtering Tuples with WHERE

A WHERE clause keeps only tuples for which its condition is true.

SELECT SensorID, SensorType
FROM Sensor
WHERE SensorType = 'Air quality';

The output contains three tuples because three sensors have the stored type 'Air quality'.

Writing values correctly

Comparison Operators

The operator in a WHERE condition determines how an attribute value is compared with another value.

Combining Conditions

Use AND when every condition must be true. Use OR when at least one of the conditions may be true.

AND: both tests must pass

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
WHERE SensorType = 'Air quality'
AND SampleInterval <= 15;

Only S-104 and S-311 satisfy both conditions.

OR: either test may pass

SELECT SensorID, SensorType
FROM Sensor
WHERE SensorType = 'Water level'
OR SensorType = 'Temperature';

This returns S-405 and S-522.

Sorting Query Output with ORDER BY

ORDER BY arranges the result set. It does not permanently reorder the tuples stored in the source table.

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
ORDER BY SampleInterval;

Ascending order is the usual default. It may also be written explicitly:

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
ORDER BY SampleInterval ASC;

Use DESC for descending order:

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
ORDER BY SampleInterval DESC;

Sorting by more than one attribute

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
ORDER BY SensorType ASC, SampleInterval DESC;

The first attribute is the main sort key. The second resolves ties between tuples that have the same first value.

How the Clauses Shape the Result

SQL is written in one order, but it is helpful to imagine the DBMS building the result in the following logical sequence:

Worked Example: Build a Query from a Request

Step 1: Identify the output attributes

The requested columns are SensorID, SensorType and SampleInterval.

Step 2: Identify the source table

All three attributes are stored in Sensor.

Step 3: Translate the filter

“20 minutes or less” becomes SampleInterval <= 20.

Step 4: Translate the required order

“Slowest qualifying interval first” means the largest value must appear first, so use DESC.

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
WHERE SampleInterval <= 20
ORDER BY SampleInterval DESC;

Result

Interactive: SELECT Query Explorer

Build queries from controls, trace a query clause by clause and diagnose common errors.

-- Build a query

-- Build the query in written order

Common Mistakes and Misconceptions

Practice

Review