SQL Cheat Sheet

SQL Cheat Sheet — Quick Reference Guide

A comprehensive SQL cheat sheet for data analysts, developers, and anyone working with databases. Bookmark this page for quick reference during your work or interview preparation.

SQL Basics

-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2 FROM table_name;

-- Filter with WHERE
SELECT * FROM employees WHERE department = 'Sales';

-- Sort results
SELECT * FROM employees ORDER BY salary DESC;

-- Limit results
SELECT * FROM employees LIMIT 10;

Aggregate Functions

-- Count rows
SELECT COUNT(*) FROM orders;

-- Sum values
SELECT SUM(amount) FROM orders;

-- Average
SELECT AVG(salary) FROM employees;

-- Min and Max
SELECT MIN(salary), MAX(salary) FROM employees;

-- Group By
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- Having (filter after grouping)
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

SQL Joins

-- INNER JOIN (matching rows in both tables)
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN (all rows from left table)
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN (all rows from right table)
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS JOIN (all combinations)
SELECT e.name, p.project_name
FROM employees e
CROSS JOIN projects p;

Subqueries

-- Subquery in WHERE
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (
  SELECT department as dept, AVG(salary) as avg_sal
  FROM employees
  GROUP BY department
) subquery
WHERE avg_sal > 50000;

-- Correlated subquery
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) 
  FROM employees 
  WHERE department = e.department
);

Window Functions

-- ROW_NUMBER
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK (with gaps)
SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- DENSE_RANK (no gaps)
SELECT name, salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- PARTITION BY
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- LAG and LEAD
SELECT name, salary,
  LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
  LEAD(salary) OVER (ORDER BY hire_date) as next_salary
FROM employees;

-- Running Total
SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

String Functions

-- UPPER and LOWER
SELECT UPPER(name), LOWER(email) FROM users;

-- LENGTH
SELECT name, LENGTH(name) as name_length FROM users;

-- SUBSTRING / SUBSTR
SELECT SUBSTRING(name, 1, 5) FROM users;

-- CONCAT
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

-- TRIM
SELECT TRIM(name) FROM users;

-- REPLACE
SELECT REPLACE(phone, '-', '') FROM users;

Date Functions

-- Current date and time
SELECT NOW();
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;

-- Extract parts
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date) FROM orders;

-- Date difference
SELECT DATEDIFF(end_date, start_date) as days_diff FROM projects;

-- Date formatting
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;

-- Add/subtract dates
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) as delivery_date FROM orders;

CTEs (Common Table Expressions)

-- Simple CTE
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 80000
)
SELECT * FROM high_earners WHERE department = 'Engineering';

-- Multiple CTEs
WITH 
dept_avg AS (
  SELECT department, AVG(salary) as avg_sal
  FROM employees GROUP BY department
),
high_dept AS (
  SELECT department FROM dept_avg WHERE avg_sal > 75000
)
SELECT e.* FROM employees e
INNER JOIN high_dept h ON e.department = h.department;

Data Manipulation (DML)

-- INSERT
INSERT INTO employees (name, department, salary)
VALUES ('John Doe', 'Analytics', 75000);

-- UPDATE
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Analytics';

-- DELETE
DELETE FROM employees
WHERE status = 'inactive';

-- UPSERT (MySQL)
INSERT INTO employees (id, name, salary)
VALUES (1, 'John', 80000)
ON DUPLICATE KEY UPDATE salary = 80000;

Download our complete 1000 SQL Interview Questions e-book for more SQL tips and interview preparation!

📥 Download Free SQL E-book