Create Table

To create a table, use the CREATE TABLE statement. At a minimum, you need to list the column names and datatypes for the table. For example:

CREATE TABLE change_log ( log_id NUMBER, who VARCHAR2(64)
,when TIMESTAMP, what VARCHAR2(200) );

You can add some attributes to your table definition such as the tablespace in which you want your table stored:

CREATE TABLE change_log ( log_id NUMBER ,who VARCHAR2(64)
,when TIMESTAMP ,what VARCHAR2(200)
) TABLESPACE users;

The TABLESPACE option tells the database where to store the table. After you create the table, you can display the structure of a table with the SQL*Plus DESCRIBE command:

SQL> describe change_log;

Name Null? Type
------ ----- ----------------
LOG_ID NUMBER
WHO VARCHAR2(64)
WHEN TIMESTAMP(6)
WHAT VARCHAR2(200)


Creating a Table Using a Query

You can create a table based on a query, you do not need to specify the column attributes; they are inherited from the existing schema object like a single table, a view, or join multiple tables. This table creation syntax is frequently identified with the abbreviation CTAS (Create Table As Select):

CREATE TABLE january2004_log TABLESPACE archives AS SELECT * FROM change_log WHERE when BETWEEN TO_DATE('01-JAN-2004','DD-Mon-YYYY’) AND TO_DATE('31-JAN-2004','DD-Mon-YYYY’);

SQL> describe january2004_log

Name Null? Type
-------- -------- -------
LOG_ID NUMBER
WHO VARCHAR2(64)
WHEN TIMESTAMP(6)
WHAT VARCHAR2(200)



Creating a Temporary Table

You can create a temporary table whose contents are transitory and only visible to the session that inserted data into it. The definition of the table persists, but the data in a temporary table lasts only for either the duration of the transaction (ON COMMIT DELETE ROWS) or for the duration of the session (ON COMMIT PRESERVE ROWS). Programs can manipulate data in temporary tables or join them to permanent tables in the same manner as any other table. Here is an example:

CREATE GLOBAL TEMPORARY TABLE my_session
(category VARCHAR2(16) ,running_count NUMBER
) ON COMMIT DELETE ROWS;

Setting Default Values

You can set default values for the columns in your table. When subsequent INSERT statements do not explicitly populate these columns, the database assigns the default value to the column. Having a default value does not ensure that the column will always have a value. An INSERT or an UPDATE statement can always explicitly set a column to NULL unless there is a NOT NULL constraint on the column.Default values can be any SQL expression that does not reference a PL/SQL function, other columns, or the pseudocolumns ROWNUM, NEXTVAL, CURRVAL, LEVEL, or PRIOR.Default values are defined as part of a column specification. This definition can be made at table creation time, like this:

CREATE TABLE change_log
(log_id NUMBER ,who VARCHAR2(64) DEFAULT USER
,when TIMESTAMP DEFAULT SYSTIMESTAMP ,what VARCHAR2(200)
) TABLESPACE users;

To define a default value after a table has been created, use the ALTER TABLE statement to modify the column specification, like this:
ALTER TABLE change_log MODIFY who VARCHAR2(64) DEFAULT USER;


Adding Comments to a Table or a Column

You can add descriptive comments to your tables and columns in order to better describe the content or usage of these database objects. Comments can be a maximum of 4,000 bytes in length and can have embedded white space and punctuation. Use the COMMENT ON statement to assign a comment to either a table or a column, like this:

COMMENT ON TABLE change_log IS 'This table is where you record changes to the configuration of the DEMO system';

COMMENT ON COLUMN change_log.log_id IS 'System generated key for change log table Populated with the change_seq sequence.';

The comment must be enclosed in single quotes, but can span physical lines. To display the comments on a table, query the DBA_TAB_COMMENTS, ALL_TAB_COMMENTS, or USER_TAB_COMMENTS data dictionary views.


Renaming a Table

You can use the RENAME statement to change the name of a table, view, sequence, or private synonym—objects that share a namespace with tables. The syntax is RENAME old_name TO new_name. When you rename a table, the database invalidates all objects that depend on (refer to) the table, such as views, procedures, or synonyms. The database automatically alters associated indexes, grants, and constraints to reference the new name. To change the name of the change_log table to demo_change_log, execute this:
RENAME change_log TO demo_change_log;

Another way to rename a table is with the RENAME TO clause of the ALTER TABLE statement. For example:
ALTER TABLE change_log RENAME TO demo_change_log;

Neither syntax is preferred; both semantics are fully supported.


Adding and Dropping Columns in a Table

You will need to perform while managing tables is adding new columns to an existing table. Use the ALTER TABLE statement to add columns. When adding a single column, use the syntax ALTER TABLE table_name ADD column_spec. When you add multiple columns to a table, enclose a comma-delimited list of column specifications with parentheses. The column specification includes the column name, the column’s datatype, and any default value that the column will have. For example, to add a column named HOW to the change_log table, execute the following SQL:

ALTER TABLE change_log ADD how VARCHAR2(45);

To add the two columns—HOW and WHY—to the change_log table, use the syntax with parentheses, like this:

ALTER TABLE change_log ADD ( how VARCHAR2(45) ,why VARCHAR2(60) );

To remove a column from a table, use the ALTER TABLE DROP COLUMN statement, as in this example:
ALTER TABLE change_log DROP COLUMN how;

To drop multiple columns, you don’t use the keyword COLUMN, and instead enclose the comma-delimited list of columns in parentheses:
ALTER TABLE change_log DROP (how,why);


Modifying Columns

You may need to occasionally make changes to the columns of a table—increase or decrease the size of a column, rename a column, or assign a default value to a column. You use the ALTER TABLE MODIFY statement to make these column-level changes. As with the ADD and DROP options, you have two syntactical options: one for modifying a single column and one for modifying multiple columns. To make changes to a single column, you specify the column name together with the new characteristics. For example, to change the column WHAT from VARCHAR2(200) to VARCHAR2(250), you execute:
ALTER TABLE change_log MODIFY what VARCHAR2(250);

To change multiple columns, enclose a comma-delimited list of modified column specs in parentheses, like this:

ALTER TABLE change_log MODIFY (what VARCHAR2(250) ,who VARCHAR2(50) DEFAULT user );

Viewing the Attributes of a Table

You can use several data dictionary views to display the attributes of a table. The first place to start is usually with the DESCRIBE command from SQL*Plus or iSQL*Plus:

SQL> describe employees
Name Null? Type
----------------------- -------- ----------------
EMPLOYEE_ID NOT NULL NUMBER
HIRE_DATE NOT NULL DATE
FIRST_NAME VARCHAR2(42)
LAST_NAME VARCHAR2(42)
PAYROLL_ID VARCHAR2(10)
DEPT_NBR NUMBER
MANAGER NUMBER

The DESCRIBE command displays the column names, datatypes, and nullity of each column. To see the table physical attributes, query the ALL_TABLES or USER_TABLES dictionary views.

DROPING A TABLE

To drop a Table user the following SQL:

1. drop table table_name;
2. drop table table_name cascade constraints;
3. drop table table_name purge;

The drop table command moves a table into the recycle bin unless purge was also specified. However, if the purge modifier is specified as well, the table is unrecoverably (entirely) dropped from the database. The cascade constraints

modifier deletes all foreign keys that reference the table to be dropped, then drops the table.

To learn more about CONSTRAINT please see 'Create and Manage Constraints' post.