SQL Interview Questions Advanced

This section covers questions typically asked for:

Advanced interviews focus on:

rather than simple SQL syntax.

Q1. What is the difference between Clustered and Non-Clustered Indexes?

Clustered Index

Determines physical order of data.

Only one clustered index per table.

Non-Clustered Index

Separate structure pointing to table rows.

Multiple non-clustered indexes allowed.

Q2. What is a Covering Index?

A covering index contains all columns required by a query.

Example:

Index:

No table lookup needed.

Q3. What is the Leftmost Prefix Rule?

For index:

SQL can efficiently use:

and

Not always:

alone.

Q4. What is Index Fragmentation?

Over time indexes become inefficient due to:

Result:

Q5. Difference Between Rebuild and Reorganize Index?

REBUILD

Recreates entire index.

REORGANIZE

Defragments existing index.

Q6. What is a Query Execution Plan?

A roadmap showing how SQL executes a query.

Displays:

Q7. What is the difference between Index Scan and Index Seek?

Index Scan

Reads entire index.

Index Seek

Directly locates required rows.

Index Seek is usually faster.

Q8. What is a Table Scan?

Reading every row in a table.

Usually caused by:

Q9. What is Cardinality?

Cardinality refers to the uniqueness of values in a column.

High Cardinality:

Low Cardinality:

Q10. Why Are High Cardinality Columns Better for Indexing?

Because indexes become more selective.

Example:

is better than:

Q11. What is Query Optimization?

Improving performance while reducing:

Q12. What is Cost-Based Optimization?

The optimizer evaluates multiple execution plans and chooses the lowest estimated cost.

Q13. What is a Hash Join?

Join strategy that builds a hash table and matches rows.

Best for:

Q14. What is a Nested Loop Join?

For each row in one table:

in another table.

Best for:

Q15. What is a Merge Join?

Combines already sorted datasets.

Best for:

Q16. What is Partitioning?

Dividing a large table into smaller partitions.

Benefits:

Q17. What is Partition Pruning?

Reading only relevant partitions during query execution.

Example:

Q18. Difference Between Partitioning and Indexing?

Q19. What is a Materialized View?

A view whose results are physically stored.

Advantages:

Disadvantages:

Q20. What is a CTE?

Common Table Expression.

Temporary result set used within a query.

Q21. What is a Recursive CTE?

A CTE that references itself.

Used for:

Q22. What is a Window Function?

Performs calculations across related rows without collapsing results.

Example:

Q23. Difference Between RANK() and ROW_NUMBER()?

ROW_NUMBER()

Always unique.

RANK()

Duplicate values receive same rank.

Example:

Q24. What is DENSE_RANK()?

Like RANK but without gaps.

Example:

Q25. What is a Deadlock?

Two transactions waiting for each other indefinitely.

Example:

Q26. How Can Deadlocks Be Prevented?

Use:

Q27. What are Isolation Levels?

Control visibility between transactions.

Levels:

Q28. What is Phantom Read?

Rows appearing or disappearing between reads within the same transaction.

Q29. What is Dirty Read?

Reading uncommitted data from another transaction.

Q30. What is Repeatable Read?

Ensures same row returns same values during a transaction.

Q31. What is Serializable Isolation?

Highest isolation level.

Prevents:

Q32. What is SQL Injection?

Malicious SQL inserted into user input.

Example:

Q33. How to Prevent SQL Injection?

Q34. What is Dynamic SQL?

SQL generated at runtime.

Example:

Q35. Difference Between EXISTS and IN?

Large datasets often perform better with:

because it stops after finding a match.

Q36. Difference Between OLTP and OLAP?

Q37. What is a Star Schema?

Common Data Warehouse structure.

Components:

Q38. What is a Snowflake Schema?

Normalized version of Star Schema.

Benefits:

Q39. What is CAP Theorem?

Distributed systems can guarantee only two of:

simultaneously.

Q40. What is Database Sharding?

Splitting data across multiple servers.

Example:

Benefits:

Most Asked Advanced SQL Questions

Advanced Interview Quick Revision

Summary

Advanced SQL interviews focus heavily on performance tuning, indexing, transactions, execution plans, scalability, and database architecture. Understanding these concepts demonstrates the ability to work with large-scale production databases and enterprise systems.

In this lesson, you learned:

• 40 Advanced SQL Interview Questions
• Query Optimization
• Execution Plans
• Indexing
• Transactions
• Isolation Levels
• Window Functions
• Data Warehousing
• Sharding
• Distributed Systems

Next Step

Continue to the next lesson:

SQL Scenario-Based Interview Questions →