SQL statements

An SQL (Structured Query Language) statement is a database independent command used to retrieve data or edit data in a database. The form of the statement depends only on the names of the tables and fields within the database. However, some databases only perform a subset of functions from the full SQL 92 specification.

SQL statements can be used to change the structure and data within a database, but this functionality will not be covered here.

The basic SQL retrieval command is the SELECT statement:

SELECT <List of fields> FROM <Table>
WHERE <Logical statement>
ORDER BY <List of sort fields>

<Table> is the name of a table or view in the database. The names of some tables in SQL statements may require an extra section in two tier databases such as Oracle which can group tables together into SCHEMA e.g. (FARM.CROPS). A dot is used to separate the schema name from the table name. The names may need to be delimited if they contain reserved characters as in fields below.
<List of fields> is a set of field names separated by commas. A * can be used to stand for all fields in a table, e.g.:

SELECT * from Invoices
SELECT Invoices.* from Invoices

A field name can be qualified by the table it belongs to. If it is unqualified it is understood to belong to the first FROM table. If the field or table name contains reserved characters (not a letter A-Z, a-z or a number 0-9) then it must be delimited (the SQL default is single quote marks ” e.g. ‘Pay Day’). Genstat will use the delimiters specified by the ODBC driver you select in its SQL statements.

A subset of rows in the query can be selected with the WHERE clause, which is optional. The <Logical statement> must return values of either TRUE (1) or FALSE (0). This statement can be made up of functions, field values and operators. The logical operators AND OR and NOT can be used to connect comparisons using operators such as =, >, =, <=, etc. A full range of functions (ABS, SQRT, LOG, UCASE, LEFT etc.) is available and literal numbers and text (quoted with ”) can also be used in expressions. Literal dates are entered in a special format: {d YYYY-MM-DD} (e.g. {d 1998-10-27}), and date/time values in the format {ts YYYY-MM-DD HH-MM-SS}. E.g.:

Date > {d 1995-01-01}
  ABS(PaymentDate-InvoiceDate) > 30
  Weight1 < Weight2 AND Weight2 < Weight3
  UCASE(Treat) = ‘CONTROL’ OR UCASE(Treat) = ‘SHORT’

The order the rows are returned can be altered using the ORDER BY clause, which is optional. The <List of sort fields> is a list of field names separated by commas. The field names may be optionally suffixed with the ASC (ascending order, the default) or DESC (descending order) key words. E.g.:

ORDER BY Age,Height
ORDER BY Age DESC, Height ASC

Multiple tables may also be JOINED together with an outer join clause:

{oj <table name> OUTER JOIN <table name> … ON <Search condition>}

The rows from the first table are matched with all rows from subsequent tables, and the resulting row combinations are restricted to those that meet the requirements of the ON clause and any subsequent WHERE clauses. E.g.:

SELECT Invoices.* FROM {oj Invoices OUTER JOIN Company ON Invoices.CID = Company.CID}
  WHERE Company.Country = ‘New Zealand’
  SELECT Paddock.*,Farm.*,County.Name FROM
    {oj Paddock OUTER JOIN Farm OUTER JOIN County
    ON Paddock.FarmNo = Farm.FarmNo AND Farm.CountyNo = County.CountyNo }
    ORDER BY County.Name,Farm.FarmNo,Paddock.No

SQL statements

See also

Was this article helpful?