The four pillars of SQL (2024)

Rohit Sharma

4 min read

Jan 2, 2024

SQL, Call it “S-Q-L” or “Sequl.”

It seems that SQL has four pillars: DDL, DML, DCL and TCL.

So, let’s break it down one by one:

Data Definition Language (DDL): The first pillar of SQL relates to Databases and Tables.

This contains the following sub topics:

A. Create a Database: This is used to create databases or tables using SQL.
The code for this is given below-

--This will create a database named Medium
Create Database Medium;
Create Table Medium_blogs;
--This will create a table named Medium_blogs

B. Alter Databases: This is used to modify the database that you have just created.
The code for this is given below-

--This piece of code is used to change the name of database called medium1_name
--to medium2_name.
ALTER DATABASE medium1_name
MODIFY NAME = medium2_name;

C. Truncate: This command is used to remove all the rows from the table.
The code for this is given below-

--This will remove all the rows from the table medium.
TRUNCATE TABLE medium;

D. Drop: This command is used to drop databases, tables, or any other entities related with databases.
The code for this is given below-

--This is used to drop the database named medium.
DROP DATABASE medium;
--this is used to drop the table named medium_article.
DROP TABLE medium_article;

2. Data Manipulation Language (DML): The second pillar that is holding SQL stand is the DML which mostly relates to operations we perform on tables present in the database that we have created.

A. Insert: This command is used to insert data into table we created in the databases.
The code for inserting data into these tables is given below-

--This creates table named users with columnslike name, age and email.
Create Table medium_authors(name VARCHAR(255), 
 age Integer, 
 email VARCHAR(255)
);--Inserting data into the 'medium_authors' table
INSERT INTO medium_authors (name, age, email) VALUES
 ('X', 25, 'x@gmail.com'),
 ('Y', 30, 'y@gmail.com'),
 ('Z', 22, 'z@gmail.com');

B. Modify: This command is used to modify the data that you have inserted in the table.
The code for modifying the data in the table is given below-

--Update the age for a specific medium author in table medium_authors
UPDATE medium_authors
SET age = 26
WHERE name = 'X';

C. Delete: This command is used to delete the data that you have been already inserted into the table.
The code for this is given below-

--Delete a specific row based on a condition
DELETE FROM medium_authors WHERE name='X';--Delete all rows in the 'medium_authors' table
DELETE FROM medium_authors;

D. Select: This is often combined with one of another SQL pillar called as Data Query Language (DQL) but we have kept in DML only.
‘Select’ command in SQL is used to retrieve data from one or more tables in a database.
The code for the ‘Select’ command is given below-

--Retrieve all data from medium_authors
SELECT * FROM medium_authors;--Retrieve specific columns of data for medium_authors
SELECT name, age FROM medium_authors;
--Retrieve specific columns with a condition
SELECT name, email FROM medium_authors WHERE age > 25;

3. Data Control Language (DCL): The third pillar of SQL acts like the security guard for the SQL and is related with the access that we provide or revoke from someone.

A. Grant: The ‘Grant’ command is used to provide specific privileges or permissions to a user or a group of users. Privileges can include the ability to perform operations like SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc., on database objects.
The code for ‘Grant’ command is given below-

--This command is used to grant 'select' permission to the sql_user2 for the
--table medium_authors
GRANT SELECT ON medium_authors TO sql_user2;

B. Revoke: The ‘Revoke’ command is used to revoke previously granted privileges or permissions from a user or a group of users.
The code for ‘Revoke’ command is given below-

--Revoking SELECT permission on a medium_authors table from a 'sql_user2' user
REVOKE SELECT ON medium_authors FROM sql_user2;

4. Transaction Control Language (TCL): This is the last pillar of the SQL and it controls transactions that happen in SQL. Imagine you are doing money transaction from one bank to the another, in SQL the transactions happens in terms of transferring data from one table to another.

A. Commit: Just like committing a transaction in a bank finalizes the money transfer, a ‘Commit’ statement in SQL finalizes the changes made during a transaction. Once committed, the changes are permanent.
The code for ‘Commit’ command is given below-

--First we will update the information in medium_authors table and then commit
UPDATE medium_authors
SET age = 30
WHERE name = 'Z';--Commit the transaction to make the changes permanent
COMMIT;

B. Rollback: On the other hand, a ‘Rollback’ statement in SQL is similar to undoing a financial transaction. It reverts the database to the state it was in before the transaction started, just like canceling a money transfer.
The code for ‘Rollback’ command is given below-

--Start a transaction
BEGIN TRANSACTION;--Update the age column with an invalid value
UPDATE medium_authors
SET age = 'abc'
WHERE name = 'Z';
--If an error occurs, rollback the transaction
ROLLBACK;
--Check the status after the transaction (it should remain unchanged due to rollback)
SELECT * FROM medium_authors;

So, these were the four pillars of SQL.

Let me know what other pillars you know of?