Skip to main content

🟒 1. Database Design & Keys (FOUNDATION)

πŸ‘‰ Very frequently asked at the beginning

  • What is a primary key?
  • What is a foreign key?
  • Can a table exist without PK?
  • Composite key β€” pros/cons?
  • Why should PK not change?
  • Difference between PK and UK?
  • Can PK be non-clustered?

πŸ”΅ 2. Constraints & Data Types

πŸ‘‰ Basic but important

  • Constraints in SQL Server?
  • Types in SQL Server?
  • What is IDENTITY column?

🟣 3. SQL Query Basics (WRITING vs EXECUTION)

πŸ‘‰ VERY COMMON interview trap

  • Written order for SQL query?
  • Execution order for SQL query?
  • What is DISTINCT?
  • Difference between WHERE and HAVING?
  • Difference between DELETE vs TRUNCATE vs DROP

🟑 4. Normalization & Data Modeling

πŸ‘‰ Tests database design thinking

  • 3NF

πŸ”΄ 5. Indexing (VERY IMPORTANT 🚨)

πŸ‘‰ This is one of the MOST important sections

  • What is an index?
  • Clustered vs non-clustered index
  • Is PK always clustered?
  • What is a heap table?
  • What happens without clustered index?
  • What is composite index?
  • Why column order matters?
  • What is covering index?
  • What is index fragmentation?
  • Why GUID is bad as clustered index?
  • How to design indexes?

🟠 6. Transactions & Concurrency

πŸ‘‰ High-level backend understanding

  • ACID
  • What is a transaction?
  • COMMIT vs ROLLBACK
  • What is isolation level?

🟒 7. Joins & Query Logic

πŸ‘‰ Core SQL skills

  • INNER JOIN, LEFT JOIN, RIGHT JOIN
  • EXISTS vs IN (also repeated)
  • Difference between EXISTS and IN

πŸ”΅ 8. Performance & Query Optimization (CRITICAL 🚨)

πŸ‘‰ This is where most candidates fail

  • What is query optimization?
  • Why is query slow?
  • What is execution plan?
  • What is SARGable query?
  • Why functions in WHERE are bad?
  • Why SELECT * is bad?
  • When index is not used?
  • Why LIKE '%abc%' is slow?
  • How to optimize a slow query?

🟣 9. Advanced / Real-world Issues (VERY IMPORTANT)

πŸ‘‰ This shows you are not junior anymore

  • What is N+1 problem?
  • How to fix N+1?
  • Include vs Select?
  • When to use AsEnumerable?
  • IQueryable vs IEnumerable?
  • What is deferred execution?

🟑 10. Database Objects

πŸ‘‰ Basic but expected

  • What is a view?
  • What is a stored procedure?
  • What is a function?