Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

Lexical Conventions

2 Basic Elements of Oracle SQL

Literals

Datatypes

Format Models

Nulls

Pseudocolumns

Comments

Database Objects

Schema Object Names and Qualifiers

Referring to Schema Objects and Parts

3 Operators

Unary and Binary Operators

Precedence

Arithmetic Operators

Concatenation Operator

Comparison Operators

Logical Operators

Set Operators

Other Built-In Operators

User-Defined Operators

4 Functions

SQL Functions

ABS

ACOS

ADD_MONTHS

ASCII

ASIN

ATAN

ATAN2

AVG

BFILENAME

CEIL

CHARTOROWID

CHR

CONCAT

CONVERT

CORR

COS

COSH

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

DENSE_RANK

DEREF

DUMP

EMPTY_[B | C]LOB

EXP

FIRST_VALUE

FLOOR

GREATEST

GROUPING

HEXTORAW

INITCAP

INSTR

INSTRB

LAG

LAST_DAY

LAST_VALUE

LEAD

LEAST

LENGTH

LENGTHB

LN

LOG

LOWER

LPAD

LTRIM

MAKE_REF

MAX

MIN

MOD

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

NTILE

NUMTODSINTERVAL

NUMTOYMINTERVAL

NVL

NVL2

PERCENT_RANK

POWER

RANK

RATIO_TO_REPORT

RAWTOHEX

REF

REFTOHEX

REGR_ (linear regression) functions

REPLACE

ROUND (Number Function)

ROUND (Date Function)

ROW_NUMBER

ROWIDTOCHAR

RPAD

RTRIM

SIGN

SIN

SINH

SOUNDEX

SQRT

STDDEV

STDDEV_POP

STDDEV_SAMP

SUBSTR

SUBSTRB

SUM

SYS_CONTEXT

SYS_GUID

SYSDATE

TAN

TANH

TO_CHAR (date conversion)

TO_CHAR (number conversion)

TO_DATE

TO_LOB

TO_MULTI_BYTE

TO_NUMBER

TO_SINGLE_BYTE

TRANSLATE

TRANSLATE ... USING

TRIM

TRUNC (Number Function)

TRUNC (Date Function)

UID

UPPER

USER

USERENV

VALUE

VAR_POP

VAR_SAMP

VARIANCE

VSIZE

ROUND and TRUNC Date Functions

User-Defined Functions

5 Expressions, Conditions, and Queries

Expressions

Conditions

Queries and Subqueries

6 About SQL Statements

Summary of SQL Statements

Finding the Right SQL Statement

7 SQL Statements

ALTER CLUSTER

ALTER DATABASE

ALTER DIMENSION

ALTER FUNCTION

ALTER INDEX

ALTER JAVA

ALTER MATERIALIZED VIEW / SNAPSHOT

ALTER MATERIALIZED VIEW LOG / SNAPSHOT LOG

ALTER OUTLINE

ALTER PACKAGE

ALTER PROCEDURE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLE

ALTER ROLLBACK SEGMENT

ALTER SEQUENCE

ALTER SESSION

ALTER SNAPSHOT

ALTER SNAPSHOT LOG

ALTER SYSTEM

ALTER TABLE

ALTER TABLESPACE

ALTER TRIGGER

ALTER TYPE

ALTER USER

ALTER VIEW

ANALYZE

ASSOCIATE STATISTICS

AUDIT sql_statements

AUDIT schema_objects

CALL

COMMENT

COMMIT

constraint_clause

CREATE CLUSTER

CREATE CONTEXT

CREATE CONTROLFILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE FUNCTION

CREATE INDEX

CREATE INDEXTYPE

CREATE JAVA

CREATE LIBRARY

CREATE MATERIALIZED VIEW / SNAPSHOT

CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG

CREATE OPERATOR

CREATE OUTLINE

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

CREATE PROFILE

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SCHEMA

CREATE SEQUENCE

CREATE SNAPSHOT

CREATE SNAPSHOT LOG

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TEMPORARY TABLESPACE

CREATE TRIGGER

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 / SNAPSHOT

DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG

DROP OPERATOR

DROP OUTLINE

DROP PACKAGE

DROP PROCEDURE

DROP PROFILE

DROP ROLE

DROP ROLLBACK SEGMENT

DROP SEQUENCE

DROP SNAPSHOT

DROP SNAPSHOT LOG

DROP SYNONYM

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE

DROP TYPE BODY

DROP USER

DROP VIEW

EXPLAIN PLAN

filespec

GRANT system_privileges_and_roles

GRANT object_privileges

INSERT

LOCK TABLE

NOAUDIT sql_statements

NOAUDIT schema_objects

RENAME

REVOKE system_privileges_and_roles

REVOKE schema_object_privileges

ROLLBACK

SAVEPOINT

SELECT and Subqueries

SET CONSTRAINT(S)

SET ROLE

SET TRANSACTION

storage_clause

TRUNCATE

UPDATE

A Syntax Diagrams

B Oracle and Standard SQL

Conformance with Standard SQL

Oracle Extensions to Standard SQL

C Oracle Reserved Words