DDL Statement Examples in SQL

Data Definition Language

DDL statements enable you to alter the attributes of an object without altering the applications that access the object. You can also use DDL statements to alter the structure of objects while database users are performing work in the database. These statements are most frequently used to:

• Create, alter, and drop schema objects and other database structures, including the database itself and database users
• Delete all the data in schema objects without removing the structure of these objects
• Grant and revoke privileges and roles
• The commonly used DDL statements are: CREATE TABLE, ALTER TABLE, DROP TABLE, GRANT, REVOKE, TRUNCATE

Oracle Database implicitly commits the current transaction before and after every DDL statement.

CREATE TABLE Statement

Use the CREATE TABLE statement to create a table in the database. To create a table, you must have the CREATE TABLE privilege and a storage area in which to create objects.

Syntax:

CREATE TABLE tablename (
{column-definition | Table-level constraint}
[ , {column-definition | Table-level constraint} ] * )

Example:

CREATE TABLE teach_dept (
department_id NUMBER(3) PRIMARY KEY,
department_name VARCHAR2(10)); 

The table owner and the database owner automatically gain the following privileges on the table after it is created:

• INSERT
• SELECT
• REFERENCES
• ALTER
• UPDATE

The table owner and the database owner can grant the preceding privileges to other users.

ALTER TABLE Statement

Use the ALTER TABLE statement to modify the definition of an existing table in the database. You can:

• Add a column to a table
• Add a constraint to a table
• Modify an existing column definition
• Drop a column from a table
• Drop an existing constraint from a table
• Increase the width of the VARCHAR and CHAR columns
• Change a table to have read-only status

Example shown below adds a new column called LOCATION_ID to the TEACH_DEPT table:

ALTER TABLE teach_dept
ADD location_id NUMBER NOT NULL;

Example shown below updates the existing DEPARTMENT_NAME column from VARCHAR2(10) to VARCHAR2(30), and adds a NOT NULL constraint to it:

ALTER TABLE teach_dept
MODIFY department_name VARCHAR2(30) NOT NULL;

DROP TABLE Statement

The DROP TABLE statement allows you to remove a table and its contents from the database, and pushes it to the recycle bin. Dropping a table invalidates dependent objects and removes object privileges on the table.

Example:

DROP TABLE teach_dept; 

Use the PURGE clause along with the DROP TABLE statement to release back to the tablespace the space allocated for the table. You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.

DROP TABLE teach_dept PURGE; 

The CASCADE CONSTRAINTS clause allows you to drop the reference to the primary key and unique keys in the dropped table.

DROP TABLE teach_dept CASCADE CONSTRAINTS;

GRANT Statement

You can use the GRANT statement to:

• Assign privileges to a specific user or role, or to all users, to perform actions on database objects.
• Grant a role to a user, to PUBLIC, or to another role.

The GRANT statement assigns privilege to perform the following operations:

• Insert or delete data.
• Create a foreign key reference to the named table or to a subset of columns from a table.
• Select data, a view, or a subset of columns from a table.
• Create a trigger on a table.
• Execute a specified function or procedure.

Example:

GRANT SELECT any table to PUBLIC;

Before you issue a GRANT statement, check that the derby.database.sql Authorization property is set to True. This property enables the SQL Authorization mode. You can grant privileges on an object if you are the owner of the database.

You can grant privileges to all users by using the PUBLIC keyword. When PUBLIC is specified, the privileges or roles affect all current and future users.

Privilege Types

Oracle Database provides a variety of privilege types to grant privileges to a user or role:

• Use the ALL PRIVILEGES privilege type to grant all privileges to the user or role for the specified table.
• Use the DELETE privilege type to grant permission to delete rows from the specified table.
• Use the INSERT privilege type to grant permission to insert rows into the specified table.
• Use the REFERENCES privilege type to grant permission to create a foreign key reference to the specified table.
• Use the SELECT privilege type to grant permission to perform SELECT statements on a table or view.
• Use the UPDATE privilege type to grant permission to use the UPDATE statement on the specified table.

REVOKE Statement

The REVOKE statement removes privileges from a specific user (or users) or role to perform actions on database objects. It performs the following operations:

• Revokes a role from a user, from PUBLIC, or from another role.
• Revokes privileges for an object if you are the owner of the object or the database owner.

Examples:
1. Revoke a system privilege from a user:

REVOKE DROP ANY TABLE
FROM hr;

2. Revoke a role from a user:

REVOKE dw_manager
FROM sh;

TRUNCATE TABLE Statement

The TRUNCATE TABLE statement deletes all the rows from a specific table. Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table:

• Invalidates the dependent objects of the table
• Requires you to regrant object privileges
• Requires you to re-create indexes, integrity constraints, and triggers
• Requires you to respecify its storage parameters

The TRUNCATE TABLE statement spares you from these efforts.

Example:

TRUNCATE TABLE employees_demo;

By default, Oracle Database performs the following tasks:

• Deallocates space used by the removed rows.
• Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process.