SQL Keywords Complete Reference

📚 Table of Contents

🔹 Core SQL Language Keywords

SELECT

-- Basic SELECT
SELECT first_name, last_name FROM employees;
-- SELECT with calculations
SELECT product_name, price, price * 0.1 AS tax FROM products;
-- SELECT with subquery
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);

INSERT

-- Single row insert
INSERT INTO employees (first_name, last_name, salary) 
VALUES ('John', 'Doe', 50000);
-- Multiple rows insert
INSERT INTO employees (first_name, last_name, salary) 
VALUES 
 ('Jane', 'Smith', 55000),
 ('Bob', 'Johnson', 48000);
-- Insert from SELECT
INSERT INTO archived_orders 
SELECT * FROM orders WHERE order_date < '2023年01月01日';

UPDATE

-- Basic update
UPDATE employees SET salary = 55000 WHERE employee_id = 1;
-- Update multiple columns
UPDATE products 
SET price = price * 1.1, last_updated = NOW() 
WHERE category = 'Electronics';
-- Update with JOIN
UPDATE employees e 
SET salary = salary * 1.05 
FROM departments d 
WHERE e.dept_id = d.id AND d.name = 'Engineering';

DELETE

-- Conditional delete
DELETE FROM employees WHERE last_login < '2023年01月01日';
-- Delete with subquery
DELETE FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'inactive');
-- Delete all records (dangerous!)
DELETE FROM temp_table;

FROM

-- Basic FROM
SELECT * FROM employees;
-- FROM with alias
SELECT e.name, d.department_name 
FROM employees e, departments d;
-- FROM with subquery
SELECT * FROM (
 SELECT name, salary FROM employees WHERE salary > 50000
) AS high_earners;

🔹 Logical Operators & Conditions

AND / OR / NOT

-- AND operator
SELECT * FROM employees 
WHERE salary > 50000 AND department = 'Engineering';
-- OR operator
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';
-- NOT operator
SELECT * FROM customers 
WHERE NOT country = 'USA';
-- Complex combinations with parentheses
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
 AND price BETWEEN 10 AND 100 
 AND NOT discontinued;

IN / NOT IN

-- IN with list
SELECT * FROM employees 
WHERE department IN ('Sales', 'Marketing', 'HR');
-- IN with subquery
SELECT * FROM products 
WHERE category_id IN (
 SELECT id FROM categories WHERE active = true
);
-- NOT IN
SELECT * FROM customers 
WHERE country NOT IN ('USA', 'Canada', 'Mexico');

EXISTS / NOT EXISTS

-- EXISTS
SELECT * FROM customers c
WHERE EXISTS (
 SELECT 1 FROM orders o 
 WHERE o.customer_id = c.id
);
-- NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
 SELECT 1 FROM order_items oi 
 WHERE oi.product_id = p.id
);

BETWEEN / NOT BETWEEN

-- Numeric range
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
-- Date range
SELECT * FROM orders 
WHERE order_date BETWEEN '2024年01月01日' AND '2024年12月31日';
-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;

LIKE / ILIKE / NOT LIKE

-- Basic pattern matching
SELECT * FROM customers WHERE name LIKE 'John%';
-- Single character wildcard
SELECT * FROM products WHERE code LIKE 'A_B_';
-- Case-insensitive (PostgreSQL)
SELECT * FROM customers WHERE name ILIKE '%smith%';
-- Escape special characters
SELECT * FROM products WHERE description LIKE '%50\% off%' ESCAPE '\';

🔹 JOIN Operations

JOIN / INNER JOIN

-- Basic INNER JOIN
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- Multiple JOINs
SELECT c.name, o.order_date, p.product_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

LEFT JOIN

-- All employees with their departments (even if no department)
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

USING (in JOINs)

-- Traditional JOIN
SELECT * FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- Using USING
SELECT * FROM employees e
JOIN departments d USING (department_id);

🔹 Advanced SQL Features

FETCH

