DDL COMMANDS
CREATE TABLE Statement
The Oracle CREATE TABLE statement allows to create and define a table.
Syntax
The syntax for the CREATE TABLE statement in Oracle/PLSQL is:
CREATE TABLE table_name
(
column1 datatype (size),
column2 datatype (size),
...
column_n datatype (size)
);
Example
Let's look at an Oracle CREATE TABLE example.
CREATE TABLE customers
( customer_id number(10),
customer_name varchar2(50),
city varchar2(50)
);
CREATE TABLE AS Statement
Description
Oracle CREATE TABLE AS statement to create a table from an existing table by copying the existing
table's columns.
It is important to note that when creating a table in this way, the new table will be populated with the
records from the existing table (based on the SELECT Statement).
Create TAble - By Copying all columns from another table
Syntax
The syntax for the CREATE TABLE AS statement that copies all of the columns in Oracle/PLSQL is:
CREATE TABLE new_table
AS (SELECT * FROM old_table);
Example
Let's look at a CREATE TABLE AS example that shows how to create a table by copying all columns
from another table.
CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE company_id < 5000);
This example would create a new table called suppliers that included all columns from
the companies table.
If there were records in the companies table, then the new suppliers table would be populated with the
records returned by the SELECT statement.
Create TAble - By Copying selected columns from another table
Syntax
The syntax for the CREATE TABLE AS statement that copies the selected columns in Oracle/PLSQL is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table);
Example
Let's look at a CREATE TABLE AS example that shows how to create a table by copying selected
columns from another table.
For Example:
CREATE TABLE suppliers
AS (SELECT company_id, address, city, state, zip
FROM companies
WHERE company_id < 5000);
This example would create a new table called suppliers, but the new table would only include the
specified columns (ie: company_id, address, city, state, and zip) from the companies table.
Again, if there were records in the companies table, then the new suppliers table would be populated with
the records returned by the SELECT statement.
Create table - By Copying selected columns from multiple tables
Syntax
The syntax for the CREATE TABLE AS statement that copies columns from multiple tables in
Oracle/PLSQL is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);
Example
Let's look at a CREATE TABLE AS example that shows how to create a table by copying selected
columns from multiple tables.
For example:
CREATE TABLE suppliers
AS (SELECT companies.company_id, companies.address, categories.category_type
FROM companies, categories
WHERE companies.company_id = categories.category_id AND companies.company_id < 5000);
This example would create a new table called suppliers based on columns definitions from both
the companiesand categories tables (ie: company_id, address, and category_type).
Question: How can I create an Oracle table from another table without copying any values from the old
table?
Answer: To do this, the Oracle CREATE TABLE syntax is:
CREATE TABLE new_table
AS (SELECT *
FROM old_table WHERE 1=2);
For example:
CREATE TABLE suppliers
AS (SELECT *
FROM companies WHERE 1=2);
This would create a new table called suppliers that included all column definitions from
the companies table, but no data from the companies table.
Oracle / PLSQL: ALTER TABLE Statement
ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or
rename a table (with syntax, examples and practice exercises).
Description
The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The
Oracle ALTER TABLE statement is also used to rename a table.
Add column in table
Syntax
To ADD A COLUMN in a table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name
ADD column_name column-definition;
Example
Let's look at an example that shows how to add a column in an Oracle table using the ALTER TABLE
statement.
For example:
ALTER TABLE customers
ADD customer_name varchar2(45);
This Oracle ALTER TABLE example will add a column called customer_name to the customers table
that is a data type of varchar2(45).
In a more complicated example, you could use the ALTER TABLE statement to add a new column that
also has a default value:
ALTER TABLE customers
ADD city varchar2(40) DEFAULT 'Seattle';
In this example, the column called city has been added to the customers table with a data type of
varchar2(40) and a default value of 'Seattle'.
Add multiple columns in table
Syntax
To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
...
column_n column_definition);
Example
Let's look at an example that shows how to add multiple columns in an Oracle table using the ALTER
TABLE statement.
For example:
ALTER TABLE customers
ADD (customer_name varchar2(45),
city varchar2(40) DEFAULT 'Seattle');
This Oracle ALTER TABLE example will add two columns, customer_name as a varchar2(45) field
and city as a varchar2(40) field with a default value of 'Seattle' to the customers table.
Modify column in table
Syntax
To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY column_name column_type;
Example
Let's look at an example that shows how to modify a column in an Oracle table using the ALTER TABLE
statement.
ALTER TABLE customers
MODIFY customer_name varchar2(100) NOT NULL;
This Oracle ALTER TABLE example will modify the column called customer_name to be a data type of
varchar2(100) and force the column to not allow null values.
In a more complicated example, you could use the ALTER TABLE statement to add a default value as
well as modify the column definition:
ALTER TABLE customers
MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL;
Modify Multiple columns in table
Syntax
To MODIFY MULTIPLE COLUMNS in an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type);
Example
Let's look at an example that shows how to modify multiple columns in an Oracle table using the ALTER
TABLE statement.
ALTER TABLE customers
MODIFY (customer_name varchar2(100) NOT NULL, city varchar2(75) DEFAULT 'Seattle' NOT
NULL);
Drop column in table
Syntax
To DROP A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name
DROP COLUMN column_name;
Example
Let's look at an example that shows how to drop a column in an Oracle table using the ALTER TABLE
statement.
ALTER TABLE customers
DROP COLUMN customer_name;
This Oracle ALTER TABLE example will drop the column called customer_name from the table
called customers.
Rename column in table
(NEW in Oracle 9i Release 2)
Syntax
Starting in Oracle 9i Release 2, you can now rename a column.
To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
Example
Let's look at an example that shows how to rename a column in an Oracle table using the ALTER TABLE
statement.
ALTER TABLE customers
RENAME COLUMN customer_name TO cname;
This Oracle ALTER TABLE example will rename the column called customer_name to cname.
Rename table
Syntax
To RENAME A TABLE, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name RENAME TO new_table_name;
Example
Let's look at an example that shows how to rename a table in Oracle using the ALTER TABLE statement.
ALTER TABLE customers
RENAME TO contacts;
This Oracle ALTER TABLE example will rename the customers table to contacts.
Oracle / PLSQL: DROP TABLE Statement
This Oracle tutorial explains how to use the Oracle DROP TABLE statement with syntax and examples.
Description
The Oracle DROP TABLE statement allows you to remove or delete a table from the Oracle database.
Syntax
The syntax for the Oracle DROP TABLE statement is:
DROP TABLE [schema_name].table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];
Parameters or Arguments
schema_name
The name of the schema that owns the table.
table_name
The name of the table to remove from the Oracle database.
CASCADE CONSTRAINTS
Optional. If specified, all referential integrity constraints will be dropped as well.
PURGE
Optional. If specified, the table and its dependent objects will be purged from the recycle bin and
you will not be able to recover the table.
If not specified, the table and its dependent objects are placed in the recycle bin and can be
recovered later, if needed.
Note
• If there are referential integrity constraints on table_name and you do not specify
the CASCADE CONSTRAINTS option, the DROP TABLE statement will return an error
and Oracle will not drop the table.
Example
Let's look at an example that shows how to drop a table in Oracle by using the DROP TABLE statement.
For example:
DROP TABLE customers;
This Oracle DROP TABLE example would drop the table called customers.
Purge
Let's look at how to use the PURGE option with the DROP TABLE statement in Oracle.
When issuing a DROP TABLE statement in Oracle, you can specify the PURGE option. The PURGE
option will purge the table and its dependent objects so that they do not appear in the recycle bin. The risk
of specifying the PURGE option is that you will not be able to recover the table. However, the benefit of
using PURGE is that you can ensure that sensitive data will not be left sitting in the recycle bin.
For example:
DROP TABLE customers PURGE;
This DROP TABLE statement would drop the table called customers and issue a PURGE so that the
space associated with the customers table is released. In other words, the customers table is not placed
into the recycle bin and, therefore, cannot be recovered later if required.
