Interview questions for 7 to 10 years experience in SQL

​

Advanced SQL Interview Questions for 7-10 Years Experience

• Explain the difference between correlated and non-correlated subqueries with examples.
  Correlated subqueries reference the outer query and execute row-by-row, whereas non-correlated subqueries execute once independently. Understanding when to use each impacts query performance.

• How would you optimize a slow-running SQL query?
  Analyze execution plans, add appropriate indexes, rewrite queries for set-based operations, avoid cursors, update statistics, and consider partitioning large tables.

• Write a query to find the 2nd highest salary without using TOP or LIMIT.
  Using nested subqueries or window functions like ROW_NUMBER() or DENSE_RANK() helps achieve this in scalable ways.

• What are window functions? Provide use cases involving ranking and running totals.
  Window functions like ROW_NUMBER(), RANK(), SUM() OVER() allow complex analytics such as ranking rows and calculating cumulative sums without collapsing result sets.

• Explain the use and advantages of Common Table Expressions (CTEs) and recursive CTEs.
  CTEs improve query readability and modularity; recursive CTEs solve hierarchical data problems like organizational charts or tree traversals.

• Describe database partitioning strategies and scenarios where each is appropriate.
  Range, list, hash, and composite partitioning reduce query times and improve maintenance in very large databases.

• How do you implement data synchronization or UPSERT operations in SQL Server?
  The MERGE statement enables efficient inserts, updates, or deletes based on matching conditions between source and target tables.

• What are SQL Server’s indexing types and how do you choose indexes for a query?
  Clustered vs. non-clustered, filtered, and columnstore indexes play key roles in performance tuning based on data distribution and query patterns.

• Write a query to retrieve a manager hierarchy in an employee table with self-referencing foreign keys.
  Recursive CTEs are commonly used to traverse hierarchical relationships effectively.

• How do you handle NULLs in aggregate functions and joins?
  Use ISNULL(), COALESCE(), and careful join condition design to avoid surprises in query results.

Preparation Tips

• Be ready to discuss your experience optimizing large-scale SQL Server environments.

• Illustrate your knowledge of query tuning, indexing strategies, and new SQL Server features.

• Use real-life problem statements and explain your approach to solving data complexities.

• Practice writing queries using window functions, CTEs, recursive queries, and MERGE statements.

These questions and strategies will prepare you well for senior-level SQL interviews requiring 7 to 10 years of expertise.