-- Fetch first 10 rows
SELECT * FROM employees 
ORDER BY hire_date
FETCH FIRST 10 ROWS ONLY;
-- With offset
SELECT * FROM employees 
ORDER BY hire_date
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

ON CONFLICT / UPSERT

-- Insert or update
INSERT INTO products (id, name, price) 
VALUES (1, 'Widget', 19.99)
ON CONFLICT (id) 
DO UPDATE SET 
 name = EXCLUDED.name,
 price = EXCLUDED.price,
 updated_at = NOW();
-- Insert or ignore
INSERT INTO categories (name) 
VALUES ('Electronics')
ON CONFLICT (name) DO NOTHING;

RETURNING

-- Get generated ID
INSERT INTO customers (name, email) 
VALUES ('John Doe', 'john@example.com')
RETURNING id, created_at;
-- See updated values
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'Sales'
RETURNING name, salary;

🔹 Data Definition Language (DDL)

CREATE TABLE

-- Basic table creation
CREATE TABLE employees (
 id SERIAL PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50) NOT NULL,
 email VARCHAR(100) UNIQUE,
 hire_date DATE DEFAULT CURRENT_DATE,
 salary NUMERIC(10,2) CHECK (salary > 0)
);
-- Table with foreign key
CREATE TABLE orders (
 id SERIAL PRIMARY KEY,
 customer_id INTEGER REFERENCES customers(id),
 order_date TIMESTAMP DEFAULT NOW(),
 total DECIMAL(10,2)
);

ALTER TABLE

-- Add column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
-- Modify column
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12,2);
-- Add constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary 
 CHECK (salary BETWEEN 20000 AND 200000);

CREATE INDEX

-- Basic index
CREATE INDEX idx_employees_department ON employees(department);
-- Composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Partial index
CREATE INDEX idx_active_employees ON employees(name) 
WHERE status = 'active';
-- Functional index
CREATE INDEX idx_lower_email ON customers(LOWER(email));

🔹 Constraints & Keys

PRIMARY KEY

