SQL Interview Questions Demystified: Detailed Answers with Code Snippets

 

30 SQL Interview Questions & Answers (Beginner → Advanced) — With Detailed Explanations & Code

SQL is the language of relational data. Interviews test not just syntax but how you think about data modeling, correctness, and performance. Crack your next SQL interview with confidence! Explore detailed SQL interview questions, real-world answers, and practical code snippets for all levels.Use these Q&As to sharpen fundamentals and ace practical scenarios.

---

🌱 Beginner / Fresher Level (1–10)

1) What is SQL? What problems does it solve?

Why it matters: Interviewers want to see if you grasp SQL’s role across systems and why relational databases still dominate.

Answer:
SQL (Structured Query Language) is the standard language for working with relational databases. It lets you define schemas (DDL), manipulate data (DML), control transactions (TCL), and manage permissions (DCL). SQL is declarative—you state what you want, and the optimizer figures out how to get it through indexes, joins, and plans. Because of its standardization and ACID guarantees, SQL remains the backbone for OLTP systems, analytics staging, and data warehousing.

Example SQL:

-- Get active customers created in 2025
SELECT customer_id, full_name, created_at
FROM customers
WHERE status = 'ACTIVE' AND created_at >= DATE '2025-01-01';

Pitfalls/Tips:

• SQL is 3-valued logic (TRUE/FALSE/UNKNOWN via NULL).

• Different engines add extensions (e.g., TOP, LIMIT, QUALIFY).

---

2) DDL vs DML vs DCL vs TCL — explain with examples

Why it matters: Shows you understand the full lifecycle: structure, data, security, consistency.

Answer:

• DDL (Data Definition Language): schema changes — CREATE, ALTER, DROP.

• DML (Data Manipulation Language): data changes — SELECT, INSERT, UPDATE, DELETE, MERGE.

• DCL (Data Control Language): security — GRANT, REVOKE.

• TCL (Transaction Control Language): atomic changes — COMMIT, ROLLBACK, SAVEPOINT.
  Separation helps auditing, deployments, least-privilege access, and rollback planning.

Example SQL:

-- DDL
CREATE TABLE dept (dept_id INT PRIMARY KEY, dept_name VARCHAR(100) UNIQUE);

-- DML
INSERT INTO dept VALUES (10, 'Finance');

-- DCL (syntax varies)
GRANT SELECT ON dept TO analyst_role;

-- TCL
BEGIN;
UPDATE dept SET dept_name = 'Corporate Finance' WHERE dept_id = 10;
COMMIT;  -- or ROLLBACK;

Pitfalls/Tips:

• Some systems auto-commit DDL.

• Favor least-privilege DCL for production.

---

3) Primary Key vs Unique vs Foreign Key

Why it matters: Keys protect integrity and enable efficient joins.

Answer:
A Primary Key uniquely identifies a row and cannot be NULL (one per table). A Unique constraint enforces uniqueness but may allow one NULL (DB-specific). A Foreign Key references a parent table’s key and enforces referential integrity, optionally with ON DELETE/UPDATE CASCADE/RESTRICT/SET NULL.

Example SQL:

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(100) UNIQUE
);

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(100) NOT NULL,
  dept_id INT,
  CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
      ON DELETE SET NULL
);

Pitfalls/Tips:

• Choose surrogate vs natural keys deliberately.

• Foreign keys help correctness and optimizer statistics.

---

4) WHERE vs HAVING (and why HAVING isn’t a WHERE replacement)

Why it matters: Aggregation logic is a common source of wrong answers.

Answer:
WHERE filters rows before grouping/aggregation; HAVING filters groups after GROUP BY. Use WHERE for column-level conditions and HAVING for aggregate conditions (like SUM(), COUNT()).

Example SQL:

-- High-value orders by customer, only include customers with > 3 orders
SELECT customer_id, COUNT(*) AS orders_cnt, SUM(amount) AS total_amt
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
HAVING COUNT(*) > 3 AND SUM(amount) > 1000;

