Preface

This reference contains a complete description of the Structured Query Language (SQL) used to manage information in an Oracle database.

Oracle SQL is a superset of the American National Standards Institute (ANSI) and the International Standards Organization (ISO) SQL92 standard at entry level conformance.

For information on PL/SQL, Oracle's procedural language extension to SQL, see PL/SQL User's Guide and Reference.

Detailed descriptions of Oracle embedded SQL can be found in the Pro*C/C++ Precompiler Programmer's Guide, SQL*Module for Ada Programmer's Guide, and the Pro*COBOL Precompiler Programmer's Guide.

Features and Functionality

Oracle8 SQL Reference contains information that describes the features and functionality of the Oracle8 and the Oracle8 Enterprise Edition products. Oracle8 and Oracle8 Enterprise Edition have the same basic features. However, several advanced features are available only with the Enterprise Edition, and some of these are optional. For example, to use the CREATE TYPE command, you must have the Enterprise Edition and the Objects Option.

For information about the differences between Oracle8 and the Oracle8 Enterprise Edition and the features and options that are available to you, see Getting to Know Oracle8 and the Oracle8 Enterprise Edition.

Audience

This reference is intended for all users of Oracle SQL.

How this Reference Is Organized

This reference is divided into the following parts:

Chapter 1: Introduction

This chapter defines SQL and describes its history as well as the advantages of using it to access relational databases.

Chapter 2: Elements of Oracle8 SQL

This chapter describes the basic building blocks of an Oracle database and the elements of Oracle SQL.

Chapter 3: Operators, Functions, Expressions, Conditions

This chapter describes how to use SQL operators and functions to combine data into expressions and conditions.

Chapter 4: Commands

This chapter lists and describes all of the SQL commands in alphabetical order.

Appendix A: Syntax Diagrams

This appendix describes how to read the syntax diagrams that appear in this reference.

Appendix B: Oracle and Standard SQL

This appendix describes Oracle compliance with ANSI and ISO standards and lists Oracle extensions beyond the standards.

Appendix C: Oracle Reserved Words and Keywords

This appendix lists Oracle reserved words and keywords.

Conventions Used in this Reference

This section explains the conventions used in this book including:

Text

The text in this reference adheres to the following conventions:

UPPERCASE

Uppercase text calls attention to SQL commands, keywords, filenames, and initialization parameters.

italics

Italicized text calls attention to definitions of terms and parameters of SQL commands.

Syntax Diagrams and Notation

Syntax Diagrams

This reference uses syntax diagrams to show SQL commands in Chapter 4, "Commands", and to show other elements of the SQL language in Chapter 2, "Elements of Oracle8 SQL", and Chapter 3, "Operators, Functions, Expressions, Conditions". These syntax diagrams use lines and arrows to show syntactic structure, as shown here:

If you are not familiar with this type of syntax diagram, refer to Appendix A, "Syntax Diagrams", for a description of how to read them. This section describes the components of syntax diagrams and gives examples of how to write SQL statements. Syntax diagrams are made up of these items:

Keywords

Keywords have special meanings in the SQL language. In the syntax diagrams, keywords appear in UPPERCASE. You must use keywords in your SQL statements exactly as they appear in the syntax diagram, except that they can be either uppercase or lowercase. For example, you must use the CREATE keyword to begin your CREATE TABLE statements just as it appears in the CREATE TABLE syntax diagram.

Parameters

Parameters act as placeholders in syntax diagrams. They appear in lowercase. Parameters are usually names of database objects, Oracle datatype names, or expressions. When you see a parameter in a syntax diagram, substitute an object or expression of the appropriate type in your SQL statement. For example, to write a CREATE TABLE statement, use the name of the table you want to create, such as EMP, in place of the table parameter in the syntax diagram. (Note that parameter names appear in italics in the text.)

This lists shows parameters that appear in the syntax diagrams and provides examples of the values you might substitute for them in your statements:

Code Examples

This reference contains many examples of SQL statements. These examples show you how to use elements of SQL. The following example shows a CREATE TABLE statement:

CREATE TABLE accounts 
    ( accno     NUMBER, 
      owner     VARCHAR2(10), 
      balance   NUMBER(7,2) );

Note that examples appear in a different font than the text.

Examples follow these case conventions:

Keywords, such as CREATE and NUMBER, appear in uppercase.
Names of database objects and their parts, such as ACCOUNTS and ACCNO, appear in lowercase, although they appear in uppercase in the text.

SQL is not case sensitive (except for quoted identifiers), so you need not follow these conventions when writing your own SQL statements, although your statements may be easier for you to read if you do.

Some Oracle tools require you to terminate SQL statements with a special character. For example, the code examples in this reference were issued through SQL*Plus, and therefore are terminated with a semicolon (;). If you issue these example statements to Oracle, you must terminate them with the special character expected by the Oracle tool you are using.

Example Data

Many of the examples in this reference operate on sample tables. The definitions of some of these tables appear in a SQL script available on your distribution medium. On most operating systems the name of this script is UTLSAMPL.SQL, although its exact name and location depend on your operating system. This script creates sample users and creates these sample tables in the schema of the user SCOTT:

CREATE TABLE dept 
    (deptno    NUMBER(2)      CONSTRAINT pk_dept PRIMARY KEY, 
     dname     VARCHAR2(14), 
     loc       VARCHAR2(13) ); 
