SQL Fundamentals.
Interview cheat sheet.

SQL (Structured Query Language) is the standard language for relational database management systems (RDBMS) used to store, manipulate, and retrieve data. Key components include DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). Essential concepts include ACID properties, normalization, joins, indexing, constraints, and query optimization. Fundamental for any role involving databases, data analysis, or backend development where structured data management is required.

What is SQL?

• Structured Query Language for managing relational databases
• RDBMS: Relational Database Management System (MySQL, PostgreSQL, Oracle, SQL Server)

SQL Categories

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

Common Data Types

-- Numeric
INT, BIGINT, DECIMAL(p,s), FLOAT, DOUBLE

-- String
VARCHAR(n), CHAR(n), TEXT

-- Date/Time
DATE, TIME, DATETIME, TIMESTAMP

-- Boolean
BOOLEAN (TRUE/FALSE)

CREATE

-- Create Database
CREATE DATABASE company_db;

-- Create Table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    hire_date DATE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

ALTER

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

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

-- Drop column
ALTER TABLE employees DROP COLUMN phone;

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

DROP & TRUNCATE

-- Drop table (removes structure + data)
DROP TABLE employees;

-- Truncate (removes all data, keeps structure)
TRUNCATE TABLE employees;

INSERT

-- Single row
INSERT INTO employees (name, email, salary) 
VALUES ('John Doe', 'john@email.com', 50000);

-- Multiple rows
INSERT INTO employees (name, email, salary) VALUES 
('Jane Smith', 'jane@email.com', 60000),
('Bob Johnson', 'bob@email.com', 55000);

SELECT

-- Basic select
SELECT * FROM employees;
SELECT name, salary FROM employees;

-- With conditions
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE name LIKE 'J%';

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

-- Limiting results
SELECT * FROM employees LIMIT 10;

UPDATE

UPDATE employees 
SET salary = salary * 1.1 
WHERE dept_id = 5;

DELETE

DELETE FROM employees WHERE id = 10;

WHERE Clause Operators

-- Comparison
=, !=, <>, <, >, <=, >=

-- Logical
AND, OR, NOT

-- Special operators
IN, NOT IN, BETWEEN, LIKE, IS NULL, IS NOT NULL

-- Examples
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
SELECT * FROM employees WHERE dept_id IN (1, 2, 3);
SELECT * FROM employees WHERE email IS NOT NULL;

Pattern Matching

-- LIKE wildcards
% : Multiple characters
_ : Single character

SELECT * FROM employees WHERE name LIKE 'J%';    -- Starts with J
SELECT * FROM employees WHERE email LIKE '%@gmail.com';
SELECT * FROM employees WHERE phone LIKE '555-___-____';

Types of Joins

-- INNER JOIN (matching records in both tables)
SELECT e.name, d.dept_name 
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN (all from left, matching from right)
SELECT e.name, d.dept_name 
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN (all from right, matching from left)
SELECT e.name, d.dept_name 
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN (all from both)
SELECT e.name, d.dept_name 
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS JOIN (Cartesian product)
SELECT e.name, d.dept_name 
FROM employees e
CROSS JOIN departments d;

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

Common Aggregate Functions

COUNT(), SUM(), AVG(), MAX(), MIN()

-- Examples
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT dept_id, COUNT(*) as emp_count 
FROM employees 
GROUP BY dept_id;

GROUP BY & HAVING

-- GROUP BY with aggregate
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id;

-- HAVING (filter groups)
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 50000;

Types of Subqueries

-- Scalar subquery (returns single value)
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Column subquery (returns single column)
SELECT name 
FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NYC');

-- Row subquery (returns single row)
SELECT * FROM employees 
WHERE (dept_id, salary) = (SELECT dept_id, MAX(salary) FROM employees);

-- Table subquery (returns table)
SELECT * FROM 
(SELECT dept_id, AVG(salary) as avg_sal FROM employees GROUP BY dept_id) t
WHERE avg_sal > 50000;

Correlated Subqueries