Pitfalls/Tips:

• Don’t push aggregate predicates into WHERE.

• You can use both to minimize scanned rows early.

---

5) INNER vs LEFT vs RIGHT vs FULL OUTER JOIN (with NULL behavior)

Why it matters: Joins are the heart of relational querying.

Answer:

• INNER JOIN: only matched rows.

• LEFT JOIN: all left rows, NULLs for unmatched right.

• RIGHT JOIN: mirror of left.

• FULL OUTER JOIN: everything from both sides, NULLs where no match.
  Understand NULL propagation and duplicate matches (one-to-many expands rows).

Example SQL:

-- Employees with department names (include employees without a department)
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Pitfalls/Tips:

• Filters on right-table columns in LEFT JOIN should be placed in ON, not WHERE (to preserve null-extended rows).

• Prefer LEFT JOIN over RIGHT; it’s easier to reason about.

---

6) DELETE vs TRUNCATE vs DROP

Why it matters: Data operations differ in speed, logging, and rollback.

Answer:
DELETE removes selected rows and logs each change; often rollback-able and triggers fire. TRUNCATE deallocates data pages (faster), resets identity (DB-specific), usually cannot filter, minimal logging. DROP removes the entire object (table + indexes + constraints). Plan carefully for permissions, locking, and recovery.

Example SQL:

DELETE FROM employees WHERE status = 'INACTIVE';
TRUNCATE TABLE staging_sales;  -- fast clear before reload
DROP TABLE tmp_work;           -- remove object completely

Pitfalls/Tips:

• TRUNCATE may be blocked by FK references.

• Always confirm you’re not in production before DROP.

---

7) NULL semantics (why NULL = NULL is UNKNOWN)

Why it matters: Misunderstood NULLs cause wrong results.

Answer:
NULL represents unknown. Comparisons with NULL yield UNKNOWN, not TRUE/FALSE, so use IS NULL / IS NOT NULL. Aggregates skip NULLs (except COUNT(*)). COALESCE/IFNULL can provide defaults, but use them carefully to avoid masking data quality issues.

Example SQL:

SELECT
  COALESCE(middle_name, 'N/A') AS mid,
  CASE WHEN phone IS NULL THEN 'MISSING' ELSE phone END AS phone_label
FROM customers;

Pitfalls/Tips:

• WHERE col = NULL returns no rows. Use IS NULL.

• Be mindful of NULLs in joins and uniqueness.

---

8) DISTINCT vs GROUP BY (and when to use which)

Why it matters: Both deduplicate but serve different purposes.

Answer:
DISTINCT removes duplicate rows in the projection. GROUP BY aggregates rows and can compute measures. If you only need unique pairs, DISTINCT is shorter; if you need aggregates per key, GROUP BY is correct.

Example SQL:

-- Unique (customer_id, product_id) pairs
SELECT DISTINCT customer_id, product_id FROM orders;

-- Total orders per customer
SELECT customer_id, COUNT(*) AS orders_cnt
FROM orders
GROUP BY customer_id;

Pitfalls/Tips:

• DISTINCT applies to entire select list.

• Don’t use DISTINCT to “fix” duplication caused by a wrong join.

---

9) CHAR vs VARCHAR (and NVARCHAR)

Why it matters: Storage, performance, and correctness for text.

Answer:
CHAR(n) is fixed-length (pads spaces) and good for truly fixed codes; VARCHAR(n) is variable-length and preferred for most text. NVARCHAR stores Unicode (wider). Over-allocating length can bloat indexes; under-allocating truncates data.

Example SQL:

CREATE TABLE countries (
  iso_code CHAR(2) PRIMARY KEY,   -- fixed
  name     VARCHAR(100),          -- variable
  native_name NVARCHAR(100)       -- Unicode
);

Pitfalls/Tips:

• Trimming space-padded CHAR values may be needed.

• Use Unicode for multi-lingual datasets.

---

10) Aggregate functions & GROUP BY correctness

Why it matters: Ensures accurate metrics under groupings.

