Top 30 SQL Interview Questions for Professionals with 7+ Years of Experience

Top 30 SQL Interview Questions for Professionals with 7+ Years of Experience

SQL expertise is a must-have for experienced developers working with databases, performance tuning, and large-scale applications. This guide covers 30 advanced SQL interview questions designed to test your knowledge on indexing, query optimization, transactions, and more.

1. What are the different types of indexes in SQL, and when should each be used?

Answer: Indexes improve query performance by reducing data retrieval time. Types include:

• Clustered Index: Stores data physically in sorted order; one per table.
• Non-Clustered Index: Stores pointers to the data; multiple per table.
• Unique Index: Enforces uniqueness on a column.
• Full-Text Index: Supports full-text searches.
• Filtered Index: Applied to a subset of data for performance tuning. Best Practice: Use indexes judiciously to avoid performance degradation due to excessive maintenance.

2. Explain the difference between a CTE (Common Table Expression) and a Temp Table.

Answer:

• CTE: A temporary result set used within a query; improves readability.
• Temp Table: A physical table stored in tempdb, persists until explicitly dropped or session ends. Example:

WITH CTE_Example AS (
    SELECT id, name FROM Employees WHERE department = 'HR'
)
SELECT * FROM CTE_Example;

3. How does indexing impact query performance?

Answer:

• Pros: Faster reads, optimized WHERE clause, quick joins.
• Cons: Slower writes (INSERT/UPDATE/DELETE), increased storage.
• Example:

CREATE INDEX idx_employee_name ON Employees(name);

Best Practice: Avoid indexing columns with frequent updates.

4. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Answer:

• ROW_NUMBER(): Assigns a unique row number.
• RANK(): Assigns the same rank for duplicate values, skipping ranks.
• DENSE_RANK(): Assigns the same rank but without skipping numbers. Example:

SELECT id, name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM Employees;

5. How do you optimize a slow SQL query?

Answer:

• Use EXPLAIN PLAN to analyze execution.
• Index optimization.
• Avoid SELECT *.
• Use joins instead of subqueries where applicable.
• Optimize WHERE clauses. Example:

EXPLAIN ANALYZE SELECT id, name FROM Employees WHERE department = 'HR';

6. What is a covering index?

Answer: An index that contains all the columns required by a query, reducing the need to access the base table.

CREATE INDEX idx_covering ON Employees(department, name);

Benefit: Improves read performance significantly.

7. How do you handle database deadlocks?

Answer:

• Identify and minimize transactions locking the same resources.
• Use lock timeouts.
• Ensure consistent locking order.
• Implement retry logic for failed transactions.

8. Explain the difference between INNER JOIN and LEFT JOIN performance-wise.

Answer:

• INNER JOIN: Returns only matching rows.
• LEFT JOIN: Returns all rows from the left table + matching rows from the right.
• Performance Impact: LEFT JOIN can be slower due to NULL checks.

9. What is a materialized view, and when should it be used?

Answer: A stored result of a query that can be refreshed periodically. Used for performance optimization in complex queries.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(sales) FROM Sales GROUP BY region;

10. What are partitioned tables, and how do they help in performance tuning?

Answer: Partitioning improves query performance by dividing large tables into smaller chunks.

• Range Partitioning
• List Partitioning
• Hash Partitioning
• Example:

CREATE TABLE Sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE(sale_date) (
    PARTITION p1 VALUES LESS THAN ('2022-01-01'),
    PARTITION p2 VALUES LESS THAN ('2023-01-01')
);

Here’s a detailed breakdown of each SQL question with explanations, examples, best practices, and potential pitfalls:

11. Difference between DELETE, TRUNCATE, and DROP

• DELETE: Removes specific rows based on a condition but retains table structure and logs transactions.
• TRUNCATE: Removes all rows without logging individual row deletions; resets identity.
• DROP: Deletes the table structure and all associated data permanently.

Example:

DELETE FROM users WHERE age > 30;
TRUNCATE TABLE users;
DROP TABLE users;

Best Practice: Use DELETE when needing to remove selective rows, TRUNCATE for fast table clearing, and DROP to completely remove a table.

Pitfall: TRUNCATE cannot be rolled back in most databases unless inside a transaction.

12. How to Prevent SQL Injection

SQL injection occurs when user input manipulates queries. Prevention techniques include:

• Using Prepared Statements:

  SELECT * FROM users WHERE username = ? AND password = ?

• Escaping Input: Avoid directly concatenating user input.
• Using ORM and Security Libraries: Like Sequelize, SQLAlchemy, or Hibernate.

Pitfall: Never trust user input; always sanitize and validate.

