Oracle SQL Syntax Reference

(This is prepared by myself for your information. Some DBA tips are also given here. Descriptions are not formal (so some ambiguity might exist). Refer to Oracle's manual for official information. Pls let me know if you find any errors or inaccurate descriptions in this document. This is be updated soon. -- Xiaofang 7 July 2003)

Further information can be found at Online Oracle SQL References (registration with OTN required to access. It's free.).

ALTER TABLE

Change an existing table definition. The table indicated in the ALTER statement must already exist. This statement can be used to add a new column or remove an existing column in a table, modify the data type for an existing column, add or drop a constraint.

ALTER TABLE has the following syntax for adding a new column to an existing table:

ALTER TABLE <table-name> ADD <column-definition>

<column-definition> consists one or more column definitions (with data types and constraints). See create table for how to specify column definitions.

To drop an existing column, use the following:

ALTER TABLE <table-name> DROP <column-name>

Instructions such as CASCADE can be use when dropping a column. This statement can also be used to drop a constraint (if it has a name). Another ALTER TABLE syntax for modifying an existing column definition is:

ALTER TABLE MODIFY <new-column-definition>

Sometimes tables cannot be created using CREATE TABLE statements alone. For example, for two tables that have foreign key relationships to each other, one has to use alter table statement to add one of the two foreign key constraints. Note that some constraints cannot be modified (for example, adding a 'not null' constraint for a column which may already have instances in the table with null values.

CREATE INDEX

Create a new Index that facilitates rapid lookup of data. The basic syntax for the CREATE INDEX statement is:

CREATE INDEX <index-name> ON < table-name > (< column-name> , < column-name> ...)

It depends on the set of frequent or critical queries on a table to decide on which attributes, or attribute comminations, to create indexes, and a table can have many indexes. Note that an index can be a significant overhead if the data in the table are changing frequently (so the indexes on the table need to be maintained all the time). Such dynamic maintenance of indexes can also degrade the search performance using the indexes over time; so sometimes it is a good idea to drop and re-create indexes after a period of changes.

CREATE SEQUENCE

A sequence is used to generate sequential numbers. This is often used to assign a unique ID to some data items in the database. Its syntax is:

create sequence <seq-name> [increment by <integer>] [start with [integer>] [maxvalue <integer> | nomaxvalue] [minvalue <integer> | nominvalue] [cycle | nocycle]

In its simplest (and most common) form, one can just use "create sequence my_seq". Then one can use my_seq.nextval or sid_seq.currval to access the sequence number. For example, "select my_seq from dual", or "insert into student(my_seq.nextval, ‘John’, ‘Smith’)".

CREATE TABLE

Create a new table in the database. The table name must not already exist. CREATE TABLE has the following syntax:

CREATE TABLE <table-name> (<column-list> [<table-constraint-list>]);

where <column-list> is one or several column definitions separated by commas, where each column definition is of the form:

<column-name> <date-type> [default <expr>] [<column-constraint>]

Some Oracle data types are explained here. The optional <default expr> specifies the default value of the column if no value for the column is given when a record is inserted. A <column-constraint> can be one or several of the following:

not null | check (<condition>) | unique | primary key | references <table-name> [(<col-name>)] [on delete cascade]

The check option can specify the condition for this column to meet (e.g., for column age, 'check age >0' will ensure no negative value can be used as the value of this column, and this is enforced by the DBMS). The reference option specifies a foreign key condition, and 'on delete cascade' instructs the DBMS to drop all dependant rows in this table when a referenced row is deleted.

The optional <table-constrain-list> are defined in the following format:

unique (<column-list>) | primary key (<column-list>)) | foreign key (<column-list>) references <table-name> [(<column-list>)] [on delete cascade]

where <column-list> is a list of column names in the table separated by comma. Obviously, some multi-attribute primary or foreign key can only specified in this way (as they cannot be specified with any column using column-constraint).

An alternate syntax can be used to create a table with a subset of rows or columns from an existing table.

CREATE TABLE <table-name> AS <sql statement select>

CREATE VIEW

Create a new view based on existing tables in the database. The table names must already exist. The new view name must not exist. A view is really just a named query, as one can see from the syntax below:

CREATE VIEW <view-name> AS <sql statement select>

where <sql select statement select> is in the form:

SELECT <column names> FROM < table name > WHERE < where clause>

Note that an ORDER BY clause may not be added to the sql select statement when defining a view.

In general, views are read-only. That is, one may query a view but it is normally the case that views can not be operated on with INSERT, UPDATE or DELETE. This is especially true in cases where views join two or more tables together or when a view contains an aggregate function.

COMMIT

Make all recent changes to the database permanent. Changes that have occurred since the last commit are made permanent. A commit can be done explicitly using the following syntax:

COMMIT    

A commit is also done implicitly when the next SQL statement is executed.

Data Types

char(n)
Fixed length character string of length n (eg, ‘infs3202’)
varchar2(n)
Variable-length character string of up to n characters
date
Date, typically in string format (eg, ‘2-May-2002’)
number
Integer and real values up to 40 spaces
number(n)
Integer and real values up to n spaces
number(n, d)
Real values up to n spaces with d digits after the decimal point
integer
As number, but integer only
integer(n)
As integer, but occupying n spaces

DELETE

Delete one or more rows from a table. The syntax for this SQL statement is:

DELETE FROM <table name> WHERE <where clause>

If the WHERE clause is omitted, all rows in the table will be deleted. Otherwise, only those rows meeting the specified conditions are dropped.

DROP TABLE

Drop a table from the database. The table name must already exist in the database. The syntax for the DROP TABLE statement is:

DROP TABLE < table-name >

Note that tables are often dropped and then reloaded (using DBMS bulk loading facilities) to improve data retrieval performance and storage utilisation.

DROP INDEX

Drop an index of a table. The basic syntax for the DROP INDEX statement is:

DROP INDEX <index name>

Indexes on a table are often dropped before significant amount of updates (such as bulk loading). This can not only dramatically improve the performance of updates, and also maintain the quality of indexes (after they are re-created after updates). However, it is very time consuming to create indexes for a large table.

INSERT

Insert a row of data into a table. The syntax for this SQL statement is:

INSERT INTO <table-name> (column1, column2 . . .) VALUES (value1, value2, . . .)

If a value for each column in the table is supplied in the right order, then the columns do not need to be listed in the first set of parenthesis. Values can be of 3 types: Character, Number or Date. Each one requires a slightly different format when inserting:

  • Character - Must be enclosed within single quotes. For example: 'Bill Smith'
  • Number - No quotes are required. For example: 123, 44000.12
  • Date - Enclosed in single quotes in the format 'DD-MON-YYYY'. For example: '22-May-2003'

All values, regardless of data type, must be separated by commas.

Another option for the INSERT statement is to pull some data from another table. The syntax is:

INSERT INTO < table-name > SELECT < columns> FROM < table> WHERE <where-clause>

For example, assume table1 and table2have the same number of columns and the corresponding columns have the same data types. To insert all data currently in table1 into table2, using "INSERT INTO table2 SELECT * FROM table1".

ROLLBACK

Undo all recent changes to the database. A rollback can only undo changes made since the last commit. The syntax for the ROLLBACK command is:

ROLLBACK

SELECT

Retrieve existing rows from a table or several tables. A simplified syntax for the SELECT statement is:

SELECT <column1, column2, .>
FROM <table1, . table2, >
WHERE <where clause>
GROUP BY <column1, column2, ...>
HAVING <having condition>
ORDER BY < column1, column2, . . >

The WHERE clause, GROUP BY, HAVING and ORDER BY statements are optional. If a WHERE clause if omitted, all rows in the table (or the Cartesian product of all tables involved) will be retrieved. If the ORDER BY statement is omitted, there is no specific order in which the rows will be displayed. GROUP BY and HAVING are used in conjunction with aggregate functions (functions that operate on more than one record).

If all columns in a table are to be retrieved, an asterisk (*) may be substituted for the entire list of columns after the SELECT key word. Duplicate records in query results can be removed by using keyword DISTINCT after SELECT.

A more complete syntax for the SELECT statement is:

SELECT <column1, column2, . . .>
FROM <schema.table1, schema.table2, . . .> | <view>
WHERE <where clause>
GROUP BY< group by expression> HAVING < having clause>
ORDER BY < column1, column2, .... > ASC | DESC

UPDATE

Change the values of existing rows in a table in the database. The syntax for this SQL statement is:

UPDATE SET = <expression> WHERE < where-clause>

The expression can be either a single value or an arithmetic expression including another column in the table. More than one column can be updated at a time by adding additional column name = expression pairs separated by commas. If the WHERE clause is omitted, the update is applied to all rows in the table.