Answer:
Aggregates (SUM, AVG, COUNT, MIN, MAX) summarize rows. With GROUP BY, all non-aggregated selected columns must be grouped. COUNT(*) counts rows; COUNT(col) ignores NULLs. Always confirm granularity matches business logic.

Example SQL:

SELECT product_id,
       COUNT(*)                AS orders_cnt,
       COUNT(shipped_at)       AS shipped_cnt,   -- ignores NULL shipped_at
       AVG(amount)             AS avg_amount,
       SUM(amount)             AS total_amount
FROM orders
GROUP BY product_id;

Pitfalls/Tips:

• Mixing granularities (e.g., join to a detail table) can inflate aggregates.

• Validate totals with a unit test query.

---

⚙️ Intermediate Level (11–20)

11) Subqueries: correlated vs non-correlated (and performance)

Why it matters: Common filter pattern; correlated subqueries can be slow.

Answer:
A non-correlated subquery is independent and runs once; a correlated subquery references the outer row and runs per row (optimizer may rewrite as join). Prefer EXISTS/JOIN for performance when appropriate, but subqueries can be the clearest expression.

Example SQL:

-- Non-correlated: filter by global average
SELECT emp_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated: employees earning above their dept average
SELECT e.emp_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id
);

Pitfalls/Tips:

• Correlated subqueries may scan repeatedly; check plan.

• EXISTS often wins for existence checks.

---

12) Window functions (analytics without collapsing rows)

Why it matters: Essential for ranking, running totals, time comparisons.

Answer:
Window (analytic) functions compute values across a window of rows relative to the current row, preserving row cardinality. Use PARTITION BY to define groups and ORDER BY to define order; optionally add frames (ROWS/RANGE BETWEEN ...) for moving windows.

Example SQL:

-- Top earners per department and delta to department avg
SELECT emp_id, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)        AS dept_rank,
       AVG(salary) OVER (PARTITION BY dept_id)                        AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept_id)               AS delta_vs_avg
FROM employees;

Pitfalls/Tips:

• Default frame differs by DB (e.g., RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

• For moving averages, specify frame explicitly.

---

13) UNION vs UNION ALL vs INTERSECT vs EXCEPT/MINUS

Why it matters: Set logic clarity and performance.

Answer:
UNION deduplicates; UNION ALL preserves duplicates (faster). INTERSECT returns common rows; EXCEPT/MINUS returns rows in left not in right. All require same number and compatible types. Prefer UNION ALL when you don’t need deduplication.

Example SQL:

-- All cities from customers and suppliers, keep duplicates
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Pitfalls/Tips:

• Dedup can force sorts/hash, increasing cost.

• Order columns consistently to avoid implicit casts.

---

14) EXISTS vs IN vs ANY/ALL

Why it matters: Correctness and performance in semi/anti joins.

Answer:
IN tests membership in a list or subquery result. EXISTS checks whether a subquery returns any row—often faster on large sets because it can short-circuit. ANY/ALL compare against set values. For anti-joins, use NOT EXISTS (safer with NULLs) rather than NOT IN with a nullable subquery.

Example SQL:

-- Customers who placed at least one order (semi-join)
SELECT c.customer_id
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- Customers with no orders (anti-join)
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Pitfalls/Tips:

• NOT IN (subquery) returns no rows if subquery contains NULL; prefer NOT EXISTS.

---

15) Indexes: clustered vs non-clustered, composite, selectivity, sargability

Why it matters: Index literacy = query speed.

Answer:
A clustered index defines the physical row order (one per table in many DBs); non-clustered are separate structures pointing to rows. Composite indexes follow leftmost prefix—order columns by selectivity and filtering usage. Write sargable predicates (Search ARGument ABLE) so the engine can use indexes (avoid wrapping indexed columns in functions).

Example SQL:

-- Composite index: filter by (status, created_at) and then order by created_at
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Sargable: good
SELECT * FROM orders WHERE created_at >= DATE '2025-01-01';

