SQL statement types

SQL stands for Structured Query Language and can be used to communicate with a relational database. Almost every backend developer should have had contact with one or other form of SQL depening on the underlying database system.

SQL statements can be grouped into three main groups:

Data Definition Language (DDL) - We can use DDL statements to interact with the database in order to create, modify, and delete tables and other database objects from the database. Some of the common DDL statements are:

Statement	Usage
CREATE	To create a new database object, like a table, view or stored procedure
ALTER	To modify an existing database object, for example changing type of a column of a table
RENAME	To rename an existing database object
DROP	To drop/delete an existing database object

Table 1: Common DDL Statements

Example Create Statement

CREATE TABLE Person
(
    ID INT PRIMARY KEY,
    Name VARCHAR(40) NOT NULL,
    Age DECIMAL NULL,
    Country VARCHAR(128) NOT NULL,
);

The datatypes that are avialable for columns may differ from one database system to another.

Data Control Language (DCL) - We can use DCL statements to manage access to objects in a database. Some of the common DCL statements are:

Statement	Usage
GRANT	To grant permission to a database object to perform a specific action or actions
DENY	To deny permission to a database object to perform a specific action or actions
REVOKE	To revoke a previously granted permission

Table 1: Common DCL Statements

Example Grant Statement

GRANT SELECT, INSERT
ON Person
TO user1;

Data Manipulation Language (DML) - We can use DML statements to manipulate the rows of data in the tables. They enable us to create new rows, retrieve, modify or delete existing rows as well. These are also the most commonly used statements. Some of the common DML statements are:

Statement	Usage
INSERT	To create a new row in a table
SELECT	To read rows from a table
DELETE	To delete existing rows from a table
UPDATE	To modfiy existing rows in a table

Table 1: Common DML Statements

Example Select Statement

SELECT *
FROM Person
WHERE Name = 'Example';