5 Different Types of SQL Commands | with Examples

by

Structured query languages (SQL) play a protagonist role in processing data sets in the database. It is imperative to understand and learn types of SQL commands if you are dealing with the database.

Types of SQL Commands with Examples | Classifications

What are the different types of SQL commands?

  1. Data Query Language (DQL)
  2. Data Definition Language (DDL)
  3. Data Manipulation Language (DML)
  4. Data Control Language (DCL)
  5. Transaction Control Language (TCL)

Let’s dive into each command.

1. Data Query Language (DQL)

Data query language (DQL) is used to retrieve or read the data from the database (i.e. table). The ‘SELECT’ command is an example of DQL.

  • The ‘SELECT’ command reads data from the table. The developer can specify further conditions based on the business requirements to fetch the required data or the intended data set.

SQL Syntax:

SELECT * FROM table_name
WHERE conditions;

or

SELECT 
column1, 
column2,
...,
columnN
FROM table_name
WHERE conditions;

For example:

SELECT employee_name
FROM customers
WHERE employee_salary>=100000;

2. Data Definition Language (DDL)

Data definition language (DDL) is used to define the schema or database structure. There are five DDL commands (‘CREATE’, ‘ALTER’, ‘DROP’, and ‘TRUNCATE’) used in SQL.

  • The ‘CREATE’ command is to create a new database, table, index, or other database-related object.

SQL Syntax:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
…,
columnN datatype
);

For example:

CREATE TABLE customers
(
customer_id number(4),
customer_name varchar2(100)
);
  • The ‘ALTER’ statement modifies the structure of the existing database object. The SQL developer can add the COLUMN in the existing TABLE.

SQL Syntax:

ALTER TABLE table_name
ADD column_name datatype;

For example:

ALTER TABLE contacts
ADD contact_number varchar2(20);
  • The ‘DROP’ command deletes the table structure and its whole data from the Relational Database Management System (RDBMS).

SQL Syntax:

DROP TABLE table_name;

For example:

DROP TABLE agents;
  • The ‘TRUNCATE’ command empties the table. It keeps the table structure as it is.

SQL Syntax:

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE customers_details;

3. Data Manipulation Language (DML)

Data manipulation language offers data operations such as inserting, modifying, and deleting data from the data set. There are three basic DML commands including as ‘INSERT’, ’UPDATE’, and ‘DELETE’.

  • The ‘INSERT’ statement helps to add new records (or data) into the row of a table.

SQL Syntax:

INSERT INTO table_name (column1, column2,...,columnN)
VALUES (value1,value2,...,valueN);

For example:

INSERT INTO students(roll_number, full_name,mark)
VALUES (10,’shri’,95);
  • The ‘UPDATE’ command is used to update the value of a column in the table based on specified conditions.

SQL Syntax:

UPDATE table_name
SET column_name1= value1, column_name2= value2,...,column_nameN= valueN   
WHERE condition;

For example:

UPDATE agents
SET agent_name='jack'
WHERE agent_id=10;
  • The ‘DELETE’ command is to remove or delete one or more rows (or records) from the existing table based on the given condition.

SQL Syntax:

DELETE FROM table_name
WHERE condition;

For example:

DELETE FROM fields
WHERE field_name='engineering';

Note: A ‘DELETE’ statement without the ‘WHERE’ condition can wipe out entire table records. Always make sure to run the ‘DELETE’ statement with the ‘WHERE’ clause.

4. Data Control Language (DCL)

The data control language offers control over authorization privileges to users and user groups.

  • The ‘GRANT’ command authorizes access to the mentioned table, schema or database to the user or user group.

SQL Syntax:

GRANT SELECT ON table_name TO user_name;

For example:

GRANT SELECT ON agents TO 5024524;
  • The ‘REVOKE’ command removes existing privileges of users from the specified tables, schemas, or databases.

SQL Syntax:

REVOKE SELECT ON table_name FROM user_name;

For example:

REVOKE SELECT ON agents FROM 5024524;

5. Transaction Control Language (TCL)

Transaction control language deals with saving or restoring the last transaction in the table. ‘COMMIT’ and ‘ROLLBACK’ are TCL commands.

  • The ‘COMMIT’ command is used to save the transaction or save changes during the current transaction.

SQL Syntax:

COMMIT;

For example:

DELETE FROM fields
WHERE field_name='engineering';
COMMIT;
  • The ‘ROLLBACK’ command restores the last transaction.

SQL Syntax;

ROLLBACK;

For example:

DELETE FROM fields
WHERE field_name='engineering';
ROLLBACK;

I have explained different types of SQL commands with examples at a high level. Also, i have explained difference between delete, drop and truncate commands.

However, more commands exist in SQL. 95% of business requirements are fulfilled using the above commands. SQL has become a universal language in the data processing World. A developer of any stream needs an understanding of SQL code.

ETL development comprises of 90% of SQL code. If you are an Informatica ETL developer consider reading the below article.

Informatica Power Center (IPC) related articles:

Thanks for Reading!

Are you looking for job?

Job openings »

 

Leave a Comment