Contents

Title and Copyright Information

Send Us Your Comments

Preface

What's New in SQL Reference?

1 Introduction to Oracle SQL

2 Basic Elements of Oracle SQL

Datatypes

Literals

Format Models

Nulls

Pseudocolumns

Comments

Database Objects

Schema Object Names and Qualifiers

Syntax for Schema Objects and Parts in SQL Statements

3 Operators

About SQL Operators

Arithmetic Operators

Concatenation Operator

Set Operators

User-Defined Operators

4 Expressions

About SQL Expressions

Simple Expressions

Compound Expressions

CASE Expressions

CURSOR Expressions

Datetime Expressions

Function Expressions

INTERVAL Expressions

Object Access Expressions

Scalar Subquery Expressions

Type Constructor Expressions

Variable Expressions

Expression Lists

5 Conditions

About SQL Conditions

Comparison Conditions

Logical Conditions

Membership Conditions

Range Conditions

Null Conditions

EQUALS_PATH

EXISTS Conditions

LIKE Conditions

IS OF type Conditions

UNDER_PATH

Compound Conditions

6 Functions

SQL Functions

ABS

ACOS

ADD_MONTHS

ASCII

ASCIISTR

ASIN

ATAN

ATAN2

AVG

BFILENAME

BIN_TO_NUM

BITAND

CAST

CEIL

CHARTOROWID

CHR

COALESCE

COMPOSE

CONCAT

CONVERT

CORR

COS

COSH

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

DECODE

DECOMPOSE

DENSE_RANK

DEPTH

DEREF

DUMP

EMPTY_BLOB, EMPTY_CLOB

EXISTSNODE

EXP

EXTRACT (datetime)

EXTRACT (XML)

EXTRACTVALUE

FIRST

FIRST_VALUE

FLOOR

FROM_TZ

GREATEST

GROUP_ID

GROUPING

GROUPING_ID

HEXTORAW

INITCAP

INSTR

LAG

LAST

LAST_DAY

LAST_VALUE

LEAD

LEAST

LENGTH

LN

LOCALTIMESTAMP

LOG

LOWER

LPAD

LTRIM

MAKE_REF

MAX

MIN

MOD

MONTHS_BETWEEN

NCHR

NEW_TIME

NEXT_DAY

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

NTILE

NULLIF

NUMTODSINTERVAL

NUMTOYMINTERVAL

NVL

NVL2

PATH

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

POWER

RANK

RATIO_TO_REPORT

RAWTOHEX

RAWTONHEX

REF

REFTOHEX

REGR_ (Linear Regression) Functions

REPLACE

ROUND (number)

ROUND (date)

ROW_NUMBER

ROWIDTOCHAR

ROWIDTONCHAR

RPAD

RTRIM

SESSIONTIMEZONE

SIGN

SIN

SINH

SOUNDEX

SQRT

STDDEV

STDDEV_POP

STDDEV_SAMP

SUBSTR

SUM

SYS_CONNECT_BY_PATH

SYS_CONTEXT

SYS_DBURIGEN

SYS_EXTRACT_UTC

SYS_GUID

SYS_TYPEID

SYS_XMLAGG

SYS_XMLGEN

SYSDATE

SYSTIMESTAMP

TAN

TANH

TO_CHAR (character)

TO_CHAR (datetime)

TO_CHAR (number)

TO_CLOB

TO_DATE

TO_DSINTERVAL

TO_LOB

TO_MULTI_BYTE

TO_NCHAR (character)

TO_NCHAR (datetime)

TO_NCHAR (number)

TO_NCLOB

TO_NUMBER

TO_SINGLE_BYTE

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TRANSLATE

TRANSLATE ... USING

TREAT

TRIM

TRUNC (number)

TRUNC (date)

TZ_OFFSET

UID

UNISTR

UPDATEXML

UPPER

USER

USERENV

VALUE

VAR_POP

VAR_SAMP

