SQL Cheat Sheet for Quick Reference [PDF Download]

Whether you need a quick reference for major SQL concepts before an interview, or you want to level-up your existing SQL skills, our SQL cheat sheet is the ideal starting point. Plus, we’ve broken it down into 11 essential SQL topics to help you get the most from this SQL query cheat sheet. 

Looking to learn SQL from scratch, or want to know the difference between SQL and MySQL? You should definitely check out the rest of our SQL content. But for now, let’s dive into this SQL commands cheat sheet.

Download Hackr.io’s SQL Cheat Sheet PDF here.

SQL Cheat Sheet

1. What is SQL? Why Do We Need It?

SQL is a database language that’s used to query and manipulate data in a database, while also giving you an efficient and convenient environment for database management.

We can group commands into the following categories of SQL statements:

Data Definition Language (DDL) Commands

• CREATE: creates a new database object, such as a table
• ALTER: used to modify a database object
• DROP: used to delete database objects

Data Manipulation Language (DML) Commands

• INSERT: used to insert a new row (record) in a database table
• UPDATE: used to modify an existing row (record) in a database table
• DELETE: used to delete a row (record) from a database table

Data Control Language (DCL) Commands

• GRANT: used to assign user permissions to access database objects
• REVOKE: used to remove previously granted user permissions for accessing database objects

Data Query Language (DQL) Commands

• SELECT: used to select and return data (query) from a database

Data Transfer Language (DTL) Commands

• COMMIT: used to save a transaction within the database permanently
• ROLLBACK: restores a database to the last committed state

2. SQL Data Types

Data types specify the type of data that an object can contain, such as numeric data or character data. We need to choose a data type to match the data that will be stored using the following list of essential pre-defined data types:

3. Managing Tables

After we’ve created a database, the next step is to create and subsequently manage a database table using a range of our DDL commands.

Create a Table

A table can be created with the CREATE TABLE statement. 

Syntax for CREATE TABLE:

CREATE TABLE table_name

(

col_name1 data_type,

col_name2 data_type,

col_name3 data_type,

…

);

Example: Create a table named EmployeeLeave within the Human Resource schema and with the following attributes.

CREATETABLE HumanResources.EmployeeLeave

(

EmployeeID INTNOTNULL,

LeaveStartDate DATETIMENOTNULL,

LeaveEndDate DATETIMENOTNULL,

LeaveReason VARCHAR(100),

LeaveType CHAR(2) NOTNULL

);

SQL Table Constraints

Constraints define rules that ensure consistency and correctness of data. A CONSTRAINT can be created with either of the following approaches.

CREATETABLEstatement;

ALTERTABLEstatement;

CREATETABLE table_name

( Col_name data_type,

CONSTRAINT constraint_name constraint_type col_name(s) 

);

The following list details the various options for Constraints:

CREATETABLE table_name

(

col_name data_type, 

CONSTRAINT constraint_name

PRIMARYKEY (col_name(s)) 

);

CREATETABLE table_name

(

col_name data_type, 

CONSTRAINT constraint_name

UNIQUEKEY (col_name(s)) 

);

CREATETABLE table_name

(

col_name data_type, 

CONSTRAINT constraint_name

FOREIGNKEY (col_name)

REFERENCES

table_name(col_name)

);

CREATETABLE table_name

(

col_name data_type, 

CONSTRAINT constraint_name

CHECK (expression) 

);

Modifying a Table

We can use the ALTER TABLE statementto modify a table when:

1. Adding a column
2. Altering a column’s data type
3. Adding or removing constraints

Syntax for ALTER TABLE:

ALTERTABLE table_name

ADD column_name data_type;

ALTERTABLE table_name

DROPCOLUMN column_name;

ALTERTABLE table_name

ALTERCOLUMN column_name data_type;

Renaming a Table

A table can be renamed with the RENAME TABLE statement:

RENAMETABLE old_table_name TO new_table_name;

Dropping a Table vs. Truncating a Table

A table can be dropped or deleted by using the DROP TABLE statement:

DROPTABLE table_name;

The contents of a table can be deleted (without deleting the table) by using the TRUNCATE TABLE statement:

TRUNCATETABLE table_name;

