Oracle DBA FAQ - Understanding SQL DDL Statements

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3

A collection of 11 FAQs on Oracle SQL DDL statements. Clear answers are provided with tutorial exercises on creating, altering and dropping tables, indexes, and views. Topics included in this FAQ are:

What Are DDL Statements?
How To Create a New Table?
How To Create a New Table by Selecting Rows from Another Table?
How To Add a New Column to an Existing Table?
How To Delete a Column in an Existing Table?
How To Drop an Existing Table?
How To Create a Table Index?
How To Rename an Index?
How To Drop an Index?
How To Create a New View?
How To Drop an Existing View?

Sample scripts used in this FAQ assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server.

What Are DDL Statements?

DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:

CREATE - Creating a new database object.
ALTER - Altering the definition of an existing data object.
DROP - Dropping an existing data object.

How To Create a New Table?

If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:

>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
  2    subject VARCHAR(80) NOT NULL,
  3    description VARCHAR(256) NOT NULL,
  4    create_date DATE DEFAULT (sysdate));

Table created.

This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".

How To Create a New Table by Selecting Rows from Another Table?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:

>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
  2  AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> SELECT first_name, last_name, salary 
  2  FROM emp_dept_10;
FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Jennifer             Whalen                          4400

As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.

This is really a quick and easy way to create a table.

How To Add a New Column to an Existing Table?

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_110 
  2  AS SELECT * FROM employees WHERE department_id=110;
Table created.

SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.

SQL> SELECT first_name, last_name, vacation 
  2  FROM emp_dept_110;
FIRST_NAME           LAST_NAME                   VACATION
-------------------- ------------------------- ----------
Shelley              Higgins
William              Gietz

This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.

(Continued on next part...)

Part: 1 2 3

More...