A select statement defines a new table either by listing the values in a single row or, more commonly, by projecting an existing table using a from clause:

select [column description] from [existing table name]
    

The columns of the resulting table are described by a comma-separated list of expressions that are each evaluated for each row of the existing input table.

For example, we can create a two-column table that describes each city by how far north or south it is of Berkeley. Each degree of latitude measures 60 nautical miles to the north.

sqlite> select name, 60*abs(latitude-38) from cities;
    Berkeley|0
    Cambridge|240
    Minneapolis|420
    

Column descriptions are expressions in a language that shares many properties with Python: infix operators such as + and %, built-in functions such as abs and round, and parentheses that describe evaluation order. Names in these expressions, such as latitude above, evaluate to the column value in the row being projected.

Optionally, each expression can be followed by the keyword as and a column name. When the entire table is given a name, it is often helpful to give each column a name so that it can be referenced in future select statements. Columns described by a simple name are named automatically.

sqlite> create table distances as
       ...>   select name, 60*abs(latitude-38) as distance from cities;
    sqlite> select distance/5, name from distances;
    0|Berkeley
    48|Cambridge
    84|Minneapolis
    

Where Clauses. A select statement can also include a where clause with a filtering expression. This expression filters the rows that are projected. Only a row for which the filtering expression evaluates to a true value will be used to produce a row in the resulting table.

sqlite> create table cold as
       ...>   select name from cities where latitude > 43;
    sqlite> select name, "is cold!" from cold;
    Minneapolis|is cold!
    

Order Clauses. A select statement can also express an ordering over the resulting table. An order clause contains an ordering expression that is evaluated for each unfiltered row. The resulting values of this expression are used as a sorting criterion for the result table.

sqlite> select distance, name from distances order by -distance;
    84|Minneapolis
    48|Cambridge
    0|Berkeley
    

The combination of these features allows a select statement to express a wide range of projections of an input table into a related output table.