To retrieve data from a table, the table is
queried. An SQL
SELECT statement is used to do this. The
statement is divided into a select list (the part that lists the
columns to be returned), a table list (the part that lists the
tables from which to retrieve the data), and an optional
qualification (the part that specifies any restrictions). For
example, to retrieve all the rows of table
weather, type:
SELECT * FROM weather;
(here * means "all columns") and
the output should be:
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
You may specify any arbitrary expressions in the target list. For
example, you can do:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
This should give:
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
Notice how the AS clause is used to relabel the
output column. (It is optional.)
Arbitrary Boolean operators (AND,
OR, and NOT) are allowed in
the qualification of a query. For example, the following
retrieves the weather of San Francisco on rainy days:
SELECT * FROM weather
WHERE city = 'San Francisco'
AND prcp > 0.0;
Result:
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)
As a final note, you can request that the results of a select can
be returned in sorted order or with duplicate rows removed:
SELECT DISTINCT city
FROM weather
ORDER BY city;
city
---------------
Hayward
San Francisco
(2 rows)
DISTINCT and ORDER BY can be
used separately, of course.