The Complete SQL Bootcamp 2024: Go from Zero to Hero

4. Manipulating Data

Database tables are rarely static and we often need to add new data, change existing data, or remove data using our DML commands.

Storing Data in a Table

Data can be added to a table with the INSERT statement.

Syntax for INSERT:

INSERTINTO table_name ( col_name1, col_name2, col_name3… )

VALUES ( value1, value2, value3… );

Example: Inserting data into the Student table.

INSERTINTO Student ( StudentID, FirstName, LastName, Marks )

VALUES ( ‘101’, ’John’, ’Ray’, ’78’ );

Example: Inserting multiple rows of data into the Student table.

INSERTINTO Student 

VALUES ( 101, ’John’, ’Ray’, 78 ),

( 102, ‘Steve’, ’Jobs’, 89 ),

( 103, ‘Ben’, ’Matt’, 77 ),

( 104, ‘Ron’, ’Neil’, 65 ),

( 105, ‘Andy’, ’Clifton’, 65 ),

( 106, ‘Park’, ’Jin’, 90 );

Syntax for copying data from one table to another with the INSERT statement:

INSERTINTO table_name2

SELECT * FROM table_name1

WHERE [condition];

Updating Data in a Table

Data can be updated in a table with the UPDATE statement.

Syntax for UPDATE:

UPDATE table_name

SET col_name1 = value1, col_name2 = value2…

WHERE condition;

Example: Update the value in the Marks column to ‘85’ when FirstName equals ‘Andy’

UPDATE table_name

SET Marks = 85

WHERE FirstName = ‘Andy’;

Deleting Data from a Table

A row can be deleted with the DELETE statement.

Syntax for DELETE:

DELETEFROM table_name

WHERE condition;

DELETEFROM Student

WHERE StudentID = ‘103’;

Remove all rows (records) from a table without deleting the table with DELETE:

DELETEFROM table_name;

5. Retrieving Data

We can display one or more columns when we retrieve data from a table. For example, we may want to view all of the details from the Employee table, or we may want to view a selection of particular columns.

Data can be retrieved from a database table(s) by using the SELECT statement.

Syntax for SELECT:

SELECT [ALL | DISTINCT] column_list

FROM [table_name | view_name]

WHERE condition;

Consider the data and schema for the Student table below.

Retrieving Selected Rows

We can retrieve a selection of rows from a table with the WHERE clause and a SELECT statement:

SELECT * FROM Student 

WHERE StudentID = 104;

Note: We should use the HAVING clause instead of WHERE with aggregate functions.

Comparison Operators

Comparison operators test for the similarity between two expressions.

Syntax for Comparisons:

SELECT column_list FROM table_name

WHERE expression1 [COMP_OPERATOR] expression2;

Example: Various comparison operations.

SELECT StudentID, Marks FROM Student 
WHERE Marks = 90;

SELECT StudentID, Marks FROM Student 
WHERE StudentID > 101;

SELECT StudentID, Marks FROM Student 
WHERE Marks != 89;

SELECT StudentID, Marks FROM Student 
WHERE Marks >= 50;

Logical Operators

Logical operators are used with SELECT statements to retrieve records based on one or more logical conditions. You can combine multiple logical operators to apply multiple search conditions.

Syntax for Logical Operators:

Types of Logical Operator

We can use a range of logical operators to filter our data selections.

Syntax for Logical OR Operator:

SELECT StudentID, Marks FROM Student

WHERE Marks = 40OR Marks = 56OR Marks = 65;

Syntax for Logical AND Operator:

SELECT StudentID, Marks FROM Student

WHERE Marks = 89AND LastName = ‘Jones’;

Syntax for Logical NOT Operator:

SELECT StudentID, Marks FROM Student

WHERENOT LastName = ‘Jobs’;

Range Operations

We can use BETWEEN and NOT BETWEEN statements to retrieve data based on a range.

Syntax for Range Operations:

SELECT column_name1, col_name2… FROM table_name

WHERE expression1 RANGE_OPERATOR expression2 [LOGICAL_OPERATOR expression3…];

Syntax for BETWEEN:

SELECT StudentID, Marks FROM Student

