SQL

---

SQL Basics

1. DELETE vs TRUNCATE vs DROP

	DELETE	TRUNCATE	DROP
Scope	Selected rows	All rows	Entire table
Rollback	Yes	Usually no	No
Triggers	Yes	No	No
Speed	Slower	Faster	Instant

---

2. Primary Key vs Unique Key

Primary Key — uniquely identifies a row. Cannot be NULL. Only one per table.

Unique Key — also enforces uniqueness. Allows NULLs (DB-dependent). Multiple per table.

---

3. Clustered vs Non-Clustered Index

Clustered — sorts the actual table data on disk. Only one per table. Best for range queries.

Non-Clustered — separate structure pointing to data rows. Multiple allowed. Best for exact-match lookups on non-PK columns.

Textbook analogy

Clustered index = the page numbers in a book. The physical pages are ordered.

Non-clustered index = the index at the back. It says “Photosynthesis → page 45.” You find the entry, then flip to page 45 (a second step).

Trade-off: Indexes make SELECT faster but make INSERT/UPDATE/DELETE slower — the index must be updated too. Only index columns you query frequently.

---

4. Composite Index

Index built on multiple columns. Useful when queries filter or sort on those columns together. Column order matters — queries must reference the leading column.

---

5. View vs Materialized View

View — stores only the query definition. Data fetched on every execution.

Materialized View — stores the actual query result. Faster reads but requires periodic refresh.

---

6. Normalization

Process of eliminating redundancy and improving data integrity by splitting data into related tables. Each piece of data lives in one place.

Normalization vs Denormalization trade-off

Normalization — no redundancy, great for OLTP (banking, e-commerce). If Alice changes her email, update once.

Denormalization — adds redundancy to speed up reads. Used for OLAP/analytics (e.g., Twitter pre-computes the timeline table so the app doesn’t JOIN 4 tables on every page load).

→ Normalize for write-heavy systems. Denormalize for read-heavy analytics.

---

7. OLTP vs OLAP

	OLTP	OLAP
Purpose	Transactions	Analytics
Operations	Many small reads/writes	Large aggregations
Optimization	Row-based	Columnar
Examples	Banking, e-commerce	Data warehouse, BI

---

8. SQL Constraints

PRIMARY KEY · FOREIGN KEY · UNIQUE · CHECK · DEFAULT · NOT NULL

---

9. Foreign Key

Maintains referential integrity between two tables. A child row cannot exist without a matching parent row.

---

SQL Joins

10. Join types

Join	Returns
INNER JOIN	Rows matching in both tables
LEFT JOIN	All left rows + matching right rows
RIGHT JOIN	All right rows + matching left rows
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Cartesian product (A × B rows)
SELF JOIN	Table joined to itself (hierarchy queries)

---

11. When would you use a SELF JOIN?

Querying hierarchical data within the same table — e.g., employees table where manager_id references another employee_id.

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

---

Aggregations & Grouping

12. WHERE vs HAVING

WHERE — filters individual rows before grouping.

HAVING — filters aggregated groups after GROUP BY.

Why HAVING exists

You cannot use aggregate functions in WHERE. WHERE SUM(salary) > 500000 throws an error — SUM() hasn’t run yet.

Think of it as:

• WHERE = bouncer at the door (checks IDs before entry)
• GROUP BY = seating hostess (groups people at tables)
• HAVING = floor manager (removes tables that didn’t order enough)

SELECT department, SUM(salary) AS total
FROM employees
WHERE employment_type = 'Full-Time'   -- rows filtered first
GROUP BY department
HAVING SUM(salary) > 500000;          -- groups filtered after aggregation

---

13. COUNT(*) vs COUNT(column)

COUNT(*) — counts all rows including NULLs.

COUNT(column) — counts only non-NULL values in that column.

---

14. DISTINCT

Removes duplicate values from the result. Can slow queries on large datasets — use sparingly.

