SQL statements always begin with a command , (a word, or group of words, which describes what action the statement will initiate). The command can be called the verb of the SQL statement, as it always describes an action to be taken. Statements typically contain one or more clauses , which are formal modifiers which further describe the function of the SQL statement.

Table 3-2 contains a list of some of the most commonly used PostgreSQL commands.

Table 3-2. Fundamental PostgreSQL Commands

Command

Description

CREATE DATABASE

Creates a new database

CREATE INDEX

Creates a new index on a table column

CREATE SEQUENCE

Creates a new sequence in an existing database

CREATE TABLE

Creates a new table in an existing database

CREATE TRIGGER

Creates a new trigger definition

CREATE VIEW

Creates a new view on an existing table

SELECT

Retrieves records from a table

INSERT

Adds one or more new records into a table

UPDATE

Modifies the data in existing table records

DELETE

Removes existing records from a table

DROP DATABASE

Destroys an existing database

DROP INDEX

Removes a column index from an existing table

DROP SEQUENCE

Destroys an existing sequence generator

DROP TABLE

Destroys an existing table

DROP TRIGGER

Destroys an existing trigger definition

DROP VIEW

Destroys an existing table view

CREATE USER

Adds a new PostgreSQL user account to the system

ALTER USER

Modifies an existing PostgreSQL user account

DROP USER

Removes an existing PostgreSQL user account

GRANT

Grant rights on a database object to a user

REVOKE

Deny rights on a database object from a user

CREATE FUNCTION

Creates a new SQL function within a database

CREATE LANGUAGE

Creates a new language definition within a database

CREATE OPERATOR

Creates a new SQL operator within a database

CREATE TYPE

Creates a new SQL data type within a database

While obviously code-like in nature, SQL was designed with ease of use and readability in mind. As a result, SQL statements often bear a strong resemblance to simple, instructional English sentences. A strong feature of SQL is that its statements are designed to instruct the server what data to find, not literally how to find it, as you would be forced to in an ordinary programming language. Reading a well-designed SQL query should be nearly as easy as reading an ordinary sentence.

Note "Statements" vs "Queries"
 

In SQL texts, the word query is frequently used interchangeably with statement . In order to be clear, within this book the term query is used only to refer to statements which return data (e.g., SELECT statements), rather than a general SQL statement which may instead create, add or modify data (without returning rows).

Internally, PostgreSQL interprets structured SQL statements as a sequence of tokens , usually delimited by whitespace (spaces or newlines, outside of quotes), though some tokens may be placed adjacently if there is no chance of ambiguity (such as when operators are placed directly next to identifiers). A token in this context is a word or character which can be identified meaningfully by the server when the SQL statement is parsed , or interpreted.

Technically, each token can either be considered a keyword , an identifier , a quoted identifier , a constant (also called a literal ), or one of several special character symbols.

Keywords are words PostgreSQL recognizes as a word with a pre-defined SQL or PostgreSQL-specific meaning; these includes SQL commands, clauses, function names, and special noise terms which are often accompanied optionally with SQL commands (e.g., the noise term COLUMN in the ALTER TABLE command). In contrast, identifiers represent variable names for tables, columns, and any other database object.

Both keywords and identifiers are essentially references to an internally defined function, value or record as far as PostgreSQL is concerned. A literal, on the other hand, describes a literal piece of data, such as a number, or character string.

Finally, a SQL statement will contain special character symbols. These are reserved characters (such as the parentheses, semi-colon, and square brackets) which logically affect the meaning and arrangement of your keywords, identifiers and literals. You can think of these characters as the punctuation for your SQL statement. Operators fall under the category of special character symbols. Operators can be used to imply logical operations or evaluations between data values (either literals, or represented by identifiers), and are generally between one and four characters in length.

The following sections explain and expand upon the nature of these elementary components of SQL.