-- Bank transfer: Move ₹5000 from account 101 to account 202

BEGIN TRANSACTION;

  -- Step 1: Debit sender
  UPDATE accounts
  SET balance = balance - 5000
  WHERE account_id = 101;

  -- Step 2: Credit receiver
  UPDATE accounts
  SET balance = balance + 5000
  WHERE account_id = 202;

  -- Step 3: Log the transfer
  INSERT INTO transfer_log (from_acct, to_acct, amount, txn_time)
  VALUES (101, 202, 5000, CURRENT_TIMESTAMP);

COMMIT;
-- If ANY step fails → ROLLBACK undoes everything (Atomicity)

-- What happens on failure:
BEGIN TRANSACTION;

  UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
  -- ^^^ This succeeds

  UPDATE accounts SET balance = balance + 5000 WHERE account_id = 999;
  -- ^^^ Account 999 doesn't exist — ERROR

ROLLBACK;
-- Both updates are undone. Account 101 keeps its original balance.

-- Setting isolation level (PostgreSQL syntax)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
  SELECT balance FROM accounts WHERE account_id = 101;
  -- If another transaction changes this row and commits,
  -- a second SELECT in this transaction WILL see the new value.
  -- (Non-repeatable read is possible)
COMMIT;

-- Setting isolation level (MySQL syntax)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
  SELECT balance FROM accounts WHERE account_id = 101;
  -- Result: 50000

  -- Another session updates balance to 60000 and commits.

  SELECT balance FROM accounts WHERE account_id = 101;
  -- Result: STILL 50000 (snapshot from start of transaction)
COMMIT;

-- SQL Server syntax
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
  SELECT * FROM orders WHERE customer_id = 42;
  -- No other transaction can INSERT/UPDATE/DELETE rows
  -- matching customer_id = 42 until this transaction ends.
COMMIT;

-- Normalized: Requires JOIN every time
SELECT o.order_id, c.customer_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Denormalized: customer_name stored directly in orders table
-- Faster reads, but you must update it everywhere if the name changes
SELECT order_id, customer_name, total
FROM orders_denormalized;

-- Basic index
CREATE INDEX idx_customers_email
ON customers (email);

-- Composite index (multi-column — order matters!)
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);
-- ✅ This helps: WHERE customer_id = 42 AND order_date > '2025-01-01'
-- ✅ This helps: WHERE customer_id = 42 (leftmost prefix)
-- ❌ This does NOT help: WHERE order_date > '2025-01-01' (skips first column)

-- Covering index: includes all columns the query needs
-- The DB can answer the query entirely from the index without touching the table
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date)
INCLUDE (total_amount, status);

-- Unique index
CREATE UNIQUE INDEX idx_users_email
ON users (email);

-- Drop an index
DROP INDEX idx_customers_email;

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
  AND order_date > '2025-01-01';

-- Example EXPLAIN output (PostgreSQL):
-- Index Scan using idx_orders_cust_date on orders
--   Index Cond: (customer_id = 42)
--   Filter: (order_date > '2025-01-01')
--   Rows Removed by Filter: 12
--   actual time=0.045..0.089 rows=38 loops=1

-- Translation: Used the index on customer_id, then filtered by date.
-- Very fast (0.089 ms). Returned 38 rows.

-- Approach 1: IN
SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);
-- Works fine for small orders table
-- PROBLEM: If orders.customer_id has NULLs, IN can behave unexpectedly

-- Approach 2: EXISTS (generally preferred for existence checks)
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);
-- Stops scanning orders as soon as it finds ONE match (short-circuit)
-- Handles NULLs safely

-- Approach 3: JOIN
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Need DISTINCT because a customer with 5 orders appears 5 times
-- Best when you also need order columns in the output

-- This is the classic trap interviewers love:

-- Suppose orders.customer_id has values: (1, 2, NULL)

-- NOT IN with NULLs: RETURNS NOTHING!
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- SQL evaluates: customer_id != 1 AND customer_id != 2 AND customer_id != NULL
-- Anything compared to NULL → UNKNOWN → entire WHERE clause → UNKNOWN → row excluded
-- Result: ZERO rows returned, regardless of data!

-- NOT EXISTS: Works correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Returns customers who genuinely have no orders

-- ❌ WRONG — this will NEVER find NULL rows
SELECT * FROM employees WHERE manager_id = NULL;
-- Evaluates to NULL (not TRUE), so no rows returned

-- ✅ CORRECT
SELECT * FROM employees WHERE manager_id IS NULL;

