Complete SQL Cheatsheet with All Commands and Examples

-- Create Table with Basic Data Types
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Table with Multiple Constraints
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    manager_id INT,
    location VARCHAR(100),
    budget DECIMAL(15, 2) CHECK (budget > 0),
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) 
        REFERENCES employees(id)
);

-- Common Data Types

-- Numeric Types
INT, SMALLINT, BIGINT          -- Integers
DECIMAL(10,2), NUMERIC(10,2)   -- Fixed-point
FLOAT, DOUBLE, REAL            -- Floating-point

-- String Types
CHAR(10)                       -- Fixed length
VARCHAR(255)                   -- Variable length
TEXT, MEDIUMTEXT, LONGTEXT     -- Long text

-- Date and Time Types
DATE                           -- YYYY-MM-DD
TIME                           -- HH:MM:SS
DATETIME                       -- YYYY-MM-DD HH:MM:SS
TIMESTAMP                      -- With timezone
YEAR                           -- YYYY

-- Binary Types
BLOB, MEDIUMBLOB, LONGBLOB     -- Binary data
BINARY, VARBINARY              -- Binary strings

-- Other Types
BOOLEAN, BOOL                  -- True/False (TINYINT(1))
ENUM('S','M','L')             -- Enumeration
JSON                           -- JSON data (MySQL 5.7+)

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

