Can you explain the difference between DDL, DML, and DCL in PostgreSQL? — PostgreSQL Interview Question #11

In PostgreSQL, like in many other relational database management systems, there are three categories of SQL statements that perform different operations: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). Here is a brief explanation of each category:

1. Data Definition Language (DDL): DDL statements are used to define the structure of the database schema, including tables, columns, indexes, and constraints. Examples of DDL statements in PostgreSQL are CREATE, ALTER, and DROP. These statements are used to create and modify database objects.

Example DDL statement:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

2. Data Manipulation Language (DML): DML statements are used to manipulate the data stored in the database. Examples of DML statements in PostgreSQL are SELECT, INSERT, UPDATE, and DELETE. These statements are used to add, modify, and remove data from database tables.

Example DML statement:

INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');

3. Data Control Language (DCL): DCL statements are used to manage the access granted to database users and roles. Examples of DCL statements in PostgreSQL are GRANT and REVOKE. These statements are used to grant and revoke privileges to database objects.

Example DCL statement:

GRANT SELECT ON users TO marketing;

In summary, DDL is used to define and modify the structure of the database schema, DML is used to manipulate the data stored in the database, and DCL is used to manage the access granted to database users and roles.