13. Explain Query Hints in SQL

Query hints optimize execution plans by guiding the optimizer.

Example in SQL Server:

SELECT * FROM employees WITH (INDEX(idx_name));

Best Practice: Use hints only when necessary as overuse can degrade performance.

14. Benefits of Stored Procedures

Stored procedures:

• Improve performance through precompiled execution.
• Enhance security by limiting direct table access.
• Reduce redundancy.

Example:

CREATE PROCEDURE GetUserById (@id INT)
AS
BEGIN
    SELECT * FROM users WHERE id = @id;
END;

15. What is Sharding in Databases?

Sharding is horizontal partitioning of data across multiple databases.

Example: A large user database split into users_1, users_2, etc.

Best Practice: Choose sharding keys carefully to balance loads.

Pitfall: Re-sharding is complex if the partitioning strategy is wrong.

16. Difference Between ACID and BASE Transactions

• ACID: Ensures Atomicity, Consistency, Isolation, Durability.
• BASE: Prioritizes availability over consistency in distributed systems.

Best Practice: Use ACID for financial apps; BASE for scalable distributed systems.

17. Role of Normalization and Denormalization

• Normalization: Reduces redundancy, improves consistency (e.g., 3NF).
• Denormalization: Improves read performance by duplicating data.

Example:

• Normalized (Orders and Customers split): Prevents duplicate customer data.
• Denormalized (Merged into one table): Faster reads.

18. Using CROSS APPLY vs OUTER APPLY

• CROSS APPLY: Acts like an INNER JOIN, requiring matching records.
• OUTER APPLY: Includes unmatched rows (like LEFT JOIN).

Example:

SELECT a.*, b.*
FROM Users a
CROSS APPLY fn_GetUserOrders(a.id) b;

19. Impact of NULL Values on Performance

• NULLs require special handling (IS NULL instead of =).
• Indexes perform worse on NULLable columns.
• Aggregation (COUNT(*) vs COUNT(column)) may yield unexpected results.

20. How to Implement Row-Level Security

Restricts access to specific rows based on user roles.

Example in SQL Server:

CREATE SECURITY POLICY RowSecurity
ADD FILTER PREDICATE user_id = SESSION_USER ON orders;

21. What is the CAP Theorem?

A distributed system can guarantee at most two of:

• Consistency
• Availability
• Partition Tolerance

Example:

• CP (MongoDB, HBase): Prioritizes consistency.
• AP (Cassandra, DynamoDB): Prioritizes availability.

22. Recursive Queries with CTE

Common Table Expressions (CTE) handle hierarchical data.

Example:

WITH EmployeeHierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

23. Understanding LAG() and LEAD() Functions

• LAG(): Retrieves previous row’s value.
• LEAD(): Retrieves next row’s value.

Example:

SELECT id, salary, 
       LAG(salary, 1) OVER (ORDER BY id) AS previous_salary,
       LEAD(salary, 1) OVER (ORDER BY id) AS next_salary
FROM employees;

24. Strategies for Handling Large Datasets

• Indexing: Speed up queries.
• Partitioning: Distribute data.
• Archiving: Remove old data.
• Materialized Views: Precompute results.

25. Index Fragmentation and How to Fix It

Fragmentation slows performance.

Fix:

ALTER INDEX ALL ON employees REBUILD;

or

DBCC INDEXDEFRAG('dbname', 'employees', index_id);

26. When to Use JSON Data Types in SQL

• Flexible schema storage.
• Querying JSON data:

  SELECT data->>'$.name' FROM users;

Pitfall: Harder to index and enforce constraints.

27. Difference Between SARGable and Non-SARGable Queries

• SARGable: Uses indexes efficiently.

  WHERE age > 30

• Non-SARGable: Prevents index usage.

  WHERE YEAR(birthdate) = 1990

28. SQL Server Always On Availability Groups

Provides high availability by syncing databases across servers.

Best Practice: Use readable secondaries for load balancing.

29. Best Practices for Query Performance Tuning

• Use EXPLAIN PLAN.
• Avoid SELECT*.
• Use appropriate indexing.
• Opt for JOINs over subqueries.
• Avoid functions on indexed columns.

30. Optimizing Queries with EXISTS vs IN

• EXISTS: Stops searching after first match.
• IN: Checks all values.

Example:

-- EXISTS (faster for large datasets)
SELECT * FROM employees e WHERE EXISTS (
    SELECT 1 FROM departments d WHERE d.id = e.dept_id
);

Conclusion: Mastering these advanced SQL concepts can significantly improve performance and database efficiency. Keep exploring execution plans, indexing strategies, and query optimization techniques to enhance your SQL expertise.