Whether you’re looking to hire a database administrator, QA tester, software engineer, or business analyst, you need to assess your candidates’ SQL knowledge. To do so, you should give them scenarios to explain, sample SQL queries to write, and commands to define, among other things.
Below are some of the most important SQL interview questions to ask your candidate as you evaluate both their SQL skills and non-technical soft skills.
Looking to hire the best remote developers? Arc can help you:
⚡️ Get instant candidate matches without searching
⚡️ Identify top applicants from our network of 350,000+
⚡️ Hire 4x faster with vetted candidates (qualified and interview-ready)
Try Arc to hire top developers now →
Fundamental SQL Interview Questions
What are joins in SQL?
A join is an operation that is used to combine data from multiple tables into a new table. Different types of joins specify how data between tables are matched into the new table. When you need to retrieve data from multiple tables in a single query, there’s a good chance that you’ll be using a join operation.
You may ask this question as a way to test your candidate’s fundamental understanding of how data is queried and how it’s necessary for almost every kind of application. We cannot simply dump all data into one table as it will get unwieldy and poorly organized.
Example: We would want to store a table of customers (with their name, address, company they represent, etc.) separately from a table of transactions (with the items purchased, when the transaction was made, how much items cost, who made the purchase, etc.).
When we store the information of who made the purchase, we would not want to duplicate all of the customer’s information into every row of the transactions table, and we would only want to store the customer ID. In order to answer a question such as “how many purchases were made by customers who live in a specific zip code?”, we would need to join the two tables to get this answer:
SELECT COUNT(*) FROM customers c JOIN transactions t ON t.customer_id = c.id WHERE c.zipcode = 94107;What is the difference between DELETE and TRUNCATE statements?
This is one of those SQL interview questions designed to gauge your candidate’s understanding of how database rows are stored and managed internally. While DELETE and TRUNCATE can both be used to remove all data from a table, the database processes these queries differently. DELETE operations can filter rows that are targeted as it supports a WHERE clause, whereas a TRUNCATE operation removes an entire table.
When deleting a whole table with these two operations, TRUNCATE performs faster at the expense of being unable to perform some operations that rely on the transaction log. For example, some SQL servers can rollback DELETE operations and not TRUNCATE operations.
In general, you should opt to use the DELETE operation due to benefits such as a history in the transaction log. In situations where a DELETE operation on an entire table takes too long, you can look into using the TRUNCATE operation. As the interviewer, you want to ask about the tradeoffs between the two operations.
What is the difference between a primary key and unique key?
Primary keys must be unique and are used to identify table records, whereas unique keys serve as constraints in the table’s data. Understanding unique keys shows the interviewer that you understand some of the important ways to maintain a database table’s data integrity.
For example, when you’re designing a table to track users, you may have a column for user_id as the primary key. user_id will be unique and is used to reference the rest of the data in a row. To maintain data quality, you may have a column for phone_number as a unique key to ensure that only one of each phone number can exist in the table.
What is the purpose of a foreign key in SQL?
Foreign keys are columns used to reference primary keys in other tables. This helps set a constraint on the column to enforce referential integrity across tables. Foreign keys are a fundamental concept in database table design, and interviewers will want to verify that you understand them and have used them before in the past. Understanding them shows that your candidate knows how to design schemas that span across multiple database tables.
For example, imagine that you have two tables: orders and users. Every order should have been created by a user so the orders table can have a foreign key to the user table’s primary key. This constraint ensures that every user defined in the orders table is referencing a valid row.
What are some ways to optimize a query?
Query plans are a useful way to examine how a query will be performed, and can help you better understand what is making your query slow.
For example, prepending a query in PostgreSQL with EXPLAIN will show the query plan for the command. This will reveal the table scans that will be involved in the query.
So if we wanted to check the behavior of the query: SELECT * FROM table_1;, we can run the query EXPLAIN SELECT * FROM table_1;. From there, the query plan will break down the various steps that will be taken to execute the query. It’s a common way to identify unnecessary full table scans that could be alleviated with setting up proper indices.
These techniques are important because interviewers want to understand how you may approach troubleshooting database queries. If a candidate only knows how to run a query against the database to measure performance, it shows a lack of familiarity and industry experience with using relational databases.
What are some ways to identify how a query can be optimized?
Queries can be optimized in many ways. A few common examples are as follows:
- Reduce amount of data to query with
WHEREclauses. - Limit the amount of useful rows the database needs to query with a
LIMITclause. - Add an index on columns that are frequently queried.
As tech recruiters, it’s essential for you to understand the purpose of this question. The goal here is not merely for your candidate to list out every example mentioned above. This open-ended question allows you to present scenarios where candidates can identify and apply specific optimizations. Being aware of this approach will help you assess candidates’ problem-solving skills and their ability to think critically in various situations.
What is normalization and what are the advantages of it?
Database normalization is a strategy to efficiently organize data in a database. The goal is to reduce redundant data so that the same data is not stored across multiple tables. Instead, data will be referenced with a primary key.
For example, a table named orders may have a column named user_id. Rather than maintaining a copy of user data in the orders table, we can simply reference that data from another table with a join.
This is often asked to gauge a candidate’s understanding of table design. Normalizing data is a key component of designing table schemas in relational databases.
What are entities and relationships?
An entity is an abstraction of a set of related data and are represented as tables. Relationships define how entities are associated with one another.
For example, let’s say we have two tables named orders and users. Our orders and users are our entities. We can imagine that one user could possibly have many orders. Therefore, users can have a “one-to-many” relationship with orders.
Entities and relationships are often used in the table schema design process. By understanding how to define entities and map their relationships, your candidate can show that they can be productive in a team’s collaborative session on database table design.
How can you set up a table so that queries for certain rows won’t result in full table scans?
Full table scans can be possibly avoided by querying on indexed columns and using limits. Indexed columns help the database optimize how it performs lookups on the tables.
There are many strategies for reducing the rows queried and these can be confirmed with query plans. You’ll want to ask this SQL question to gauge your candidate’s understanding of how to properly design SQL tables and optimize queries.
Struggling with interview prep? Meet senior developers from Amazon, Microsoft, and Google now on Codementor. They’ll help you tackle coding challenges, practice interviews, and sharpen your skills in live 1:1 sessions.
Book a session with our interview prep tutors today! Your first 15 minutes are free.
Explore our other software development interview questions and answers to prep for your next remote job.
Advanced SQL Interview Questions and Answers
Explain some different types of indexes in SQL.
A clustered index has data physically stored in the same area on a table so that they can be efficiently queried and retrieved together. A non-clustered index is often used for key-based queries whereas a clustered index is often used for ranges.
Most SQL databases will choose the best index type for your use case. You’d typically ask this question to gauge your candidate’s understanding of how indices are set up internally.
What is a scenario when you would choose to use a zero or blank space over a NULL value in a row?
Using a zero or blank space over a NULL value is a design decision. NULL can represent the absence of data.
An example can be made with middle names: if a user has not provided a middle name, then their middle name would be NULL. If a user has indicated that they have no middle name, then their middle name would be an empty string.
If we know we will never care for differentiating between a zero / blank space value and the absence of a value, then we can go ahead and default a column to a zero or blank space value. This might be useful in a table that keeps track of how many times something has occurred (e.g. number of website visits). We have no need to differentiate between 0 and absence of data as the absence of data implies 0, so we can simplify our application code by defaulting to 0.
As a hiring manager, you may ask a SQL interview question like this to gauge your candidate’s understanding of how different types are stored in a database table. Being able to communicate these decisions shows that they understand some considerations for choosing appropriate design tradeoffs.
What is the difference between a cross join and natural join?
When unspecified, a join will default to using an inner join. While there’s a chance that you may not have used a cross join or natural join before, understanding different join types can show your interviewer that you are familiar with different ways to combine data across tables.
A cross join will produce the Cartesian product between two tables. It is typically used when you want to create a combination using every row from two tables. For example, if you want to find every combination of colors between various pieces of clothing, you would use a cross join to retrieve every combination.
A natural join will produce a table that joins columns with the same names and types. Any shared column between tables will be considered for a join. This is different from the commonly-used inner join where joins are explicitly done on specified columns. What’s important to note is that if a natural join does not find any matching columns, it will essentially produce the same result as a cross join.
What are UNION, MINUS, and INTERSECT commands?
These three commands are known as set operations. You’ll ask your candidate this question to gauge their familiarity with data analysis and data processing using SQL. If they have used SQL extensively but haven’t been exposed to these commands, you may find that they use SQL commands to perform CRUD operations instead of data analysis.
UNIONwill produce a table that has the data contained in two tables.MINUSwill produce a table that has data from the first table minus that of the second table.INTERSECTwill produce a table that has shared data between the two tables.
If we imagine a Venn diagram with two intersecting circles and three distinct parts, UNION represents all three parts, MINUS represents the left part, and INTERSECT represents the middle part.
Write a SQL query to get the third-highest salary of an employee from employee_table.
SELECT salary FROM employee_table ORDER BY salary DESC LIMIT 1 OFFSET 2;
SELECT salaryspecifies that we only want to return the salary and not to include other columns.ORDER BY salary DESCreturns all of the results ordered from the greatest to least salaries.LIMIT 1specifies that we only want to retrieve a single row. Since the results are already ordered by theORDER BYfilter, this will return the greatest salary.OFFSET 2specifies that we want to skip the first 2 rows.
This question is composed of common commands in SQL. Your candidate should be familiar with all of these commands to effectively write SQL queries. Mastering SQL interview questions is crucial for developers, especially when dealing with complex concepts like dbt models.
Note that you might want to ask similar types of SQL interview questions with different phrasing. When you ask this type of question, you should look for candidates who are able to break down the query into subparts that build up to the final solution. This will show that they have a disciplined process for writing queries.
What is the need for group functions in SQL?
Group functions are one of the key ways to perform data analysis with SQL. You might ask this question to gauge whether your candidate uses SQL for CRUD, or if they have used SQL for data analysis.
Group functions help aggregate a set of rows into one group of data represented by the rows. These functions are often used to analyze tables to better make sense of the data they represent.
For example, let’s say that we have a users table that has a row named country that specifies the country the user is from. By using GROUP BY, we can determine a count of the number of users we have for each country in our table.
When you ask your interview questions for SQL, it’s also important to look for concrete examples of how they may have used group functions in the past.
Why are SQL functions used?
SQL functions provide ways to perform calculations on the database. These can include aggregations that are often used for analytics. An example of an aggregation function would be the AVG function that returns the average prices paid for a purchase: SELECT AVG(price) AS average_price FROM purchases.
When you ask this question, your candidate should be able to provide some examples of SQL functions that they have used and how it was used in their query.
What is the difference between HAVING clause and a WHERE clause?
WHERE is used to filter rows before they are possibly grouped. HAVING is used to filter out rows after they have been grouped. HAVING functions very similar to WHERE but is used after some form of aggregation. WHERE clauses should be preferred when possible as our query will be faster if we filter out data pre-aggregation than post, but some filters can only be done post-aggregation and so require the use of a WHERE clause.
Example: Given a table of orders, return the customer id and how many orders they’ve made among customers who have made at least 10 purchases. The only way to know how many orders a customer made is to first count (aggregate) all of their orders before filtering. We could write this query as SELECT customer_id FROM (SELECT COUNT(*) AS count, customer_id from orders GROUP BY 2) WHERE COUNT >= 10, or we can simplify it with a HAVING clause: SELECT COUNT(*) AS count, customer_id from orders GROUP BY 2 HAVING count >= 10
You may not ask this question to your candidate directly. You can also ask your candidate to write a query where both a WHERE and HAVING clauses are necessary to return the correct result.
How can you fetch alternate records from a table?
There are several ways to retrieve alternating records. Your goal is to gauge your candidate’s familiarity and comfort with writing SQL queries and leveraging functions.
It’s common for tables to have auto-incrementing primary keys. We can use the modulus operator % to retrieve alternating rows. This works because a number divided by 2 will always return either a 0 or 1.
SELECT * FROM <table_name> WHERE <table_id> % 2 = 0;
SELECT * FROM <table_name> WHERE <table_id> % 2 = 1;
Name the operator which is used in the query for pattern matching.
LIKE is used for string matching. Different forms of LIKE operations can be available in different SQL database implementations. For example, LIKE can often be used to perform case-insensitive pattern matching.
If we have a table named products with a column named name, we can search across every row for products with names that contain the word toy: SELECT * FROM products WHERE name LIKE '%toy%.
Pattern matching is often used to search for specific text in a column across many rows. You want a candidate who understands how pattern matching works and how it’s often applied in production databases.
When would it be more appropriate to use a Materialized View over a View?
Both Materialized Views and Views make it easier to query data by transforming some table data into its own structure. Materialized Views persist data whereas Views compute its data each time it is queried.
This means that Materialized Views have better read performance because the data is persisted. When performance is sufficient, it’s sometimes recommended to go with a View to reduce the data that would be stored with a Materialized View.
When you ask SQL interview questions like this one, you want to see that your candidate understands how to make performance tradeoffs in SQL. Even if you don’t ask this exact question, your candidate should be able to reference the benefits of a Materialized View or View in a situation where they have to craft complex queries.
List some advantages and disadvantages of a stored procedure.
Stored procedures are useful for SQL queries that are used repeatedly. In practice, we can construct a stored procedure in lieu of keeping a list of repeatedly-used SQL queries. This reduces room for error and standardizes a set of operations for reproducibility.
However, stored procedures are often very use case-specific and not portable. A stored procedure will not work in another database table with similar structure and will need to be recreated to make it work.
As they are custom, they are difficult to test and integrate with other tools. Developers often use tools that sit on top of databases to make application development easier — stored procedures are known to have incompatibilities with some of these tools.
When your candidate answers this SQL interview question, ask them to provide and reference an example of when they’ve used or considered using a stored procedure. Encourage your candidate is share relevant experience instead of simple definitions.
What are the differences between OLTP (ex. MySQL) and OLAP (ex. data warehouse) databases?
This is another one of those SQL developer interview questions designed to help you gauge whether a candidate understands the different SQL databases that may exist and how they differ from one another. Different use cases often dictate the type of SQL database to use.
OLTP databases are designed for fast queries with strong data integrity. They are typically optimized for handling day-to-day business operations that involve real-time reads and writes.
OLAP databases are optimized for offline analytics and typically involve complex aggregations. Compared to OLTP databases, they often have lower volumes of queries, contain more historical data, and involve significantly fewer edits.
If we illustrate this with a web application, an OLTP database probably handles the metadata for the web application. User activity and data is stored and retrieved for the OLTP database. OLAP databases will probably store the same type of data but be leveraged to understand key metrics such as user retention and behavior.
Your candidate’s response to this SQL question will be much more impactful if they provide examples of various OLTP databases and OLAP databases that they may have used.
Wrapping Up
A SQL technical interview is meant to assess your candidates’ level of understanding and to see if they’re right for the job. You can use these SQL interview questions to dig deeper into how your candidates approach solving problems and communicating.
With that said, soft skills are just as important if you want to recruit a great developer. As such, make sure to also spend some time crafting behavioral questions to get a better understanding of your candidates’ personalities, passions, and more.
Read More: Remember to Send a Thank-You Email After Your Interview!
You can also explore HireAI to skip the line and:
⚡️ Get instant candidate matches without searching
⚡️ Identify top applicants from our network of 350,000+ with no manual screening
⚡️ Hire 4x faster with vetted candidates (qualified and interview-ready)