-- Non-sargable: bad (function on column)
SELECT * FROM orders WHERE DATE(created_at) >= DATE '2025-01-01';

Pitfalls/Tips:

• Too many indexes slow writes.

• Covering indexes (include columns) can eliminate lookups.

---

16) CTEs (WITH) vs subqueries vs temp tables

Why it matters: Readability, reuse, and optimizer behavior.

Answer:
CTEs improve readability and can be recursive. Many engines inline CTEs (like views) at optimization time; some materialize them depending on settings/size. Subqueries are concise but can become nested and hard to read. Temp tables can persist intermediate results across steps and allow indexing—handy for complex ETL or when the optimizer struggles.

Example SQL:

WITH sales_2025 AS (
  SELECT * FROM sales WHERE order_date >= DATE '2025-01-01'
)
SELECT region, SUM(amount) AS total
FROM sales_2025
GROUP BY region;

Pitfalls/Tips:

• Don’t overuse temp tables—watch I/O and concurrency.

• Measure plans: sometimes a temp table + index outperforms a mega-join.

---

17) Stored procedures & parameterization (and SQL injection safety)

Why it matters: Reuse, security, and plan stability.

Answer:
Stored procedures encapsulate logic and promote parameterized execution (reducing SQL injection and improving plan reuse). Use strongly typed parameters, avoid dynamic SQL or sanitize/quote identifiers properly. Keep procedures idempotent where possible and log diagnostics.

Example SQL (T-SQL-ish):

CREATE PROCEDURE get_customer_orders @customer_id INT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT order_id, order_date, amount
  FROM orders
  WHERE customer_id = @customer_id;
END;

Pitfalls/Tips:

• Parameter sniffing can cause bad plans; consider recompile hints or parameter masking when needed.

---

18) CASE expressions (conditional logic)

Why it matters: Transform data without multiple passes.

Answer:
CASE evaluates conditions and returns derived values inline. Use it for bucketing, decoding codes, or guarded divisions. Prefer CASE WHEN ... THEN ... ELSE ... END over multiple queries.

Example SQL:

SELECT emp_id, salary,
       CASE
         WHEN salary >= 100000 THEN 'HIGH'
         WHEN salary >= 70000  THEN 'MID'
         ELSE 'LOW'
       END AS salary_band
FROM employees;

Pitfalls/Tips:

• Watch derived types (numeric vs text) to avoid implicit casts.

• Order conditions carefully—first TRUE wins.

---

19) COALESCE vs ISNULL/IFNULL and NULL-safe logic

Why it matters: Null handling and portability.

Answer:
COALESCE(a,b,...) returns the first non-NULL and is ANSI standard; ISNULL/IFNULL are engine-specific 2-arg variants. Use COALESCE for portability and multiple fallbacks. Avoid defaulting NULLs in a way that hides data quality issues in analytics.

Example SQL:

SELECT order_id,
       COALESCE(discount, 0) AS discount_applied
FROM orders;

Pitfalls/Tips:

• COALESCE can affect index use if applied to the indexed column—prefer applying to literals or select lists, not predicates.

---

20) RANK vs DENSE_RANK vs ROW_NUMBER (Top-N per group)

Why it matters: Essential for “top k” and dedup tasks.

Answer:
ROW_NUMBER() assigns a unique sequence; RANK() gives ties the same rank but leaves gaps; DENSE_RANK() removes gaps. For top-N per group, rank within PARTITION BY then filter.

Example SQL:

-- Top 3 products by sales amount in each category
WITH ranked AS (
  SELECT category_id, product_id, SUM(amount) AS total_amt,
         RANK() OVER (PARTITION BY category_id ORDER BY SUM(amount) DESC) AS rnk
  FROM sales
  GROUP BY category_id, product_id
)
SELECT * FROM ranked WHERE rnk <= 3;

Pitfalls/Tips:

• Use DENSE_RANK when you want all tied rows even if it exceeds N.

• For speed, ensure a supporting index on partition/order columns.

---

🚀 Advanced / Senior Level (21–30)

