What is DDL, DML, DQL and DCL in SQL?

SQL stands for Structured Query Language and it is a comprehensive language for manipulating databases. SQL can be used to create, modify, query, and control data in database systems such as PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database. However, SQL is not a single monolithic language; it can be divided into four sublanguages that correspond to different types of operations on the database. These sublanguages are:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Data Control Language (DCL)

In this blog post, I will try to explain what these sublanguages are with some easy to understand examples and what commands or attributes they include.

Data Definition Language (DDL)

DDL stands for Data Definition Language or Data Description Language. It is the sublanguage that deals with the structure and schema of the database objects, such as tables, indexes, views, functions, procedures, and triggers. DDL commands can be used to create, modify, or delete these objects in the database. Some of the common DDL commands are:

• CREATE: This command is used to create a new database object.
• ALTER: This command is used to modify an existing database object.
• DROP: This command is used to delete an existing database object.
• TRUNCATE: This command is used to remove all data from a table without deleting the table itself.
• COMMENT: This command is used to add comments or descriptions to the database objects.
• RENAME: This command is used to change the name of a database object.

For example,

-- Create a table named customers with four columns
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
phone VARCHAR(15)
);

-- Add a column named address to the customers table
ALTER TABLE customers ADD address VARCHAR(100);

-- Delete the customers table
DROP TABLE customers;

-- Remove all data from the customers table
TRUNCATE TABLE customers;

-- Add a comment to the customers table
COMMENT ON TABLE customers IS 'This table stores customer information';

-- Rename the customers table to clients
RENAME TABLE customers TO clients;

Data Manipulation Language (DML)

DML stands for Data Manipulation Language. It is the sublanguage that deals with the data stored in the database objects. DML commands can be used to insert, update, delete, or lock data in tables or views. Some of the common DML commands are:

• INSERT: This command is used to insert new data into a table or view.
• UPDATE: This command is used to modify existing data in a table or view.
• DELETE: This command is used to remove existing data from a table or view.
• LOCK: This command is used to control concurrency and prevent data corruption by locking tables or rows.

For example,

-- Insert a new record into clients table
INSERT INTO clients (id,name,email) VALUES (1,'Alice','alice@example.com');

-- Update email address of client with id 1
UPDATE clients SET email = 'alice@gmail.com' WHERE id = 1;

-- Delete client with id 1 from clients table
DELETE FROM clients WHERE id = 1;

-- Lock clients table for exclusive access
LOCK TABLE clients IN EXCLUSIVE MODE;

Data Query Language (DQL)

DQL stands for Data Query Language. It is the sublanguage that deals with querying or retrieving data from tables or views. The main DQL command is SELECT which can be used with various clauses and operators to filter, sort, group, join, and aggregate data according to different criteria. Some of these clauses are:

• WHERE: This clause specifies conditions for filtering rows based on column values.
• ORDER BY: This clause specifies how to sort rows based on one or more columns.
• GROUP BY: This clause specifies how to group rows based on one or more columns and apply aggregate functions such as SUM, COUNT, AVG, MIN,or MAX.
• HAVING: This clause specifies conditions for filtering groups based on aggregate values.
• JOIN: This clause specifies how to combine rows from two or more tables based on common columns.
• DISTINCT: to eliminate duplicate rows
• LIMIT: to limit the number of rows returned
• AS: to assign aliases to columns or tables

For example, suppose you have a database called library that contains four tables: authors, books, publishers, and loans. You can use DQL commands to answer questions like:

• What are the names and ISBNs of all books written by Gabriel García Márquez?

SELECT books.name AS book_name, books.isbn AS book_isbn
FROM books JOIN authors ON books.author_id = authors.id
WHERE authors.name = 'Gabriel García Márquez';

• How many books have been loaned by each customer?

SELECT customers.name AS customer_name, COUNT(loans.book_id) AS loan_count
FROM customers JOIN loans ON customers.id = loans.customer_id
GROUP BY customers.id;

• Which publisher has published the most books?

SELECT publishers.name AS publisher_name, COUNT(books.id) AS book_count
FROM publishers JOIN books ON publishers.id = books.publisher_id
GROUP BY publishers.id
ORDER BY book_count DESC
LIMIT 1;

DQL commands are essential for querying data from a database and performing analysis on it. You can learn more about DQL commands and their syntax from various online resources .

Data Control Language (DCL)

Data Control Language (DCL) is a set of commands that enable us to manage the access and security of data and database objects. It allows us to grant or revoke permissions to users or roles on tables, views, functions and other database objects.

DCL is mainly used for enforcing data security and protecting data from unauthorized access or modification. It is also useful for delegating tasks and responsibilities to different users or roles according to their needs and privileges.

The main DCL commands in SQL are:

• GRANT: This command allows us to give access or privileges to specific users or roles on certain database objects. For example, we can use GRANT to allow a user to select, insert, update or delete data from a table, or to execute a function or procedure.

• REVOKE: This command allows us to take back or remove the access or privileges that were previously granted by using GRANT. For example, we can use REVOKE to deny a user from selecting, inserting, updating or deleting data from a table, or from executing a function or procedure.

• DENY: This command allows us to explicitly block access or privileges for specific users or roles on certain database objects. For example, we can use DENY to prevent a user from selecting data from a view, even if they have been granted SELECT permission on the underlying table.

The general syntax for writing DCL commands in SQL is:

sql
GRANT <privileges> ON <object name> TO <user/role>;
REVOKE <privileges> ON <object name> FROM <user/role>;
DENY <privileges> ON <object name> TO <user/role>;

Where:

• <privileges> are the actions that can be performed on the database object, such as SELECT, INSERT, UPDATE, DELETE, EXECUTE etc.
• <object name> is the name of the database object that we want to grant/revoke/deny access on such as table name , view name , function name etc.
• <user/role> is the name of the user account or role that we want to grant/revoke/deny access for such as username , rolename etc.

Let see some examples of how we can use DCL commands in SQL:

• To grant SELECT and INSERT permissions on product_details table to Epsol user account , we can write:

GRANT SELECT , INSERT ON product_details TO Epsol;

• To revoke all permissions on product_stock table from Luc user account , we can write:

REVOKE ALL PRIVILEGES ON product_stock FROM Luc;

These are the four main sublanguages in SQL that help us categorize different types of operations on databases. By learning these sublanguages, we can become more proficient in using SQL for various purposes. I hope this article may have helped you in your pursuit of better understanding SQL, if so, please check out my other articles and follow me for more such content.