---

Window Functions

15. What is a Window Function?

Performs calculations across a set of rows without collapsing them like GROUP BY. Each row keeps its identity plus the computed value.

SELECT name, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

---

16. ROW_NUMBER() vs RANK() vs DENSE_RANK()

Function	Behavior	Example output
ROW_NUMBER()	Unique sequential numbers	1, 2, 3, 4
RANK()	Gaps after ties	1, 2, 2, 4
DENSE_RANK()	No gaps after ties	1, 2, 2, 3

ROW_NUMBER() is commonly used for deduplication — keep the row with ROW_NUMBER() = 1 per partition.

---

17. LAG() and LEAD()

LAG(col, n) — accesses the value n rows before the current row. Useful for trend analysis, period-over-period comparisons.

LEAD(col, n) — accesses the value n rows after the current row. Useful for forecasting.

SELECT date, revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue
FROM sales;

---

18. PARTITION BY

Divides result set into groups for window functions — similar to GROUP BY but keeps individual rows.

---

19. NTILE()

Divides rows into n equal-sized buckets. Useful for quartile/percentile analysis.

---

20. FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE(col) — returns the first value in an ordered partition. LAST_VALUE(col) — returns the last value in an ordered partition. (Note: requires adjusting the default window frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value).

---

SQL Performance

21. Why is a query slow?

• Missing indexes
• Full table scans
• Poor join conditions
• Too many rows without early filtering
• Bad execution plan

---

22. What is an Execution Plan?

Shows how the database executes a query step by step. Use EXPLAIN or EXPLAIN ANALYZE to identify bottlenecks (full scans, missing indexes, bad join order).

---

23. When should you NOT use indexes?

• Frequently updated columns (index must be updated on each write)
• Small tables (full scan is already fast)
• Low-cardinality columns (e.g., boolean — index rarely helps)

---

24. CTE vs Subquery

CTE — improves readability, can be recursive, referenced multiple times in the same query.

Subquery — embedded inside another query, harder to read for complex logic.

---

25. Partitioning (table-level)

Splits large tables into smaller logical segments (by date, region, etc.) for faster queries and easier maintenance.

---

26. Query Optimization checklist

• Filter early with WHERE before joining
• Avoid SELECT * — select only needed columns
• Index columns used in WHERE, JOIN, ORDER BY
• Use EXPLAIN to identify full scans
• Prefer CTEs for readability, temp tables for reuse within a session

---

Transactions & ACID

27. What is a Transaction?

A sequence of operations treated as one atomic unit — either all succeed or all fail.

---

28. ACID Properties

Atomicity — all or nothing.

Consistency — database moves from one valid state to another.

Isolation — concurrent transactions don’t interfere.

Durability — committed transactions survive failures.

---

29. Isolation Levels

Level	What it allows
Read Uncommitted	Dirty reads possible
Read Committed	Reads committed data only
Repeatable Read	Same reads within transaction
Serializable	Full isolation, highest safety

---

30. What is a Dirty Read?

Reading uncommitted data from another transaction that may later be rolled back.

---

31. What is a Deadlock?

Two transactions waiting on each other indefinitely. Database detects and kills one to break the cycle.

Advanced SQL

32. Recursive CTE

A CTE that references itself, used for hierarchical or tree-structured data (like organizational charts or bill of materials). Uses UNION ALL to combine the anchor member with the recursive member.

---

33. MERGE / UPSERT

A statement that inserts a row if it doesn’t exist, or updates it if it does. Often used in data warehousing to apply incremental changes.

---

34. PIVOT

Transforms row-level data into columnar data (e.g., turning months in a column into 12 separate columns). Unpivot does the reverse.

---

35. Dynamic SQL

SQL statements constructed and executed at runtime (e.g., using EXEC or sp_executesql). Useful when table names or column names aren’t known until execution, but carries a higher risk of SQL injection if not parameterized.