SQL statements

Types of SQL statements

• Data definition language (DDL) statements
• Data manipulation language (DML) statements
• Data control language (DCL) statements
• Transactional Control Language (TCL) statements

   DDL Statements

               DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, and DROP statements

Create Command: This Sql Statements is used to create a Table

Syntax: Create Table Tbl_Name

Alter Command: This Sql Statement is used to alter the table definition like adding any columns or deleting any table column.

·         To add a column to existing table

·         To rename any existing column

·         To change any data type of any column or to modify its size

·         Alter is also used to drop a column

To add a column to existing table

Syntax: Alter table Tbl_name add column-name datatype;

To add multiple column to existing table

Syntax: Alter table Tbl_name add (column-name1 datatype, column-name2 datatype, column-name3 datatype);

To add column with Default Values

Syntax: Alter table Tbl_name add (column-name datatype default data);

To modify an existing column

Syntax: Alter table Tbl_name modify (column-name datatype default data);

To rename a Column

Syntax: alter table tbl_name rename old-column-name to new-column-name

To Drop a column

Syntax: alter table tbl_name drop(column_name);

To Drop table:  This Sql Statement is used to Drop the table.

Syntax: Drop table Tbl_name

Create Index:- This Sql Statement is used to Create a Index on a table

Drop Index:- This Sql Statement is used to drop a table from the table

DML Statements

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT, and DELETE statements

Select: This Sql Statement is used to extract the data from one or combination of tables

Syntax: select col1,col2  from Table_name

Update: This Sql Statement is used to update the data in a database table.

Syntax:Update Table_name set col1=’New Values’ where col1=’value’

Delete: This Sql Statement is used to delete data from the database table.

Syntax:delete from Tbl_name where col1=’value’

Insert Into :- This Sql Statement is used to insert data into a database(table)

DCL Statements

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

Grant: This Sql Statement is used to give access rights to the user for the database 

Syntax: Grant select,insert,update on dbo.yourtable to username

Revoke: This Sql Statement is used to take back permissions from user 

Syntax: REVOKE create table from username

TCL Statements

TCL is abbreviation of Transactional Control Language. These are used to manage transactions in database. These are used to manage the changes made by DML statements. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK,Savepoint statements

Commit:  This command used is used to permanently save any transactions into database.

Syntax: Commit;

Rollback: This command restores the database to last committed state. It is also use with savepoint command to jump to a savepoint in a transcation.

Syntax: rollback to savepoint-name;

Savepoint: This command is used temporarily save a transaction so that you can roll back to that point whenever necessary.

Syntax: Savepoint Savepoint-name;

Examples of Savepoint and Rollback

Following is a class table

       Id        Name

1                    abhi

2                    adam

3                    alex

Lets use some sql queries on the above table and see the results.

Insert into class values(4,’mahesh’);

Commit;

Update class set name=’abhijit’ where id=4;

Savepoint A;

Insert into class values(5,’chris’);

Savepoint B;

Insert into class values(6,’Bravo’);

Savepoint C;

Select * from Class;

Now The results table look like this.

ID                Name

1                                Abhi

2                                Adam

3                                Alex

4                                Abhijit

5                                Chris

6                                bravo              

Now Rollback to Savepoint B

Syntax: rollback to B;

            Select * from class;

The results table look likes this

ID         Name

1                    Abhi

2                    Adam

3                    Alex

4                    Abhijit

5                    Chris

Now Rollback to Savepoint A

Syntax: rollback to A;

            Select * from class;

The results table look likes this

ID        Name

1                Abhi

2                Adam

3                Alex

4                Abhijit