SQL Cheatsheet

REFERENCE vSQL (ANSI)

SQL Quick Reference

Everything you need day‑to‑day – querying, joins, aggregations, and database management.

Database Basics

-- Create a database
CREATE DATABASE database_name;

-- Drop a database
DROP DATABASE database_name;

-- Use a database
USE database_name;

-- Show all databases
SHOW DATABASES;        -- MySQL
\l                     -- PostgreSQL

-- Show current database
SELECT DATABASE();     -- MySQL
SELECT CURRENT_DATABASE(); -- PostgreSQL

Table Operations

CREATE TABLE

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2) DEFAULT 0.00,
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

-- Add column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Modify column
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);

-- Rename column
ALTER TABLE employees RENAME COLUMN name TO full_name;  -- MySQL
ALTER TABLE employees RENAME COLUMN name TO full_name;  -- PostgreSQL

-- Drop column
ALTER TABLE employees DROP COLUMN phone;

-- Add constraint
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- Drop constraint
ALTER TABLE employees DROP INDEX unique_email;  -- MySQL
ALTER TABLE employees DROP CONSTRAINT unique_email; -- PostgreSQL

DROP TABLE

-- Drop table
DROP TABLE employees;

-- Drop table if exists
DROP TABLE IF EXISTS employees;

-- Truncate (remove all rows, keep structure)
TRUNCATE TABLE employees;

CRUD Operations

INSERT

-- Insert a single row
INSERT INTO employees (name, email, department, salary, hire_date)
VALUES ('Alice Johnson', 'alice@company.com', 'Engineering', 75000.00, '2024-01-15');

-- Insert multiple rows
INSERT INTO employees (name, email, department, salary, hire_date) VALUES
    ('Bob Smith', 'bob@company.com', 'Marketing', 65000.00, '2024-02-01'),
    ('Charlie Brown', 'charlie@company.com', 'Engineering', 80000.00, '2024-02-15');

-- Insert from another table
INSERT INTO employees_archive
SELECT * FROM employees WHERE hire_date < '2023-01-01';

SELECT

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT name, email, salary FROM employees;

-- Select distinct values
SELECT DISTINCT department FROM employees;

-- Select with alias
SELECT name AS full_name, salary * 1.1 AS new_salary FROM employees;

-- Select with LIMIT
SELECT * FROM employees LIMIT 10;

-- Select with OFFSET (pagination)
SELECT * FROM employees LIMIT 10 OFFSET 20;  -- MySQL, PostgreSQL
SELECT * FROM employees LIMIT 10, 20;        -- MySQL (alternative)

WHERE Clause

-- Basic comparison
SELECT * FROM employees WHERE salary > 70000;
SELECT * FROM employees WHERE department = 'Engineering';

-- Multiple conditions
SELECT * FROM employees
WHERE salary > 70000 AND department = 'Engineering';

SELECT * FROM employees
WHERE salary > 70000 OR department = 'Marketing';

-- Pattern matching (LIKE)
SELECT * FROM employees WHERE name LIKE 'A%';      -- starts with A
SELECT * FROM employees WHERE name LIKE '%son';    -- ends with son
SELECT * FROM employees WHERE name LIKE '%bob%';   -- contains bob

-- IN operator
SELECT * FROM employees WHERE department IN ('Engineering', 'Marketing');

-- BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 60000 AND 80000;

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE email IS NULL;
SELECT * FROM employees WHERE email IS NOT NULL;

-- EXISTS
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM projects p WHERE p.employee_id = e.id);

ORDER BY

-- Ascending (default)
SELECT * FROM employees ORDER BY salary;

-- Descending
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM employees ORDER BY department, salary DESC;

GROUP BY & HAVING

-- Group by department
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Group by with HAVING (filter groups)
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 70000;

UPDATE

-- Update a single row
UPDATE employees SET salary = 80000 WHERE id = 1;

-- Update multiple rows
UPDATE employees SET is_active = FALSE WHERE hire_date < '2023-01-01';

-- Update with calculation
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering';

DELETE

-- Delete a single row
DELETE FROM employees WHERE id = 10;

-- Delete multiple rows
DELETE FROM employees WHERE department = 'Intern';

-- Delete all rows
DELETE FROM employees;

Joins

INNER JOIN

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN (OUTER)

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

RIGHT JOIN (OUTER)

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

FULL OUTER JOIN

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

-- MySQL doesn't support FULL OUTER JOIN directly
-- Use UNION of LEFT JOIN and RIGHT JOIN

CROSS JOIN

SELECT e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

SELF JOIN

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Multiple Joins

SELECT e.name, d.department_name, p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON e.id = p.employee_id;

Subqueries

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

-- Subquery with IN
SELECT name, department
FROM employees
WHERE department IN (SELECT department FROM departments WHERE location = 'New York');

-- Subquery with EXISTS
SELECT name
FROM employees e
WHERE EXISTS (SELECT 1 FROM projects p WHERE p.employee_id = e.id);

-- Subquery in SELECT
SELECT name,
       (SELECT COUNT(*) FROM projects p WHERE p.employee_id = e.id) AS project_count
FROM employees e;

-- Subquery in FROM (derived table)
SELECT department, avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department) AS dept_stats
WHERE avg_salary > 70000;

Set Operations

