SQL Commands

There are 5 types of SQL Commands:

1. DDL

2. DML

3. DCL

4. TCL

5. DQL

1. DDL : Data Definition Language

DDL commands are used to make the changes in the table structure.

Following commands come under DDL.

a. Create

b. Alter

c. Rename

d. DROP 

e. Truncate

Lets discuss each of them.

a. Create:  Used to create the table/database

Syntax: 

create database database_name

create table table_name ( column_name1 datatype,column_name2 datatype.....)

Example: 

create database mnc;

create table emp (emp_id int, emp_name varchar(20));

b. Alter: Used to make the changes in the table columns like add column, modify column and drop column

Syntax: 

alter table table_name add column column_name datatype;

alter table table_name change column existing_column_name new_column_name datatype;

alter table table_name drop column column_name ;

Example: 

alter table table_name add column emp_dob date;

alter table table_name change column emp_name empname varchar(50);

alter table table_name drop column empname; 

 

c. Rename : Used to change the table name

Syntax: 

rename table existing_table_name to new_table_name;

Example:

rename table emp to employees;

d. Truncate : Used to delete all the records from the table

Syntax: 

Truncate table table_name or Truncate table_name;

Example: 

Truncate employees;

e. DROP : Used to drop the table/database.

Syntax:

drop table table_name;

drop database database_name;

Example: 

drop table employees;

drop database mnc;

2. DML: Data Manipulation Language : is used to update or modify the table records.

Following commands come under the DML

a. Insert

b. Update

c. Delete

a. Insert: To load the data

Syntax: 

insert into table_name (col_name1,col_name2..col_nameN) values (col_value1, col_value2...col_valueN)

Example: 

insert into emp (emp_id,emp_name) values(101, "Jani");

b. Update: To change the column value of a table.

Syntax: 

update table_name set colum_name=column_value where column_name=column_value;

Example: 

update emp set emp_id=1001 where emp_name='Jani';

c. delete: To delete the specific column values or all column values.

Syntax: 

delete from table_name where column_name=column_value;

Example:

delete from emp where emp_id=1001

3. TCL: Transaction Control Language: To save and rollback the DML commands

The following commands come under TCL:

a. commit

b. Rollback

a. commit: is used to save the transaction/DML changes

Syntax:

start transaction;

DML script

commit;

Example:

start transaction

delete from emp where emp_id=1001;

commit;

b. Rollback: is used to undo transactions/dml scripts 

Syntax:

start transaction;

DML script

rollback;

Example:

start transaction

delete from emp where emp_id=1001;

rollback;