15 SQL Interview Questions for Hiring SQL Developers

When hiring SQL developers, it’s essential to assess their proficiency in database design, query optimization, and data management. SQL developers play a vital role in ensuring efficient data storage and retrieval, critical to the success of any data-driven organization. The following 15 SQL interview questions are designed to test candidates’ technical knowledge and practical skills in SQL, helping you identify the best fit for your team.

SQL Interview Questions

1. What are the different types of JOINs in SQL, and how do they work?

Question Explanation:

JOINS are used to retrieve data from multiple tables based on related columns. A solid understanding of JOINS demonstrates the candidate’s ability to work with relational data effectively.

Expected Answer:

There are several types of JOINS in SQL:

1. INNER JOIN: Retrieves records with matching values in both tables.sqlCopy code

SELECT employees.name, departments.name  
FROM employees  
INNER JOIN departments  
ON employees.dept_id = departments.id;

2. LEFT JOIN (or LEFT OUTER JOIN): Retrieves all records from the left table and the matching ones from the right table.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Retrieves all records from the right table and the matching ones from the left table.
4. FULL JOIN (or FULL OUTER JOIN): Combines the results of LEFT JOIN and RIGHT JOIN, retrieving all records with or without matches.
5. CROSS JOIN: Produces a Cartesian product of the two tables, combining every row from the first table with every row from the second.

Evaluating Responses:

Look for accurate definitions and examples of JOINs. Strong candidates may provide use cases or discuss performance considerations when using each type of JOIN.

2. How do you write a query to find duplicate records in a table?

Question Explanation:

This tests the candidate’s ability to identify and resolve data quality issues using SQL.

Expected Answer:

To find duplicates, you use GROUP BY with HAVING to filter groups having more than one record.
Example:

SELECT column1, column2, COUNT(*) AS duplicate_count  
FROM table_name  
GROUP BY column1, column2  
HAVING COUNT(*) > 1;

Here, column1 and column2 are the columns to check for duplicates.

Evaluating Responses:

A complete answer should include a clear explanation of the GROUP BY and HAVING clauses. Bonus points if the candidate explains how to delete duplicates safely or handle complex cases.

3. What is the difference between a clustered index and a non-clustered index?

Question Explanation:

Indexes are essential for performance optimization. This SQL interview question checks the candidate’s understanding of different index types and their impact on queries.

Expected Answer:

• Clustered Index:
  • Determines the physical order of data in the table.
  • There can only be one clustered index per table.
  • Example: Primary keys are often implemented as clustered indexes.
• Non-Clustered Index:
  • Stores a separate structure with pointers to the physical data in the table.
  • There can be multiple non-clustered indexes per table.

Example:

CREATE CLUSTERED INDEX idx_clustered ON employees(id);  
CREATE NONCLUSTERED INDEX idx_nonclustered ON employees(name);  

Evaluating Responses:

Look for clarity in explaining the differences and an understanding of when to use each type. Candidates should mention how indexes affect performance and storage.

4. Explain the difference between DELETE and TRUNCATE commands.

Question Explanation:

DELETE and TRUNCATE are often used for removing data, but they differ significantly in execution and impact. Understanding these differences is crucial for database management.

Expected Answer:

• DELETE:
  • Removes specific rows from a table based on a condition.
  • Can include a WHERE clause.
  • Triggers and transaction logs are maintained.
  • Slower than TRUNCATE for large datasets.

DELETE FROM employees WHERE department = 'Sales';  

• TRUNCATE:
  • Removes all rows from a table.
  • Cannot include a WHERE clause.
  • Faster because it does not log individual row deletions.
  • Resets table identity counters.

TRUNCATE TABLE employees;  

Evaluating Responses:

The candidate should clearly state the differences and implications, such as the inability to rollback TRUNCATE operations if not wrapped in a transaction. Look for an understanding of practical use cases for each command.

5. How would you write a query to fetch the nth highest salary from an employee table?

Question Explanation:

This tests the candidate’s ability to solve complex queries, often requiring window functions, subqueries, or ranking mechanisms.

Expected Answer:

To fetch the nth highest salary, there are several methods:

Using the DENSE_RANK() function:

WITH RankedSalaries AS (  
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank  
    FROM employees  
)  
SELECT salary  
FROM RankedSalaries  
WHERE rank = n;  

Using a correlated subquery:

SELECT DISTINCT salary  
FROM employees e1  
WHERE n - 1 = (  
    SELECT COUNT(DISTINCT salary)  
    FROM employees e2  
    WHERE e2.salary > e1.salary  
);  

Replace n with the desired rank, e.g., 2 for the second-highest salary.

Evaluating Responses:

A good response demonstrates knowledge of different methods and their performance trade-offs. Bonus points if the candidate discusses indexing or efficiency for large datasets.

6. What are Common Table Expressions (CTEs), and how are they used?

Question Explanation:

CTEs simplify complex queries by breaking them into manageable parts. This SQL interview question checks the candidate’s ability to write readable and maintainable SQL code.

Expected Answer:

A CTE is a temporary, named result set that can be referenced within a single SELECT, INSERT, UPDATE, or DELETE statement.

