What is SQL? And the terminology you need first

-- DDL: Structure (Schemas/Tables)
CREATE, ALTER, DROP, TRUNCATE

-- DML: Rows (Data Manipulation)
INSERT, UPDATE, DELETE

-- DQL: Query (90% of Analyst Time)
SELECT col1, col2 FROM table;

-- DCL: Security & Permissions
GRANT, REVOKE

-- TCL: Transaction Integrity (ACID)
COMMIT, ROLLBACK, SAVEPOINT

-- Universal 6-Level Hierarchy:
Server Cluster   (Port 5432)
  └── Database   (RetailMart)
       └── Schema (Folder Namespace)
            └── Table (customers, orders)
                 ├── Column (Type + Rule)
                 └── Row (Atomic Entity)
                      └── Cell (Scalar)

-- Production Scope:
RetailMart: 16 schemas, 55 tables, ~24L rows.

-- Primary Key (PK):
-- Never NULL, never duplicate. Unique row ID.
id SERIAL PRIMARY KEY

-- Foreign Key (FK):
-- Points to parent PK. Prevents orphan records.
customer_id INT REFERENCES customers(id)

-- ACID Guarantees:
Atomicity  — All or nothing
Consistency — Rules never broken
Isolation   — Concurrency safety
Durability  — Survives server crashes

-- On 'Execute', PostgreSQL engine runs:

Stage 1: Parser
-- Checks grammar/syntax (catches typos like FORM)

Stage 2: Analyzer
-- Catalog lookup: do tables/cols exist? Types.

Stage 3: Planner (Cost-Based Optimizer)
-- The "Brain": evaluates 1000+ paths, picks best.

Stage 4: Executor
-- The "Hands": touches RAM buffer/disk, returns rows.

-- Imperative (Python / Java):
-- You micromanage HOW step-by-step:
results = []
for row in addresses:
    if row['city'] == 'Mumbai':
        results.append(row)

-- Declarative (SQL):
-- You declare WHAT data you want. Engine optimizes:
SELECT *
FROM customers.addresses
WHERE city = 'Mumbai';

-- Universal Production Rules:
• Semicolon (;) terminates every SQL statement.
• Keywords are case-insensitive (SELECT = select).
• Excel Hard Limit: 10,48,576 rows (crashes >10L).
• PostgreSQL: Crores of rows with index in ms.

-- SQL vs NoSQL (80 / 20):
• SQL: Strict schema, ACID, money & orders (80%).
• NoSQL: Flexible JSON, Redis caching, logs (20%).
• Polyglot Persistence: Production uses BOTH.