Contents

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

1 Using SQL

1.1 SQL Overview

1.1.2 Oracle SQL and SQL-92

1.1.2.1 Running SQL-92 on Oracle Lite

1.2 Oracle Lite SQL and Oracle SQL Comparison

1.2.2 Operators

1.2.3 Functions

1.2.5 Miscellaneous Data Definition Language (DDL)

1.2.6 Datatypes

1.2.7 Indicator Variables

1.2.8 Data Precision During Arithmetic Operations

1.2.9 Data Dictionaries

1.2.10 Tables Not Installed with Oracle Database Lite

1.2.11 Messages

1.2.12 Sequences

1.2.14 SQL Functions

1.2.15 Locking and Transactions

1.3 Oracle Database Lite SQL Conventions

1.3.1 SQL Statement Syntax

1.3.1.1 Capital Letters

1.3.1.2 Lowercase

1.3.1.3 Bracket Delimited

1.3.1.5 Vertical Bars

1.3.1.6 Ellipsis

1.3.1.7 Underline

1.3.1.8 Block Letters

1.3.1.9 Initial Colon

1.3.2 SQL Tables

1.3.3 SQL Object Names

1.3.4 SQL Operator Precedence

1.3.5 SQL Sessions

1.3.6 SQL Transactions

1.3.7 Issuing SQL Statements From a Program

1.3.8 SQL and ODBC

1.4 ODBC SQL Syntax Conventions

1.5 Oracle Database Lite Database Object Naming Conventions

1.6.1 Number Format Elements

1.6.2 Date Format Elements

1.7 Specifying SQL Conditions

1.7.1 Simple Comparison Conditions

1.7.2 Group Comparison Conditions

1.7.2.1 A Row_Value_Constructor in a Subquery Comparison

1.7.2.2 Subquery in Place of a Column

1.7.3 Membership Conditions

1.7.4 Range Conditions

1.7.5 NULL Conditions

1.7.6 EXISTS Conditions

1.7.7 LIKE Conditions

1.7.8 Compound Conditions

1.8 Specifying Expressions

1.8.1 Form I, Simple Expression

1.8.2 Form II, Function Expression

1.8.3 Form III, Java Function Expression

1.8.4 Form IV, Compound Expression

1.8.5 Form V, DECODE Expression

1.8.6 Form VI, Expression List

1.8.7 Form VII, Variable Expression

1.8.8 Form VIII, CAST Expression

1.9 Oracle Database Lite SQL Datatypes and Literals

1.9.1 Character String Comparison Rules

1.9.1.1 Blank-Padded Comparison Semantics

1.9.1.2 Non-Padded Comparison Semantics

1.10 Comments Within SQL Statements

1.11 Tuning SQL Statement Execution Performance With the EXPLAIN PLAN

1.11.1 The PLAN Table

1.11.2 EXPLAIN PLAN Examples

1.11.2.1 Example for Select Distinct and Group By

1.11.2.2 Example for Select Statement with Union

1.11.2.3 Example for Select Statement With Multiple Qualifiers

2 SQL Operators

2.1 SQL Operators Overview

2.1.1 Unary Operators

2.1.2 Binary Operators

2.1.3 Set Operators

2.1.4 Other Operators

2.2 Arithmetic Operators

2.3 Character Operators

2.3.1 Concatenating Character Strings

2.4 Comparison Operators

2.5 Logical Operators

2.6 Set Operators

2.7 Other Operators

3 SQL Functions

3.1 SQL Function Types

3.2 SQL Functions Overview

3.2.1 Number Functions

3.2.2 Character Functions

3.2.3 Character Functions Returning Number Values

3.2.4 Date Functions

3.2.5 Conversion Functions

3.3 SQL Functions Alphabetical Listing

3.3.1 ADD_MONTHS

3.3.12 CURRENT_DATE

3.3.13 CURRENT_TIME

3.3.14 CURRENT_TIMESTAMP

3.3.16 DATABASE

3.3.18 DAYOFMONTH

3.3.19 DAYOFWEEK

3.3.20 DAYOFYEAR

3.3.24 GREATEST

3.3.29 INTERVAL

3.3.30 LAST_DAY

3.3.43 MONTHNAME

3.3.44 MONTHS_BETWEEN

3.3.45 NEXT_DAY

3.3.48 POSITION