-- Single column primary key
CREATE TABLE customers (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100)
);
-- Composite primary key
CREATE TABLE order_items (
 order_id INTEGER,
 product_id INTEGER,
 quantity INTEGER,
 PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY

-- Basic foreign key
CREATE TABLE orders (
 id SERIAL PRIMARY KEY,
 customer_id INTEGER REFERENCES customers(id)
);
-- With cascade options
CREATE TABLE order_items (
 order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
 product_id INTEGER REFERENCES products(id) ON UPDATE CASCADE
);

CHECK

-- Simple check constraint
CREATE TABLE employees (
 salary DECIMAL(10,2) CHECK (salary > 0)
);
-- Complex check constraint
CREATE TABLE products (
 price DECIMAL(10,2),
 discount_price DECIMAL(10,2),
 CHECK (discount_price < price)
);

🔹 Functions

String Functions

LENGTH()

SELECT name, LENGTH(name) as name_length FROM customers;

TRIM()

SELECT TRIM(' hello world '); -- Returns 'hello world'
SELECT TRIM(BOTH 'x' FROM 'xxxhelloxxx'); -- Returns 'hello'

UPPER() / LOWER()

SELECT UPPER(first_name), LOWER(last_name) FROM employees;

CONCAT()

SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;
-- PostgreSQL also supports: first_name || ' ' || last_name

Date/Time Functions

NOW()

SELECT NOW(); -- Current timestamp
INSERT INTO logs (created_at) VALUES (NOW());

DATE_PART() / EXTRACT()

SELECT EXTRACT(YEAR FROM birth_date) as birth_year FROM employees;
SELECT DATE_PART('month', order_date) as order_month FROM orders;

Mathematical Functions

ROUND()

SELECT product_name, ROUND(price, 2) FROM products;
SELECT ROUND(AVG(salary)) as avg_salary FROM employees;

ABS()

SELECT ABS(-42); -- Returns 42
SELECT ABS(budget - actual) as variance FROM projects;

Aggregate Functions

COUNT()

SELECT COUNT(*) FROM employees; -- All rows
SELECT COUNT(email) FROM customers; -- Non-NULL emails
SELECT COUNT(DISTINCT department) FROM employees; -- Unique departments

SUM() / AVG()

SELECT department, SUM(salary), AVG(salary) 
FROM employees 
GROUP BY department;

Advanced Functions

COALESCE / NULLIF

-- COALESCE for default values
SELECT name, COALESCE(phone, email, 'No contact') as contact
FROM customers;
-- NULLIF to convert empty strings to NULL
SELECT NULLIF(TRIM(description), '') as clean_description
FROM products;

🔹 Window Functions

ROW_NUMBER()

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

RANK() / DENSE_RANK()

SELECT name, salary,
 RANK() OVER (ORDER BY salary DESC) as rank,
 DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

LEAD() / LAG()

SELECT order_date, total,
 LAG(total) OVER (ORDER BY order_date) as previous_total,
 LEAD(total) OVER (ORDER BY order_date) as next_total
FROM orders;

PARTITION BY

SELECT name, department, salary,
 AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

🔹 Transaction Control

BEGIN / COMMIT / ROLLBACK

-- Transaction example
BEGIN;
 UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- With error handling
BEGIN;
 INSERT INTO orders (customer_id, total) VALUES (1, 500);
 -- If error occurs:
ROLLBACK;

SAVEPOINT

BEGIN;
 INSERT INTO table1 VALUES (1, 'data');
 SAVEPOINT sp1;
 INSERT INTO table2 VALUES (2, 'more data');
 -- If needed:
 ROLLBACK TO sp1; -- Only rollback to savepoint
COMMIT;

🔹 Views and Performance

CREATE VIEW

-- Simple view
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE status = 'active';
-- Complex view with joins
CREATE VIEW employee_details AS
SELECT 
 e.name,
 d.department_name,
 p.project_name,
 e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
LEFT JOIN projects p ON e.project_id = p.id;

CREATE MATERIALIZED VIEW

-- Create materialized view
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
 DATE_TRUNC('month', order_date) as month,
 COUNT(*) as order_count,
 SUM(total) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

EXPLAIN / EXPLAIN ANALYZE

-- Show execution plan
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';
-- Show actual execution statistics
EXPLAIN ANALYZE 
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 50000;

🔹 PL/pgSQL Special Variables

FOUND

CREATE OR REPLACE FUNCTION update_employee_salary(emp_id INTEGER, new_salary NUMERIC)
RETURNS TEXT AS $$
DECLARE
 result TEXT;
BEGIN
 UPDATE employees SET salary = new_salary WHERE id = emp_id;
 
 IF FOUND THEN
 result := 'Employee salary updated successfully';
 ELSE
 result := 'Employee not found';
 END IF;
 
 RETURN result;
END;
$$ LANGUAGE plpgsql;

ROW_COUNT

CREATE OR REPLACE FUNCTION bulk_update_salaries(dept_name TEXT, increase_pct NUMERIC)
RETURNS INTEGER AS $$
DECLARE
 affected_rows INTEGER;
BEGIN
 UPDATE employees 
 SET salary = salary * (1 + increase_pct / 100)
 WHERE department = dept_name;
 
 GET DIAGNOSTICS affected_rows = ROW_COUNT;
 
 RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

Trigger Variables (TG_*)

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS trigger AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
 INSERT INTO audit_log (table_name, operation, new_data, timestamp)
 VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), NOW());
 RETURN NEW;
 ELSIF TG_OP = 'UPDATE' THEN
 INSERT INTO audit_log (table_name, operation, old_data, new_data, timestamp)
 VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), NOW());
 RETURN NEW;
 END IF;
END;
$$ LANGUAGE plpgsql;

🔹 Environment Variables

PostgreSQL Environment Variables

# Connection variables
export PGHOST=localhost
export PGPORT=5432
export PGUSER=myuser
export PGPASSWORD=mypassword
export PGDATABASE=mydb
# SSL configuration
export PGSSLMODE=require
export PGSSLCERT=/path/to/client-cert.pem
# Now psql uses these settings automatically
psql