-- Subquery references outer query
SELECT e1.name, e1.salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.dept_id = e1.dept_id);

-- UNION (distinct values)
SELECT name FROM employees
UNION
SELECT name FROM contractors;

-- UNION ALL (includes duplicates)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- INTERSECT (common values)
SELECT product_id FROM orders_2023
INTERSECT
SELECT product_id FROM orders_2024;

-- EXCEPT/MINUS (difference)
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Types of Constraints

-- PRIMARY KEY
id INT PRIMARY KEY

-- FOREIGN KEY
FOREIGN KEY (dept_id) REFERENCES departments(id)

-- UNIQUE
email VARCHAR(100) UNIQUE

-- NOT NULL
name VARCHAR(100) NOT NULL

-- CHECK
CONSTRAINT chk_age CHECK (age >= 18)

-- DEFAULT
status VARCHAR(20) DEFAULT 'active'

-- Create view
CREATE VIEW high_earners AS
SELECT name, salary, dept_id 
FROM employees 
WHERE salary > 70000;

-- Use view
SELECT * FROM high_earners;

-- Drop view
DROP VIEW high_earners;

-- Create index
CREATE INDEX idx_emp_name ON employees(name);
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Drop index
DROP INDEX idx_emp_name;

When to Use Indexes

• Columns in WHERE, JOIN, ORDER BY clauses
• Primary keys and foreign keys (usually automatic)
• Columns with high selectivity

Index Considerations

• Pros: Faster queries, improved performance
• Cons: Slower INSERT/UPDATE/DELETE, storage overhead

Stored Procedure

DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
    SELECT * FROM employees WHERE dept_id = dept_id;
END //
DELIMITER ;

-- Call procedure
CALL GetEmployeesByDept(5);

Function

DELIMITER //
CREATE FUNCTION CalculateBonus(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN salary * 0.1;
END //
DELIMITER ;

-- Use function
SELECT name, salary, CalculateBonus(salary) as bonus FROM employees;

ACID Properties

• Atomicity: All or nothing
• Consistency: Valid state to valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

Transaction Commands

START TRANSACTION;

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

-- If successful
COMMIT;

-- If error
ROLLBACK;

-- Savepoint
SAVEPOINT before_delete;
DELETE FROM orders WHERE order_date < '2020-01-01';
ROLLBACK TO before_delete;

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

-- RANK() and 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;

-- Partition By
SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank
FROM employees;

-- LAG and LEAD
SELECT name, salary,
       LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
       LEAD(salary) OVER (ORDER BY hire_date) as next_salary
FROM employees;

-- Basic CTE
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 70000
)
SELECT * FROM high_earners WHERE dept_id = 5;

-- Recursive CTE
WITH RECURSIVE emp_hierarchy AS (
    -- Anchor member
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy;

Query Optimization Tips

1. Use indexes on columns in WHERE, JOIN, ORDER BY
2. **Avoid SELECT *** - specify needed columns
3. Use LIMIT when possible
4. Avoid functions in WHERE clause on indexed columns
5. Use EXISTS instead of IN for subqueries
6. Proper JOIN order - smaller tables first
7. Use EXPLAIN to analyze query execution

Example Optimizations

-- Bad: Function on indexed column
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- Good: Range condition
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

-- Bad: IN with subquery
SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NYC');

-- Good: EXISTS
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d 
              WHERE d.id = e.dept_id AND d.location = 'NYC');

Complex Query Patterns

Find Employees Earning More Than Their Manager

SELECT e.name as employee_name, e.salary as emp_salary,
       m.name as manager_name, m.salary as mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Find Departments With No Employees

-- Using LEFT JOIN
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
WHERE e.dept_id IS NULL;

-- Using NOT EXISTS
SELECT dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

Find Top N Employees Per Department

-- Using Window Functions (Modern SQL)
WITH ranked_employees AS (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rn
    FROM employees
)
SELECT name, dept_id, salary
FROM ranked_employees
WHERE rn <= 3;

-- Using Correlated Subquery (Traditional)
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id AND e2.salary >= e1.salary
) <= 3;