-- ✅ Also correct
SELECT * FROM employees WHERE manager_id IS NOT NULL;

-- Sample data:
-- sales: (100, 200, NULL, 300, NULL)

SELECT
    COUNT(*)          AS total_rows,      -- 5 (counts ALL rows including NULLs)
    COUNT(amount)     AS non_null_count,  -- 3 (skips NULLs)
    SUM(amount)       AS total,           -- 600 (NULLs ignored)
    AVG(amount)       AS average,         -- 200 (600/3, NOT 600/5!)
    MIN(amount)       AS minimum,         -- 100 (NULLs ignored)
    MAX(amount)       AS maximum          -- 300 (NULLs ignored)
FROM sales;

-- KEY INSIGHT: AVG ignores NULLs.
-- If you want NULLs treated as 0:
SELECT AVG(COALESCE(amount, 0)) FROM sales;  -- 600/5 = 120

-- NULLs NEVER match in JOINs
-- If orders.customer_id = NULL and customers.customer_id = NULL,
-- they will NOT join together because NULL = NULL → NULL (not TRUE)

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Rows with NULL customer_id are silently dropped from both sides

-- COALESCE: Returns the first non-NULL value
SELECT COALESCE(phone, mobile, email, 'No Contact')
FROM employees;
-- If phone is NULL → try mobile → try email → fallback to 'No Contact'

-- COALESCE for safe calculations
SELECT
    employee_name,
    base_salary + COALESCE(bonus, 0) + COALESCE(commission, 0) AS total_comp
FROM employees;
-- Without COALESCE: if bonus is NULL, total_comp becomes NULL

-- NULLIF: Returns NULL if the two values are equal
SELECT NULLIF(actual_count, 0)  -- Returns NULL if actual_count is 0
FROM inventory;
-- Useful to prevent division-by-zero:
SELECT total / NULLIF(count, 0) AS average  -- Returns NULL instead of error
FROM summary;

-- IFNULL (MySQL) / ISNULL (SQL Server): Two-argument COALESCE
SELECT IFNULL(phone, 'N/A') FROM employees;        -- MySQL
SELECT ISNULL(phone, 'N/A') FROM employees;        -- SQL Server
SELECT COALESCE(phone, 'N/A') FROM employees;      -- Standard SQL (use this)

DELETE FROM orders WHERE order_date < '2020-01-01';  -- Remove old orders
TRUNCATE TABLE temp_staging;                          -- Empty staging table
DROP TABLE IF EXISTS old_backup;                      -- Remove table entirely

-- WHERE filters rows, HAVING filters groups
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE status = 'active'          -- Filter rows BEFORE grouping
GROUP BY department
HAVING COUNT(*) > 5;             -- Filter groups AFTER grouping

-- UNION ALL is almost always what you want in data pipelines
SELECT name FROM employees_us
UNION ALL
SELECT name FROM employees_eu;

-- UNION only when dedup is needed
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

CREATE TABLE users (
    country_code  CHAR(2),        -- Always exactly 2 chars: 'US', 'IN', 'UK'
    email         VARCHAR(255)    -- Could be 5 chars or 200 chars
);

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,             -- One per table, no NULLs
    email         VARCHAR(255) UNIQUE,         -- Must be unique, but can be NULL
    ssn           VARCHAR(11) UNIQUE           -- Another unique constraint
);

-- Regular View (no data stored)
CREATE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE status = 'active';

-- Materialized View (PostgreSQL)
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

-- Must manually refresh:
REFRESH MATERIALIZED VIEW monthly_sales;

-- Stored Procedure (PostgreSQL)
CREATE PROCEDURE transfer_funds(sender INT, receiver INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = sender;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver;
    COMMIT;
END;
$$;

CALL transfer_funds(101, 202, 5000);

-- Function (PostgreSQL)
CREATE FUNCTION get_full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

SELECT get_full_name(first_name, last_name) FROM employees;

-- Audit trigger: Log every salary change
CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

-- Temp Table
CREATE TEMPORARY TABLE high_value_orders AS
SELECT * FROM orders WHERE total > 10000;
-- Can now run multiple queries against it, add indexes, etc.

-- CTE (preferred for interview coding)
WITH high_value AS (
    SELECT * FROM orders WHERE total > 10000
),
customer_totals AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM high_value
    GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE total_spent > 50000;

-- Subquery (avoid for complex logic — hard to read)
SELECT *
FROM (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    WHERE total > 10000
    GROUP BY customer_id
) sub
WHERE total_spent > 50000;