SQL
Table of Contents

Summary of supported SQL syntax


This is intended to give a basic overview of the SQL language and how EuSQL has implemented it. Here are some resources you can try if you'd like to learn more about SQL (there are many, many more):

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.

  • punctuation &   
  • keyword AS   
  • aggregate AVG()   
  • aggregate COUNT()   
  • command CREATE   
  • func DATE_TIME( 'YYYY-MM-DD HH:MM:SS' )   
  • command DELETE   
  • keyword DISTINCT   
  • command DROP   
  • keyword FROM   
  • func GET_DATE( date_time )   
  • func GET_DAY( date_time )   
  • func GET_HOUR( date_time )   
  • func GET_MINUTE( date_time )   
  • func GET_MONTH( date_time )   
  • func GET_SECOND( date_time )   
  • func GET_TIME( date_time )   
  • func GET_YEAR( date_time )   
  • aggregate GROUP   
  • keyword IF( condition, true value, false value )   
  • keyword IN   
  • command INSERT   
  • keyword JOIN:   
  • keyword LEFT( [string], [chars] )   
  • keyword LEN( [string] )   
  • keyword LIKE   
  • keyword LOWER( [string] )   
  • aggregate MAX()   
  • keyword MID( [string], [start], [chars] )   
  • aggregate MIN()   
  • func NOW()   
  • keyword ORDER   
  • keyword RIGHT ( [string], [chars] )   
  • command SELECT   
  • keyword STR( [number] )   
  • aggregate SUM()   
  • keyword UNION   
  • command UPDATE   
  • keyword UPPER( [string] )   
  • keyword VAL( [string] )   
  • keyword WHERE   
  • punctuation [...]   

    SQL
    Table of Contents

    [punctuation]
    &

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    AS

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [aggregate]
    AVG
    ()

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [aggregate]
    COUNT
    ()

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [command]
    CREATE

    Category: SQL

    Create a database:

     CREATE DATABASE database-name
    
    Create a table:
     CREATE TABLE table-name, field-1-name AS field-1-datatype, ...
    
    Create an index (note that unique indices are not supported at this time):
     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, [...]


    SQL
    Table of Contents

    [func]
    DATE_TIME
    ( 'YYYY-MM-DD HH:MM:SS' )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [command]
    DELETE

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    DISTINCT

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [command]
    DROP

    Category: SQL

    Drop a table from the database:

     DROP TABLE EMPLOYEE
    
    Drop an index from the database:
     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, [...]


    SQL
    Table of Contents

    [keyword]
    FROM

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_DATE
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_DAY
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_HOUR
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_MINUTE
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_MONTH
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_SECOND
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_TIME
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    GET_YEAR
    ( date_time )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [aggregate]
    GROUP

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    IF
    ( condition, true value, false value )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    IN

    Category: SQL

    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)
    
    This would select the names of all employees in departments 1, 3, and 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, [...]


    SQL
    Table of Contents

    [command]
    INSERT

    Category: SQL

    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
    
    Inserts resulting records of SELECT into TABLE1.

    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, [...]


    SQL
    Table of Contents

    [keyword]
    JOIN:

    Category: SQL

    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;
    
    SCHEDULE.ID is a foreign key to EMPLOYEE.ID, and both fields must be non-null
    SELECT EMPLOYEE.ID, MGR.NAME FROM EMPLOYEE LEFT JOIN MGR ON EMPLOYEE.MGR_ID = MGR.ID;
    
    EMPLOYEE.MGR_ID is the foreign key to MGR.ID. MGR_ID can be null (not all employees have managers)

    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, [...]


    SQL
    Table of Contents

    [keyword]
    LEFT
    ( [string], [chars] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    LEN
    ( [string] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    LIKE

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    LOWER
    ( [string] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [aggregate]
    MAX
    ()

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    MID
    ( [string], [start], [chars] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [aggregate]
    MIN
    ()

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [func]
    NOW
    ()

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    ORDER

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    RIGHT
    ( [string], [chars] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [command]
    SELECT

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    STR
    ( [number] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [aggregate]
    SUM
    ()

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    UNION

    Category: SQL

    Allows you to combine two SELECT statements into one query:

       SELECT NAME, ADDRESS FROM EMPLOYEES
       UNION
       SELECT NAME, ADDRESS FROM DEPENDENTS
    
    The resulting dataset would contain two fields (name and address) with the combined data from each SELECT statement.

    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, [...]


    SQL
    Table of Contents

    [command]
    UPDATE

    Category: SQL

    Update records in a table. The format for the UPDATE command is:

       UPDATE iTABLENAME SET iFIELD1 = iVAL1, [/iFIELDN = iVALN...] [WHERE iCONDITION]
    
    For example, if you wanted to change all employees in department 3 to department 2:
      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, [...]


    SQL
    Table of Contents

    [keyword]
    UPPER
    ( [string] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    VAL
    ( [string] )

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [keyword]
    WHERE

    Category: SQL

    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, [...]


    SQL
    Table of Contents

    [punctuation]
    [...]

    Category: SQL

    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