SQL DDL Commands

Structured Query Language (SQL) is known as the database language. SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data.

SQL commands are mainly divided into four major categories based on their functionality

• • Data Definition language (DDL)
  • Data Manipulation language (DML)
  • Transaction Control language (TCL)
  • Data Control language (DCL)

Data Definition language (DDL):

The SQL of DDL statements are deals with description of database schema, these commands are used for creating and modifying the structure of database objects in the database. DDL commands are Create, Alter, Drop, Rename, Truncate, Comment.

Examples of DDL commands:

Create:  create is used for creating the database or its objects (Ex table, index, function, views, store procedure and triggers).

Syntax for creating table or view:

Create table table_name (column1 data_type (size), column2 data_type (size), ………..);

Below screenshot for creating table

Example:

Create table testtable (sno int, sname varchar2(50));

After table created, if we execute select statement, we will get the below output (as shown in screenshot) with no rows as we did not insert any data yet.

Alter:

Alter is used for modifying the structure of table by modifying the definition of its columns.

By using the below functions, we will perform required data from table.

• • Alter table-Add
  • Alter table-Drop
  • Alter table-Modify
  • Alter table or Rename

Alter table-Add:

 This statement is used to add the new column to the existing structure of the table.

Syntax for adding column in table:

ALTER TABLE table_name ADD (Columnname_1  datatype, Columnname_2  datatype, …….);

Example:

Alter table testable ADD (sname varchar2(50), sage int);

After altering the table with add command, the table will be added with the 2-columns to the existing structure of the table.

Before adding columns, the below is the table with two columns:

Below is the screenshot for the example after adding the two more columns:

Drop:

Drop is used for deleting the column data.

Syntax for drop a column:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

Alter table testable Drop column sname,sage;

After altering the table with drop command, the table will drop the column data.

Below is the screenshot for the example

Modify:

Modify is used for modifying the data type or size of the Data.

Syntax for modifying table:

ALTER TABLE table_name MODIFY column_name column_type;

Alter table testable modify sname varchar2(60);

After altering the table with Modify command the table will be modified the column data.

Below is the screenshot for the example

Syntax for rename the table

Rename: RENAME command is used to change the name of the table or a database object.

RENAME old_table_name To new_table_name;

Example:

Rename testable to test_table;

Below is the screenshot for renaming the table from testtable to test_table.

Below is the select statement executed after renaming the table to test_table.

Truncate Command:

Truncate is used for removing all the rows from a table

Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.

Before truncating the table:

Select * from test_table;

After truncating the table:

Example:

Truncate table test_table;

Below is the screenshot for the example

After truncating the table if we select the table, we will not get any data, the data will be truncated

Drop Command:

Drop is used for dropping the data base or data base objects like table and view, index, procedure.

Note: Table can be dropped in two ways.one using in Alter and other way is Drop Command

The drop statement cannot be rollback.

Syntax for Drop:

Drop table table_name;

Example:

Drop table test_table;

Below is the screenshot for the example

After dropping the table if we select the table, we will not get any data, it is showing pop up message like the table or view doesn’t exist.