CREATE INDEX

CREATE INDEX empidx ON emp (emp_id, emp_name)

CREATE UNIQUE INDEX empidx ON emp (emp_id, emp_name)

To designate a key as a primary key, FairCom supports the UNIQUE option.

CREATE VIEW

CREATE VIEW vw_sal (v_col1, v_col2) AS SELECT emp_id, name FROM sal

The column list is optional.

DROP INDEX

DROP INDEX emp.empuniq

DROP VIEW

DROP VIEW vw_sal

[CASCADE | RESTRICT] is not supported.

SELECT

SELECT COUNT(emp_id), dept FROM mgrs GROUP BY dept HAVdept > 15

In addition to supporting an order by on a column-list, as specified in the ODBC Programmer’s Reference, FairCom has extended the syntax to support an order by on an expression-list or on any expression in a group by expression-list. For example: SELECT * FROM emp ORDER BY a+b,c+d,e This causes the result table to be ordered by three expressions: a+b, c+d, and e. If the expression is a positive integer literal, then that literal will be interpreted as the number of the column in the result set and ordering will be done on that column. No ordering is allowed on set functions or an expression that contains a set function.

subqueries

The following types of subqueries are supported: comparison, exists, quantified, in, and correlated. Order by clauses are not allowed in a subclause.

approximate-numeric-literal

SELECT * FROM results WHERE quotient = -4.5E-2

between- predicate

SELECT c1 FROM emp
WHERE emp_id
BETWEEN 10000 AND 20000

The syntax expr1 BETWEEN expr2 AND expr3returns TRUE if expr1 >= expr2 and expr1 <= expr3. expr2 and expr3 may be dynamic parameters (e.g., SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?).

correlation-name

SELECT * FROM emp t1, addr t2 WHERE t1.emp_id = t2.emp_id

FairCom supports both table and column correlation names.

exact-numeric-literal

INSERT INTO cars (car_no, price) VALUES (49042, 49999.99)

SELECT * FROM numtbl WHERE c1 = -208.6543189

in-predicate

SELECT * from colors WHERE color IN (‘red’, ‘blue’, ‘green’)

set-function

SELECT COUNT(a+b) FROM q

SELECT MIN(salary) FROM emp

MIN(expr), MAX(expr), AVG(expr), SUM(expr), COUNT(*), and COUNT(expr) are supported. COUNT(expr) counts all non-NULL values for an expression across a predicate. The following example counts all the rows in q where a+b does not equal NULL:

SELECT COUNT (a+b) FROM q

inner join syn

SELECT *
FROM mytableA, mytableB WHERE myColA = myColB

SELECT *
FROM mytableA, mytableB INNER JOIN myColA = myColB

These two statements are considered identical.