In summary, a SQL statement is comprised of tokens, where each token can represent either a keyword, an identifier, a quoted identifier, a constant, or a special character symbol. Table 3-6 uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components.

Table 3-6. A Simple SQL Query

As shown in the table, the SELECT statement contains the keywords SELECT and FROM . The FROM keyword is also called a clause, as it modifies and further describes the SELECT command.

The id , name and states tokens are the identifiers of the statement. The id and name identifiers specify the columns to be selected, while the states identifier specifies the table name to select from. Therefore, with the preceding SQL query, you are instructing PostgreSQL to display the columns named id and name for each row from the states table.

Example 3-13. Example SQL Query

booktown=# SELECT id, name FROM states;
 id |    name
----+------------
 42 | Washington
 51 | Oregon
(2 rows)
 
booktown=#

Getting more complicated, Table 3-7 and Table 3-8 break down another example statement. This statement uses the UPDATE command, along with SET and WHERE clauses, which respectively specify what to update the records with, and how to find the records to update.

Table 3-7. UPDATE Example: The SET Clause

Table 3-8. UPDATE Example: The WHERE Clause

When executed, this statement compares each record's name column to find matches for the WHERE clause's stated condition (equivalence to the string constant 'Oregon'). Then, for each column which matches that condition, it updates the id column with the value 51 .

Breaking it down, this UPDATE statement has three keywords, three identifiers, two operators, and two constants. The keywords are UPDATE (the SQL command), SET , and WHERE (the SQL modifying clauses). The identifiers are the states table name, the id column name, and the name column name.

The operators are both represented by the = operator. When used with the SET clause, this operator is used for assignment (to assign a new value to an existing record's identified column); this is a special use which is unique to the SET clause. In contrast, when used with the WHERE clause, the = operator is used to check equivalence between values. In this case, this means that the equivalence operator will check the value of a record's name column and a string constant with the value of Oregon .

Finally, the constants in this statement are the integer constant 51 (the new value for the id column), and the string constant Oregon (compared to the name column through the WHERE clause).

Example 3-14 therefore updates the states table by setting the id column to 51 wherever the name column matches the value Oregon . It then checks the results of that UPDATE statement with another SELECT statement.

Example 3-14. A SQL Update

booktown=# UPDATE states 
booktown-#    SET id = 51
booktown-#  WHERE name = 'Oregon';
UPDATE 1
booktown=# SELECT * FROM states 
booktown-#  WHERE name = 'Oregon';
 id |  name  | abbreviation
----+--------+--------------
 51 | Oregon | OR
(1 row)