Types of SQL Commands

SQL command are divided in to 4 parts

1. DDL
2. DML
3. DQL
4. TCL

1. DDL 

• DDL stands for Data definition language.
• It is use to define structure of database and table.
• user can create change and delete structure of table.

List of DDL commands are

1. create
• Create command is use to create structure of table.
• Syntax: create table <table_name> (<columnName1> <datatype>(<size>));
• Example: crate table User_Master(Name varchar(50),Age numeric(18,0), Address1 varchar(500));
2. alter
• after create structure changes or modification is done by alter command
• alter command is use to add new column, delete existing column and increase size of column
• Add new column
• Syntax: alter table <tableName> add(<NewColumnName1> <dataType>(<Size>),<NewColumnName2><datatype>(<size>)..);
• Example: alter table User_Master add Address2 varchar(400);
• this command will add new column in  existing table User_Master
• Delete column
• Syntax: alter table <tableName> drop column<columnName>;
• Example: alter table User_Master drop column Address2;
• This will delete column Address2 from table User_Master
• Modify column size
• Syntax: alter table <tableName> alter (<columnName> <DataType> <newSize>));
• Example: alter table User_Master alter Address2 varchar(500);
3. rename
• this command is used to rename the existing table.
• Syntax: exec sp_rename <OldtableName>,<newTableName>
• Example: exec sp_rename User_Master ,Member_Master
• This will change name of User_Master to Member_Master
4. truncate
• Truncate will empty table completely. 
• It will drop table first and then will recreate structure of table.
• Syntax : truncate table <tableName>;
• Example: truncate table Member_Master;
• This will delete and recreate table again.
5. drop
• This command will discard whole table.
• Syntax : drop table <tableName>;
• Example : drop table Member_Master;
6. describe
• This command will describe the structure of table
• Syntax : exec sp_help tablename
• Example : exec sp_help Member_Master;
• result is in format of column name, datatype and size

2. DML

DML is data manipulation language.
It is used to manipulate data inside table.

list of DML Commands are:

1. Insert
• This command is used to insert records inside table.
• Syntax: insert into <tableName>(columnName1,columnName2,..column Name...n) values (value1,value2,..valuen);
• Example: insert into Member_Master(FName,LNane,Age,Address) values (‘Name1’,‘Name2’,28,’Address_here’);
2. Update
• This command is used to modify data inside table.
• Syntax: update <tableName> set <columnName1>=value1,<columnName2>=value2..<columnName n=value n;
• Example:update Member_Master set Name=‘new name’;
• Importnt : Condition is specified by where clause
• Example : update Member_Master set Name=‘new name’ where id = 55
• it will only update record which has id 55
3. Delete
• This command is used to delete records from table.
• Syntax: delete from <tableName>;
• Example: delete from Member_Master;
• This will delete all records from Member_Master, 
• for delete particular record we need to specify where clause as like update command.
• Example : delete from Member_Master where id = 55
• it will only delete rcord which has id 55

3. DQL

DQL stands for Data Query Language.

List of DQL commands are

1. Select
• use to get data from table
• Syntax : select <columnName1>,<columnName2>,..<ColumnName n> from <tableName>;
• This will select all columns from table.
• Example : select Name,Age,Gender,Address from Person_Master;
• for get specific record add where clause who have age > 15 then query will be,
• select * from Member_Master where Age > 15;

4. TCL

TCL stands for Transaction control language

List of TCL commands are

1. Commit
• Once any transaction has been performed successfully
• commit will save changes to the database.
• Commit is saving of result of all actions or queries.
• Syntax : commit;
• Example : 

                    begin tran
                    delete from User_Master where id =111
                    Commit

• Than after execute rollback command it will not show deleted record
1. Rollback
• Once any transaction has been done, to undo that transaction, rollback can be used.
• Rollback can undo only last transaction.
• Syntax : rollback;
• Example: 

                    begin tran
                    delete from Member_Masterwhere id =104
                    select * from Student_Master
                    rollback tran

                    select * from Student_Master

• When we execute above code it will not show deleted record but after rollback execution record will display
• after commit you can not rollback transaction.
• every transaction is started with begin tran
1. Save point and Rollback to save point
• Sometimes, during execution of transactions, we need to check whether some conditions are met after transactions or not, if conditions are met, we want to commit changes but If condition doesn’t met we need to rollback to any previous transactions.
• For this type of requirement, we can use rollback to save point.
• In this, we are defining one save point.
• Example : 

                    begin tran

                    save tran trans1

                    delete from ....

                    delete from ....

                    delete from ....

                    save tran trans2

                    delete from ....

                    delete from ....

• now we can rollback on any saved transaction like.
• rollback tran trans2
• rollback tran trans1