ALTER TABLE employees
ADD CONSTRAINT fk_dept FOREIGN KEY (department_id)
    REFERENCES departments(dept_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Drop Constraints
ALTER TABLE employees
DROP CONSTRAINT unique_email;

ALTER TABLE employees
DROP FOREIGN KEY fk_dept;

ALTER TABLE employees
DROP INDEX unique_email;

-- Other Table Operations

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

-- Drop Table
DROP TABLE employees;
DROP TABLE IF EXISTS employees;

-- Show Table Structure
DESCRIBE employees;
DESC employees;
SHOW COLUMNS FROM employees;

-- Show Create Statement
SHOW CREATE TABLE employees;

-- Show All Tables
SHOW TABLES;

-- Copy Table Structure
CREATE TABLE employees_backup LIKE employees;

-- Copy Table with Data
CREATE TABLE employees_backup AS
SELECT * FROM employees;

-- Insert with SELECT (copy data from another table)
INSERT INTO employees_backup (first_name, last_name, email, salary)
SELECT first_name, last_name, email, salary
FROM employees
WHERE department_id = 1;

-- Insert with Default Values
INSERT INTO employees (first_name, last_name, email)
VALUES ('David', 'Miller', 'david@email.com');
-- Other columns use DEFAULT values

-- Insert Ignore (skip if duplicate key)
INSERT IGNORE INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john@email.com');

-- Insert On Duplicate Key Update (MySQL)
INSERT INTO employees (id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 80000.00)
ON DUPLICATE KEY UPDATE 
    salary = VALUES(salary),
    last_name = VALUES(last_name);

-- Insert with RETURNING (PostgreSQL)
INSERT INTO employees (first_name, last_name, email)
VALUES ('Emma', 'Davis', 'emma@email.com')
RETURNING id, first_name, created_at;

-- Insert with OUTPUT (SQL Server)
INSERT INTO employees (first_name, last_name, email)
OUTPUT INSERTED.id, INSERTED.first_name
VALUES ('Frank', 'Wilson', 'frank@email.com');

-- Upsert (PostgreSQL)
INSERT INTO employees (id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 85000.00)
ON CONFLICT (id) 
DO UPDATE SET 
    salary = EXCLUDED.salary,
    last_name = EXCLUDED.last_name;

-- WHERE Clause (Filtering)
SELECT * FROM employees WHERE salary > 70000;

SELECT * FROM employees WHERE department_id = 1;

SELECT * FROM employees 
WHERE first_name = 'John' AND last_name = 'Doe';

SELECT * FROM employees 
WHERE salary >= 60000 AND salary <= 80000;

SELECT * FROM employees 
WHERE department_id IN (1, 2, 3);

SELECT * FROM employees 
WHERE first_name LIKE 'J%';  -- Starts with J

SELECT * FROM employees 
WHERE email LIKE '%@gmail.com';  -- Ends with @gmail.com

SELECT * FROM employees 
WHERE first_name LIKE '%oh%';  -- Contains 'oh'

SELECT * FROM employees 
WHERE phone IS NULL;

SELECT * FROM employees 
WHERE phone IS NOT NULL;

-- ORDER BY (Sorting)
SELECT * FROM employees 
ORDER BY salary DESC;

SELECT * FROM employees 
ORDER BY last_name ASC, first_name ASC;

SELECT * FROM employees 
ORDER BY hire_date DESC;

-- LIMIT (Restricting Results)
SELECT * FROM employees 
LIMIT 10;

SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 5;  -- Top 5 highest salaries

-- OFFSET (Pagination)
SELECT * FROM employees 
LIMIT 10 OFFSET 20;  -- Skip first 20, get next 10

-- MySQL alternative
SELECT * FROM employees 
LIMIT 20, 10;  -- Skip 20, get 10

-- LIKE Patterns
SELECT * FROM employees 
WHERE first_name LIKE 'J%';     -- Starts with J

SELECT * FROM employees 
WHERE first_name LIKE '%n';     -- Ends with n

SELECT * FROM employees 
WHERE first_name LIKE '%oh%';   -- Contains oh

SELECT * FROM employees 
WHERE first_name LIKE 'J___';   -- J followed by 3 chars

SELECT * FROM employees 
WHERE first_name LIKE '[JM]%';  -- Starts with J or M (SQL Server)

SELECT * FROM employees 
WHERE first_name LIKE '[A-M]%'; -- Starts with A to M

SELECT * FROM employees 
WHERE email NOT LIKE '%@gmail.com';

-- REGEXP (MySQL, PostgreSQL)
SELECT * FROM employees 
WHERE first_name REGEXP '^[A-M]';  -- Starts with A-M

SELECT * FROM employees 
WHERE email REGEXP '[0-9]';  -- Contains digit

-- CASE Expression
SELECT 
    first_name,
    last_name,
    salary,
    CASE 
        WHEN salary < 50000 THEN 'Low'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
        WHEN salary > 80000 THEN 'High'
        ELSE 'Unknown'
    END AS salary_category
FROM employees;

-- Simple CASE
SELECT 
    first_name,
    department_id,
    CASE department_id
        WHEN 1 THEN 'IT'
        WHEN 2 THEN 'HR'
        WHEN 3 THEN 'Sales'
        ELSE 'Other'
    END AS department_name
FROM employees;

-- CASE in ORDER BY
SELECT * FROM employees
ORDER BY 
    CASE 
        WHEN department_id = 1 THEN 1
        WHEN department_id = 2 THEN 2
        ELSE 3
    END;

-- Update with Subquery
UPDATE employees
SET department_id = (
    SELECT dept_id FROM departments 
    WHERE dept_name = 'IT'
)
WHERE first_name = 'John';

-- Update with JOIN (MySQL)
UPDATE employees e
INNER JOIN departments d ON e.department_id = d.dept_id
SET e.salary = e.salary * 1.1
WHERE d.dept_name = 'Sales';

-- Update Multiple Tables (MySQL)
UPDATE employees e, departments d
SET 
    e.salary = e.salary * 1.1,
    d.budget = d.budget - 10000
WHERE e.department_id = d.dept_id 
    AND d.dept_name = 'IT';

-- Update with RETURNING (PostgreSQL)
UPDATE employees
SET salary = 90000
WHERE id = 1
RETURNING id, first_name, salary;

-- Safe Update Practices

-- 1. Always use WHERE clause
UPDATE employees
SET salary = 80000
WHERE id = 1;  -- NOT: without WHERE

-- 2. Test with SELECT first
SELECT * FROM employees WHERE id = 1;
-- Then UPDATE

-- 3. Use transactions for safety
START TRANSACTION;
UPDATE employees SET salary = 90000 WHERE id = 1;
-- Check results
SELECT * FROM employees WHERE id = 1;
-- If correct: COMMIT, else: ROLLBACK

-- 4. Backup before mass updates
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- 5. Use LIMIT for testing (MySQL)
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 1
LIMIT 5;

-- Delete with Subquery
DELETE FROM employees
WHERE department_id IN (
    SELECT dept_id FROM departments 
    WHERE budget < 50000
);

DELETE FROM employees
WHERE id NOT IN (
    SELECT DISTINCT employee_id FROM projects
);

-- Delete with JOIN (MySQL)
DELETE e FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_name = 'Closed';

-- Delete with RETURNING (PostgreSQL)
DELETE FROM employees
WHERE id = 1
RETURNING id, first_name, last_name;

-- TRUNCATE vs DELETE

-- DELETE: Removes rows one by one
DELETE FROM employees;
-- - Slower
-- - Can be rolled back
-- - Fires triggers
-- - Can use WHERE clause
-- - Logs each row deletion

-- TRUNCATE: Removes all rows at once
TRUNCATE TABLE employees;
-- - Faster
-- - Cannot be rolled back (in most DBs)
-- - Does not fire triggers
-- - No WHERE clause
-- - Minimal logging
-- - Resets auto-increment counter

-- DROP: Removes entire table
DROP TABLE employees;
-- - Removes structure and data
-- - Cannot recover without backup

-- Safe Delete Practices
-- 1. Use SELECT first
SELECT * FROM employees WHERE id = 1;
-- 2. Then DELETE
DELETE FROM employees WHERE id = 1;

-- Multiple Aggregates
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    AVG(salary) AS avg_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

-- Aggregates with WHERE
SELECT 
    COUNT(*) AS it_employees,
    AVG(salary) AS avg_it_salary
FROM employees
WHERE department_id = 1;

-- ROUND for precision
SELECT 
    ROUND(AVG(salary), 2) AS avg_salary,
    ROUND(SUM(salary), 2) AS total_payroll
FROM employees;

-- Aggregates with DISTINCT
SELECT 
    COUNT(DISTINCT department_id) AS unique_departments,
    COUNT(DISTINCT last_name) AS unique_surnames
FROM employees;

-- Statistical Functions

-- STDDEV: Standard deviation
SELECT STDDEV(salary) AS salary_std_dev FROM employees;

-- VARIANCE: Variance
SELECT VARIANCE(salary) AS salary_variance FROM employees;

-- String Aggregation (MySQL)
SELECT GROUP_CONCAT(first_name) AS all_names 
FROM employees;

SELECT GROUP_CONCAT(first_name SEPARATOR ', ') AS names
FROM employees;

-- String Aggregation (PostgreSQL)
SELECT STRING_AGG(first_name, ', ') AS names 
FROM employees;

-- Conditional Aggregation
SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN salary > 70000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary <= 70000 THEN 1 END) AS others
FROM employees;