Example of a CTE to calculate average salaries by department:

WITH DepartmentAverage AS (  
    SELECT department_id, AVG(salary) AS avg_salary  
    FROM employees  
    GROUP BY department_id  
)  
SELECT e.name, e.salary, da.avg_salary  
FROM employees e  
JOIN DepartmentAverage da  
ON e.department_id = da.department_id;  

Evaluating Responses:

Strong candidates explain CTEs clearly, provide an example, and describe their benefits, such as improved readability and debugging. Advanced responses may highlight recursive CTEs for hierarchical data.

7. How do transactions work in SQL, and why are they important?

Question Explanation:

Transactions ensure data integrity and consistency. This SQL interview question assesses the candidate’s understanding of ACID properties and their practical applications.

Expected Answer:

A transaction is a sequence of SQL statements that are executed as a single unit, adhering to the following ACID properties:

1. Atomicity: All operations within a transaction succeed or fail as a whole.
2. Consistency: Ensures the database remains in a valid state before and after the transaction.
3. Isolation: Prevents transactions from interfering with each other.
4. Durability: Ensures that committed transactions persist even in case of system failure.

Example:

BEGIN TRANSACTION;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
COMMIT;  

If an error occurs, the transaction can be rolled back:

ROLLBACK;  

Evaluating Responses:

The candidate should explain the concept and provide a relevant example. A strong answer includes the importance of error handling and isolation levels like READ COMMITTED and SERIALIZABLE.

8. Can you explain the concept of normalization? How many normal forms are there?

Question Explanation:

Normalization is a cornerstone of database design. This SQL interview question checks the candidate’s theoretical knowledge and understanding of efficient database structures.

Expected Answer:

Normalization organizes a database to reduce redundancy and improve data integrity. There are six recognized normal forms:

1. 1NF (First Normal Form): Ensures each column contains atomic values, and each row is unique.
2. 2NF (Second Normal Form): Meets 1NF and removes partial dependencies by ensuring every non-primary key attribute is fully dependent on the primary key.
3. 3NF (Third Normal Form): Meets 2NF and removes transitive dependencies, ensuring non-primary attributes depend only on the primary key.
4. BCNF (Boyce-Codd Normal Form): A stricter version of 3NF that eliminates anomalies caused by functional dependencies.
5. 4NF and 5NF: Address multi-valued and join dependencies, respectively, ensuring even higher levels of database integrity.

Example for 3NF:
A table with attributes StudentID, CourseID, and Instructor may be split into two tables:

• StudentCourse (StudentID, CourseID)
• CourseInstructor (CourseID, Instructor)

Evaluating Responses:

A strong response includes clear definitions, examples, and an understanding of the trade-offs between normalization and denormalization for performance optimization.

9. How would you optimize a query that is performing poorly?

Question Explanation:

Query optimization is vital for database performance, especially for large datasets. This SQL interview question evaluates the candidate’s knowledge of SQL performance tuning and problem-solving skills.

Expected Answer:

Several techniques can be employed to optimize a query:

1. Use Indexes:
   Indexes speed up data retrieval. Ensure the columns in WHERE, JOIN, and ORDER BY clauses are indexed appropriately

CREATE INDEX idx_employee_department ON employees(department_id);

2. Analyze Query Execution Plan:
   Use tools like EXPLAIN or EXPLAIN ANALYZE to identify bottlenecks, such as table scans.
3. Reduce Data Volume:
   • Use LIMIT to restrict results.
   • Use specific columns instead of SELECT *.
4. Optimize Joins:
   • Ensure proper indexing on join columns.
   • Consider join order for efficiency.
5. Avoid Subqueries When Possible:
   Replace subqueries with JOINs or CTEs for better performance.
6. Partitioning and Sharding:
   Split large tables into partitions for faster query execution.

Evaluating Responses:

Look for specific strategies with examples. Advanced candidates may discuss trade-offs, such as the impact of too many indexes on write performance or database-specific tuning tools.

10. What is the difference between an INNER JOIN and an OUTER JOIN? Provide examples.

Question Explanation:

This SQL interview question checks the candidate’s ability to differentiate between JOIN types and their practical applications in combining tables.

Expected Answer:

• INNER JOIN: Returns rows where there is a match in both tables

Example:

SELECT e.name, d.name  
FROM employees e  
INNER JOIN departments d  
ON e.dept_id = d.id;  

• OUTER JOIN: Includes rows with no matches:
  • LEFT JOIN: Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table are filled with NULL.

SELECT e.name, d.name  
FROM employees e  
LEFT JOIN departments d  
ON e.dept_id = d.id;  

• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in either table.

Evaluating Responses:

Look for clear definitions and examples. Bonus points if the candidate discusses performance implications or scenarios where one join type is preferable.

11. Explain the use of indexes in SQL. What are the advantages and potential downsides?

Question Explanation:

Indexes are critical for performance optimization, and this SQL interview question tests the candidate’s ability to understand their proper use and trade-offs.

Expected Answer:

• Advantages:
  • Speeds up data retrieval by reducing the number of rows scanned.
  • Improves performance of queries with WHERE, JOIN, GROUP BY, and ORDER BY.