🔹 System Catalogs & Information Schema

pg_stat_activity

-- Current active connections
SELECT 
 pid,
 usename,
 application_name,
 client_addr,
 state,
 query_start,
 LEFT(query, 50) as current_query
FROM pg_stat_activity 
WHERE state = 'active' AND pid <> pg_backend_pid();
-- Long-running queries
SELECT 
 pid,
 now() - query_start as duration,
 usename,
 LEFT(query, 100) as query
FROM pg_stat_activity 
WHERE state = 'active' 
 AND now() - query_start > interval '5 minutes';

Information Schema (Standard SQL)

-- Table information
SELECT 
 table_schema,
 table_name,
 table_type
FROM information_schema.tables 
WHERE table_schema = 'public';
-- Column information
SELECT 
 column_name,
 data_type,
 is_nullable,
 column_default
FROM information_schema.columns 
WHERE table_name = 'employees'
ORDER BY ordinal_position;

🔹 Advanced Data Types

ENUM Types

-- Create enum type
CREATE TYPE status_type AS ENUM ('pending', 'active', 'inactive', 'deleted');
-- Use in table
CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100),
 status status_type DEFAULT 'pending'
);
-- Query with enum
SELECT * FROM users WHERE status = 'active';

Array Types

-- Create table with array columns
CREATE TABLE articles (
 id SERIAL PRIMARY KEY,
 title VARCHAR(200),
 tags TEXT[],
 ratings INTEGER[]
);
-- Insert array data
INSERT INTO articles (title, tags, ratings) VALUES
('PostgreSQL Guide', ARRAY['database', 'sql', 'postgresql'], ARRAY[5, 4, 5, 4]);
-- Array queries
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);
SELECT * FROM articles WHERE tags && ARRAY['database', 'web'];

UUID Type

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Create table with UUID
CREATE TABLE sessions (
 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
 user_id INTEGER,
 created_at TIMESTAMP DEFAULT NOW()
);

🔹 Text Search and Full-Text Search

TSVECTOR and TSQUERY

-- Create table with text search
CREATE TABLE documents (
 id SERIAL PRIMARY KEY,
 title VARCHAR(200),
 content TEXT,
 search_vector tsvector
);
-- Update search vector
UPDATE documents SET search_vector = 
 to_tsvector('english', title || ' ' || content);
-- Create index for fast searching
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);
-- Full-text search
SELECT *, ts_rank(search_vector, query) as rank
FROM documents, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Regular Expressions

-- Basic regex matching
SELECT * FROM products WHERE code ~ '^[A-Z]{2}[0-9]{4}$';
-- Case-insensitive matching
SELECT * FROM customers WHERE name ~* '^john.*smith$';
-- Extract patterns
SELECT 
 email,
 substring(email from '([^@]+)@(.+)') as username
FROM users;

🔹 Stored Procedures and Functions

Function Types

-- Simple SQL function
CREATE OR REPLACE FUNCTION get_employee_count(dept_name TEXT)
RETURNS INTEGER AS $$
 SELECT COUNT(*) FROM employees WHERE department = dept_name;
