http://w3.one.net/~jhoffman/sqltut.htm
http://msdn.microsoft.com/library/wcedoc/vbce/sql.htm
http://www.spnc.demon.co.uk/ora_sql/sqlmain.htm
There are several strategies for optimizing queries using EuSQL. First, try to focus you queries using a WHERE clause, especially in queries with a join, as this allows EuSQL to eliminate many records from its search. Another way to improve performance is to create indices on fields in your tables. You should try to create an index on a field that is likely to be part of a join, or that will likely be included in a WHERE clause. An index on a field will slow down INSERT, DELETE and UPDATE operations, but can dramatically improve SELECT operations. Since SELECT is usually done on records more often than operations that change the data, this is usually a very profitable trade off.
Concatenates two strings into one string.
SELECT THE.FIELD, 'FIELD: ' & THE.FIELD AS FIELD FROM MY_TABLE
THE.FIELD FIELD
--------- -----------
ID FIELD: ID
NAME FIELD: NAME
See Also: AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Used to create an alias for a field or table. When a table is aliased, the table may be joined with itself, so that a query based on relationships between table fields can be used.
ex:
SELECT FNAME AS NAME FROM EMPLOYEES WHERE NAME LIKE 'A*'; SELECT S.FNAME AS SUPERVISOR, E.FNAME AS EMPLOYEE FROM EMPLOYEES AS S INNER JOIN EMPLOYEES AS E ON S.ID = E.MANAGER
See Also: &, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the average of records selected.
ex:
SELECT AVG(SALARY) FROM EMPLOYEES;
See Also: &, AS, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns a count of the number of records selected.
ex:
SELECT COUNT(ID) FROM EMPLOYEES;
See Also: &, AS, AVG, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Create a database:
CREATE DATABASE database-name
CREATE TABLE table-name, field-1-name AS field-1-datatype, ...
CREATE [UNIQUE] INDEX index-name ON table-name ( field-name )
See Also: &, AS, AVG, COUNT, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Converts a quoted text date-time into a native EuSQL DATE_TIME piece of data. The format required is:
YYYY-MM-DD HH:MM:SS
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Delete records in one or more tables. Syntax is similar to to SELECT statement. Any table with a field mentioned between DELETE and FROM will have selected records deleted. Use the WHERE clause to specify records to delete.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Query will not return duplicate values.
ex:
SELECT DISTINCT LNAME FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Drop a table from the database:
DROP TABLE EMPLOYEE
DROP INDEX index-name
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Specifies table from which fields are selected.
ex:
SELECT NAME FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Zeroes out all time data in the DATE_TIME.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the day in the DATE_TIME as an integer.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the hour in the DATE_TIME as an integer.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the minute in the DATE_TIME as an integer.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the month in the DATE_TIME as an integer.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the second in the DATE_TIME as an integer.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Zeroes out all date data in the DATE_TIME.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the year in the DATE_TIME as an integer.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
BYIf other aggregate functions are used (SUM, COUNT, AVG, etc), any field not within a function must be specified by GROUP BY.
ex:
SELECT JOB_DESC, AVG(SALARY) FROM EMPLOYEES GROUP BY JOB_DESC;
This returns the average salary for each JOB_DESC:
JOB_DESC AVG OF SALARY SECRETARY 25000 MANAGER 60000 ...etc
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
IF can be used in a calculated field to do conditional calculations:
SELECT NUM, NUM + IF( NUM > 10, 0, 10 ) AS TEST FROM NUMBERS
NUM TEST
--- ----
1 10
10 10
11 0
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
IN is used in a WHERE clause to select from a list of values:
SELECT NAME, DEPARTMENT FROM EMPLOYEES WHERE DEPARTMENT IN(1,3,5)
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Insert records into a table. Values to be inserted can be specified either with a SELECT statement or explicity using VALUES( f1, f2, f3, ... ).
The order in which fields are specified must match the order in which the values are given. If the number fields and values (supplied explicitly or implicitly) do not match, an error will be returned.
INSERT INTO TABLE1 FIELD1, FIELD3, FIELD4, FIELD2 VALUES( VAL1, VAL3, VAL4, VAL2) FIELD1 <- VAL1 FIELD3 <- VAL3 FIELD4 <- VAL4 FIELD2 <- VAL2 INSERT INTO TABLE1 FIELD1, FIELD3, FIELD4, FIELD2 SELECT FIELD1, FIELD3, FIELD4, FIELD2 FROM TABLE2
The inserted records will be returned with a status field appended to each record, indicating either "OK" or "ERROR". The most common error is duplication of primary keys.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
LEFT/RIGHT/INNER JOIN ON Use relationship between two tables to 'join' into one table for querying purposes. You must specify either LEFT, RIGHT OR INNER. An INNER join requires that both fields be non-null, while a RIGHT or LEFT join can have either the 'left' or 'right' field, respectively, be null. Field names must have the table name preceding the field name (EMPLOYEES.NAME).
ex:
SELECT EMPLOYEE.ID, SCHEDULE.DATE FROM EMPLOYEE INNER JOIN SCHEDULE ON EMPLOYEE.ID = SCHEDULE.ID;
SELECT EMPLOYEE.ID, MGR.NAME FROM EMPLOYEE LEFT JOIN MGR ON EMPLOYEE.MGR_ID = MGR.ID;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the first chars characters of string.
SELECT THE.FIELD, LEFT(THE.FIELD, 2) AS LEFT_2 FROM MY_TABLE
THE.FIELD LEFT_2
--------- ------
JOHN JO
MARY MA
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the length of string.
SELECT THE.FIELD, LEN(THE.FIELD) AS LENGTH FROM MY_TABLE
THE.FIELD LENGTH
--------- ------
ABCDEFG 7
XYZ 3
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Used for wildcard string comparisons.
ex:
SELECT NAME FROM EMPLOYEES WHERE NAME LIKE "A*";
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Converts the string to lower case
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the largest value from the records selected
ex:
SELECT MAX(SALARY) FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the chars characters of string, starting with character number start.
SELECT THE.FIELD, MID(THE.FIELD, 2, 2) AS MID_2 FROM MY_TABLE
THE.FIELD MID_2
--------- -----
JOHN OH
MARY AR
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the smallest value from the records selected
ex:
SELECT MIN(SALARY) FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the current DATE_TIME.
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Set a sort order for the returned records. The default is to order in ascending order. Use DESC to order by descending order.
ex:
SELECT NAME, AGE FROM EMPLOYEES ORDER BY AGE DESC, NAME;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the last chars characters of string.
SELECT THE.FIELD, RIGHT(THE.FIELD, 2) AS RIGHT_2 FROM MY_TABLE
THE.FIELD RIGHT_2
--------- -------
JOHN HN
MARY RY
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Used to query a database to return values
ex:
SELECT NAME, AGE FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Converts an atom (number) into a string.
SELECT THE.FIELD, STR(THE.FIELD) AS STRING FROM MY_TABLE
THE.FIELD STRING
--------- ------
1 "1"
6.7 "6.7"
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, SUM, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Returns the sum of records selected.
ex:
SELECT SUM(SALARY) FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, UNION, UPDATE, UPPER, VAL, WHERE, [...]
Allows you to combine two SELECT statements into one query:
SELECT NAME, ADDRESS FROM EMPLOYEES UNION SELECT NAME, ADDRESS FROM DEPENDENTS
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UPDATE, UPPER, VAL, WHERE, [...]
Update records in a table. The format for the UPDATE command is:
UPDATE iTABLENAME SET iFIELD1 = iVAL1, [/iFIELDN = iVALN...] [WHERE iCONDITION]
UPDATE EMPLOYEES SET DEPARTMENT = 2 WHERE DEPARTMENT = 3
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPPER, VAL, WHERE, [...]
Converts the string to upper case
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Converts a string to an atom (number), or leaves an atom value alone.
SELECT THE.FIELD, VAL( THE.FIELD ) AS NUM FROM MY_TABLE
THE.FIELD NUM
--------- ---
"1" 1
"6.7" 6.7
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, WHERE, [...]
Set constraints on records selected. You can use =, >, <, <> (not equal) or LIKE for comparisons. String arguments must be enclosed by single quotes ' '. If you have multiple conditions (using AND and OR to tie them together, you should put the individual conditions in parentheses, since the precedence order may not be what you believe it to be.
ex:
SELECT NAME, AGE FROM EMPLOYEES WHERE (AGE > 40) AND (NAME LIKE 'A*');
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, [...]
Used to specify a parameter that can be set independently of parsing/compiling the query. Use set_parameter to set the value of a parameter. Parameters can be used as values for fields or for comparison purposes. This can be used for queries that are run often, but which need different values. Rather than building/parsing a new SQL command, the parameters in a compiled query can be modified.
ex:
SELECT NAME FROM EMPLOYEES WHERE NAME LIKE [NAME]; set_parameter( "name", "A*" )
See Also: &, AS, AVG, COUNT, CREATE, DATE_TIME, DELETE, DISTINCT, DROP, FROM, GET_DATE, GET_DAY, GET_HOUR, GET_MINUTE, GET_MONTH, GET_SECOND, GET_TIME, GET_YEAR, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, LOWER, MAX, MID, MIN, NOW, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, UPPER, VAL, WHERE