• Types of Indexes:
  • Clustered Index: Data is stored physically in sorted order.
  • Non-Clustered Index: Creates a separate structure to point to data locations.
  • Unique Index: Ensures all values in a column are distinct.
• Downsides:
  • Slower INSERT, UPDATE, and DELETE operations due to index maintenance.
  • Increased storage requirements.
  • Over-indexing can lead to inefficiencies.

Example:

CREATE INDEX idx_name ON employees(name);  
SELECT * FROM employees WHERE name = 'John';  

Evaluating Responses:

Strong answers provide balanced views of advantages and disadvantages, examples, and scenarios for appropriate index usage. Advanced candidates may discuss database-specific indexing features like covering indexes.

12. How would you enforce referential integrity in a relational database?

Question Explanation:

This SQL interview question assesses the candidate’s understanding of database constraints and their role in maintaining consistent relationships between tables.

Expected Answer:

Referential integrity ensures that foreign key values in one table correspond to primary key values in another.

1. Using Foreign Key Constraints:

Define foreign keys when creating or altering tables.

CREATE TABLE orders (  
    order_id INT PRIMARY KEY,  
    customer_id INT,  
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)  
);  

2. Enforce Actions on Deletion or Update:

Specify cascading behavior to maintain data integrity:

• ON DELETE CASCADE: Automatically deletes dependent records.
• ON UPDATE CASCADE: Updates foreign key values when the referenced key changes.

3. Validations with Triggers:

Use triggers to enforce complex integrity rules beyond built-in constraints.

Evaluating Responses:

The candidate should demonstrate understanding of foreign keys, their syntax, and cascading options. Bonus points for mentioning scenarios where cascading actions may not be desirable and alternative methods like application-level validation.

13. What is the purpose of SQL window functions? Provide examples.

Question Explanation:

Window functions are an advanced SQL feature used for analytics and ranking. This SQL interview question tests the candidate’s ability to use them effectively in practical scenarios.

Expected Answer:

SQL window functions perform calculations across a set of table rows related to the current row, without collapsing the result into grouped data. Common uses include ranking, running totals, and moving averages.

Example 1: Ranking Employees by Salary in Each Department

SELECT name, department_id, salary,  
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank  
FROM employees;  

Example 2: Calculating Running Totals

SELECT order_id, customer_id, amount,  
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total  
FROM orders;  

Key Functions:

• ROW_NUMBER(), RANK(), DENSE_RANK()
• SUM(), AVG(), MAX() with OVER()
• LEAD() and LAG() for accessing rows relative to the current row.

Evaluating Responses:

Look for an explanation of how window functions differ from aggregate functions and examples of use cases. Bonus points if the candidate discusses the significance of the PARTITION BY and ORDER BY clauses.

14. How do you handle errors in SQL?

Question Explanation:

Error handling is critical for building robust systems. This SQL interview question evaluates the candidate’s ability to write resilient SQL code.

Expected Answer:

Error handling ensures the system gracefully manages unexpected issues during SQL execution.

1. Using Transactions:

Wrap operations in transactions and use ROLLBACK for recovery in case of failure.

BEGIN TRANSACTION;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
COMMIT;  
-- Use ROLLBACK if an error occurs.  

2. TRY…CATCH Blocks (in systems like SQL Server):

BEGIN TRY  
    UPDATE employees SET salary = salary * 1.1 WHERE id = 1;  
END TRY  
BEGIN CATCH  
    PRINT ERROR_MESSAGE();  
END CATCH;  

3. Validations and Constraints:

Use constraints (e.g., NOT NULL, CHECK, FOREIGN KEY) to prevent errors at the database level.

Evaluating Responses:

Strong candidates mention database-specific error-handling techniques and tools. Look for examples of transactions and recovery mechanisms, along with an emphasis on preventing errors with constraints.

15. Describe a challenging SQL problem you’ve solved in the past. How did you approach it?

Question Explanation:

This open-ended SQL interview question evaluates the candidate’s real-world experience, problem-solving ability, and communication skills.

Expected Answer:

The answer should describe a specific SQL challenge, such as:

• Problem: Analyzing millions of rows for duplicate records affecting reporting accuracy.
• Approach: Used CTEs and window functions to identify duplicates.

WITH RankedData AS (  
    SELECT id, data,  
           ROW_NUMBER() OVER (PARTITION BY data ORDER BY id) AS row_num  
    FROM large_table  
)  
DELETE FROM RankedData WHERE row_num > 1;  

• Outcome: Improved reporting accuracy and reduced query runtime by 50% after optimizing indexes.

Evaluating Responses:

Look for a clear problem statement, logical steps to solve it, and a measurable outcome. Strong candidates may also reflect on what they learned and how they applied it in future scenarios.

SQL Interview Questions Conclusion

These SQL interview questions help evaluate a candidate’s theoretical knowledge, practical expertise, and ability to handle real-world database challenges. By leveraging these questions, you can identify developers with the technical acumen and problem-solving skills needed to manage and optimize your organization’s database systems effectively.