-- UNION (combine results, remove duplicates)
SELECT name, email FROM employees
UNION
SELECT name, email FROM former_employees;

-- UNION ALL (combine results, keep duplicates)
SELECT name, email FROM employees
UNION ALL
SELECT name, email FROM former_employees;

-- INTERSECT (rows in both queries)
SELECT name FROM employees
INTERSECT
SELECT name FROM contractors;

-- EXCEPT / MINUS (rows in first query not in second)
SELECT name FROM employees
EXCEPT
SELECT name FROM former_employees;

Aggregate Functions

-- COUNT
SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department) FROM employees;

-- SUM
SELECT SUM(salary) FROM employees;

-- AVG
SELECT AVG(salary) FROM employees;

-- MIN / MAX
SELECT MIN(salary), MAX(salary) FROM employees;

-- Group with multiple aggregates
SELECT department,
       COUNT(*) AS total_employees,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Constraints

-- PRIMARY KEY (unique + not null)
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- FOREIGN KEY
CREATE TABLE projects (
    id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- UNIQUE
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

-- CHECK
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price >= 0)
);

-- DEFAULT
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- NOT NULL
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255)
);

String Functions

Common String Functions

LENGTH(str) – length of string
UPPER(str) – uppercase
LOWER(str) – lowercase
TRIM(str) – remove leading/trailing spaces
LTRIM(str) – remove leading spaces
RTRIM(str) – remove trailing spaces
SUBSTRING(str, start, length) – extract substring
CONCAT(str1, str2) – concatenate
REPLACE(str, old, new) – replace
INSTR(str, substr) – position of substring
LEFT(str, n) – first n characters
RIGHT(str, n) – last n characters
REVERSE(str) – reverse string

Examples

LENGTH('hello') → 5
UPPER('hello') → 'HELLO'
SUBSTRING('hello world', 7, 5) → 'world'
CONCAT('Hello', ' ', 'World') → 'Hello World'
LEFT('hello', 3) → 'hel'
RIGHT('hello', 2) → 'lo'

Date Functions

-- Current date/time
CURRENT_DATE           -- current date
CURRENT_TIME           -- current time
CURRENT_TIMESTAMP      -- current date and time

-- Extract components
YEAR(date)             -- year
MONTH(date)            -- month
DAY(date)              -- day
HOUR(time)             -- hour
MINUTE(time)           -- minute
SECOND(time)           -- second

-- Date arithmetic
DATE_ADD(date, INTERVAL 1 DAY)   -- add 1 day
DATE_SUB(date, INTERVAL 1 WEEK)  -- subtract 1 week
DATEDIFF(date1, date2)           -- difference in days

-- Formatting
DATE_FORMAT(date, '%Y-%m-%d')    -- MySQL
TO_CHAR(date, 'YYYY-MM-DD')      -- PostgreSQL

-- Examples
SELECT NOW();                     -- 2024-01-15 14:30:00
SELECT CURDATE();                 -- 2024-01-15
SELECT YEAR('2024-01-15');        -- 2024
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365

Indexes

-- Create an index
CREATE INDEX idx_employee_name ON employees(name);

-- Create a unique index
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- Create a composite index
CREATE INDEX idx_employee_dept_salary ON employees(department, salary);

-- Drop an index
DROP INDEX idx_employee_name ON employees;  -- MySQL
DROP INDEX idx_employee_name;                -- PostgreSQL

-- Show indexes
SHOW INDEX FROM employees;        -- MySQL
SELECT * FROM pg_indexes;         -- PostgreSQL

Views

-- Create a view
CREATE VIEW engineering_employees AS
SELECT id, name, email, salary
FROM employees
WHERE department = 'Engineering';

-- Query a view
SELECT * FROM engineering_employees;

-- Drop a view
DROP VIEW engineering_employees;

-- Create/Replace view
CREATE OR REPLACE VIEW engineering_employees AS
SELECT id, name, email, salary, hire_date
FROM employees
WHERE department = 'Engineering';

Transactions

-- Start a transaction
START TRANSACTION;          -- MySQL
BEGIN;                      -- PostgreSQL

-- Perform operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit (save changes)
COMMIT;

-- Rollback (undo changes)
ROLLBACK;

-- Savepoint
SAVEPOINT sp1;
-- ... operations ...
ROLLBACK TO SAVEPOINT sp1;

Best Practices

Use meaningful table and column names – singular or plural consistently.
Use JOIN syntax instead of old FROM table1, table2 syntax.
Use EXISTS instead of IN for subqueries that check existence.
Avoid SELECT * in production – explicitly list columns.
Use indexes on columns used in WHERE, JOIN, and ORDER BY.
Use LIMIT for pagination and to prevent large result sets.
Use prepared statements to prevent SQL injection.
Use EXPLAIN to understand query execution plans.
Normalize data to avoid redundancy (but denormalize for performance when needed).
Use transactions for operations that involve multiple tables.
Use COALESCE to handle NULL values.
Write readable SQL – use indentation and comments.

📌 Quick Reference

MySQL connect: mysql -u username -p
PostgreSQL connect: psql -U username -d database
Explain query: EXPLAIN SELECT ...
Show tables: SHOW TABLES; (MySQL) / \dt (PostgreSQL)

← Back to All Cheatsheets