What are the DDL commands in SQL?

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 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

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

RENAME

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

1) PostgreSQL

Syntax

ALTER DATABASE "Old_DatabaseName" RENAME TO "New_DatabaseName";

Example

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

2) MySQL

Example

SQL Command for Dump copy

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

SQL Command for creating new DB

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

SQL Command for Import

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

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

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

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

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

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

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

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

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

DELETE

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

Syntax

DELETE FROM table_name WHERE condition;

Example

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

LOCK 

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

Syntax

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

Example

DROP TABLE Student;

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

DROP TABLE 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

DROP TABLE Student;

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

DROP TABLE Student;

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

DROP TABLE Student;

ROLLBACK 

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

Syntax

Rollback;

Example

DROP TABLE Student;

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.

What are DDL DML and DCL commands in SQL?

What are DML commands in SQL?

What are the 5 types of SQL commands?