SQL Interview Cheat Sheet

Need to quickly reference all the fundamental knowledge for an SQL interview? Want to refresh your memory about an SQL concept? Our SQL Interview Cheat Sheet is your new best friend!   

Preparing for an SQL interview requires brushing up on your SQL. But it’s very easy to get lost in all the SQL concepts you must review. In such situations, you need an easily browsable resource packed with SQL knowledge. And that’s why we created this SQL Interview Cheat Sheet. If you need to quickly refer to basic SQL concepts, this cheat sheet is for you!

If you need a bit more than just a quick refresher, consider taking our SQL Basics course. It teaches you the fundamentals of SQL, from data retrieval to building simple reports, using 129 coding challenges. Then you can consolidate your knowledge by practicing on the 1,190 coding challenges in our SQL Practice track. You will be prepared for any SQL interview after that!

Without further ado, let’s dive straight into our SQL Interview Cheat Sheet. We’ll start with the most basic question:

What Is SQL?

Structured Query Language, or SQL, is a programming language designed for handling data in databases. It allows users to query, add, change, save, and delete data in a database. It can also be used to create and change database structures.

SQL Table Info

Here’s a visualization of the above terminology.

SQL Commands

SQL Queries

Basic Clauses in an SQL Query

SELECT is the most commonly used statement in SQL queries. This typical SQL query may also include several clauses that allow us to refine query results. Here’s the syntax of an SQL query:

SELECT column_1, 
 column_2, 
 aggregate_function(column_3),
 …
FROM table_1
JOIN table_2 
ON table_1.common_column = table_2.common_column
WHERE column_1 <condition>
GROUP BY column_1, column_2, …
HAVING aggregate_function(column_3) <condition>
ORDER BY column_1 [ASC|DESC];

Here’s an explanation and example of each clause:

SELECT 
  first_name, 
  last_name
FROM employees;

SELECT 
  e.first_name,
  e.last _name,
  d.name
FROM employees e
JOIN department d
ON e.department_id = d.id;

SELECT 
  first_name,
  last_name
FROM employees
WHERE last_name = 'Smith';

SELECT 
  nationality,
  AVG(salary)
FROM employees
GROUP BY nationality;

SELECT 
  nationality,
  AVG(salary)
FROM employees
GROUP BY nationality
HAVING AVG(salary) > 5000;

SELECT 
  first_name,
  last_name
FROM employees
ORDER BY last_name ASC;

JOINs

JOIN is an SQL clause that joins two or more tables, allowing data retrieval from all the joined tables.

You can find explanations and representations of different types of JOINs in the graphic below.

INNER JOIN: Returns only the matching rows from joined tables.

LEFT (OUTER) JOIN: Returns all the rows from the first (left) table and only the matching rows from the second (right) table. The non-matched values from the right table are shown as NULLs.

RIGHT (OUTER) JOIN: Returns all the rows from the second (right) table and only the matching rows from the first (left) table. The non-matched values from the left table are shown as NULLs.

FULL (OUTER) JOIN: Returns all the rows from the left and right table. The non-matched values from either table are shown as NULLs.

Aggregation in SQL

Aggregation is performing calculations on multiple rows and returning a single value that represents a summary or statistical result. It’s also known as  aggregating rows – e.g., counting rows, computing the average or the sum per group of rows, etc.

How to perform data aggregation in SQL: This operation is done using SQL’s aggregate functions; simply pass the argument in the function’s parentheses. In other words, write the name of the column you want to aggregate:

SELECT aggregate_function (column_name) 
FROM table_name;

GROUP BY: Aggregation is typically done in conjunction with the GROUP BY clause. GROUP BY organizes rows with the same column values in one group. Combining aggregate functions with GROUP BY allows you to show labels alongside the aggregated values, as in the graphic below:

HAVING: The HAVING clause filters groups based on the result of an aggregate function; only the records that satisfy the condition are shown in the output.

Here’s an example of how it works:

Aggregate functions: The most common aggregate functions are listed below. You can find more information about aggregate functions in our SQL Aggregate Functions Cheat Sheet.

SELECT 
  country,
  COUNT(id)
FROM orders
GROUP BY country;

SELECT 
  country,
  AVG(value)
FROM orders
GROUP BY country;

