SQL Senior: Interview Questions for Developers

If you’re a developer aiming for a senior SQL role, you’re probably prepping for a tough interview. SQL might look like it’s just about saying “SELECT * FROM table”, but at a senior level, it’s a whole different beast. You need to master performance, scalability, and super clean code. So let’s break down those tricky interview questions and make it fun and simple.

TLDR 🎯

Senior SQL interviews are about more than just writing queries. They test your understanding of performance, normalization, indexing, and complex joins. You’ll also get grilled on architecture, best practices, and solving real-world data puzzles. So keep calm and learn smart. Let’s dive in!

1. What’s the difference between INNER JOIN and OUTER JOIN?

This is a classic. Interviewers love this one because it tells them how well you know your joins.

• INNER JOIN returns rows that have matching values in both tables.
• LEFT OUTER JOIN returns all rows from the left table, and matched rows from the right.
• RIGHT OUTER JOIN does the opposite.
• FULL OUTER JOIN returns all rows when there is a match in one of the tables.

Know when to use each. It’s all about what data you want to keep and what to exclude.

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

This tests if you can analyze data patterns. Here’s a common way:

SELECT column_name, COUNT(*)
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

Want to delete duplicates? Then it’s time to raise your CRUD skills.

3. Explain normalization. What normal forms do you know?

Normalization is like cleaning up your room. It removes mess so data doesn’t repeat unnecessarily.

• 1NF: Eliminate repeating groups.
• 2NF: Remove partial dependencies.
• 3NF: Remove transitive dependencies.

There’s boyce codd normal form (BCNF) too, but know at least up to 3NF for interviews.

4. What are indexes? When should you not use them?

Think of indexes like a book index. They help you find stuff faster.

But there’s a twist.

• Indexes speed up reads but can slow down writes.
• Each insert/update/delete might have to update the index.
• If your table is tiny, an index could be overkill.

So be smart — index when it matters.

5. What’s a CTE? Why use it?

CTE stands for Common Table Expression.

It’s like a temporary result set that’s easier to read and reuse. Here’s an example:

WITH TopStudents AS (
  SELECT name, score
  FROM students
  WHERE score > 90
)
SELECT * FROM TopStudents;

Neat, right? Nesting queries is messy. CTEs clean it up.

6. What’s the difference between WHERE and HAVING?

This one trips up a lot of devs.

• WHERE filters rows before they’re grouped.
• HAVING filters groups after aggregation.

So if you’re working with GROUP BY, use HAVING to filter aggregated results.

7. How do you optimize a slow-running query?

Ah, the million-row question!

Here’s your go-to checklist:

• Check for missing indexes.
• Use EXPLAIN or EXECUTION PLAN.
• Avoid SELECT *.
• Reduce joins and subqueries when possible.
• Use temporary tables or materialized views if needed.

It’s part detective work, part surgery. Show you know both.

8. Can you write a recursive query?

Recursive queries are commonly asked in senior-level interviews. Usually for tree/graph problems.

Example: company employees and their managers.

WITH RECURSIVE OrgChart AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;

Memorize the pattern. And practice, practice, practice.

9. What’s a window function?

Window functions let you perform calculations across a set of table rows related to the current row.

SELECT 
  employee_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Super handy for analytics — running totals, rankings, and percentiles.

10. Can you explain ACID properties?

If databases were superheroes, ACID would be their code of honor.

• Atomicity: All or nothing transactions.
• Consistency: Data moves from one valid state to another.
• Isolation: Transactions don’t step on each other’s toes.
• Durability: Once committed, stays committed — even if the server crashes.

Expect this question at senior level — especially if you’re dealing with high-concurrency systems.

11. Tell me about database partitioning.

Partitioning breaks large tables into smaller, faster-to-query chunks.

• Horizontal partitioning: Splits rows (like sales by year).
• Vertical partitioning: Splits columns (like rarely used audit fields).

It helps performance, maintenance, archiving… and makes you sound super smart.

12. How would you design a database for a ride-sharing app?

This is a system design classic.

You’re not just expected to draw tables anymore. You need to think about:

• Passenger, Driver, Ride tables.
• Location tracking — maybe geo-indexes?
• Concurrent matching of drivers to riders.
• Data retention and analytics.

Work out real-world constraints. Include indexing, sharding, caching if needed.

13. Do you have experience with stored procedures?

Stored procedures are reusable SQL blocks stored in the database.

• Used for workflows like billing or generating reports.
• Reduces app-side complexity.
• Helps with code reuse and security.

Pro tip: Always talk about parameterization and error handling if asked.

14. What challenges have you faced with SQL in past projects?

This is your chance to shine and be human.

Talk about:

• Slow queries and how you fixed them.
• Refactoring a messy schema.
• Debugging deadlocks or blocking processes.

Keep it real. Show your journey. Reflect growth.

Final Thoughts 💡

Being a senior SQL dev is not about memorizing syntax. It’s about understanding data deeply. Optimizing queries, thinking in models, and designing for scale.

So prep well. Ask lots of questions. Read explain plans like they’re treasure maps. And have fun! Interviews are puzzles, and you’ve got the brain for it.