Top 50 SQL Interview Questions for Mid-Level Developers

Introduction to Mid-Level SQL Interviews

As a mid-level developer, your SQL interviews will go far beyond basic CRUD operations. Interviewers expect you to understand complex joins, window functions, query optimization, and database design principles. This tutorial covers the top 50 SQL interview questions for mid-level developers, providing practical examples, explanations, and best practices to help you succeed.

Core SQL Concepts and Data Manipulation

1. What is the difference between DELETE and TRUNCATE?

DELETE is a DML command used to remove rows one by one, can be filtered with a WHERE clause, and can be rolled back. TRUNCATE is a DDL command that quickly removes all rows by deallocating data pages, cannot use a WHERE clause, and typically cannot be rolled back.

2. What is a Primary Key?

A Primary Key is a column or a set of columns that uniquely identifies each row in a table. It must contain unique values and cannot contain NULLs.

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

3. What is a Foreign Key?

A Foreign Key is a column that references the primary key of another table, establishing a relationship and enforcing referential integrity.

4. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after the GROUP BY clause is applied.

SELECT department, COUNT(*)
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5;

5. What is an Index and why does it matter?

An index is a database object that improves the speed of data retrieval operations. It matters because it prevents full table scans, drastically reducing query execution time on large datasets.

6. What is the difference between UNION and UNION ALL?

UNION combines the result sets of two queries and removes duplicate rows. UNION ALL combines result sets but keeps all duplicates, making it faster because it skips the deduplication process.

7. What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and linking them via relationships.

8. What is Denormalization?

Denormalization is the process of adding redundant data to a normalized database to improve read performance, often used in data warehousing and reporting systems.

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

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

10. How do you delete duplicate records while keeping one?

WITH CTE AS (
    SELECT id, email,
           ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) as row_num
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM CTE WHERE row_num > 1);

Joins and Set Operations

11. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only the rows that have matching values in both tables. LEFT JOIN returns all rows from the left table and the matched rows from the right table; unmatched rows from the left table will have NULLs for right table columns.

12. What is a SELF JOIN?

A self join is a regular join, but the table is joined with itself. It is commonly used for hierarchical data.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

13. What is a CROSS JOIN?

A cross join produces a Cartesian product of the two tables, meaning it pairs every row from the first table with every row from the second table.

14. How do you find the second highest salary?

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

15. What is an Anti-Join?

An anti-join returns rows from the left table where there are no matching rows in the right table. It is typically implemented using LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS.

16. What is the difference between RANK() and DENSE_RANK()?

RANK() assigns the same rank to ties, but leaves a gap in the ranking sequence for the next non-tied row. DENSE_RANK() assigns the same rank to ties but does not leave a gap.

SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER(ORDER BY salary DESC) as dense_rank
FROM employees;

17. What is a Correlated Subquery?

A correlated subquery is a subquery that depends on the outer query for its values. It is executed repeatedly, once for each row processed by the outer query.

18. What is the difference between EXISTS and IN?

IN checks if a value matches any value in a list or subquery. EXISTS checks if a subquery returns any rows. EXISTS is generally more efficient for large datasets because it stops scanning once a match is found.

19. What is a CTE (Common Table Expression)?

A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and allows for recursive queries.

20. How does COALESCE work?

COALESCE returns the first non-null value in a list of arguments. It is used to handle NULL values gracefully.

SELECT COALESCE(phone, email, 'No Contact Info') FROM users;

Aggregations and Window Functions

21. What are Window Functions?

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows.

22. How do LEAD() and LAG() work?

LEAD() accesses data from a subsequent row, while LAG() accesses data from a previous row without using a self-join.

SELECT date, revenue,
       LAG(revenue, 1) OVER(ORDER BY date) as prev_day_revenue,
       LEAD(revenue, 1) OVER(ORDER BY date) as next_day_revenue
FROM daily_sales;

23. How do you calculate a running total?

SELECT date, revenue,
       SUM(revenue) OVER(ORDER BY date) as running_total
FROM daily_sales;

24. What is the difference between ROW_NUMBER() and RANK()?

ROW_NUMBER() assigns a unique, sequential integer to each row. RANK() assigns the same number to ties and skips subsequent numbers.

25. How does GROUP BY handle NULL values?

GROUP BY treats all NULL values as a single group. If a grouping column contains NULLs, they will be aggregated together.

26. What are Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Examples include SUM(), AVG(), COUNT(), MAX(), and MIN().

