SQL interview questions and answers: Study Guide

Structured Query Language (SQL) is the cornerstone of data manipulation and management in relational database systems. Whether you’re applying for a position as a Database Administrator, Data Analyst, or Backend Developer, a strong understanding of SQL is essential. During technical interviews, companies often assess your SQL skills to ensure that you can efficiently query data, design tables, and interpret relationships between data sets.

TL;DR: SQL is a critical skill for many technical roles involving databases. This guide covers key SQL interview questions along with model answers to help you prepare. It includes basic, intermediate, and advanced-level questions typically asked in interviews. Use it as a reliable resource to assess and improve your SQL knowledge.

Contents

1. Basic SQL Interview Questions and Answers

These questions test foundational SQL knowledge and are common in entry-level positions or the first round of interviews.

Q1: What is SQL?
A: SQL, or Structured Query Language, is used to communicate with and manipulate relational databases. It allows users to perform operations such as querying data, updating records, deleting data, and creating or modifying schemas.
Q2: What are the different types of SQL statements?
A: SQL statements can be categorized into the following:
- DDL (Data Definition Language): CREATE, ALTER, DROP
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language): GRANT, REVOKE
- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
Q3: What is the difference between WHERE and HAVING?
A: The WHERE clause is used to filter rows before any groupings are made, while the HAVING clause is used to filter groups after the GROUP BY operation has been performed.

2. Intermediate SQL Interview Questions and Answers

Candidates applying for mid-level roles are expected to be familiar with more complex SQL queries and relational concepts.

Q4: What is a JOIN? What are its types?
A: A JOIN clause is used to combine rows from two or more tables based on a related column between them. The main types are:
- INNER JOIN: Returns only the matching rows from both tables.
- LEFT JOIN: Returns all rows from the left table and matched rows from the right table, with NULLs for unmatched rows.
- RIGHT JOIN: Similar to LEFT JOIN, but includes all rows from the right table.
- FULL OUTER JOIN: Returns all rows when there is a match in either table.
Q5: What is normalization? Why is it important?
A: Normalization is the process of organizing data to reduce data redundancy and improve data integrity. The main goal is to ensure that data is stored logically, which helps maintain consistency and simplifies queries.
Q6: What are the different normal forms?
A: The most common normal forms include:
- 1NF: Eliminate repeating groups; ensure that each column contains atomic (indivisible) values.
- 2NF: Ensure full functional dependency; all non-key attributes should depend on the entire primary key.
- 3NF: Eliminate transitive dependencies; non-key attributes should not depend on other non-key attributes.

3. Advanced SQL Interview Questions and Answers

Advanced queries often involve window functions, subqueries, and performance optimization. These are essential for senior-level roles.

Q7: What is a Window Function?
A: Window functions perform calculations across a set of table rows that are somehow related to the current row. For example, using ROW_NUMBER(), RANK(), and SUM() OVER windows provides more complex aggregate and ranking capabilities within result sets.
Q8: What is a Common Table Expression (CTE)?
A: A CTE is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs help simplify and improve the structure and readability of recursive or nested queries.
Q9: How can you optimize a slow SQL query?
A: Key optimization techniques include:
- Using indexes on frequently accessed columns.
- Avoiding SELECT *; select only necessary columns.
- Using JOINs appropriately, avoiding unnecessary subqueries.
- Analyzing the Execution Plan to understand performance bottlenecks.
- Making use of caching strategies where appropriate.

4. SQL Query Problem-Solving Examples

Many interviews test real-world application through query writing. Practicing these examples can improve your problem-solving skills.

Q10: Find the second highest salary from an Employee table.
A:

SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Q11: How do you find duplicate records in a table?
A:

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Q12: Write a SQL query to get department-wise employee count.
A:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

5. Behavioral and Conceptual Questions

Interviewers may also test your overall approach to managing data integrity and working in database teams.

Q13: How do you ensure data integrity in databases?
A: By enforcing constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and using proper transaction control mechanisms like COMMIT and ROLLBACK when modifying data.
Q14: How do you handle conflicts in a multi-user database environment?
A: Techniques include:
- Using transactions with appropriate isolation levels.
- Implementing optimistic or pessimistic locking strategies.
- Monitoring deadlocks and resolving them through query optimization or process ordering.

6. Tips for Acing Your SQL Interview

Confidence with syntax isn’t enough; you need to demonstrate sound problem-solving, attention to performance, and scalable solutions.

Practice real-world problems: Websites like LeetCode, HackerRank, and Mode Analytics provide practical SQL exercises.
Understand your schema: Ask clarifying questions when presented with sample schemas; don’t guess.
Write clean, formatted queries: Readability matters during live interviews.
Be ready for follow-ups: After writing a query, explain its time complexity, potential indexing strategies, or trade-offs.