To retrieve data from a table, a
query is performed. The SQL
SELECT statement is used to do this. The
statement is divided into a select list (which specifies the
columns to be returned), a table list (the
tables from which to retrieve the data), and an optional
qualification (which specifies any restrictions). For
example, to retrieve all the rows of table
weather, type:
Here
"*" means
"all columns".
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 SELECT list. For
example, you can do:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; |
This should return:
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.
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
be returned in sorted order, with duplicate rows removed, or both.
SELECT DISTINCT city
FROM weather
ORDER BY city; |
city
---------------
Hayward
San Francisco
(2 rows) |