Extracts data from one or more fields from the table(s) specified by the FROM clause.
See Also:
DISTINCT, JOIN, WHERE, ORDER BY, GROUP BY, LIKE
Syntax:
SELECT
[DISTINCT]
[SUM] <field1, field2,
[CASE
WHEN field1 = Val THEN NewVal
[WHEN field2 = Val THEN NewVal]
ELSE value
END] AS <field3>], field4>
FROM <table1, table2> [AS]
[INNER] | [OUTER] JOIN ON <field1 = field2>
WHERE <where expression> [LIKE] [IN] [BETWEEN] [IS [NOT] NULL]
ORDER BY [ASC] | [DESC] <field1, field2>
GROUP BY <field1, field2>
•Semicolon at the end of the SELECT statement is optional (;)
•Table names cannot contain spaces
•Case insensitive
•Table name followed by an alias is supported. For example:
SELECT * FROM Customer c, Orders o, Invoices i
•Full expressions in every column are supported
•Operators in expressions: +, -, *, /, ^, MOD, DIV, SHL, SHR
•Embedded functions in expressions: TRUNC, ROUND, ABS, ARCTAN, COS, EXP, FRAC, INT, LN, PI, SIN, SQR, SQRT, POWER, UPPER, LOWER, COPY, POS, LENGTH
•Additional functions supported: LEFT, RIGHT, NOW; Pascal Functions: Format, FormatDateTime, FormatFloat
•The TRIM SQL function: TRIM([LEADING|TRAILING|BOTH] trimmed_char FROM column_reference)
•EXTRACT SQL function is supported
•SUBSTRING SQL function is supported
•NULL field values detected using IS NULL. Non-NULL values detected using IS NOT NULL
•Aggregate functions fully supported: SUM, MIN, MAX, AVG, COUNT(*)
•DISTINCT predicate is supported
•Standard characters for default identifiers are recognized: ['A'..'Z', 'a'..'z', '0'..'9', '_']. Other characters are not allowed: SPACEBAR, '(', ')', etc
•Extended identifiers are recognized with the notation [Field Name]. These Identifiers can contain any character between '[' and ']', except '[]' and the identifier must begin with 'A'..'Z' or 'a'..'z'
•Parameterized queries are supported. See the WHERE clause section for additional details
CAST function can be used to cast a column to a specific type: the only types supported are: CHAR(n), INTEGER, BOOLEAN, DATE, FLOAT and must be used only in SELECT clause. Ex.: "SELECT CAST(LastInvoiceDate + 28 AS DATE)"
The following is not syntactically valid because it has an embedded CAST function in another function:
/* Not valid! */
SELECT saledate, SUBSTRING(CAST(CAST(saledate AS DATE)
AS CHAR(10)) FROM 1 FOR 1) FROM orders
The CHAR casted type explicitly must have specified the length of the field:
SELECT CAST(country AS CHAR(10)) FROM customer
This example is not syntactically valid:
/* Not valid! */
SELECT CAST(lastinvoicedate AS CHAR) FROM customer;
CAST does not need to be used in an expression in order to cast a field or expression to a specific type. This is done automatically.
The rule is that casting is done to the type of higher precedence. The Casting rule is as follows:
STRING -> FLOAT -> INTEGER -> BOOLEAN
Example: a1 + a2; if a1 is of string type and a2 is of Float, Integer, or Boolean type, then a2 will be converted to string and a string concatenation will be in effect and the expression will return a string. If a1 is a Float and a2 is an Integer, then a sum will take place and the expression will return a Float.
Examples:
/* Extract all fields, and all records */
SELECT * FROM Customer
/* Extract selected fields, and all records */
SELECT First, Last, City, State FROM Customer
/* Extract selected fields from selected records
(only customers in California */
SELECT First, Last, City State FROM Customer WHERE (State = 'CA')
/* Extract all fields, where HireDate is not NULL */
SELECT * FROM Employees WHERE (HireDate IS NOT NULL)
/* Select statement using Parameters in the WHERE clause */
SELECT * FROM test
WHERE (Last = :Last) and (Age = :Age) and (HireDate = :HireDate)
/* Display FIRST and LAST name fields concatenated
under a column called Name */
SELECT first + " " + last AS Name FROM test WHERE last = 'Smith'
SELECT (AmountPaid / 1000) AS Thousands, (AmountPaid - ItemsTotal),
ShipDate As SD, SaleDate FROM Orders
SELECT (TRIMDC(Sales) / 100) AS SSales FROM Invoices
SELECT Customer.CustNo, Company, Addr1, LastInvoiceDate FROM Customer
SELECT FormatDateTime("dd/mmm/yyyy", ShipDate),
FormatDateTime('dd of mmm of yyyy', Saledate) FROM Orders
SELECT c.CustNo, Sum(AmountPaid), Avg(AmountPaid), Min(AmountPaid), Count(*)
FROM Customer c, Orders c WHERE (c.CustNo = Orders.CustNo) GROUP BY c.CustNo
SELECT Customer.*, Orders.AmountPaid FROM Customer c, Orders o
WHERE (c.CustNo = o.CustNo)
SELECT TRIM(TRAILING 'K' FROM City) AS TrimmedCity FROM customer
SELECT TRIM(LEADING ' ' FROM City) AS TrimmedCity FROM customer
SELECT TRIM(BOTH 'A' FROM City) AS TrimmedCity FROM customer
/* using CASE..WHEN..THEN */
SELECT City,
CASE
WHEN state = 'FL' THEN 'Florida'
WHEN state = 'CA' THEN 'California'
WHEN state = 'AL' THEN 'Alabama'
WHEN state = 'OR' THEN 'Oregon'
ELSE 'Unknown'
END
As StateName, zip
FROM test