WHERE Marks BETWEEN40AND70;

Syntax for NOT BETWEEN:

SELECT FirstName, Marks FROM Student

WHERE Marks NOTBETWEEN40AND50;

Retrieve Records That Match a Pattern

You can use the LIKE statement to fetch data from a table if it matches a specific string pattern. String patterns can be exact or they can make use of the ‘%’ and ‘_’ wildcard symbols.

Syntax for LIKE with ‘%’:

SELECT * FROM Student 

WHERE FirstName LIKE ‘Ro%’;

Syntax for LIKE with ‘_’:

SELECT *FROM Student 

WHERE FirstName LIKE ‘_e’;

Displaying Data in a Sequence

We can display retrieved data in a specific order (ascending or descending) with ORDER BY:

SELECT StudentID, LastName FROM Student

ORDERBY Marks DESC;

Displaying Data Without Duplication

The DISTINCT keyword can be used to eliminate rows with duplicate values in a particular column.

Syntax for DISTINCT:

SELECT[ALL]DISTINCT col_names FROM table_name

WHERE search_condition;

SELECTDISTINCT Marks FROM Student

WHERE LastName LIKE ‘o%’;

6. SQL JOINS

Joins are used to retrieve data from more than one table where the results are ‘joined’ into a combined return data set. Two or more tables can be joined based on a common attribute.

Consider two database tables, Employees and EmployeeSalary, which we’ll use to demonstrate joins.

Types of JOIN

The two main types of join are an INNER JOIN and an OUTER JOIN.

Inner JOIN

An inner join retrieves records from multiple tables when a comparison operation returns true for a common column. This can return all columns from both tables, or a set of selected columns.

Syntax for INNER JOIN:

SELECT table1.column_name1, table2.colomn_name2,…

FROM table1

INNERJOIN table2 

ON table1.column_name = table2.column_name;

Example: Inner join on Employees & EmployeeSalary tables.

SELECT Employees.LastName, Employees.Title, EmployeeSalary.salary,

FROM Employees

INNERJOIN EmployeeSalary

ON Employees.EmployeeID = EmployeeSalary.EmployeeID;

Outer JOIN

An outer join displays the following combined data set:

• Every row from one of the tables (depends on LEFT or RIGHT join)
• Rows from one table that meets a given condition

An outer join will display NULL for columns where it does not find a matching record.

Syntax for OUTER JOIN:

SELECT table1.column_name1, table2.colomn_name2,… FROM table1

[LEFT|RIGHT|FULL]OUTERJOIN table2

ON table1.column_name = table2.column_name;

LEFT OUTER JOIN: every row from the ‘left’ table (left of the LEFT OUTER JOIN keyword) is returned, and matching rows from the ‘right’ table are returned.

Example: Left outer JOIN.

SELECT Employees.LastName, Employees.Title, EmployeeSalary.salary

FROM Employees 

LEFTOUTERJOIN EmployeeSalary

ON Employees.EmployeeID = EmployeeSalary.EmployeeID;

RIGHT OUTER JOIN: every row from the ‘right’ table (right of the RIGHT OUTER JOIN keyword) is returned, and matching rows from the ‘left’ table are returned.

Example: Right outer JOIN.

SELECT Employees.LastName, Employees.Title, EmployeeSalary.salary

FROM Employees 

RIGHTOUTERJOIN EmployeeSalary

ON Employees.EmployeeID = EmployeeSalary.EmployeeID;

FULL OUTER JOIN: returns all the matching and non-matching rows from both tables, with each row being displayed no more than once.

Example: Full outer JOIN.

SELECT Employees.LastName, Employees.Title, EmployeeSalary.salary

FROM Employees 

FULLOUTERJOIN EmployeeSalary

ON Employees.EmployeeID = EmployeeSalary.EmployeeID;

Cross JOIN

Also known as the Cartesian Product, a CROSS JOIN between two tables (A and B) ‘joins’ each row from table A with each row in table B, forming ‘pairs’ of rows. The joined dataset contains ‘combinations’ of row ‘pairs’ from tables A and B.

The row count in the joined data set is equal to the number of rows in table A multiplied by the number of rows in table B.