3.3.51 ROUND - Date Function

3.3.52 ROUND - Number Function

3.3.61 TIMESTAMPADD

3.3.62 TIMESTAMPDIFF

3.3.65 TO_NUMBER

3.3.66 TRANSLATE

3.3.71 VARIANCE

4 SQL Commands

4.1 SQL Command Types

4.2 SQL Commands Overview

4.2.1 Data Definition Language (DDL) Commands

4.2.2 Data Manipulation Language (DML) Commands

4.2.3 Transaction Control Commands

4.2.5 Pseudocolumns

4.2.6 BNF Notation Conventions

4.3 SQL Commands Alphabetical Listing

4.3.1 ALTER SEQUENCE

4.3.2 ALTER SESSION

4.3.3 ALTER TABLE

4.3.4 ALTER TRIGGER

4.3.5 ALTER USER

4.3.6 ALTER VIEW

4.3.8 CONSTRAINT clause

4.3.9 CREATE DATABASE

4.3.10 CREATE FUNCTION

4.3.11 CREATE GLOBAL TEMPORARY TABLE

4.3.12 CREATE INDEX

4.3.13 CREATE JAVA

4.3.14 CREATE PROCEDURE

4.3.15 CREATE SCHEMA

4.3.16 CREATE SEQUENCE

4.3.17 CREATE SYNONYM

4.3.18 CREATE TABLE

4.3.19 CREATE TRIGGER

4.3.20 CREATE USER

4.3.21 CREATE VIEW

4.3.22 CURRVAL and NEXTVAL pseudocolumns

4.3.24 DROP clause

4.3.25 DROP FUNCTION

4.3.26 DROP INDEX

4.3.27 DROP JAVA

4.3.28 DROP PROCEDURE

4.3.29 DROP SCHEMA

4.3.30 DROP SEQUENCE

4.3.31 DROP SYNONYM

4.3.32 DROP TABLE

4.3.33 DROP TRIGGER

4.3.34 DROP USER

4.3.35 DROP VIEW

4.3.36 EXPLAIN PLAN

4.3.39 LEVEL pseudocolumn

4.3.40 OL__ROW_STATUS pseudocolumn

4.3.42 ROLLBACK

4.3.43 ROWID pseudocolumn

4.3.44 ROWNUM pseudocolumn

4.3.45 SAVEPOINT

4.3.46.1 SELECT Command Arguments

4.3.46.2 The SUBQUERY Expression

4.3.46.3 The FOR_UPDATE Clause

4.3.46.4 The ORDER_BY Clause

4.3.46.5 The TABLE_REFERENCE Expression

4.3.46.6 The ODBC_JOIN_TABLE Expression

4.3.46.7 The JOINED_TABLE Expression

4.3.46.8 The HINT Expression

4.3.46.9 The LIMIT and OFFSET Clauses

4.3.46.10 Examples For the SELECT Command

4.3.47 SET TRANSACTION

4.3.48 TRUNCATE TABLE

A Oracle Database Lite Keywords and Reserved Words

A.1 Oracle Lite Keywords

A.2 Oracle Lite Reserved Words

B SQL Limitations For Oracle Database Lite

C Oracle Database Lite Datatypes

C.9 DOUBLE PRECISION

C.14 LONG VARBINARY

C.15 LONG VARCHAR

D Oracle Database Lite Literals

D.1 CHAR, VARCHAR

D.3 DECIMAL, NUMERIC, NUMBER

D.4 REAL, FLOAT, DOUBLE PRECISION

D.5 SMALLINT, INTEGER, BIGINT, TINYINT

E Index Creation Options

E.1 Uniqueness Constraint in Oracle Lite

E.1.1 The Address Table Example

E.1.2 Using Uniqueness Constraints

E.1.3 Specifying the Number of Columns in an Index

E.1.3.1 The POLITE.INI File

E.1.3.2 The CREATE UNIQUE INDEX Statement

E.1.3.3 The CREATE TABLE and ALTER TABLE Statements

E.1.3.4 Usage Notes

F Syntax Diagram Conventions

F.1 Introduction

F.2 Required Keywords and Parameters

F.3 Optional Keywords and Parameters

F.4 Syntax Loops

F.5 Multipart Diagrams

F.6 Database Objects

F.7 BNF Notation

Glossary

Index