SQL Statements :DML, DDL, DCL
& TCL

1) Data Definition Language (DDL)

• DDL statements are used to define the data base structure or schema.
• DDL changes the structure of the table for example creating a table, altering a table and deleting a table etc.
• DDL statements are auto-committed that means it permanently save all the changes in the database.

Below are four types of DDL commands in SQL:

1) CREATE : 

SQL CREATE TABLE statement is used to create table in a database.

Syntax:

Create database database-name;
Create Table TableName (COLUMN_NAME DATATYPES[,....]); 

2) ALTER

ALTER TABLE is used to add, rename, modify or drop columns

Syntax:

ALTER TABLE table_name ADD column_name COLUMN-definition;    

To modify existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....); 

3) DROP

It is used to remove or delete a table from the SQL database.

Syntax:

drop table table-name;

4) TRUNCATE

Used to delete all the rows from the table and free the space containing the table.

Syntax:

truncate table table-name; 

2) Data Manipulation language (DML)

• DML statements are used for managing data within database.
• DML statements are not auto-committed that means changes are not permanent to database, they can be rolled back.

Below are four types of DML commands in SQL:

1) SELECT 

• • The SELECT query used to select the data from database.
  • Used to retrieve records from one or more tables in your SQL database

Syntax:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

2) INSERT

Insert command is used to insert data into a table

Syntax:

INSERT INTO TABLE_NAME  (col1, col2, col3,.... col N)  
VALUES (value1, value2, value3, .... valueN);  
Or 
INSERT INTO TABLE_NAME    
VALUES (value1, value2, value3, .... valueN);

3) DELETE

Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row.

Syntax:

DELETE FROM table WHERE condition

4) UPDATE

Update Command is used to  update a row of a table. 

Syntax:

UPDATE Table SET column_name =value [WHERE condition]

3) Data Control Language (DCL)

DCL (Data Control Language) commands /statements are useful to give “rights & permissions.”

1)GRANT

Grant command is use to give user access privileges to a database.

Syntax:

GRANT privilege_nameON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

2) REVOKE

REVOKE is used to take back permissions from the user.

Syntax:

REVOKE privilege_nameON object_name
FROM {user_name |PUBLIC |role_name}

4) Transaction Control Language (TCL)

TCL commands deal with the transaction within the database.

1) COMMIT

COMMIT commands  is used to save all the transactions to the database.

Syntax:

Commit;

2) ROLLBACK

ROLLBACK is used to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK; 

Recommended Articles:

• • SQL Operators
  • SQL-Create Table
  • SQL-Alter Table
  • SQL-Drop Table