CREATE TABLE emp 
    (empno     NUMBER(4)      CONSTRAINT pk_emp PRIMARY KEY, 
     ename     VARCHAR2(10), 
     job       VARCHAR2(9), 
     mgr       NUMBER(4), 
     hiredate  DATE, 
     sal       NUMBER(7,2), 
     comm      NUMBER(7,2), 
     deptno    NUMBER(2)    CONSTRAINT fk_deptno REFERENCES dept ); 
CREATE TABLE bonus 
    (ename     VARCHAR2(10), 
     job       VARCHAR2(9), 
     sal       NUMBER, 
     comm      NUMBER ); 
CREATE TABLE salgrade 
    (grade     NUMBER, 
     losal     NUMBER, 
     hisal     NUMBER );

The script also fills the sample tables with this data:

SELECT * FROM dept
 
DEPTNO  DNAME      LOC 
------- ---------- --------- 
10      ACCOUNTING NEW YORK 
20      RESEARCH   DALLAS 
30      SALES      CHICAGO 
40      OPERATIONS BOSTON 
 
SELECT * FROM emp 

EMPNO  ENAME   JOB          MGR  HIREDATE    SAL   COMM  DEPTNO 
-----  ------- --------- ------ --------- ------ ------ ------- 
 7369  SMITH   CLERK       7902 17-DEC-80    800             20 
 7499  ALLEN   SALESMAN    7698 20-FEB-81   1600    300      30 
 7521  WARD    SALESMAN    7698 22-FEB-81   1250    500      30 
 7566  JONES   MANAGER     7839 02-APR-81   2975             20 
 7654  MARTIN  SALESMAN    7698 28-SEP-81   1250   1400      30 
 7698  BLAKE   MANAGER     7839 01-MAY-81   2850             30 
 7782  CLARK   MANAGER     7839 09-JUN-81   2450             10 
 7788  SCOTT   ANALYST     7566 19-APR-87   3000             20 
 7839  KING    PRESIDENT        17-NOV-81   5000             10 
 7844  TURNER  SALESMAN    7698 08-SEP-81   1500             30 
 7876  ADAMS   CLERK       7788 23-MAY-87   1100             20 
 7900  JAMES   CLERK       7698 03-DEC-81    950             30 
 7902  FORD    ANALYST     7566 03-DEC-81   3000             20 
 7934  MILLER  CLERK       7782 23-JAN-82   1300             10 
 
SELECT * FROM salgrade 

GRADE LOSAL HISAL 
----- ----- ----- 
1     700   1200 
2     1201  1400 
3     1401  2000 
4     2001  3000 
5     3001  9999

To perform all the operations of the script, run it when you are logged into Oracle as the user SYSTEM.

Your Comments Are Welcome

We value and appreciate your comments as an Oracle user and reader of our references. As we write, revise, and evaluate, your opinions are the most important input we receive. At the front of this reference is a Reader's Comment Form that we encourage you to use to tell us both what you like and what you dislike about this (or other) Oracle manuals. If the form is missing, or you would like to contact us, please use the following address or fax number:

Oracle8 Server Documentation Manager
Oracle Corporation
500 Oracle Parkway
Redwood City, CA 94065
FAX: 650-506-7200

You can also e-mail your comments to: infodev@us.oracle.com

UPPERCASE	Uppercase text calls attention to SQL commands, keywords, filenames, and initialization parameters.
italics	Italicized text calls attention to definitions of terms and parameters of SQL commands.

table	The substitution value must be the name of an object of the type specified by the parameter. For a list of all types of objects, see the section, "Schema Objects".	`emp`
c	The substitution value must be a single character from your database character set.	`T` `s`
'text'	The substitution value must be a text string in single quotes. See the syntax description of 'text' in "Text".	`'Employee records'`
char	The substitution value must be an expression of datatype CHAR or VARCHAR2 or a character literal in single quotes.	`ename` `'Smith'`
condition	The substitution value must be a condition that evaluates to TRUE or FALSE. See the syntax description of condition in "Conditions".	`ename >'A'`
date d	The substitution value must be a date constant or an expression of DATE datatype.	`TO_DATE(` `'01-Jan-1994',` `'DD-MON-YYYY')`
expr	The substitution value can be an expression of any datatype as defined in the syntax description of expr in "Expressions".	`sal + 1000`
integer	The substitution value must be an integer as defined by the syntax description of integer in "Integer".	`72`
label	The substitution value must be an expression of datatype MLSLABEL. For information on such expressions, see your Trusted Oracle documentation.	`TO_LABEL(` `'SENSITIVE:ALPHA')`
number m n	The substitution value must be an expression of NUMBER datatype or a number constant as defined in the syntax description of number in "Number".	`AVG(sal)` `15 * 7`
raw	The substitution value must be an expression of datatype RAW.	`HEXTORAW('7D')`
rowid	The substitution value must be an expression of datatype ROWID.	`AAAAZzAABAAABrXAAA`
subquery	The substitution value must be a SELECT statement that will be used in another SQL statement. See "Subqueries".	`SELECT ename` `FROM emp`
:host_variable	The substitution value must be the name of a variable declared in an embedded SQL program. This reference also uses :host_integer and :d to indicate specific datatypes.	`:employee_number`
cursor	The substitution value must be the name of a cursor in an embedded SQL program.	`curs1`
db_name	The substitution value must be the name of a nondefault database in an embedded SQL program.	`sales_db`
db_string	The substitution value must be the database identification string for a Net8 database connection. For details, see the user's guide for your specific Net8 protocol.
statement_name block_name	The substitution value must be an identifier for a SQL statement or PL/SQL block.	`s1` `lab1`