-- HAVING Clause (Filter Groups)
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000;

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5;

-- WHERE vs HAVING
-- WHERE: Filters rows before grouping
-- HAVING: Filters groups after aggregation

SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000  -- Filter rows first
GROUP BY department_id
HAVING COUNT(*) > 3;  -- Then filter groups

-- Multiple HAVING Conditions
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 60000;

-- GROUP BY with ORDER BY
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;

-- GROUP BY with Expressions
SELECT 
    YEAR(hire_date) AS hire_year,
    COUNT(*) AS hires
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

-- ROLLUP (Subtotals)
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP;

-- GROUPING SETS (Advanced)
SELECT 
    department_id,
    is_active,
    COUNT(*) AS count
FROM employees
GROUP BY GROUPING SETS (
    (department_id),
    (is_active),
    (department_id, is_active)
);

-- Full Outer Join (all rows both sides)
-- PostgreSQL / SQL Server
SELECT e.first_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id;

-- MySQL alternative using UNION
SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
UNION
SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.dept_id;

-- Cross Join (cartesian product)
SELECT e.first_name, p.project_name
FROM employees e
CROSS JOIN projects p;

-- Self Join (table joins itself)
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- CTE (WITH)
WITH dept_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.dept_name, ds.avg_salary
FROM dept_salary ds
JOIN departments d ON ds.department_id = d.dept_id;

-- Recursive CTE (hierarchy)
WITH RECURSIVE employee_path AS (
    SELECT id, first_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.first_name, e.manager_id, ep.level + 1
    FROM employees e
    JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT * FROM employee_path
ORDER BY level, first_name;

-- Analyze query plan
EXPLAIN SELECT * FROM employees WHERE email = 'john@email.com';

-- PostgreSQL with runtime stats
EXPLAIN ANALYZE
SELECT * FROM employees WHERE department_id = 1;

-- Update statistics (PostgreSQL)
VACUUM ANALYZE employees;

-- MySQL optimizer hints
SELECT /*+ INDEX(employees idx_emp_dept) */ *
FROM employees
WHERE department_id = 1;

-- Stored procedure (MySQL)
DELIMITER //
CREATE PROCEDURE give_raise(IN dept INT, IN pct DECIMAL(5,2))
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE department_id = dept;
END //
DELIMITER ;

CALL give_raise(1, 5.0);

-- Scalar function (PostgreSQL)
CREATE OR REPLACE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN first || ' ' || last;
END;
$$ LANGUAGE plpgsql;