DDL Commands of SQL

DDL stands for "Data Definition Language". 

It is a group of special type of commands,  which are used to define data base schema. It means different objects of SQL like - tables, views, index, sequence, cursor,  trigger, stored procedure, constraints etc. are prepared by DDL.

DDL commands are used to apply integrity constraints, check constraints, primary key, foreign key relationships etc.

DDL commands are used under the supervision of DBA, who is responsible for Manipulating and representing data base schema of an organisation or a company. 

DDL uses following commands- 

1. Create

2. Alter

3. Drop

4. Rename

5. Describe

Explanation of commands - 

1. Create command:- with the help of this DDL command ,we can define data base objects and also apply constraints on various fields of these objects according to organisational needs.

Syntax :- create object object_name (column_name1 data type(size), column_name2 data type(size), ..., column_nameN datatype(size), check constraints);

Example :- 

a.) create table student (rollno number (7,0) PRIMARY KEY, sname varchar(20), address varchar(20), mobileno varchar(13), check mobileno like '+91%' );

b.) create table account (Acno number (14, 0) PRIMARY KEY, cname varchar(20), balance number(11, 2), check balance> 500, check Acno like '9922%');

2. Alter command:- this DDL command is used to modify design's schema in table like - add new column in table change in any column, to apply constraints in column of table, to remove constraints from column of table rename the table etc. all tasks are performed by alter command.

Syntax :- alter object object_name add/modify/rename/drop/alter column column_name datatype (size) constraints;  

Example:- 

a. Change the datatype and size of existing column of given table account.

alter table account modify/alter column Ac_no varchar 2 (15);

alter table account modify/alter column balance number (12, 2);

alter table account modify/alter column cname varchar (30);

b. Add a new column into a account table.

alter table account add mob no varchar 2 (13) check mob no like '+91%';

alter table account add address varchar2 (30);

c. Add multiple column in SQL -

alter table account add (branch varchar (20), city varchar ( 20 ),  pin code number (6, 0), address varchar (30));

d. Add a new constraint to existing column of given that - 

alter table account add branch in ('barwani' , 'indore', 'khargone');

alter table account add balance>=1000;

e. Drop a column from given table- 

alter table account drop city;

f. Drop an integrity constraint of specific column from given table - 

alter table account drop balance>=1000;

g. Rename a column of given table - 

alter table account rename column cname to customer;

h. Rename given table-

alter table account rename account to customer_details; 

3. Drop:- drop command is used to remove any object with its records and design in temporary form present in data base. This command also remove all views and indices related with that object. This command can not be rollback, so this command is less used.

Syntax:- drop object object_name; 

Example :- drop table account; 

4. Rename :- this command is used to change the name of table (object). In some data base, alter command is used in place of rename command to change the name of object.

Syntax:- rename old_object_name to new_object_name;

Example:- rename customer_details to cd1;

5. Desribe :- with the help of this command, we can see the schema of data base objects;

Syntax:- describe object_name; 

Example:- describe cd1;