Tables

The SQL language is standardized, but most database systems implement some custom variant of the language that is endowed with proprietary features. In this text, we will describe a small subset of SQL as it is implemented in Sqlite. You can follow along by downloading Sqlite or by using this online SQL interpreter.

A table, also called a relation, has a fixed number of named and typed columns. Each row of a table represents a data record and has one value for each column. For example, a table of cities might have columns latitude longitude that both hold numeric values, as well as a column name that holds a string. Each row would represent a city location position by its latitude and longitude values.

A table with a single row can be created in the SQL language using a select statement, in which the row values are separated by commas and the column names follow the keyword “as”. All SQL statements end in a semicolon.

sqlite> select 38 as latitude, 122 as longitude, "Berkeley" as name;
    38|122|Berkeley
    

The second line is the output, which includes one line per row with columns separated by a vertical bar.

A multi-line table can be constructed by union, which combines the rows of two tables. The column names of the left table are used in the constructed table. Spacing within a line does not affect the result.

sqlite> select 38 as latitude, 122 as longitude, "Berkeley" as name union
       ...> select 42,             71,               "Cambridge"        union
       ...> select 45,             93,               "Minneapolis";
    38|122|Berkeley
    42|71|Cambridge
    45|93|Minneapolis
    

A table can be given a name using a create table statement. While this statement can also be used to create empty tables, we will focus on the form that gives a name to an existing table defined by a select statement.

sqlite> create table cities as
       ...>    select 38 as latitude, 122 as longitude, "Berkeley" as name union
       ...>    select 42,             71,               "Cambridge"        union
       ...>    select 45,             93,               "Minneapolis";
    

Once a table is named, that name can be used in a from clause within a select statement. All columns of a table can be displayed using the special select * form.

sqlite> select * from cities;
    38|122|Berkeley
    42|71|Cambridge
    45|93|Minneapolis