The SQL Language (Part-2)

Querying a Table

A SQL SELECT statement is used to retrieve the data from the table. Like to retrieve all the rows of table weather:

SELECT * FROM weather;

Here * showed the all columns.

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

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 can write expression, not just simple coulumn references, in the select list, 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)

A query can be “qualified” by adding a WHERE clause that specifies which rows are wanted. The WHERE
clause contains a Boolean expression.

SELECT * FROM weather
 WHERE city = 'San Francisco' AND prcp > 0.0;

It will show:

city | temp_lo | temp_hi | prcp | date

San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)

You can sort the result:

SELECT * FROM weather
 ORDER BY city;

city | temp_lo | temp_hi | prcp | date

Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27

It may not give full results:

Try this command to:

SELECT * FROM weather
 ORDER BY city, temp_lo;

You can request duplicate rows be removed from the result:

SELECT DISTINCT city
 FROM weather;

It will show

city

Hayward
San Francisco
(2 rows)

You can use DISTINCT and ORDER BY, together for correct order.

SELECT DISTINCT city
 FROM weather
 ORDER BY city;