$$ LANGUAGE SQL IMMUTABLE;
-- PL/pgSQL function with logic
CREATE OR REPLACE FUNCTION calculate_bonus(emp_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
 emp_salary NUMERIC;
 years_service INTEGER;
 bonus_amount NUMERIC := 0;
BEGIN
 SELECT salary, EXTRACT(YEAR FROM AGE(NOW(), hire_date))
 INTO emp_salary, years_service
 FROM employees WHERE id = emp_id;
 
 IF years_service >= 10 THEN
 bonus_amount := emp_salary * 0.15;
 ELSIF years_service >= 5 THEN
 bonus_amount := emp_salary * 0.10;
 END IF;
 
 RETURN bonus_amount;
END;
$$ LANGUAGE plpgsql;

🔹 Triggers and Rules

Triggers

-- Audit trigger function
CREATE OR REPLACE FUNCTION employee_audit_func()
RETURNS trigger AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
 INSERT INTO employee_audit (action, employee_id, new_data, timestamp)
 VALUES ('INSERT', NEW.id, row_to_json(NEW), NOW());
 RETURN NEW;
 ELSIF TG_OP = 'UPDATE' THEN
 INSERT INTO employee_audit (action, employee_id, old_data, new_data, timestamp)
 VALUES ('UPDATE', NEW.id, row_to_json(OLD), row_to_json(NEW), NOW());
 RETURN NEW;
 END IF;
END;
$$ LANGUAGE plpgsql;
-- Create triggers
CREATE TRIGGER employee_audit_trigger
 AFTER INSERT OR UPDATE OR DELETE ON employees
 FOR EACH ROW EXECUTE FUNCTION employee_audit_func();

🔹 Security and Permissions

Roles and Users

-- Create roles
CREATE ROLE app_user LOGIN PASSWORD 'secure_password';
CREATE ROLE read_only_user;
-- Grant permissions
GRANT SELECT ON employees TO read_only_user;
GRANT SELECT, INSERT, UPDATE ON customers TO app_user;
-- Role inheritance
CREATE ROLE reporting_team;
GRANT read_only_user TO reporting_team;

Row Level Security (RLS)

-- Enable RLS on table
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
-- Create policies
CREATE POLICY employee_policy ON employees
 FOR ALL TO app_user
 USING (department = current_setting('app.user_department'));

🔹 Advanced Query Techniques

Common Table Expressions (CTE)

WITH department_stats AS (
 SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
 FROM employees
 GROUP BY department
)
SELECT * FROM department_stats WHERE emp_count > 5;

Recursive CTE

WITH RECURSIVE employee_hierarchy AS (
 -- Base case: top-level managers
 SELECT id, name, manager_id, 1 as level
 FROM employees
 WHERE manager_id IS NULL
 
 UNION ALL
 
 -- Recursive case: employees with managers
 SELECT e.id, e.name, e.manager_id, eh.level + 1
 FROM employees e
 JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Pivot Operations (Simulated)

-- Pivot sales data by quarter
SELECT 
 product_name,
 SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) as q1_sales,
 SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) as q2_sales,
 SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) as q3_sales,
 SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) as q4_sales
FROM quarterly_sales
GROUP BY product_name;

🔹 Keywords Relationships Summary

Query Execution Order

-- SQL Execution Order
1. FROM / JOIN -- Source tables and relationships
2. WHERE -- Row filtering
3. GROUP BY -- Row grouping
4. HAVING -- Group filtering
5. SELECT -- Column selection and calculations
6. DISTINCT -- Remove duplicates
7. ORDER BY -- Sort results
8. LIMIT/OFFSET -- Limit results

Logical Operator Precedence

1. Parentheses () -- Highest precedence
2. NOT -- Negation
3. AND -- Logical AND
4. OR -- Logical OR (lowest precedence)

Common Pattern Relationships

-- Data Query Pattern
SELECT columns FROM tables WHERE conditions;
-- Aggregation Pattern 
SELECT columns, AGG_FUNC(column) FROM tables 
GROUP BY columns HAVING conditions;
-- Join Pattern
SELECT columns FROM table1 alias1
JOIN table2 alias2 ON join_condition
WHERE filter_conditions;
-- Subquery Pattern
SELECT columns FROM table1 
WHERE column IN (SELECT column FROM table2 WHERE conditions);
-- Window Function Pattern
SELECT columns, 
 WINDOW_FUNC() OVER (PARTITION BY columns ORDER BY columns)
FROM tables;

Transaction Control Flow

BEGIN; -- Start transaction
 SQL operations; -- INSERT, UPDATE, DELETE
 SAVEPOINT name; -- Optional checkpoint
 More operations; -- Additional work
COMMIT; -- Make changes permanent
-- OR --
ROLLBACK; -- Undo all changes
-- OR --
ROLLBACK TO name; -- Undo to savepoint

Performance Optimization Patterns

