Querying a Table

2.5. Querying a Table

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;


     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:

    FROM weather
    ORDER BY city;

 San Francisco
(2 rows)

DISTINCT and ORDER BY can be used separately, of course.

Online Resources
Beginner Tutorials
Microsoft SQL
Contact Us
Random quote of the moment:
Classical music is the kind we keep thinking will turn into a tune. -- Kin Hubbard, "Abe Martin's Sayings"
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!