Chapter 3: Table Creation and Management

ALTER TABLE ... ADD syntax

ALTER TABLE tablename ADD (columname datatype, [default] ...);

ALTER TABLE ... DROP COLUMN syntax

ALTER TABLE tablename DROP COLUMN columnname; NOTE: * can reference only one column * deletion is permanent, you cannot 'undo' the deletion. * if the table contains only one column it cannot be deleted with the ALTER TABLE command * a primary key column cannot be dropped from a table

ALTER TABLE ... DROP UNUSED syntax

ALTER TABLE tablename DROP UNUSED COLUMNS:

ALTER TABLE ... MODIFY syntax

ALTER TABLE tablename MODIFY (columname datatype [default], ...); NOTE: * a column must be as wide as the data fields it already contains. * if a NUMBER column already contains data, you cannot decrease the column's precision or scale * Changing the default value of a column doesn't change the values of data already in the table.

ALTER TABLE ... SET UNUSED syntax

ALTER TABLE tablename SET UNUSED (columnname);

DROP TABLE syntax

DROP TABLE tablename [PURGE];

FLASHBACK TABLE syntax

FLASHBACK TABLE tablename TO BEFORE DROP;

CREATE TABLE syntax

CREATE TABLE [schema] tablename (columname datatype [default value], [columnname datatype [default value]]);

CREATE TABLE ... AS syntax

CREATE TABLE tablename [(columnname, ....)] AS (subquery); NOTE: * a table can contain up to 1000 columns * each column name in a table must be unique * table names can contain as many as 30 characters * table names must begin with a letter and cannot contain blank spaces

RENAME ... TO syntax

RENAME oldtablename TO newtablename;

ALTER TABLE ... ADD

SQL command that adds a column to a table

ALTER TABLE ... MODIFY

SQL command that changes a column size, datatype, or default value.

RENAME ... TO

SQL command that changes a table name

DROP UNUSED COLUMNS

SQL command that completes the deletion of a column previously marked with SET UNUSED.

CREATE TABLE

SQL command that creates a new table in the database. The user names the columns and identifies the type of data to be stored.

CREATE TABLE .... AS

SQL command that creates a table from existing database tables, using the AS clause and sub-queries.

TRUNCATE TABLE

SQL command that deletes all table rows, but the table name and column structure remain

ALTER TABLE ... DROP COLUMN

SQL command that deletes one column from a table.

ALTER TABLE ... SET UNUSED

SQL command that marks a column for deletion at a later time.

PURGE TABLE

SQL command that permanently deletes a table in the recycle bin

FLASHBACK TABLE ... TO BEFORE DROP

SQL command that recovers a dropped table if PURGE option not used when table dropped.

DROP TABLE

SQL command that removes an entire table from Oracle 11g database

FLASHBACK TABLE command

SQL command used to recover a dropped table from the recycle bin

CHAR(n)

SQL datatype that describes a fixed-lenght character column, and the n represents the column's length. The default size is 1, and the maximum size is 2000.

NUMBER(p,s)

SQL datatype that describes a numeric column. The p indicates precision, the total number of digits to the left and right of the decimal position, to a maximum of 38 digits; the s, or scale, indicates the number of positions to the right of the decimal.

VARCHAR2(n)

SQL datatype that describes a variable-length character data variable. The maximum size is 4000 characters. There is no default size for this datatype; a minimum value must be specified.

DATE

SQL datatype that stores date and time between January 1, 4712 BC and December 31, 9999 AD. Seven bytes are allocated to the column to store the century, year, month, day, hour, minute, and second of a date. Oracle 11g displays the date in the format DD-MM-YY. Other aspects of a date can be displayed by using the TO_CHAR format. Oracle 11g defines the field width as seven bytes.

TRUNCATE TABLE syntax

TRUNCATE TABLE tablename;

virtual column

a column in a SQL table that is defined by an expression that generates a value based on other column values in the table when queried.

subquery

a nested query that includes a SELECT statement used in another SQL command that can be used when creating a table.

USER_UNUSED_COL_TABS

data dictionary reference to determine whether any columns are marked as unused. Used in a SELECT statement

datatype

identifies the type of data Oracle 11g is expected to store in a column. Identifying the type of data helps you verify that you input the correct data and allows you to manipulate data in ways specific to that datatype.

PURGE

option used in a DROP TABLE command that removes the table permanently, meaning you cannot recover it from the recycle bin

schema

value concatenated with a (.) to indicate which user owns the table.