VARIANCE

VSIZE

WIDTH_BUCKET

XMLAGG

XMLCOLATTVAL

XMLCONCAT

XMLELEMENT

XMLFOREST

XMLSEQUENCE

XMLTRANSFORM

ROUND and TRUNC Date Functions

User-Defined Functions

7 Common SQL DDL Clauses

allocate_extent_clause

constraints

deallocate_unused_clause

file_specification

logging_clause

parallel_clause

physical_attributes_clause

storage_clause

8 SQL Queries and Subqueries

About Queries and Subqueries

Creating Simple Queries

Hierarchical Queries

The UNION [ALL], INTERSECT, MINUS Operators

Sorting Query Results

Joins

Using Subqueries

Unnesting of Nested Subqueries

Selecting from the DUAL Table

Distributed Queries

9 SQL Statements: ALTER CLUSTER to ALTER SEQUENCE

Types of SQL Statements

Organization of SQL Statements

ALTER CLUSTER

ALTER DATABASE

ALTER DIMENSION

ALTER FUNCTION

ALTER INDEX

ALTER INDEXTYPE

ALTER JAVA

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER OPERATOR

ALTER OUTLINE

ALTER PACKAGE

ALTER PROCEDURE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLE

ALTER ROLLBACK SEGMENT

ALTER SEQUENCE

10 SQL Statements: ALTER SESSION to ALTER SYSTEM

ALTER SESSION

ALTER SYSTEM

11 SQL Statements: ALTER TABLE to ALTER TABLESPACE

ALTER TABLE

ALTER TABLESPACE

12 SQL Statements: ALTER TRIGGER to COMMIT

ALTER TRIGGER

ALTER TYPE

ALTER USER

ALTER VIEW

ANALYZE

ASSOCIATE STATISTICS

AUDIT

CALL

COMMENT

COMMIT

13 SQL Statements: CREATE CLUSTER to CREATE JAVA

CREATE CLUSTER

CREATE CONTEXT

CREATE CONTROLFILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE FUNCTION

CREATE INDEX

CREATE INDEXTYPE

CREATE JAVA

14 SQL Statements: CREATE LIBRARY to CREATE SPFILE

CREATE LIBRARY

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

CREATE OPERATOR

CREATE OUTLINE

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PFILE

CREATE PROCEDURE

CREATE PROFILE

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SCHEMA

CREATE SEQUENCE

CREATE SPFILE

15 SQL Statements: CREATE SYNONYM to CREATE TRIGGER

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TEMPORARY TABLESPACE

CREATE TRIGGER

16 SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT

CREATE TYPE

CREATE TYPE BODY

CREATE USER

CREATE VIEW

DELETE

DISASSOCIATE STATISTICS

DROP CLUSTER

DROP CONTEXT

DROP DATABASE LINK

DROP DIMENSION

DROP DIRECTORY

DROP FUNCTION

DROP INDEX

DROP INDEXTYPE

DROP JAVA

DROP LIBRARY

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW LOG

DROP OPERATOR

DROP OUTLINE

DROP PACKAGE

DROP PROCEDURE

DROP PROFILE

DROP ROLE

DROP ROLLBACK SEGMENT

17 SQL Statements: DROP SEQUENCE to ROLLBACK

DROP SEQUENCE

DROP SYNONYM

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE

DROP TYPE BODY

DROP USER

DROP VIEW

EXPLAIN PLAN

GRANT

INSERT

LOCK TABLE

MERGE

NOAUDIT

RENAME

REVOKE

ROLLBACK

18 SQL Statements: SAVEPOINT to UPDATE

SAVEPOINT

SELECT

SET CONSTRAINT[S]

SET ROLE

SET TRANSACTION

TRUNCATE

UPDATE

A How to Read Syntax Diagrams

B Oracle and Standard SQL

C Oracle Reserved Words

D Examples

Using Extensible Indexing

Using XML in SQL Statements

Index