SQL Query Cheat Sheet

Quick reference for common SQL query patterns.

Basic SELECT

SELECT * FROM employees                           # All columns
SELECT first_name, last_name FROM employees      # Specific columns
SELECT DISTINCT department_id FROM employees     # Unique values only
SELECT TOP 10 * FROM employees                   # First 10 rows
SELECT first_name AS name FROM employees         # Rename column

WHERE: Filtering

SELECT * FROM employees WHERE salary > 50000
SELECT * FROM employees WHERE last_name = 'Smith'
SELECT * FROM employees WHERE hire_date > '2020-01-01'
SELECT * FROM employees WHERE outservice_date IS NULL        # No value
SELECT * FROM employees WHERE outservice_date IS NOT NULL    # Has value

Comparison Operators

= (equal)          !=, <> (not equal)      < (less than)
<= (less or equal) > (greater than)        >= (greater or equal)
BETWEEN            IN                      LIKE (pattern match)
IS NULL            IS NOT NULL

Logical Operators

WHERE salary > 50000 AND department_id = 10          # Both true
WHERE status = 'Active' OR status = 'Pending'        # At least one true
WHERE NOT status = 'Inactive'                        # Reverse condition

IN: Multiple Values

SELECT * FROM employees
WHERE department_id IN (10, 20, 30)                  # Any of these values

BETWEEN: Range

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000                 # 40k to 60k inclusive
WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31'

LIKE: Pattern Matching

SELECT * FROM employees WHERE first_name LIKE 'J%'        # Starts with J
SELECT * FROM employees WHERE last_name LIKE '%son'       # Ends with son
SELECT * FROM employees WHERE email LIKE '%@%'            # Contains @
SELECT * FROM employees WHERE first_name LIKE '_oh_'      # ? wildcard

ORDER BY: Sorting

SELECT * FROM employees ORDER BY last_name                # A-Z
SELECT * FROM employees ORDER BY salary DESC              # Highest first
SELECT * FROM employees ORDER BY department_id ASC, salary DESC  # Multi-column

COUNT: Counting Rows

SELECT COUNT(*) FROM employees                     # Total employees
SELECT COUNT(DISTINCT department_id) FROM employees # Unique departments

SUM, AVG, MIN, MAX: Aggregates

SELECT SUM(salary) FROM employees                  # Total salary
SELECT AVG(salary) FROM employees                  # Average salary
SELECT MIN(salary) FROM employees                  # Lowest salary
SELECT MAX(salary) FROM employees                  # Highest salary

GROUP BY: Grouping

SELECT department_id, COUNT(*) as count
FROM employees
GROUP BY department_id                             # Count per department

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id

HAVING: Filtering Groups

SELECT department_id, COUNT(*) as count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5                                # Only groups with >5 employees

JOIN: Combining Tables

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id

SELECT e.first_name, e.last_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id

LEFT JOIN: Keep All Left Table Rows

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
# Shows all employees, even if department doesn't exist

INNER JOIN vs LEFT JOIN

INNER JOIN   # Only rows that match both tables
LEFT JOIN    # All rows from left table, matching rows from right

INSERT: Adding Data

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 55000)

INSERT INTO employees (first_name, last_name, salary)
VALUES
('Alice', 'Smith', 60000),
('Bob', 'Johnson', 58000)                         # Multiple rows

UPDATE: Changing Data

UPDATE employees
SET salary = 62000
WHERE employee_id = 101

UPDATE employees
SET salary = 65000, department_id = 20
WHERE first_name = 'John'

DELETE: Removing Data

DELETE FROM employees
WHERE employee_id = 101

DELETE FROM employees
WHERE hire_date < '2010-01-01'                    # Delete old employees

CREATE TABLE

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE
)

ALTER TABLE: Modifying Tables

ALTER TABLE employees
ADD phone_number VARCHAR(30)                      # Add column

ALTER TABLE employees
DROP COLUMN phone_number                          # Remove column

ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (department_id) REFERENCES departments(department_id)

CREATE INDEX

CREATE INDEX idx_lastname ON employees(last_name)     # Faster searches
CREATE INDEX idx_multi ON employees(department_id, salary)

UNION: Combining Results

SELECT first_name FROM employees
UNION
SELECT first_name FROM dependents               # Combine results, remove duplicates

SELECT first_name FROM employees
UNION ALL
SELECT first_name FROM dependents               # Include duplicates

CASE: Conditional Values

SELECT first_name,
CASE
    WHEN salary > 60000 THEN 'High'
    WHEN salary > 50000 THEN 'Medium'
    ELSE 'Low'
END as salary_level
FROM employees

String Functions

CONCAT(first_name, ' ', last_name)              # Combine strings
UPPER(first_name)                                # Uppercase
LOWER(first_name)                                # Lowercase
LEN(first_name)                                  # String length
SUBSTRING(email, 1, 5)                           # Extract characters
REPLACE(email, '@old.com', '@new.com')           # Find and replace

Date Functions

GETDATE()                                        # Current date/time
DATEDIFF(day, hire_date, '2024-01-01')          # Days between dates
DATEADD(month, 6, hire_date)                     # Add days/months/years
YEAR(hire_date)                                  # Extract year
MONTH(hire_date)                                 # Extract month
DAY(hire_date)                                   # Extract day

DISTINCT: Unique Values

SELECT DISTINCT city FROM locations              # One row per unique city

LIMIT/TOP: Restrict Results

SELECT TOP 5 * FROM employees                    # First 5 rows (SQL Server)
SELECT * FROM employees LIMIT 5                  # First 5 rows (MySQL)

ORDER BY with LIMIT

SELECT TOP 10 *
FROM employees
ORDER BY salary DESC                             # Top 10 highest paid

Aliases in Aggregates

SELECT
    department_id,
    COUNT(*) as total_employees,
    AVG(salary) as average_salary
FROM employees
GROUP BY department_id

Comments

-- Single line comment

/* Multi-line comment
   explains complex queries */

NULL Handling

SELECT first_name, ISNULL(phone_number, 'No phone')  # Use default if NULL
SELECT first_name WHERE phone_number IS NOT NULL     # Only rows with value