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.
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 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:
Data Type
Used to Store
Integer data (exact numeric)
Integer data (exact numeric)
Integer data (exact numeric)
Integer data (exact numeric)
Numeric data type with a fixed precision and scale (exact numeric)
Numeric data type with a fixed precision and scale (exact numeric)
Floating precision data (approximate numeric)
Monetary (currency) data
Date and time data
Fixed length character data
Variable length character data
Integer data that is a 0 or 1 (binary)
Variable length binary data to store image files
Floating precision number (approximate numeric)
Fixed length binary data
Allows a column to store varying data types
Unique database that is updated every time a row is inserted or updated
Temporary set of rows returned after running a table-valued function (TVF)
Stores xml data
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.
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.
Columns
Data Type
Checks
CREATETABLE HumanResources.EmployeeLeave ( EmployeeID INTNOTNULL, LeaveStartDate DATETIMENOTNULL, LeaveEndDate DATETIMENOTNULL, LeaveReason VARCHAR(100), LeaveType CHAR(2) NOTNULL );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:
Constraint
Description
Syntax
Columns or columns that uniquely identifies each row in a table.
CREATETABLE table_name ( col_name data_type, CONSTRAINT constraint_name PRIMARYKEY (col_name(s)) );Enforces uniqueness on non-primary key columns.
CREATETABLE table_name ( col_name data_type, CONSTRAINT constraint_name UNIQUEKEY (col_name(s)) );Links two tables (parent & child), and ensures the child table’s foreign key is present as the primary key in the parent before inserting data.
CREATETABLE table_name ( col_name data_type, CONSTRAINT constraint_name FOREIGNKEY (col_name) REFERENCES table_name(col_name) );Enforce domain integrity by restricting values that can be inserted into a column.
CREATETABLE table_name ( col_name data_type, CONSTRAINT constraint_name CHECK (expression) );We can use the ALTER TABLE statementto modify a table when:
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;A table can be renamed with the RENAME TABLE statement:
RENAMETABLE old_table_name TO new_table_name;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;Database tables are rarely static and we often need to add new data, change existing data, or remove data using our DML commands.
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];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’;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;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.
StudentID
FirstName
LastName
Marks
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 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 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:
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’;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;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’;We can display retrieved data in a specific order (ascending or descending) with ORDER BY:
SELECT StudentID, LastName FROM Student ORDERBY Marks DESC;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%’;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.
EmployeeID (PK)
FirstName
LastName
Title
EmployeeID (FK)
Department
Salary
The two main types of join are an INNER JOIN and an OUTER 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;An outer join displays the following combined data set:
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;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;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.
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;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.
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] );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] );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] ) );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.
Various built-in functions can be used to customize a result set.
Syntax for Functions:
SELECT function_name (parameters);When our result set contains strings that are char and varchar data types, we can manipulate these string values by using string functions:
Function Name
Example
SELECTleft(‘RICHARD’, 4);
SELECTlen(‘RICHARD’);
SELECTlower(‘RICHARD’);
SELECTreverse(‘ACTION’);
SELECTright(‘RICHARD’, 4);
SELECT ‘RICHARD’ + space(2) + ‘HILL’;
SELECTstr(123.45, 6, 2);
SELECTsubstring(Weather’, 2, 2);
SELECTupper(‘RICHARD’);
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:
Function Name
Parameters
Description
(date part, number, date)
Adds the ‘number’ of date parts to the date
(date part, date1, date2)
Calculates the ‘number’ of date parts between two dates
Returns the date part from a given date as a character value
Returns the date part from a given date as an integer value
Returns the current date and time
Returns an integer to represent the day for a given date
Returns an integer to represents the month for a given date
Returns an integer to represents the year for a given date
We can manipulate numeric data types within our result set by using mathematical functions:
Function Name
Parameters
Description
Returns the absolute value
Returns the arc cos, sin, or tan angle in radians
cos, sin, tan, cot
Returns the cos, sine, tan or cotangent in radians
Returns an angle in degrees converted from radians
Returns the value of e raised to the power of a given number or expression
Returns the largest integer value less than or equal a given value
Returns the natural logarithm of a given value
Returns the constant value of pi which is 3.141592653589793…
Returns the value of a numeric expression raised to to the power of y
Returns an angle in radians converted from degrees
Returns a random float number between 0 and 1 inclusive
Returns a rounded version of a given numeric value to a given integer value for precision
Returns the sign of a given value, which can be positive, negative or zero
Returns the square root of a given value
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:
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.
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:
Function Name
Description
Returns the average from a range of values in a given data set or expression. Can include ALL values or DISTINCT values
Returns the quantity (count) of values in a given data set or expression. Can include ALL values or DISTINCT values
Returns the lowest value in a given data set or expression
Returns the highest value in a given data set or expression
Returns the sum of values in a given data set or expression. Can include ALL values or DISTINCT values
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.
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];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.
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;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.
The concept that an entire transaction must be processed fully, or not at all.
The requirement for a database to be consistent (valid data types, constraints, etc) both before and after a transaction is completed.
Transactions must be processed in isolation and they must not interfere with other transactions.
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.
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.
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.
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.
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.
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: