Information Technology
SQL Developer Interview Questions and Answers
SQL developers design, write, and optimise SQL queries and database objects. They work with relational databases like SQL Server, Oracle, and PostgreSQL, ensuring data integrity and performance.
20 practice questions with explanations and sample answers.
1. Why do you enjoy working with SQL?
What the interviewer is looking for
Show appreciation for data and logic.
Sample answer
SQL is powerful for data manipulation. I enjoy solving complex problems with set‑based logic and optimising queries.
2. Explain the difference between inner, left, right, and full joins.
What the interviewer is looking for
Define each join type.
Sample answer
Inner join returns matching rows; left join returns all rows from left table; right join returns all rows from right table; full join returns all rows from both.
3. What is the difference between a view and a materialised view?
What the interviewer is looking for
View is virtual; materialised view stores data physically.
Sample answer
A view is a saved query that does not store data; a materialised view stores the result set physically and can be refreshed.
4. Describe a time you optimised a slow‑running query.
What the interviewer is looking for
Use indexes, rewriting, and execution plans.
Sample answer
I rewrote a correlated subquery to a JOIN and added a clustered index, reducing execution time from minutes to seconds.
5. What is a stored procedure and when would you use one?
What the interviewer is looking for
Encapsulate business logic in the database.
Sample answer
Stored procedures are pre‑compiled SQL code that encapsulate logic for reusability and performance.
6. Explain the ACID properties of transactions.
What the interviewer is looking for
Atomicity, Consistency, Isolation, Durability.
Sample answer
ACID ensures reliable processing: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent operations don't interfere), Durability (changes persist).
7. How do you handle NULL values in SQL?
What the interviewer is looking for
Use IS NULL, COALESCE, and NULLIF.
Sample answer
I use IS NULL/IS NOT NULL for filtering, COALESCE to substitute defaults, and NULLIF to avoid division by zero.
8. What is a window function and give an example?
What the interviewer is looking for
Perform calculations across a set of rows.
Sample answer
Window functions like ROW_NUMBER() or SUM() OVER (PARTITION BY) allow ranking and running totals without grouping.
9. Describe your experience with database normalisation.
What the interviewer is looking for
1NF to 3NF and beyond.
Sample answer
I normalise to 3NF to reduce redundancy and ensure data integrity, while denormalising for performance when needed.
10. What is the difference between UNION and UNION ALL?
What the interviewer is looking for
UNION removes duplicates; UNION ALL does not.
Sample answer
UNION removes duplicate rows; UNION ALL returns all rows and is faster because it does not deduplicate.
11. How do you implement error handling in SQL (TRY...CATCH)?
What the interviewer is looking for
Use TRY...CATCH blocks.
Sample answer
I use TRY...CATCH in stored procedures to catch errors, roll back transactions, and log errors.
12. What is the purpose of an index and what types are there?
What the interviewer is looking for
Clustered, non‑clustered, unique.
Sample answer
Indexes speed up data retrieval. Clustered indexes sort data physically; non‑clustered are separate structures; unique ensures no duplicate values.
13. Describe a time you performed a data migration.
What the interviewer is looking for
ETL process and validation.
Sample answer
I migrated data from an old system using SSIS, performed transformations, and validated row counts and totals.
14. How do you handle large datasets and batch processing?
What the interviewer is looking for
Use pagination, partitioning, and batch updates.
Sample answer
I use pagination for queries, partition tables for large data, and process updates in batches to avoid long transactions.
15. What is the difference between a primary key and a unique key?
What the interviewer is looking for
Primary key is unique and not null; unique key is unique but can have nulls.
Sample answer
A primary key uniquely identifies a row and cannot be null. A unique key also ensures uniqueness but may allow one null.
16. How do you troubleshoot a slow‑running stored procedure?
What the interviewer is looking for
Use execution plans and query statistics.
Sample answer
I capture the execution plan, look for table scans or expensive operations, and add missing indexes.
17. What is the role of a database trigger and when to use one?
What the interviewer is looking for
Automate actions on DML.
Sample answer
Triggers execute automatically on insert, update, or delete. I use them for auditing and enforcing complex rules.
18. Describe your experience with SQL Server or PostgreSQL.
What the interviewer is looking for
Mention specific features.
Sample answer
I have worked with both SQL Server (SSMS, T‑SQL) and PostgreSQL (psql, PL/pgSQL) for enterprise applications.
19. How do you ensure data security in SQL?
What the interviewer is looking for
Use roles, permissions, and encryption.
Sample answer
I grant minimal privileges via roles, encrypt sensitive data, and implement row‑level security.
20. Why do you want to work for our data team?
What the interviewer is looking for
Mention their data culture or challenges.
Sample answer
Your team manages large‑scale financial data. I want to help optimise and secure that data pipeline.