Syntax for CROSS JOIN:

SELECT col_1, col_2 FROM table1

CROSSJOIN table2;

Equi JOIN

An EQUI JOIN is one which uses an EQUALITY condition for the table keys in a JOIN operation. This means that INNER and OUTER JOINS can be EQUI JOINS if the conditional clause is an equality.

Self JOIN

A SELF JOIN is when you join a table with itself. This is useful when you want to query and return correlatory information between rows in a single table. This is helpful when there is a ‘parent’ and ‘child’ relationship between rows in the same table.

Example: if the Employees table contained references that links employees to supervisors (who are also employees in the same table). 

To prevent issues with ambiguity, it’s important to use aliases for each table reference when performing a SELF JOIN.

Syntax for SELF JOIN:

SELECT t1.col1 AS “Column 1”, t2.col2 AS “Column 2” 

FROM table1 AS t1 

JOIN table1 AS t2

WHERE condition;

7. SQL Subqueries

An SQL statement that is placed within another SQL statement is a subquery.

Subqueries are nested inside WHERE, HAVING or FROM clauses for SELECT, INSERT, UPDATE, and DELETE statements.

• Outer Query: represents the main query
• Inner Query: represents the subquery

Using the IN Keyword

We can use the IN keyword as a logical operator to filter data for a main query (outer query) against a list of subquery results. This because a subquery will be evaluated first due to inner nest position. This filtering is part of the main query’s conditional clause.

Example: run a subquery with a condition to return a data set. The subquery results then become part of the main query’s conditional clause. We can then use the IN keyword to filter main query results against subquery results for a particular column(s).

Syntax for IN keyword:

SELECT column_1 FROM table_name

WHERE column_2 [NOT]IN

( SELECT column_2

FROM table_name [WHERE conditional_expression] );

Using the EXISTS Keyword

We can use the EXISTS keyword as a type of logical operator to check whether a subquery returns a set of records. This means that the operator will return TRUE if the evaluated subquery returns any rows that match the subquery statement.

We can also use EXISTS to filter subquery results based on any provided conditions. You can think of it like a conditional ‘membership’ check for any data that is processed by the subquery statement. 

Syntax for EXISTS keyword:

SELECT column FROM table_name

WHEREEXISTS

( SELECT column_name FROM table_name [WHERE condition] );

Using Nested Subqueries

Any individual subquery can also contain one or more additionally nested subqueries. This is similar to nesting conditional statements in traditional programming, which means that queries will be evaluated from the innermost level working outwards.

We use nested subqueries when the condition of one query is dependent on the result of another, which in turn, may also be dependent on the result of another etc.

Syntax for Nested Subqueries:

SELECT col_name FROM table_name

WHERE col_name(s) [LOGICAL | CONDITIONAL | COMPARISON OPERATOR]

( SELECT col_name(s) FROM table_name

WHERE col_name(s) [LOGICAL | CONDITIONAL | COMPARISON OPERATOR]

( SELECT col_name(s) FROM table_name

WHERE [condition] )

);

Correlated Subquery

A correlated subquery is a special type of subquery that uses data from the table referenced in the outer query as part of its own evaluation.

8. Using Functions to Customize a Result Set

Various built-in functions can be used to customize a result set.

Syntax for Functions:

SELECT function_name (parameters);

Using String Functions

When our result set contains strings that are char and varchar data types, we can manipulate these string values by using string functions:

Using Date Functions

When our result set contains date and time data, we may want to manipulate it to extract the day, month, year, or time, and we may also want to parse date-like data into a datetime data type. We can do this by using date functions:

Using Mathematical Functions

We can manipulate numeric data types within our result set by using mathematical functions:

Using Ranking Functions

Ranking functions (also known as window functions) generate and return sequential numbers to represent a rank for each based on a given criteria. To rank records, we use the following ranking functions:

• row_number() : returns sequential numbers starting at 1, for each row in in a result set based on a given column
• rank() : returns the rank of each row in a result set based on specified criteria (can lead to duplicate rank values)
• dense_rank() : used when consecutive ranking values are needed for a given criteria (no duplicate rank values)

