DBMS - Types of SQL Commands

Developers and analysts use the structured query language (SQL) to interact with relational databases. SQL is made up of different types of sub-languages that let us perform different tasks. Think of it like a toolkit; each tool is specially used for a particular job. Read this chapter to get a clear understanding of the types of SQL languages and how they are used.

The Four Types of SQL Languages

SQL can be divided into four main types −

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

These are used for distinct purpose. Let us see them one by one.

Data Definition Language (DDL)

DDL commands are used for defining and modifying the structure of a database. They help in setting up the foundation of the database. DDL commands are is used for creating tables, adding or deleing columns, and changing table properties.

Common Commands in DDL

Here's a list of the common commands used in DDL −

• CREATE − To make new tables, databases, or other structures
• ALTER − To modify an existing structure, like adding a column
• DROP − To delete tables or databases
• TRUNCATE − To quickly empty a table without removing its structure

Example: Setting Up a Table

Let's understand the steps by setting up a table −

CREATE TABLE Customers (
   customer_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100) UNIQUE
);

Here, we are creating a table called Customers. It has columns for customer ID, first name, last name, and email. Observe closely how we define the data types: INT for numbers and VARCHAR for text.

Modifying an Existing Table

Sometimes we need to change an existing table. For example, if you need to add a phone number column in the Customers table, then you can use the ALTER command −

ALTER TABLE Customers
ADD phone VARCHAR(15);

Data Manipulation Language (DML)

DML commands are used for querying the tables and fetching relevant data. When the tables are set up, we actually work with the data inside them. It is all about adding, updating, deleting, and fetching the stored data.

Following are the common commands in DML −

• INSERT − To add new data
• UPDATE − To change existing data
• DELETE − To remove data
• SELECT − To retrieve data

Example: Adding Data

Let's add a new row in our existing Customers table −

INSERT INTO Customers (customer_id, first_name, last_name, email, phone)
VALUES (1, 'Alice', 'Johnson', 'alice.johnson@example.com', '123-456-7890');

It adds a new row for Alice.

Fetching Data

To know what is inside the Customers table, we can use the SELECT command −

SELECT first_name, last_name, email
FROM Customers;

It fetches the first name, last name, and email of all customers.

Updating the Records

If Alice changes her phone number, we can update it in the table −

UPDATE Customers
SET phone = '987-654-3210'
WHERE customer_id = 1;

Deleting the Records

Let us say Alice asks to remove her data entirely, then you would use the following command −

DELETE FROM Customers
WHERE customer_id = 1;

Data Control Language (DCL)

DCL commands are all about managing the access. It is like setting up locks and keys to make sure only authorized people can access or change the precious data.

Following are the common commands in DCL −

• GRANT − To give permissions
• REVOKE − To take permissions away

Example: Granting Access

Suppose you want to give a new employee permission to view the Customers data, then you would use the following command −

GRANT SELECT ON Customers TO new_employee;

Revoking Access

If an employee leaves, then you can take back their access using the following command −

REVOKE SELECT ON Customers FROM new_employee;

DCL ensures the database is secure and only the authorized people have the right access.

Transaction Control Language (TCL)

Transactions are like bundles of database actions that should be treated as a single unit. TCL commands ensure that these actions are completed fully or not at all. TCL commands help keep the database consistent.

Following are the common commands in TCL –

• COMMIT − To save the changes permanently
• ROLLBACK − To undo the changes
• SAVEPOINT − To set a checkpoint in a transaction

Example: A Transaction in Action

Imagine we are processing an order where stock quantities need to be updated. Now, an order record needs to be created. These actions must be carried out together −

BEGIN TRANSACTION;

UPDATE Products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 101;

INSERT INTO Orders (order_id, customer_id, order_date, status, total_amount)
VALUES (1001, 1, NOW(), 'Pending', 99.99);

COMMIT;

If something goes wrong during the transaction, we can roll it back −

ROLLBACK;

How Do Different Types of SQL Commands Work Together?

All the four types SQL sub-languages (DDL, DML, DCL, and TCL) work in unison to provide seamless support and service to the end-users.

• We can use DDL to set up the database structure.
• DML helps us to populate and manage the data.
• DCL secures the data by controlling who can access it.
• TCL ensures the database stays consistent during critical operations.

Let us see at a practical scenario using an e-commerce example −

• We create a Products table using DDL.
• We insert product details using DML.
• We grant the team permission to view and update the Products table using DCL.
• During a big sale, we process orders and during such events, TCL ensures data consistency.

You can mix and match these different types of SQL commands to handle a variety of database tasks. Whether you are building a system from scratch, have some troubleshooting issues, or need to optimize the performance of an existing database, you will invariably need to use these SQL commands. For instance, without DDL, we cannot even have a database structure. Without DML, the tables would just sit there. DCL ensures the data is not tampered with, while TCL protects it from corruption during complex operations.

Conclusion

In this chapter, we explained in detail the different types of languages in SQL and how they work. We started with the Data Definition Language (DDL) and understood how it is used in creating and modifying the database structures. Thereafter, we explored Data Manipulation Language (DML), which helps us to manage the data itself. Finally, we covered Data Control Language (DCL) to secure the database, and Transaction Control Language (TCL) to keep the data consistent during critical operations.