Types of DBMS:

1. Relational (RDBMS) — tables with rows and columns
   Examples: MySQL, PostgreSQL, Oracle, SQL Server
   Uses SQL for queries

2. NoSQL — non-tabular, flexible schema
   Document: MongoDB (JSON-like documents)
   Key-Value: Redis (fast caching)
   Column-family: Cassandra (wide columns)
   Graph: Neo4j (relationships)

3. Hierarchical — tree structure (parent-child)
   Example: IBM IMS (legacy)

4. Network — graph structure (many-to-many)
   Example: IDMS (legacy)

In interviews, 95% of questions are about RDBMS.

ACID ensures reliable database transactions:

A — Atomicity
    All or nothing. If any part of a transaction fails,
    the entire transaction is rolled back.
    Example: Bank transfer — debit AND credit must both
    succeed, or neither happens.

C — Consistency
    Database moves from one valid state to another.
    All constraints, triggers, and rules are satisfied.
    Example: Account balance cannot go negative if
    there is a CHECK constraint.

I — Isolation
    Concurrent transactions do not interfere with each
    other. Each transaction sees a consistent snapshot.
    Isolation levels: Read Uncommitted, Read Committed,
    Repeatable Read, Serializable.

D — Durability
    Once a transaction is committed, it survives system
    crashes. Data is written to permanent storage.
    Achieved through write-ahead logging (WAL).

Primary Key   — Uniquely identifies each row. Not NULL.
                One per table.
                Example: employee_id

Foreign Key   — References primary key of another table.
                Establishes relationships.
                Example: dept_id in employees → departments

Candidate Key — All columns that CAN be primary key.
                Minimal set of attributes that uniquely
                identify a row.

Super Key     — Any set of columns that uniquely identifies
                rows (includes extra columns).

Composite Key — Primary key made of 2+ columns.
                Example: (student_id, course_id) in
                enrollment table.

Unique Key    — Like primary key but allows ONE NULL.
                Multiple unique keys per table allowed.

Normalization = organizing data to reduce redundancy

1NF (First Normal Form):
- Each column has atomic (single) values
- No repeating groups
- Bad:  | Name | Phones          |
        | Raj  | 9876, 1234      |  ← multi-valued
- Good: | Name | Phone |
        | Raj  | 9876  |
        | Raj  | 1234  |

2NF (Second Normal Form):
- Must be in 1NF
- No partial dependency (non-key column depends on
  PART of composite key, not the whole key)
- Remove partial dependencies to separate tables

3NF (Third Normal Form):
- Must be in 2NF
- No transitive dependency (A → B → C means
  C depends on A through B)
- Example: employee_id → dept_id → dept_name
  Fix: move dept_name to departments table

BCNF (Boyce-Codd Normal Form):
- Stricter version of 3NF
- Every determinant must be a candidate key

When to denormalize:
- Read-heavy systems (reporting, analytics)
- When JOIN performance is a bottleneck
- Caching layers (materialized views)

-- INNER JOIN: only matching rows from both tables
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: all rows from left + matching from right
-- (NULL if no match on right)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN: all rows 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 rows from both tables
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- CROSS JOIN: cartesian product (every combination)
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- SELF JOIN: table joined with itself
-- Example: find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Method 1: Subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 3: DENSE_RANK (works for Nth highest)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) 
    AS rank
  FROM employees
) ranked
WHERE rank = 2;

-- This is the MOST asked SQL interview question.
-- Know all 3 methods.

-- WHERE: filters rows BEFORE grouping
SELECT dept_id, COUNT(*) as emp_count
FROM employees
WHERE salary > 50000        -- filters individual rows
GROUP BY dept_id;

-- HAVING: filters groups AFTER grouping
SELECT dept_id, COUNT(*) as emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;        -- filters groups

-- Can use both together:
SELECT dept_id, AVG(salary) as avg_sal
FROM employees
WHERE status = 'active'     -- filter rows first
GROUP BY dept_id
HAVING AVG(salary) > 60000; -- then filter groups

-- SQL execution order:
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

DELETE:
- DML command (can be rolled back)
- Removes specific rows (with WHERE clause)
- Fires triggers, slower for large tables
- DELETE FROM employees WHERE dept_id = 5;

TRUNCATE:
- DDL command (cannot be rolled back in most RDBMS)
- Removes ALL rows, resets auto-increment
- Does not fire triggers, much faster
- TRUNCATE TABLE employees;

DROP:
- DDL command
- Removes the ENTIRE table (structure + data)
- Cannot be recovered
- DROP TABLE employees;

Index = data structure that speeds up data retrieval
(like a book index — find topics without reading every page)

Types:
1. Clustered Index
   - Sorts the actual table data
   - Only ONE per table (like sorting a phone book by name)
   - Primary key creates clustered index by default

2. Non-Clustered Index
   - Separate structure pointing to data
   - Multiple per table allowed
   - Like a book's index at the back

3. Unique Index
   - Ensures no duplicate values in indexed column

4. Composite Index
   - Index on multiple columns
   - CREATE INDEX idx ON employees(dept_id, salary);

When NOT to index:
- Small tables (full scan is faster)
- Columns with low cardinality (gender: M/F)
- Tables with heavy INSERT/UPDATE (index maintenance cost)
- Columns rarely used in WHERE/JOIN

SQL (Relational)           NoSQL (Non-Relational)
─────────────────────      ─────────────────────
Fixed schema (tables)      Flexible schema
Structured data            Unstructured/semi-structured
ACID compliant             BASE (eventual consistency)
Vertical scaling           Horizontal scaling
Complex queries (JOINs)    Simple queries, fast reads
MySQL, PostgreSQL          MongoDB, Redis, Cassandra

When to use SQL:
- Financial data (ACID needed)
- Complex relationships
- Structured, predictable data

When to use NoSQL:
- Real-time apps (chat, gaming)
- Big data / high write volume
- Flexible/evolving schema
- Caching (Redis)