## 1.4.11 SQL Reference

The SQL parser to parse `cursor.execute(sql, mode='portable')` statements has already been written and is available as a standalone module named `SQLParserTools`. The approach of parsing an SQL statement just to rebuild it again in an abstraction layer function might sound unnecessary but the advantage is that the SQL written in this manner is guaranteed to function in the same way across all `web.database` databases.

This specification implements what is considered the lowest possible useful SQL feature set which is commonly used and which all databases will support. A balance has had to be made between including useful features and excluding features which only some database engines support. Also no duplication of features has been included. For example `BETWEEN` can be implemented using `>` and `<` operators in the `WHERE` clause so has not been included but the `LIKE` operator has.

The specification includes:

SQL SELECT
SQL WHERE
SQL INSERT
SQL UPDATE
SQL DELETE
SQL ORDER BY
SQL AND & OR
Simple Joins
SQL CREATE
SQL DROP
NULL values

#### 1.4.11.0.1 Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. `Customers` or `Orders`). Tables contain records (rows) with data.

Below is an example of a table called `Person`:

    +----------+-----------+--------+-------------+
    | LastName | FirstName | Number | DateOfBirth |
    +----------+-----------+--------+-------------+
    | Smith    | John      | 10     | 1980-01-01  |
    +----------+-----------+--------+-------------+
    | Doe      | John      | 3      | 1981-12-25  |
    +----------+-----------+--------+-------------+

The table above contains two records (one for each person) and four columns (LastName, FirstName, Address, and DateOfBirth).

#### 1.4.11.0.2 Queries

With SQL, we can query a database and have a result set returned.

A query looks like this:

    SELECT LastName FROM Person

Gives a result set like this:

    +----------+
    | LastName |
    +----------+
    | Smith    |
    +----------+
    | Doe      |
    +----------+

Note: Some database systems require a semicolon at the end of the SQL statement. `web.database` does not.