PostgreSQL: Sources of information/reference:
There are several places to find reminders of the syntax of SQL.
They
take the form of
Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
where from_item can be:
[ ONLY ] table_name [ * ]
[ [ AS ] alias [ ( column_alias_list ) ] ]
.........(there is more)
In these, [ ] means optional, | separates alternatives, { } means
one
alternative must be present, ... means more of the same.
To find the help:
- in psql: \h
for
a list of commands, then \h
insert -- for help on insert, etc.
- The HTML documentation is on the postgresql web site,
current version. The reference section is
by SQL Commands, alphabetically from
ABORT to UPDATE.
Joining tables
You will have noticed that tables may be related by foreign key. A
SELECT statement may produce a result table from two (or more )
tables
by joining the rows that "match up." One can of course also select
certain columns, and limit rows using WHERE criteria. For
example,
suppose Joe has ordered a T-shirt, and we want to know the details
that
are in inventory, for everything Joe has ordered:
SELECT order.quantity, size, price, description
FROM order, inventory
WHERE order.item = inventory.tid
AND order.customer = 'Joe'