1.create table directly
SQL> CREATE TABLE dept
2 (deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13));
Table created.
2.Creating a Table by Using a Subquery
SQL> CREATE TABLE dept30
2 AS
3 SELECT empno, ename, sal*12 ANNSAL, hiredate
4 FROM emp
5 WHERE deptno = 30;
3.The ALTER TABLE Statement
Use the ALTER TABLE statement to:
Add a new column
Modify an existing column
Define a default value for the new column
ALTER TABLE dept30
2 ADD (job VARCHAR2(9));
Guidelines for Adding a Column
You can add or modify columns, but you cannot drop them from a table.
You cannot specify where the column is to appear. The new column becomes the last column.
ALTER TABLE dept30
MODIFY (ename VARCHAR2(15));
4.Dropping a Table
The DROP TABLE statement removes the definition of an Oracle8 table. When you drop a table, the database loses all the data in the table and all the indexes associated with it.
All data is deleted from the table.
Any views or synonyms will remain but are invalid.
Any pending transactions are committed.
Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.
The DROP TABLE statement, once executed, is irreversible. The Oracle Server does not question the action when you issue the DROP TABLE statement. If you own that table or have a high-level privilege, then the table is immediately removed. All DDL statements issue a commit, therefore making the transaction permanent.
5 Renaming a Table
Additional DDL statements include the RENAME statement, which is used to rename a table, view, sequence, or a synonym.
SQL> RENAME dept TO department;
Table renamed.
6.Truncating a Table
Another DDL statement is the TRUNCATE TABLE statement, which is used to remove all rows from a table and to release the storage space used by that table. When using the TRUNCATE TABLE statement, you cannot rollback row removal.
SQL> TRUNCATE TABLE department;
7.Adding a Comment to a Table
You can add a comment of up to 2000 bytes about a column, table, view, or snapshot by using the COMMENT statement. The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
SQL> COMMENT ON TABLE emp
2 IS 'Employee Information';
Comment created.