21) OLTP vs OLAP (and schema design implications)

Why it matters: Context decides schema, indexing, and tuning.

Answer:
OLTP systems optimize for short, frequent transactions with strict ACID, highly normalized schemas, and selective indexes. OLAP (analytics) optimizes for large scans/aggregations, using star/snowflake schemas, partitioning, and fewer but broader indexes. ETL/ELT bridges them; columnar stores dominate OLAP for compression and vectorized scans.

Example SQL (star schema aggregate):

SELECT d.date_key, p.category, SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.calendar_year = 2025
GROUP BY d.date_key, p.category;

Pitfalls/Tips:

• Avoid OLTP-style wide joins in OLAP; prefer fact-to-dim joins.

• OLAP benefits from partition pruning and columnar storage.

---

22) Query optimization: reading execution plans & sargability

Why it matters: Senior roles require diagnosing slow queries.

Answer:
Use EXPLAIN/EXPLAIN ANALYZE to inspect access paths (index seek/scan), join types (hash/merge/nested), and estimated vs actual rows. Rewrite non-sargable predicates (functions on columns, wildcards leading %) and ensure statistics are up to date. Consider covering indexes, predicate pushdown, and join order.

Example SQL:

EXPLAIN ANALYZE
SELECT o.order_id, o.amount
FROM orders o
WHERE o.status = 'COMPLETED'
  AND o.created_at >= DATE '2025-01-01';

Pitfalls/Tips:

• A wrong cardinality estimate cascades into bad join choices.

• Avoid SELECT *—it blocks narrow covering indexes.

---

23) Partitioning strategies (range, list, hash) & pruning

Why it matters: Big tables need physical strategies.

Answer:
Partitioning splits a table into segments on a key—range (e.g., by date), list (by country), hash (even distribution). Benefits: pruning (read only relevant partitions), faster maintenance (switch, rebuild), and parallelism. Choose a key aligned with common filters (e.g., order_date for time-series).

Example SQL (conceptual):

-- Range partition by order_date year (syntax varies by DB)
CREATE TABLE orders (
  order_id INT, order_date DATE, amount DECIMAL(12,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

Pitfalls/Tips:

• Too many small partitions add overhead.

• The partition key must appear in predicates to prune.

---

24) Materialized views vs views (refresh strategies)

Why it matters: Latency vs freshness trade-offs.

Answer:
A view is a saved query (virtual). A materialized view stores results physically, accelerating heavy aggregations at the cost of refresh complexity (on commit, on schedule, or manual). Use for expensive joins/aggregates with moderate freshness needs.

Example SQL:

CREATE MATERIALIZED VIEW mv_category_sales
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT category_id, DATE_TRUNC('day', order_date) AS day, SUM(amount) AS total
FROM sales
GROUP BY category_id, DATE_TRUNC('day', order_date);

Pitfalls/Tips:

• Fast refresh often requires materialized view logs.

• Validate staleness tolerance with stakeholders.

---

25) Recursive CTEs (hierarchies, paths)

Why it matters: Organizational trees, dependencies, bill of materials.

Answer:
Recursive CTEs iterate until no new rows are found. Start with an anchor (root) query, then recursive member referencing the CTE. Useful for paths, depth, and rollups. Beware cycles; some DBs support CYCLE detection.

Example SQL (employees → managers):

WITH RECURSIVE org AS (
  SELECT emp_id, manager_id, emp_name, 0 AS lvl, CAST(emp_name AS VARCHAR(400)) AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.manager_id, e.emp_name, o.lvl + 1,
         CONCAT(o.path, ' > ', e.emp_name)
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT * FROM org ORDER BY path;

Pitfalls/Tips:

• Add depth limits to prevent runaway recursion.

• Index the parent key for speed.

---

26) Handling duplicates (find and delete while keeping one)

Why it matters: Data hygiene is a core responsibility.

Answer:
Find duplicates by grouping on business keys. To delete while keeping the “first”, rank rows per business key using ROW_NUMBER() and delete where rank > 1. Always validate with a SELECT first.