Find Customers Who Made Orders Every Month

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE (
    SELECT COUNT(DISTINCT DATE_TRUNC('month', o.order_date))
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2023-01-01'
    AND o.order_date < '2024-01-01'
) = 12;  -- All 12 months

Find Products Never Ordered

SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id NOT IN (
    SELECT DISTINCT product_id 
    FROM order_items 
    WHERE product_id IS NOT NULL
);

-- Better performance with NOT EXISTS
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.product_id
);

Running Calculations

-- Running total with reset per group
SELECT 
    dept_id,
    employee_name,
    salary,
    SUM(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY hire_date 
        ROWS UNBOUNDED PRECEDING
    ) as running_total_by_dept,
    
    -- Moving average (last 3 employees)
    AVG(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY hire_date 
        ROWS 2 PRECEDING
    ) as moving_avg_3
FROM employees;

Data Analysis Patterns

Customer Cohort Analysis

WITH first_purchase AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) as cohort_month
    FROM orders
    GROUP BY customer_id
),
monthly_activity AS (
    SELECT 
        o.customer_id,
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date) as activity_month,
        DATEDIFF(month, fp.cohort_month, o.order_date) as months_since_first
    FROM orders o
    JOIN first_purchase fp ON o.customer_id = fp.customer_id
)
SELECT 
    cohort_month,
    months_since_first,
    COUNT(DISTINCT customer_id) as active_customers
FROM monthly_activity
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first;

Sales Growth Rate

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as monthly_revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY month) as prev_month_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY month)) * 100.0 
        / LAG(monthly_revenue) OVER (ORDER BY month), 2
    ) as growth_rate_percent
FROM monthly_sales
ORDER BY month;

Complex Joins and Subqueries

Self-Join for Hierarchical Data

-- Organization hierarchy with levels
WITH RECURSIVE org_hierarchy AS (
    -- Anchor: Top-level managers
    SELECT 
        id, name, manager_id, 
        name as top_manager,
        0 as level,
        CAST(name as VARCHAR(1000)) as hierarchy_path
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: All subordinates
    SELECT 
        e.id, e.name, e.manager_id,
        oh.top_manager,
        oh.level + 1,
        CONCAT(oh.hierarchy_path, ' -> ', e.name)
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT 
    name,
    level,
    hierarchy_path,
    (SELECT COUNT(*) FROM employees WHERE manager_id = org_hierarchy.id) as direct_reports
FROM org_hierarchy
ORDER BY top_manager, level, name;

Multiple Table Analysis

-- Customer lifetime value analysis
SELECT 
    c.customer_id,
    c.customer_name,
    c.registration_date,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.total_amount) as lifetime_value,
    AVG(o.total_amount) as avg_order_value,
    DATEDIFF(day, c.registration_date, MAX(o.order_date)) as days_active,
    
    -- Customer segmentation
    CASE 
        WHEN SUM(o.total_amount) >= 10000 THEN 'VIP'
        WHEN SUM(o.total_amount) >= 5000 THEN 'Premium'
        WHEN SUM(o.total_amount) >= 1000 THEN 'Regular'
        ELSE 'New'
    END as customer_segment,
    
    -- Recency, Frequency, Monetary (RFM) scoring
    NTILE(5) OVER (ORDER BY MAX(o.order_date) DESC) as recency_score,
    NTILE(5) OVER (ORDER BY COUNT(o.order_id)) as frequency_score,
    NTILE(5) OVER (ORDER BY SUM(o.total_amount)) as monetary_score

FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.registration_date
HAVING COUNT(o.order_id) > 0  -- Active customers only
ORDER BY lifetime_value DESC;

Index Strategy Examples

-- Analyze query performance
EXPLAIN ANALYZE
SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 50000 
AND e.hire_date >= '2020-01-01'
ORDER BY e.salary DESC
LIMIT 10;

-- Optimal indexes for above query:
CREATE INDEX idx_employees_salary_hire ON employees(salary, hire_date);
CREATE INDEX idx_employees_dept_lookup ON employees(dept_id, salary, name);

