The Ultimate SQL Queries cheatsheet, with examples

Even though each relational database management system has its own customized extensions and functionalities, SQL has a standard and it’s somewhat similar between different database management systems. Once you can understand some common queries listed below, you can easily pick up SQL and interact with data of any database systems.

There are 4 main types of SQL Queries:

Data definition language (DDL)
Data manipulation language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)

And there’s one extra type which is Session Control Statements (SCS).

Let’s go deeper into each category

1. Data Definition Language (DDL)

Data definition statements are used to define the structure of a database or table. It includes these common queries:

CREATE statement to create a new database or table.

CREATE TABLE table_name (
  column_1 datatype_1, 
  column_2 datatype_2, 
  column_3 datatype_3
);

ALTER statement to modify the database structure or table structure. For example, you can add columns to an existing table in a database, rename a column or rename the table.

ALTER TABLE table_name 
ADD column_name datatype;

DROP statement to delete a database or table. Everything will be removed, table definition, data, indexes, triggers, constraints, etc.

DROP TABLE table_name;

TRUNCATE statement to clean the table, remove all the existing records.

TRUNCATE TABLE table_name;

2. Data Manipulation Language (DML)

Data manipulation statements are used for managing data within the table object.

SELECT statement to retrieve data from the table. You can retrieve data of an entire table or just a collection of records based on conditions.

SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
  AND column_2 = value_2;

INSERT statement to insert a set of values into a table.

INSERT INTO table_name (column_1, column_2, column_3, ...)
VALUES (value_1, value_2, value_3, ...);

UPDATE statement to modify existing data and update with new data in a table, based on some conditions.

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, ...
WHERE condition;

DELETE statements to delete the records from the table based on some conditions.

DELETE FROM table_name
WHERE some_column = some_value;

LOCK TABLE statement to lock one or more tables in a specified mode. Table access denied to other users for the duration of your table operation.

3. Data Control Language (DCL)

Data control statements are used to give privileges to access limited data.

GRANT statement to grant privileges to a user for accessing and working with a database. This will allow them to perform some specific tasks.

GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name}  [WITH GRANT OPTION]; 

REVOKE statement to withdraw the privileges previously given to a user.

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}

ANALYZE statement to collect statistical information about index, cluster, table.
AUDIT statement to track the occurrence of a specific SQL statement or all SQL statements during the user sessions.
COMMENT to write a comment to the data table.

4. Transaction Control Language (TCL)

Transaction control language statements are used to apply the changes permanently save into database.

COMMIT command to permanently save the work into the database.
ROLLBACK command to restore the database for the last COMMIT.
SAVEPOINT command to create a temporary state of the database so that you can ROLLBACK when needed.
SET TRANSACTION command to set the transaction properties such as read-write/read-only access.

5. Session Control Statements (SCS)

Session control statements are used to manage properties dynamically of a user session.

ALTER SESSION statement to modify conditions or parameters that affect your database connection.
SET ROLE statement to enable or disable the roles that are currently enabled for the session.

Need a good tool for learning and practising SQL? Use TablePlus.

It’s modern, native tool with elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.  It’s free anyway.