Chapter 4: SQL Language Elements
4.1 SQL Features: Lite and Professional Editions
The Lite and Professional editions of the Dharma DataLink SDK support different levels of SQL functionality. Table 4–1 details which SQL features are supported by the Lite and Professional editions.
| SQL Support | Lite | Pro |
|---|---|---|
| SELECT/INSERT/UPDATE/DELETE | ✓ | ✓ |
| INSERT…SELECT | ✓ | ✓ |
| CREATE/DROP TABLE | ✓ | ✓ |
| CREATE/DROP INDEX | ✓ | ✓ |
| CREATE/DROP SYNONYM | ✓ | ✓ |
| GROUP BY clause | ✓ | ✓ |
| AVG, COUNT, MAX, MIN, SUM | ✓ | ✓ |
| 20 SQL data types (including LONG, DATE) | ✓ | ✓ |
| Interactive SQL utility | ✓ | ✓ |
| Transaction support | ✓ | ✓ |
| User authentication | ✓ | ✓ |
| Set operators (UNION/INTERSECT/MINUS) | ✓ | |
| The UNION, INTERSECT, and MINUS set operators manipulate how SQL returns result sets from multiple query expressions (Section 4.4). | ||
| Library of over 100 scalar functions (Section 4.9.2) | ✓ | |
| HAVING clause: The HAVING clause in query expressions (Section 4.4) specifies a search condition for the row groupings specified in the GROUP BY clause. | ✓ | |
| Table/column privileges: GRANT (Section 5.11) and REVOKE (Section 5.14) statements. | ✓ | |
| Outer Joins (Section 4.4.2) | ✓ | |
| CASE expressions (Sections 4.6.4 and 4.9.2.8) | ✓ | |
| Derived tables: Derived tables are specified through query expressions in the FROM clause of another query expression (Section 4.4). | ✓ | |
| Subqueries: Subqueries are query expressions used within a search condition or as an expression. Subqueries can occur in basic predicates (Section 4.5.3), quantified predicates (Section 4.5.4), IN predicates (Section 4.5.10), and expressions (Section 4.6) | ✓ | |
| View support: CREATE (Section 5.5) and DROP VIEW (Section 5.10) statements. | ✓ | |
| SET SCHEMA statement: (Section 5.16) changes default qualifier for database objects | ✓ |
The following SQL features are not supported in either the Lite or Professional editions:
- Multiple character sets and collation sequences (page 4-6)
- ALTER TABLE statement (referred to in the discussion of data types (Section 4.3) and in the discussion of DROP TABLE (Section 5.9))
- CREATE TABLE statement (Section 5.4): TABLESPACE, PCTFREE, or STORAGE_MANAGER clauses
- CREATE INDEX statement (Section 5.2): PCTFREE clause