Example SQL:

-- Identify duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete dups, keep lowest id
WITH d AS (
  SELECT user_id, email,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
  FROM users
)
DELETE FROM users
WHERE user_id IN (SELECT user_id FROM d WHERE rn > 1);

Pitfalls/Tips:

• Use transactions; consider archiving before delete.

• Define true business key to avoid false positives.

---

27) Isolation levels & MVCC vs locking

Why it matters: Explains correctness under concurrency.

Answer:
Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable (and Snapshot variants). Engines implement via locks or MVCC (multi-version concurrency control). Higher isolation reduces anomalies (dirty/non-repeatable/phantom reads) but costs throughput. Choose per workload.

Example (concept):

-- Pseudocode for setting isolation (syntax varies)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- queries here
COMMIT;

Pitfalls/Tips:

• Long transactions at high isolation can cause contention.

• Snapshot/MVCC reduces blocking but increases version storage.

---

28) Deadlocks: detection, prevention, resolution

Why it matters: Senior engineers must debug these quickly.

Answer:
A deadlock occurs when two transactions hold locks the other needs. Engines detect and abort a victim. Prevent with consistent lock ordering, keeping transactions short, and indexing to narrow lock footprints. Consider NOWAIT/SKIP LOCKED patterns for queues.

Example SQL (anti-pattern):

-- T1 locks A then B; T2 locks B then A → potential deadlock.
-- Solution: always lock A then B in all code paths.

Pitfalls/Tips:

• Review queries for different join orders that imply different lock acquisition orders.

• Add retries on deadlock errors.

---

29) SCD (Slowly Changing Dimensions) Types 1/2/3 in SQL

Why it matters: Common DW interview topic.

Answer:

• Type 1: overwrite—no history.

• Type 2: add new versioned row (valid_from/valid_to/is_current).

• Type 3: store previous value in extra column(s).
  Type 2 preserves full history and is most common for analytics.

Example SQL (Type 2 upsert concept):

-- Close current record if attribute changes and insert a new current record
UPDATE dim_customer
SET valid_to = CURRENT_DATE - INTERVAL '1 day', is_current = FALSE
WHERE business_key = :bk AND is_current = TRUE
  AND (email <> :new_email OR city <> :new_city);

INSERT INTO dim_customer(business_key, email, city, valid_from, valid_to, is_current)
VALUES (:bk, :new_email, :new_city, CURRENT_DATE, DATE '9999-12-31', TRUE);

Pitfalls/Tips:

• Add a unique constraint on (business_key, is_current) or on valid date ranges (non-overlap).

• Use surrogate keys for fact table joins.

---

30) “Second highest salary” — multiple correct patterns (pros/cons)

Why it matters: A classic that tests thinking, not memorization.

Answer:
You can solve it with MAX of < MAX, OFFSET, or DENSE_RANK. Prefer window functions for clarity; they handle ties explicitly. The set-based solutions scale better than correlated MAX for large data with proper indexes.

Example SQL (dense rank, returns ties):

WITH r AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT salary
FROM r
WHERE dr = 2;

Alternative (strict single value, ignoring ties):

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Pitfalls/Tips:

• Define whether you want second distinct or row #2.

• Ensure numeric types are consistent (avoid implicit casts).

---

✅ Bonus: Practical Patterns You’ll Be Asked To Demonstrate

• Top-N per group → RANK()/ROW_NUMBER() with partition + filter.

• Pagination → ORDER BY ... OFFSET ... FETCH NEXT ....

• Anti-join → NOT EXISTS (NULL-safe).

• Moving window metrics → explicit ROWS BETWEEN n PRECEDING AND CURRENT ROW.

• Sargability → avoid functions on indexed columns in predicates.

---

🧭 Final Tips for Interviews

• Always confirm granularity (row vs group) and NULL behavior.

• Explain why a query is written a certain way (sargability, cardinality, join order).

• If asked to “optimize,” talk about indexes, stats, plans, and I/O—then demonstrate with a rewritten query.