SQL Commands : DDL, DQL, DML, DCL, TCL with Examples

Introduction

In this article, you will learn the SQL command categories and their sub-categories. SQL is an open-source data management system. The SQL query is used to retrieve and manipulate the data from the table. With the help of SQL command we can query, filter, sort, join, group and modify the data in the database.

SQL Commands

SQL commands are categorized into below 5 categories:

  • DDL – Data Definition Language
  • DQL – Data Query Language
  • DML – Data Manipulation Language
  • DCL – Data Control Language
  • TCL - Transaction Control Language

DDL (Data Definition Language)

DDL or Data definition language is actually the definition or description of the database structure or schema, it won't change the data inside the database. Create, modify, and delete the database structures, but not the data. Only These commands are not done by all the users, who have access to the database via an application.

CREATE

Create the database or its object (ie table, index, view, function etc.).

Syntax

CREATE DATABASE databasename

Example

CREATE DATABASE Student_data;

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Example

CREATE TABLE Student (
    StudendId int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    Mark int
);

DROP

Drop command helps to delete the object from the database (ie table, index, view, function, etc.).

Syntax

DROP object object_name

Example

DROP TABLE Student;

Syntax

DROP DATABASE database_name

Example

DROP DATABASE Student_data;

ALTER

Alter command is helpful to change or modify the structure of the database or its object.

Syntax

ALTER TABLE table_name
ADD column_name datatype

Example

ALTER TABLE Student
ADD Total int;

Syntax

ALTER TABLE table_name
DROP COLUMN column_name

Example

​​​​​​​ALTER TABLE Student
DROP COLUMN Mark;

1) SQL Server / MS Access

Syntax

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Example

ALTER TABLE Student
ALTER COLUMN Total Varchar(255);

2) My SQL / Oracle (prior version 10G)

Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Example

ALTER TABLE Student
MODIFY COLUMN Total Varchar(255);

3) Oracle 10G and later

Syntax

ALTER TABLE table_name
MODIFY column_name datatype

Example

​​​​​​​ALTER TABLE Student
MODIFY Total Varchar(255);

TRUNCATE

Truncate command helps to remove all records from a table.

Syntax

TRUNCATE TABLE  table_name

Example

TRUNCATE TABLE  Student;

COMMENT

Comment is helpful to add comments to the data dictionary."--" is used to comment the notes.

Syntax

--(notes,examples)

Example

--select the student data
SELECT * FROM Student;

RENAME

Rename is helpful to rename an object existing in the database.

1) PostgreSQL

Syntax

ALTER DATABASE "Old_DatabaseName" RENAME TO "New_DatabaseName";

Example

ALTER DATABASE "Student_data" RENAME TO "Employee_data";

2) MySQL

Example

SQL Command for Dump copy

mysqldump -u username -p"password" -R testDb > testDb.sql;

SQL Command for creating new DB

mysqladmin -u username -p"password" create testDB1;

SQL Command for Import

mysql -u username -p"password" testDb1 < testDb.sql;

Also for Unix, database names are case-sensitive

3) SQL Server

In SQL Server we can rename the database through server application, by right click the existing database and renaming it.

DQL (Data Query Language)

DQL or data query language is to perform the query on the data inside the schema or object (ie table, index, view, function, etc). With the help of DQL query we can get the data from the database to perform actions or operations like analysing the data. 

SELECT

Select query on a table or tables to view the temporary table output from the database.

Syntax

Select * from Table_Name;

Example

Select * from Student;

DML(Data Manipulation Language)

DML or Data Manipulation Language is to manipulate the data inside the database. With the help of DML commands, we can insert, delete, change the data inside the database.

INSERT

Insert command is helpful to insert the data into a table.

1) All the column names are mentioned in the insert statement.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)

Example

INSERT INTO Student (StudendId, FirstName, LastName)
VALUES (12345, "Sri", "Durga");

2) Column names do not need to mentioned in the query, Values should be given in the order according to the column.

Syntax

INSERT INTO table_name
VALUES (value1, value2, value3, ...)

Example

INSERT INTO Student
VALUES (12345, "Sri", "Durga");

UPDATE

Update command is helpful to update the existing data in a table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition

Example

UPDATE Student
SET FirstName = "Navin" , LastName = "Kumar"
WHERE StudentId=12345;

DELETE

Delete command helps to delete the records from a database table.

Syntax

DELETE FROM table_name WHERE condition;

Example

DELETE FROM Student WHERE StudentId=12345;

LOCK 

Lock command is helpful to lock the table to control concurrency.

Syntax

LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE

Example

LOCK TABLE Student IN SHARE MODE;

CALL

Call command is helping to Call a PL/SQL or JAVA subprogram.

Syntax

EXEC SQL
 CALL GETEMPSVR (2, NULL)
END-EXEC

EXPLAIN PLAN 

It describes the access path to the data.

Syntax

EXPLAIN PLAN FOR
SELECT Column_name FROM table_name

Example

EXPLAIN PLAN FOR
SELECT last_name FROM Student;

This query explanation will be stored in the PLAN_TABLE table. We can then select the execution plan to review the queries.

DCL (Data Control Language)

DCL or Data Control Language is to provide rights, permissions, and other controls of the database system. 

GRANT

GRANT command is helpful to provide privileges to the database.

Syntax

GRANT privileges_names ON object TO user

Example

GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name'@'localhost;

GRANT ALL ON Users TO 'Name'@'localhost;

GRANT SELECT  ON Users TO '*'@'localhost;

Syntax

GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user

REVOKE

Revoke command is to withdraw the user’s access privileges given by using the GRANT command.

Syntax

REVOKE privileges ON object FROM user

Example

REVOKE SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name'@'localhost; 

REVOKE ALL ON Users TO 'Name'@'localhost; 

REVOKE SELECT  ON Users TO '*'@'localhost; 

Syntax

REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user

TCL (Transaction Control Language)

TCL or Transaction Control Language happens to a transaction in the database.

COMMIT

Commit command is to commit Transaction after insert or delete in the database.

Syntax

Commit;

Example

DELETE from Student where Student_Id = 12345;
COMMIT;

ROLLBACK 

Rollback command is to rollback a transaction in case of any error occurs.

Syntax

Rollback;

Example

DELETE from Student where Student_Id = 12345;
ROLLBACK;

SAVEPOINT

Savepoint command is to Set a savepoint within a transaction. If transaction happens in big data, then for checking and rollup can't do it with all the data, to rollback the small part of the data we use savepoint query.

Syntax

SAVEPOINT savepoint_name

SET TRANSACTION

Set command is to Specify the characteristics of the transaction.

Syntax

SET TRANSACTION Access NAME transaction_name

Summary

In this article, you have learned about the various SQL commands and simple SQL queries with examples.