SQL and Relational Databases

SQL (Structured Query Language) is the language which is used to access and communicate with a relational database. Note that the examples shown below are not working code samples which will function without syntax errors in any specific database engine.

SELECT statement

SELECT * | [<field name> [, [ , ... ]]
FROM <table name>
WHERE <expression> [AND | OR <expression> [ AND | OR ... ]]
ORDER BY [<field name> [, ... ]] ASC | DESC
GROUP BY [<field name> [, ... ]]
HAVING <expression>

WHERE clause

WHERE <expression>

<expression> could be a comparison, a subselect, a set content comparison, etc.

ORDER BY clause

ORDER BY [<field name> [, ... ]] ASC | DESC

The fields in the order by clause must be explictly stated in the SELECT section. Thus SELECT * can not be used with an ORDER BY clause. This is true with Ingres but not Oracle. Oracle does not require a column to be listed to allow sorting on that column.

GROUP BY clause

GROUP BY [<field name> [, ... ]]

The GROUP BY clause will retrieve a group of records as an aggregate based on an aggregation function placed into the select clause.

HAVING clause

HAVING <expression>

The HAVING clause allows restrictions placed onto the groups aggregated by the GROUP BY clause much like the WHERE clause acts on the SELECT statement. Thus the WHERE clause is faster than the HAVING clause since the WHERE clause operates in the index and data spaces and the HAVING clause operates on the implicit cursor produced by the SELECT statement after the application of the WHERE. Thus the HAVING clause does not use indexes at all. NEVER replace a WHERE clause with a HAVING clause.

INSERT statement

INSERT INTO <table name> [<field name> [, ... ]] VALUES ([<field name> [, ... ]])

INSERT INTO <table name> AS <subselect>

UPDATE statement

UPDATE <table name>
SET <field name> = <variable name> || <subselect>
WHERE <expression> [AND | OR <expression> [ AND | OR ... ]]

WHERE clause

WHERE <expression>

<expression> could be a comparison, a subselect, a set content comparison, etc.

DELETE statement

DELETE FROM <table name> WHERE <expression> || <subselect>

WHERE clause

WHERE <expression>

<expression> could be a comparison, a subselect, a set content comparison, etc.