Master SQL Interviews: 100 Essential Questions for Senior Developers

Basic SQL Questions

1. What is the difference between WHERE and HAVING?
2. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
3. What is the purpose of a PRIMARY KEY and a UNIQUE KEY?
4. Explain the concept of FOREIGN KEY.
5. What are indexes in SQL? How do they improve performance?
6. What is a view in SQL? What are its advantages and limitations?
7. How does a GROUP BY clause work? Provide an example.
8. What is the difference between TRUNCATE, DELETE, and DROP?
9. What is a subquery? How is it different from a join?
10. What are aggregate functions in SQL? Give examples.

Intermediate SQL Questions

1. Explain the concept of normalization. What are the normal forms?
2. What is denormalization? When would you use it?
3. How do you use the CASE statement in SQL?
4. What is the difference between CHAR and VARCHAR?
5. Explain ACID properties in the context of SQL databases.
6. What is the difference between a clustered index and a non-clustered index?
7. How can you optimize a slow SQL query? Provide strategies.
8. What is a CTE (Common Table Expression), and how is it different from a subquery?
9. How do you handle duplicate rows in SQL? Provide examples.
10. Explain the UNION and UNION ALL operators.

Advanced SQL Questions

1. What are window functions in SQL? Provide examples of ROW_NUMBER(), RANK(), and DENSE_RANK().
2. What is a materialized view? How is it different from a regular view?
3. Explain partitioning in SQL. What are the types of partitioning?
4. How would you implement pagination in SQL?
5. What is the difference between OLTP and OLAP databases?
6. Explain the concept of a sharded database. Why is it used?
7. How do you detect and resolve deadlocks in SQL databases?
8. What is query execution plan? How do you analyze and optimize it?
9. Explain the concept of stored procedures. How do they differ from functions?
10. What are triggers in SQL? Provide examples of their use cases.

Real-World Scenarios

1. How would you design a database schema for an e-commerce platform?
2. How would you migrate a database with zero downtime?
3. How do you handle schema changes in a production environment?
4. Explain a strategy for backup and restoration in large-scale databases.
5. How would you implement database replication for high availability?
6. What is the process of indexing a very large table in a live system?
7. How would you secure sensitive data in a database (e.g., credit card numbers)?
8. How do you optimize queries that involve large datasets (10M+ rows)?
9. Explain how you would troubleshoot a query that suddenly started running slow.
10. Describe your approach to implementing role-based access control (RBAC) in SQL.

Query-Based Questions

1. Write a query to find the second highest salary in an employee table.
2. How would you calculate the running total of sales in a table?
3. Write a query to find all employees who have the same manager.
4. Write a query to delete duplicate rows from a table while keeping one copy.
5. How do you find the top 3 customers by revenue?
6. Write a query to retrieve the nth row of a table.
7. How do you retrieve the first and last records from a table using SQL?
8. Write a query to find customers who made purchases in every month of a year.
9. Write a query to calculate the percentage contribution of each product to total sales.
10. Write a query to find products that have not been sold in the last 30 days.

Performance and Optimization

1. What are covering indexes, and how do they improve query performance?
2. Explain the use of query hints in SQL.
3. How does database partitioning improve query performance?
4. What are temp tables, and how are they used?
5. How would you handle large data imports into a database without affecting performance?
6. Explain batch processing in SQL and its advantages.
7. How would you optimize a query with multiple joins?
8. What is a correlated subquery? How do you optimize it?
9. Explain indexing strategies for composite keys.
10. How does indexing affect INSERT, UPDATE, and DELETE operations?

Data Integrity and Security

1. What are the differences between optimistic and pessimistic locking?
2. How do you implement auditing in an SQL database?
3. What is SQL injection? How do you prevent it?
4. What is row-level security in SQL, and how is it implemented?
5. How do you enforce data validation rules in SQL databases?
6. Explain the difference between soft deletes and hard deletes.
7. What is the use of CHECK constraints?
8. How do you encrypt sensitive data in a database?
9. What are access control mechanisms in SQL databases?
10. How would you implement database masking for sensitive data?

Scalability and High Availability

1. What are the challenges of scaling relational databases?
2. How do you design a read-replica setup for SQL databases?
3. Explain the concept of eventual consistency in distributed databases.
4. What is multi-master replication, and when would you use it?
5. How do you monitor database performance in a high-traffic environment?
6. How would you implement data archiving in SQL databases?
7. What is a hot standby? How is it different from a read replica?
8. How do you handle database failover?
9. What is the role of connection pooling in scalability?
10. How do you implement distributed transactions across multiple databases?

Complex Topics

1. Explain transaction isolation levels and their use cases.
2. What is the difference between a hash join and a nested loop join?
3. What is the difference between logical and physical data models?
4. How does SQL handle null values in aggregate functions?
5. What are the trade-offs of denormalizing data?
6. How would you identify and resolve fragmented indexes?
7. What are phantom reads, and how do you prevent them?
8. Explain the difference between serializable and snapshot isolation.
9. What is the purpose of a sequence in SQL?
10. How do you implement change data capture (CDC) in SQL databases?

Behavioral and Best Practices

1. Describe a challenging SQL problem you solved in your previous project.
2. How do you document complex SQL queries for other developers?
3. What steps do you take to ensure query maintainability?
4. Describe a situation where you optimized a poorly performing query.
5. How do you decide between SQL and NoSQL for a project?
6. How do you approach designing a schema for a new application?
7. How do you ensure database backups are tested regularly?
8. Describe a time when you implemented a complex database migration.
9. How do you stay updated on SQL and database technologies?
10. What tools do you use for monitoring and debugging SQL queries?