SQL - Cheatsheet

Modern SQL Cheatsheet

This cheatsheet provides a quick reference for essential and modern SQL commands, organized for practical use. It covers everything from basic queries to advanced features like window functions and CTEs. SQL is the standard language for relational databases like PostgreSQL, MySQL, SQL Server, and Oracle.

1. Basic Queries (DQL)

-- Select all columns from a table
SELECT * FROM table_name;

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

-- Use aliases for columns and tables for readability
SELECT c.name AS customer_name, o.order_date
FROM customers AS c;

-- Get unique values from a column
SELECT DISTINCT category FROM products;

2. Filtering Data with WHERE

-- Basic filtering
SELECT * FROM users WHERE status = 'active';

-- Combine conditions
SELECT * FROM products WHERE price > 100.00 AND in_stock = TRUE;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books';

-- IN operator for a list of values
SELECT * FROM orders WHERE status IN ('shipped', 'delivered');

-- BETWEEN operator for a range
SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-12-31';

-- LIKE operator for pattern matching (% = wildcard)
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Handling NULL values
SELECT * FROM users WHERE phone_number IS NULL;
SELECT * FROM users WHERE phone_number IS NOT NULL;

3. Sorting and Limiting Results

-- Sort results (ASC is default)
SELECT * FROM products ORDER BY price DESC;

-- Sort by multiple columns
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

-- Limit number of rows (MySQL/PostgreSQL)
SELECT * FROM articles ORDER BY published_date DESC LIMIT 10;

-- Limit number of rows (SQL Server)
SELECT TOP 10 * FROM articles ORDER BY published_date DESC;

-- Limit with offset (ANSI SQL standard)
SELECT * FROM articles ORDER BY published_date DESC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

4. Data Modification (DML)

-- Insert a single row (best practice: specify columns)
INSERT INTO users (username, email) VALUES ('john_doe', 'john.doe@example.com');

-- Update existing rows (ALWAYS use WHERE!)
UPDATE products SET price = price * 1.10 WHERE category = 'Imported';

-- Delete rows (ALWAYS use WHERE!)
DELETE FROM logs WHERE log_date < '2022-01-01';

-- Delete all rows from a table (faster than DELETE, cannot be rolled back)
TRUNCATE TABLE staging_data;

5. Aggregation and Grouping

Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()

-- Count rows
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(phone_number) AS users_with_phone FROM users; -- Ignores NULLs

-- Group data and apply aggregate functions
SELECT category, AVG(price) AS avg_price, COUNT(*) AS num_products
FROM products
GROUP BY category;

-- Filter groups with HAVING
SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;

6. Joining Tables

-- Inner join: returns only matching rows from both tables
SELECT u.name, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- Left join: returns all rows from the left table, and matched rows from the right
SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- Right join: returns all rows from the right table, and matched rows from the left
SELECT u.name, o.order_id FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

-- Full outer join: returns all rows when there is a match in either table
SELECT u.name, o.order_id FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Cross join: returns the Cartesian product of both tables (all combinations)
SELECT u.name, p.product_name FROM users u CROSS JOIN products p;

7. Data Definition (DDL)

-- Create a new database
CREATE DATABASE new_app_db;

-- Create a new table with constraints
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Modify a table
ALTER TABLE employees ADD COLUMN status VARCHAR(20) DEFAULT 'active';
ALTER TABLE employees DROP COLUMN hire_date;

-- Drop a table (deletes structure and data)
DROP TABLE old_employees;

8. Advanced SQL

Subqueries & Common Table Expressions (CTEs)

-- Subquery in WHERE clause
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE is_active = TRUE);

-- Common Table Expression (CTE) for readability
WITH active_categories AS (
  SELECT id FROM categories WHERE is_active = TRUE
)
SELECT * FROM products WHERE category_id IN (SELECT id FROM active_categories);

Window Functions

-- Rank employees by salary within each department
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;

UNION vs. UNION ALL

-- Combine results, removing duplicates (slower)
SELECT email FROM customers UNION SELECT email FROM leads;

-- Combine results, keeping all duplicates (faster)
SELECT email FROM customers UNION ALL SELECT email FROM leads;

CASE Statement

-- Conditional logic in a SELECT statement
SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount > 1000 THEN 'High Value'
    WHEN total_amount > 500  THEN 'Medium Value'
    ELSE 'Low Value'
  END AS order_tier
FROM orders;

9. Transaction Control (TCL)

-- Group statements into a single transaction
BEGIN TRANSACTION;

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

-- If all commands succeed, save the changes
COMMIT;

-- If an error occurs, undo all changes since BEGIN
ROLLBACK;