What is SQL ?

SQL stands for Structured Query Language and it is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. It is used for managing data in relational database management system which stores data in the form of tables and relationship between data is also stored in the form of tables. SQL statements are used to retrieve and update data in a database.

SQL statements are broadly categorized into 4 types.

1.   Data definition language (DDL)

2.   Data manipulation language (DML)

3.   Data Control Language (DCL)

4.   Transaction Control Statement (TCS)

Data definition language (DDL):

Data definition language type of SQL statement is used to define database objects like tables and index structures and example of DDL statements are Create, Alter, Delete, Truncate and Drop.

There are T-SQL example of T-SQL DDL statements to create database table and delete column name of table

CREATE TABLE OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50)    

)

GO

ALTER TABLE OrderStatus

DROP COLUMN StatusDescription;

GO

DROP TABLE OrderStatus

Data manipulation language (DML):

Data manipulation language type of SQL statement is used to managing data in database and example of DML statements are Select, Insert, Update, Delete and Merge

There are example of T-SQL DML statements to select records from table, insert data in table and update data in table based on condition

SELECT Name, Sex, Address FROM dbo.persons

GO

INSERT INTO Persons(Name, Sex, Address)

VALUES ('Smith', 'M','8693 Main ST')

GO

Update Persons SET Sex = 'F' Where Name = 'Julie'

Data Control Language (DCL):

Data Control Language type of SQL statement is used to grant the access permission on database object and control access to data stored in database and main DCL statement are Grant and Revoke.

There are example of T-SQL DCL statement to grant and revoke select permission on Person table from user

GRANT SELECT ON Person TO 'rtiwari'

GO

REVOKE SELECT ON Person FROM 'rtiwari'

Transaction Control Statement (TCS):

Transaction Control Statement (TCS) type of SQL statement is used to manage the current transaction and it includes Commit, Rollback and Begin Transaction

There are example of T-SQL TCS statement to commit the current transactions and rollback transaction

BEGIN TRANSACTION;  

Update Persons SET Sex = 'F' Where Name = 'Julie'

COMMIT TRANSACTION; 

GO

BEGIN TRANSACTION;  

DELETE FROM Persons 

    WHERE Address like 'Main%';  

ROLLBACK TRANSACTION;