DDL (Data DefinAtion Language)

DDL is a sub-language of SQL used to create and manipulate objects in a database. DDL provides the following statements to manipulate the object in database:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • RENAME

We have shown examples of all the statements with respect to a table object.


Create Statement

Create Statement is used to create a new object in the database

Syntax:
CREATE TABLE <TABLE_NAME> (<COLUMN_NAME> <DATATYPE>(<SIZE>), <COLUMN_NAME> <DATATYPE>(<SIZE>) ... N);
Example:
CREATE TABLE EMP (EMPID NUMBER(10), ENAME VARCHAR2(20), SAL NUMBER(7,2))

Notice that the number datatype has (7,2) as its size. Here 7 is the size and 2 is the precision i.e. two digits are allowed after the decimal point and 5 digits before.(12345.67)

Note: The table structure can be viewed with the DESC command.

DESC <TABLE_NAME>

Alter Statement

Alter statement is used to modify the structure of an existing object.

Below alteration can be done on a table:

  • Addition of new columns
  • Deletion of existing columns
  • Changing the data type of columns
  • Changing the size of columns
Syntax:
ALTER TABLE <TABLE_NAME> ADD|MODIFY (<COLUMN_NAME> <DATATYPE> (<SIZE>) [,<COLUMN_NAME> <DATATYPE> (<SIZE>)... N]);
Examples:
ALTER TABLE EMP ADD(DEPTID NUMBER(5)); ALTER TABLE EMP MODIFY(EMPID NUMBER(5));

Note: To change the datatype of a column, the column must be empty.


Drop Statement

Drop statement is used to delete an existing object from the database. It can also be used to delete any column from a table.

Syntax:

To delete a single column
ALTER TABLE <TABLE_NAME>
DROP COLUMN <COLUMN_NAME>;

To delete multiple columns
ALTER TABLE <TABLE_NAME>
DROP (COLUMN1, COLUMN2 ... N);

Delete a table
DROP TABLE <TABLE_NAME>;
Examples:
ALTER TABLE EMP
DROP COLUMN EMPID;

ALTER TABLE EMP
DROP(EMPID, DEPTID);

DROP TABLE EMP;

Note: All columns cannot be deleted from a table using the ALTER statement.


Truncate Statement

Truncate statement is used to delete all data from the table, leaving only the table structure behind.

Truncate is a part of DDL(not DML) because the truncate command basically deletes all the records without putting them into temporary memory space. Any DML statement executed will make changes to the temporary copy of the table, and a commit or rollback command is required to make the changes persist in the database. Truncate on the other hand is directly executed on the table, no commit or rollback required.

Syntax:
TRUNCATE TABLE <TABLE_NAME>

Rename Statement

Rename statement is used to rename an existing object from a database.

Syntax:
RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>
Example:
RENAME EMP TO EMPLOYEE;
POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article