SELECT 
  country,
  SUM(value)
FROM orders
GROUP BY country;

SELECT 
  country,
  MIN(value)
FROM orders
GROUP BY country;

SELECT 
  country,
  MAX(value)
FROM orders
GROUP BY country;

For a deep dive into GROUP BY and aggregate functions, check out our article GROUP BY and Aggregate Functions: A Complete Overview.

Set Operators

SQL’s set operators combine the outputs of one or more SELECT statements into one output.

Subqueries

SELECT queries that are embedded in another SQL query are called subqueries.

They can be used in the following main statements:

• SELECT
• INSERT
• UPDATE
• DELETE

The clauses that accept subqueries are:

• SELECT
• FROM
• WHERE
• HAVING
• JOIN

The types of subqueries are:

• Scalar – Returns exactly one value.
• Multi-row – Returns multiple values.
• Correlated – References the main query.

The infographic below demonstrates the different types of subqueries.

Example 1: Scalar subquery

Example 2: Multi-row subquery

Example 3: Correlated subquery

Subquery operators: Subqueries are commonly used with several operators that allow you to compare data from the main query with the subquery output.

SQL Operators and Expressions

Operators in SQL are used for comparison, arithmetic, and logical operations.

Expressions consist of one or more values, operators, functions, and other query components that calculate values and create conditions. The most common expression is CASE WHEN.

Comparison Operators

Comparison operators are used for comparing values within a query.

SELECT 
  first_name,
  last_name
FROM employees
WHERE salary = 5000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE salary < 5000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE salary <= 5000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE salary > 5000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE salary >= 5000

SELECT 
  first_name,
  last_name
FROM employees
WHERE salary <> 5000;

SELECT 
  first_name, 
  last_name
FROM employees
WHERE salary BETWEEN 5000 
  AND 6000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE last_name LIKE 'van%';

SELECT 
  first_name,
  last_name
FROM employees
WHERE last_name ILIKE 'van%';

SELECT 
  first_name,
  last_name
FROM employees
WHERE end_date IS NULL;

SELECT 
  first_name,
  last_name
FROM employees
WHERE end_date IS NOT NULL;

SELECT 
  first_name,
  last_name
FROM employees
WHERE city IN (
  'New York', 
  'Los Angeles', 
  'Baltimore');

Arithmetic Operators

Arithmetic operators are used for performing mathematical operations in SQL.

SELECT 
  first_name,
  last_name,
  salary + bonus
FROM employees;

SELECT 
  first_name,
  last_name,
  vacation_days - used_vacation_day
FROM employees;

SELECT 
  first_name,
  last_name,
  salary*1.03
FROM employees;

SELECT 
  first_name,
  last_name,
  salary/12
FROM employees;

SELECT 
  first_name,
  last_name,
salary * 1.05 ** 3
FROM employees;

SELECT 
  first_name,
  last_name
FROM employees
WHERE id % 2 = 0;

Logical Operators

Logical operators combine multiple conditions.

SELECT 
  first_name,
  last_name
FROM employees
WHERE department = 'IT' 
  AND salary > 5000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE department= 'IT' 
  OR salary > 5000;

SELECT 
  first_name,
  last_name
FROM employees
WHERE NOT department = 'IT';

CASE WHEN

CASE WHEN is a conditional expression that returns specified values in the cases when the condition is and is not met. This is SQL’s implementation of the IF-THEN logic.

The CASE WHEN syntax is:

CASE 
    WHEN condition_1 THEN value_1
    WHEN condition_2 THEN value_2
    ...
    ELSE default_value
END;

• CASE initiates the expression.
• WHEN specifies the condition that has to be met.
• THEN specifies the value that will be returned if the condition is met.
• ELSE specifies the value that will be returned if the condition is not met.
• END terminates the expression.

SQL Functions

SQL functions are named and predefined pieces of code that can be used in an SQL query. These functions accept arguments (written in the function’s parentheses) that tell the function how or where to execute.

The basic types of SQL functions are:

• Text functions
• Numeric functions
• NULL functions

Text Functions

SELECT 
  CONCAT(first_name, 
    ' ', last_name)
FROM employees;

SELECT 
  first_name,
  last_name,
  SUBSTRING(last_name, 4)