Each ranking function uses the OVER clause to specify the ranking criteria. Within this, we choose a column to use for assigning a rank along with the ORDER BY keyword to determine whether ranks should be applied based on ascending or descending values.

Using Aggregate Functions

Aggregate functions summarize values for a column or group of columns to produce a single (aggregated) value.

Syntax for Aggregate Functions:

SELECTAGG_FUNCTION( [ALL |DISTINCT] expression )

FROM table_name;

The table below summarizes the various SQLaggregate functions:

9. Grouping Data

We have the option to group data in our result set based on a specific criteria. We do this by using the optional GROUP BY, COMPUTE, COMPUTE BY, and PIVOT clauses with a SELECT statement.

GROUP BY Clause

When used without additional criteria, GROUP BY places data from a result set into unique groups. But when used with an aggregate function, we can summarize (aggregate) data into individual rows per group.

Syntax for GROUP BY:

SELECT column(s) FROM table_name

GROUPBY expression

[HAVING search_condition];

COMPUTE and COMPUTE BY Clause

We can use the COMPUTE clause with a SELECT statement and an aggregate function to generate summary rows as a separate result from our query. We can also use the optional BY keyword to calculate summary values on a column–by-column basis.

Syntax for COMPUTE [BY]:

SELECT column(s) FROM table_name

[ORDERBY column_name]

COMPUTE [BY column_name]AGG_FUNCTION(column_name)

Note: support for this keyword was dropped by MS SQL Server in 2012. 

PIVOT Clause

The PIVOT operator is used to transform unique rows into column headings. You can think of this as rotating or pivoting the data into a new ‘pivot table’ that contains the summary (aggregate) values for each rotated column. With this table, you can examine trends or summary values on a columnar basis.

Syntax for PIVOT:

SELECT * FROM table_name

PIVOT ( AGG_FUNCTION (value_column) 

FOR pivot_column

IN column(s) ) 

AS pivot_table_alias;

10. The ACID Property

The term ACID stands for Atomicity, Consistency, Isolation, and Durability. These individual properties represent a standardized group that are required to ensure the reliable processing of database transactions.

Atomicity

The concept that an entire transaction must be processed fully, or not at all.

Consistency

The requirement for a database to be consistent (valid data types, constraints, etc) both before and after a transaction is completed.

Isolation

Transactions must be processed in isolation and they must not interfere with other transactions.

Durability

After a transaction has been started, it must be processed successfully. This applies even if there is a system failure soon after the transaction is started.

11. RDBMS

A Relational Database Management System (RDBMS) is a piece of software that allows you to perform database administration tasks on a relational database, including creating, reading, updating, and deleting data (CRUD).

Relational databases store collections of data via columns and rows in various tables. Each table can be related to others via common attributes in the form of Primary and Foreign Keys. 

Conclusion

That’s all for our SQL cheat sheet. Whether you’re looking for an SQL cheat sheet for interviews, you’d like to boost your existing SQL skills, or you need an SQL basics cheat sheet to help you learn SQL for the first time, we hope it helps you!

Remember, our SQL cheat sheet PDF is a helpful tool, but what really matters is that you put all of this theory into practice. As always, it’s the best way to learn! 

If you want to learn SQL, check out the best SQL courses available online. 

You can also download Hackr.io’s SQL injection cheat sheet.

Frequently Asked Questions

1. What is an SQL Syntax Cheat Sheet?

An SQL cheat sheet is a brief summary of all the major concepts of the language for ease of reference. The SQL cheat sheet we have detailed above covers all the essential concepts.

2. How do I Memorize SQL Queries?

Practice is the best way to memorize SQL queries. You’ll find that it’s a lot easier to learn when you put them to actual use.

3. What are the 5 basic SQL commands?

A basic SQL commands cheat sheet is useful for quick learning. The 5 basic SQL command groups are Data Definition Language, Data Manipulation Language, Data Control Language, Data Query Language, and Data Transfer Language.

People are also reading:

• Best SQL Courses
• Best SQL Server Certifications
• SQL Certifications
• Best SQL Books
• SQL Interview Questions
• SQL Commands
• Difference between NoSQL vs MySQL
• DBMS Interview Questions
• Normalization in DBMS
• What is MongoDB?