SQL Interviewing FAQs

SQL interview questions can vary depending on role, seniority, and type of project. Nevertheless, the MSSQLTips.com team recommends mastering the following topics ahead of any data-related position interview. The list assumes foundational SQL knowledge.

Typical questions can include:

– What are the differences between structured, semi-structured, and unstructured data?
– Explain the concepts behind the relational database.
– What type of programming language is SQL?
– How do we combine data from different tables?
– What are the different JOIN types?
– Explain the performance implications of the lack of clustered index.
– How can you improve the performance of a query with a WHERE clause?
– Name some of the SQL aggregate functions and provide an example of how to use them.

For more in-depth preparation for SQL-related role interviews, candidates should cover complex queries, query optimization and performance tuning, index strategies, analyzing and debugging execution plans, data modeling, transaction management, types of SQL Server reads.

The following SQL commands and concepts are important for mastering complex queries:

JOINs
JOINs combine data from multiple tables based on related columns. Understanding when and how to use each type is crucial. Here is a list of resources to help you master JOINs: SQL JOIN Types with Examples, Learn about SQL Joins on Multiple Columns, Join 3 Tables in SQL, SQL Update Statement with Join, Join SQL Server tables where columns include NULL values, Find Mismatched Data between SQL Server Tables using LEFT JOIN, EXCEPT, NOT IN and NOT EXISTS.

Subqueries
A query nested in another query for returning a scalar value, a row, or a set of rows. Subqueries may reference a value from the outer query. Here is a list of resources to help you master subqueries: Introduction to Subqueries in SQL Server, SQL Server Subquery Example, SQL Server Uncorrelated and Correlated Subquery.

CTEs
CTEs(Common Table Expressions) use the WITH clause to create temporary named result sets that exist only during the current session’s query execution. Here is a list of resources to help you master CTEs: CTE in SQL Server Examples, SQL Server CTE vs Temp Table vs Table Variable Performance Test, Recursive Queries using Common Table Expressions (CTE) in SQL Server, Fix SQL Server CTE Maximum Recursion Exhausted Error, SQL Server Common Table Expressions (CTE) usage and examples

Window Functions
Window functions perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does. Here is a tutorial to help you master window functions: SQL Server T-SQL Window Functions Tutorial.

To master the optimization and performance aspect you must be familiar with the query parser, query optimizer, and the SQL execution engine. In other words, understand how databases execute queries, the roles of the Query Parser for syntax validation, the Query Optimizer for creating an execution plan, and the Execution Engine for executing the plan and returning results. A helpful guide is available here: Understand how SQL Server works to write better T-SQL queries.

Indexes are data structures that improve query performance by providing fast data lookup paths, like a book’s index. They trade write performance and storage space for read performance. Here is a list of resources to help you master indexes: SQL Server Index Tutorial, Types of SQL Server Indexes, SQL Server Indexing Basics, SQL Server Index Basics, SQL Server Indexes to Improve Query Performance.

The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan, or execution plan. Here is a list of resources to help you make sense of execution plans: How to read SQL Server graphical query execution plans, Query Tuning in SQL Server with Set Statistics IO.

Normalization, denormalization, and Star/Snowflake schemas are the key concepts here. Various levels of normalization in SQL can be used to reduce data redundancy and have a better-structured relational data model. Strictly normalized models are often found in operational data stores, whereas the star/snowflake schemas are usually associated with data warehouses. Here is a list of resources to help you master data modeling: Understanding First Normal Form in SQL Server, Star Schema vs Snowflake Schema for Dimensional Modeling, Normalization in SQL to Reduce Data Redundancy.

Transaction isolation is the key term. A transaction is a logical unit of work that contains one or more SQL statements. It is treated as a single, indivisible operation – either all statements succeed (COMMIT) or none do (ROLLBACK). Here are several resource to start learning about transaction management: What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?, The Pros and Cons of Using T-SQL Soft Transactions, Reduce SQL Server Blocking with READ_COMMITTED_SNAPSHOT.

You need to know and be able to explain the difference between a physical read vs logical read. The I/O from an instance of the SQL Server Database Engine includes logical and physical reads. A logical read occurs every time the Database Engine requests a page from the buffer cache, also known as the buffer pool. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache. Here is more information on Read data pages in the Database Engine and SQL Server Read Ahead Reads.

On some occasions the interviewer will give you a problem description or a set of business requirements to solve by writing SQL code, building a data model, or asking you to provide a complete solution. The time can vary and can be between twenty to thirty minutes for a single SQL statement to over an hour for problems requiring a deeper answer. Interviewers use these scenarios to identify your level (junior, medior or senior) as well as gauge your thinking, core knowledge, problem-solving approach, and ability to map the problem to a specific set of commands, approach, or architecture.

The soft skills that make you a valuable team member are not much different between SQL related roles and any other role. Key soft skills include communication, transparency, and ability to balance between technical and non-technical stakeholders. Some jobs may require specific domain knowledge more than others. In any case contextualizing a problem and understanding its business impact is always helpful. As a data professional you should be open and willing to solidify current skills and learn new tools or technologies. This document summarizes the key qualities for a data professional.

Salary ranges can vary widely based on location, seniority, project, company, and other factors. According to Glassdoor, the median total pay gravitates around one hundred and twenty to one hundred and thirty thousand dollars in the US. In the EU, the gross annual pay can range from the low thirty thousand all the way to over seventy-five thousand Euros for top-tier senior roles.