FROM employees;

SELECT 
  first_name,
  last_name,
  LENGTH(last_name)
FROM employees;

SELECT 
  first_name,
  last_name,
  LEFT(last_name, 3)
FROM employees;

SELECT 
  first_name,
  last_name,
  RIGHT(last_name, 3)
FROM employees;

SELECT 
  TRIM(first_name),
  TRIM(last_name),
FROM employees;

SELECT 
  first_name,
  last_name,
  REPLACE(department,
    'Acuonting', 'Accounting')
FROM employees; 

SELECT REVERSE(last_name)
FROM employees;

SELECT UPPER(last_name)
FROM employees;

SELECT LOWER(last_name)
FROM employees;

You can find an in-depth review of text functions in our article An Overview of SQL Text Functions.

Numeric Functions

SELECT ABS(-169.23);

SELECT ROUND(169.2389, 3);

SELECT CEILING(169.2389);

SELECT FLOOR(169.2389);

SELECT POWER(5, 4);

SELECT SQRT(25);

SELECT RANDOM();

SELECT LOG(12, 2);

SELECT LN(12);

You can find a detailed dive into numeric functions in our article SQL Numeric Functions.

NULL Functions

SELECT 
  first_name,
  last_name,
  COALESCE(department, 'N/A')
FROM employees;

SELECT 
  first_name,
  last_name
  NULLIF(department, 'N/A')
FROM employees;

DML – Modifying Data in SQL

An overview of three commands used for data modification (DML commands) is given below.

INSERT INTO employees (first_name, 
  last_name, department)
VALUES ('Francis', 'Ford', 'Sales');

UPDATE employees
SET department = 'Accounting'
WHERE department = 'Acuonting';

DELETE
FROM employees
WHERE id = 6;

DDL – Defining Data Structure in SQL

Key DDL (Data Definition Language) concepts are:

• DDL commands
• SQL data types
• SQL constraints

DDL Commands

Here’s an  overview of DDL commands:

CREATE TABLE customers(
  id INT PRIMARY KEY,
  first_name VARCHAR(120),
  last_name VARCHAR(120)
);

DROP TABLE customers;

CREATE VIEW salary_summary AS
SELECT 
  department, 
  AVG(salary)
FROM employees
GROUP BY department;

DROP VIEW salary_summary;

ALTER TABLE employees
RENAME TO salaries;

ALTER TABLE employees
RENAME COLUMN last_name TO surname;

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(10,2);

ALTER TABLE employees
ADD date_of_birth DATE;

ALTER TABLE employees
DROP COLUMN  end_date;

SQL Data Types

Below is an overview of the most common SQL data types.

SQL Constraints

Here’s an overview of the most common SQL constraints:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  value DECIMAL(10,2)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  value DECIMAL(10,2),
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  value DECIMAL(10,2) NOT NULL
);

CREATE TABLE customer(
  id INT PRIMARY KEY,
  name VARCHAR(120),
  nin INT UNIQUE
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  value DECIMAL(10,2),
  order_date DEFAULT CURRENT_DATE
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  value DECIMAL(10,2),
  check(value>0)
);

Additional SQL Interview Resources

Along with this SQL interview cheat sheet, use these additional resources to review SQL interview questions and topics:

1. Complete SQL Practice for Interviews
2. Top 100+ SQL Interview Questions and Practice Exercises
3. The Top 10 SQL JOIN Interview Questions with Answers
4. Top 9 SQL GROUP BY Interview Questions
5. Top 10 SQL Window Functions Interview Questions
6. Top 5 SQL CTE Interview Questions
7. Top 27 Advanced SQL Interview Questions with Answers
8. 15 Tricky SQL Interview Questions for Experienced Users

Going Beyond the SQL Interview Cheat Sheet

We’ve covered all the fundamental concepts in this SQL Interview Cheat Sheet. To impress during your interview, you’ll need to show your mastery of these ideas.

This cheat sheet will help you quickly look up the definition or an example of a particular SQL command. Use it whenever you get stuck on SQL terminology or concepts. However, this is not a standalone learning resource. It’s best used alongside our SQL Basics course, SQL Practice track, or other main resources for learning and practicing SQL.

Combine these materials, keep practicing, and you’ll ace that SQL interview!