-- Index Usage Pattern
CREATE INDEX idx_table_columns ON table_name (column1, column2);
EXPLAIN ANALYZE SELECT * FROM table_name WHERE column1 = 'value';
-- Materialized View Pattern
CREATE MATERIALIZED VIEW summary_view AS
SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department;
REFRESH MATERIALIZED VIEW summary_view;
-- Partition Pattern (PostgreSQL 10+)
CREATE TABLE sales (
 id SERIAL,
 sale_date DATE,
 amount DECIMAL
) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024年01月01日') TO ('2025年01月01日');

📊 Quick Reference Tables

SQL Keywords by Category

Data Types by Database

Function Categories

🚀 Best Practices

Query Optimization

-- ✅ Good: Use specific columns
SELECT id, name FROM employees WHERE department = 'Sales';
-- ❌ Bad: Select all columns when not needed
SELECT * FROM employees WHERE department = 'Sales';
-- ✅ Good: Use indexes effectively
CREATE INDEX idx_emp_dept ON employees(department);
SELECT * FROM employees WHERE department = 'Sales';
-- ✅ Good: Use EXISTS instead of IN for large datasets
SELECT * FROM customers c 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- ❌ Bad: Use IN with large subqueries
SELECT * FROM customers 
WHERE id IN (SELECT customer_id FROM orders);

Security Best Practices

-- ✅ Good: Use parameterized queries (application code)
-- PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
-- ✅ Good: Grant minimal necessary permissions
GRANT SELECT ON employees TO read_only_user;
-- ✅ Good: Use Row Level Security
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_policy ON sensitive_data 
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- ❌ Bad: Using string concatenation for queries (SQL injection risk)
-- SELECT * FROM users WHERE name = '" + user_input + "'";

Performance Monitoring

-- Monitor query performance
EXPLAIN ANALYZE SELECT * FROM large_table WHERE indexed_column = 'value';
-- Check index usage
SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_tup_read DESC;
-- Monitor slow queries
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC;

🔧 Common Patterns and Examples

Data Analysis Patterns

-- Running totals
SELECT 
 order_date,
 daily_sales,
 SUM(daily_sales) OVER (ORDER BY order_date) as running_total
FROM daily_sales_summary;
-- Top N per group
SELECT *
FROM (
 SELECT 
 product_name,
 category,
 sales,
 ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
 FROM product_sales
) ranked
WHERE rn <= 3;
-- Moving averages
SELECT 
 date,
 value,
 AVG(value) OVER (
 ORDER BY date
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 ) as seven_day_avg
FROM metrics;

Data Cleaning Patterns

-- Remove duplicates
DELETE FROM employees e1
WHERE EXISTS (
 SELECT 1 FROM employees e2
 WHERE e2.email = e1.email 
 AND e2.id > e1.id
);
-- Update with COALESCE for NULL handling
UPDATE customers 
SET phone = COALESCE(NULLIF(TRIM(phone), ''), mobile_phone, 'No phone');
-- Standardize data format
UPDATE addresses 
SET state = UPPER(TRIM(state))
WHERE LENGTH(TRIM(state)) = 2;

ETL (Extract, Transform, Load) Patterns

-- Bulk insert with conflict resolution
INSERT INTO target_table (id, name, updated_at)
SELECT id, name, NOW()
FROM source_table
ON CONFLICT (id) 
DO UPDATE SET 
 name = EXCLUDED.name,
 updated_at = EXCLUDED.updated_at;
-- Data transformation with CTE
WITH cleaned_data AS (
 SELECT 
 id,
 TRIM(UPPER(name)) as name,
 CASE 
 WHEN age < 0 THEN NULL 
 WHEN age > 150 THEN NULL 
 ELSE age 
 END as age
 FROM raw_data
 WHERE name IS NOT NULL
)
INSERT INTO processed_data 
SELECT * FROM cleaned_data;

📚 Learning Resources

Official Documentation

🤝 Contributing

How to Contribute

Contribution Guidelines

📝 License

🙏 Acknowledgments

📞 Support