Query Rewriting Techniques

-- Original slow query
SELECT DISTINCT c.customer_name
FROM customers c
WHERE c.customer_id IN (
    SELECT o.customer_id 
    FROM orders o 
    WHERE o.order_date >= '2023-01-01'
);

-- Optimized with EXISTS
SELECT DISTINCT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
    AND o.order_date >= '2023-01-01'
);

-- Even better with JOIN
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01';

Avoiding Common Performance Pitfalls

-- BAD: Function on indexed column
SELECT * FROM orders 
WHERE YEAR(order_date) = 2023;

-- GOOD: Range condition
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' 
AND order_date < '2024-01-01';

-- BAD: Leading wildcard
SELECT * FROM customers 
WHERE customer_name LIKE '%Smith%';

-- GOOD: Use full-text search or leading characters
SELECT * FROM customers 
WHERE customer_name LIKE 'Smith%';

-- BAD: OR with different columns (doesn't use indexes well)
SELECT * FROM employees 
WHERE dept_id = 5 OR salary > 100000;

-- GOOD: UNION with specific conditions
SELECT * FROM employees WHERE dept_id = 5
UNION
SELECT * FROM employees WHERE salary > 100000;

Fundamental Concepts

1. ACID Properties: Atomicity, Consistency, Isolation, Durability
2. Normalization: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies)
3. Query Execution Order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
4. NULL Handling: NULL ≠ NULL, use IS NULL/IS NOT NULL, COALESCE for defaults

Join Expertise

• Inner vs Outer Joins: When to use each type
• Self-joins: Hierarchical data, comparing rows within same table
• Cross joins: Cartesian product use cases
• Join optimization: Proper indexing and join order

Advanced Features

• Window Functions: ROW_NUMBER(), RANK(), LAG(), LEAD(), running totals
• CTEs: Recursive queries, complex data transformations
• Subqueries: Correlated vs non-correlated, EXISTS vs IN performance

Performance Optimization

• Index strategies: Composite indexes, covering indexes
• Query rewriting: EXISTS vs IN, avoiding functions on indexed columns
• EXPLAIN plans: Understanding query execution paths
• Common pitfalls: Leading wildcards, OR conditions, functions in WHERE

Real-World Problem Solving

• Data deduplication: Finding and removing duplicates
• Hierarchical queries: Organization charts, category trees
• Analytics queries: Cohort analysis, growth calculations
• Complex filtering: Multi-table conditions, date ranges

Study Approach

1. Practice basic queries first: SELECT, JOIN, GROUP BY
2. Master window functions: Essential for modern SQL interviews
3. Understand performance: EXPLAIN plans, indexing strategies
4. Study real scenarios: E-commerce, HR, financial data problems

Common Question Types

• Write a query to find...: Employees without managers, duplicate records
• Optimize this query: Identify bottlenecks, suggest indexes
• Explain the difference: INNER vs LEFT JOIN, WHERE vs HAVING
• Design a schema: Normalize tables, define relationships

Best Practices for Interviews

• Think aloud: Explain your approach before writing
• Consider edge cases: NULL values, empty results
• Optimize as you go: Mention index needs, performance concerns
• Test your logic: Walk through with sample data

Common Mistakes to Avoid

• Forgetting NULL handling: Always consider NULL values
• Inefficient subqueries: Use EXISTS instead of IN when possible
• Missing GROUP BY columns: All non-aggregate SELECT columns must be in GROUP BY
• Cartesian products: Always specify join conditions

Advanced Topics to Master

• Recursive CTEs: For hierarchical data
• Pivot/Unpivot: Data transformation techniques
• Date/Time functions: Handling temporal data effectively
• String manipulation: CONCAT, SUBSTRING, REPLACE patterns

Remember: SQL mastery comes from practice. Focus on understanding the logical flow of queries, when to use different techniques, and how to optimize for performance. Always consider data integrity, NULL handling, and edge cases in your solutions.