27. How do you pivot data in SQL?

Pivoting transforms rows into columns. In SQL Server, you use the PIVOT operator. In PostgreSQL or MySQL, you use conditional aggregation with CASE or FILTER.

SELECT department,
       SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) as male_count,
       SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) as female_count
FROM employees
GROUP BY department;

28. What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

COUNT(*) counts all rows. COUNT(column) counts non-null values in the column. COUNT(DISTINCT column) counts unique non-null values.

29. How do you find the Nth highest value using Window Functions?

WITH RankedSalaries AS (
    SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) as rnk
    FROM employees
)
SELECT name, salary FROM RankedSalaries WHERE rnk = 3;

30. What is the PARTITION BY clause?

PARTITION BY divides the result set into partitions to which the window function is applied. If omitted, the function treats the entire result set as a single partition.

Advanced SQL and Database Design

31. What is a Recursive CTE?

A recursive CTE is a CTE that references itself, allowing you to query hierarchical data like organizational charts or folder structures.

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
    INNER JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;

32. What are ACID properties?

• Atomicity: All operations in a transaction succeed or fail together.
• Consistency: The database transitions from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once committed, changes are permanent.

33. Explain Isolation Levels.

Isolation levels define the degree to which one transaction must be isolated from others. They include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

34. What is a Deadlock?

A deadlock occurs when two transactions are waiting for each other to release locks, causing a cycle of dependencies. The database system typically detects this and kills one transaction.

35. What is a View?

A view is a virtual table based on the result-set of an SQL statement. It does not store data physically (unless materialized) and is used to simplify complex queries and restrict data access.

36. What is a Materialized View?

Unlike a standard view, a materialized view stores the query result physically in the database. It must be refreshed to update the data, making it ideal for reporting and data warehousing.

37. What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save and reuse. It can accept input parameters, execute multiple statements, and return multiple values.

38. What is the difference between Functions and Stored Procedures?

Functions must return a value and cannot perform DML (INSERT, UPDATE, DELETE) operations on permanent tables. Stored procedures can perform DML operations and do not necessarily return a value.

39. What is a Trigger?

A trigger is a special type of stored procedure that automatically executes in response to certain events (INSERT, UPDATE, DELETE) on a table.

40. What is a Composite Key?

A composite key is a primary key consisting of two or more columns used together to uniquely identify a row.

Performance Tuning and Best Practices

41. How do you optimize a slow SQL query?

• Use EXPLAIN or Execution Plan to identify bottlenecks.
• Add appropriate indexes on columns used in WHERE, JOIN, and ORDER BY clauses.
• Avoid SELECT * and only fetch needed columns.
• Replace correlated subqueries with JOINs or CTEs where possible.

42. Why should you avoid SELECT *?

Using SELECT * retrieves all columns, consuming more network bandwidth, memory, and CPU. It also prevents the database from using covering indexes effectively.

43. What is an Execution Plan?

An execution plan is a roadmap generated by the query optimizer that details the steps the database will take to execute a query. It shows table scans, index seeks, and join types.

44. What is the difference between Clustered and Non-Clustered Indexes?

A clustered index determines the physical order of data in a table (a table can only have one). A non-clustered index is a separate structure containing pointers to the physical data (a table can have many).

45. How do you handle transactions in SQL?

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If an error occurs, use ROLLBACK;

46. What is a Savepoint?

A savepoint is a marker within a transaction that allows you to roll back part of the transaction instead of the entire thing.

47. How do you update a column based on another table?

UPDATE employees e
SET salary = e.salary * 1.10
FROM departments d
WHERE e.department_id = d.id AND d.name = 'Engineering';

48. What is the difference between CHAR and VARCHAR?

CHAR is a fixed-length data type, padding with spaces if the input is shorter. VARCHAR is a variable-length data type, storing only the actual characters plus a small overhead.

49. How do you find employees who earn more than their managers?

SELECT e.name AS employee, e.salary, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

50. What is the difference between DROP, TRUNCATE, and DELETE?

DROP completely removes the table structure and data. TRUNCATE removes all data but keeps the structure. DELETE removes specific rows based on a condition and can be rolled back.

Conclusion

Mastering these mid-level SQL concepts is crucial for building robust, performant applications and passing technical interviews. By understanding how to leverage window functions, optimize queries with proper indexing, and design normalized schemas, you demonstrate a deep proficiency in database management. Practice these questions, write out the queries, and use execution plans to see exactly how the database processes your logic.