6 DDL commands in SQL with Syntax and Examples

Here we are going see some useful basic SQL DDL commands with syntax and example, which are used frequently while working on SQL. These DDL ( Data Defination Language) Commands are very useful for creating Table and Database to Create, Delete and making changes in them. Following are DDL commands with syntax and example.


List of DDL Commands:

• Create 
• Drop
• Truncate 
• Alter
• Comment
• Rename

Create Database:

Syntax:

Create database databasename;

Example:

Create database mydb;

Or you can use already given database “test”.

If you want to create new database you can create it with query or clicking on database symbol. After clicking on database symbol it will ask for new schema then write name of your database and click on apply.

Creating table:

Before creating table you need to mention in which database you want to create that table, so click on database and select option connect to database.

then create table schema or structure of table.

Syntax:

Create table tablename
(column1 datatype, column2 datatype, column3 datatype);

Example:

Create table Sample

(ID int, Name varchar(255), salary int);

Drop:

Syntax:

Drop table tablename

Example:

Drop table Sample

Truncate:

Syntax:

Truncate table tablename

Example:

Truncate table Sample

Difference between Drop, Delete and Truncate.

   

Create one sample table for yourself and check this all three with there queries, if they are giving same result as mentioned. delete command is explained in DML part.

Alter:



Alter statement is used to make changes in columns. It is used to add, drop or modify datatype of column.

Example for adding column:

Alter table tablename

Add column_name datatype;

Alter table Sample

Add Address Varchar(255);

Example to Drop (delete) column:

Alter table tablename

drop column column_name;

Alter table Sample

drop column Address;

Example to Modify datatype of column: 

Alter table tablename

Modify column column_name datatype;

Alter table Sample

Modify column ID int;

Comment:

Comments are used to explain Specific part of SQL queries. 

if we wanted to add single line comment then,

Example: 

--select all columns from table
select * from tablename

if we wanted to add multiple line comment then,

Example:

/* select all columns 
   from Employee table
   to show its records */
select * from Sample


Rename:

Rename statement used to change the name of table.

Syntax to Rename table name:

Rename tablename to newtablename

Example:

Rename Sample to Temporary


Some DML commands are explained in next part....