Top 50+ SQL Interview Questions and Answers for Intermediate to Advanced

Technical SQL Interview Questions for Beginners

Let’s move on to technical questions to check your deeper understanding of SQL concepts, query optimization and database structures.

Tip : While answering try to give clear and practical answers. Focus on what works well and why. Don’t make things too complicated, just explain things in a simple way with examples if possible. Show that you understand how to use SQL effectively in real situations.

11. What’s the difference between WHERE and HAVING clause?

The WHERE clause filters individual rows before any grouping or aggregation takes place and The HAVING clause filters grouped/aggregated results after GROUP BY is applied.

Imagine you're managing an HR dashboard. You want to see only departments with more than 10 active employees

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

• Use WHERE to ignore inactive or terminated employees. WHERE status = 'active' ensures only active employees are considered.
• Use GROUP BY department groups active employees by department.
• Use HAVING to find departments that are large enough. HAVING COUNT(*) > 10 only includes departments with more than 10 active employees.

12. What is the difference between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL?

These three functions are window (analytic) functions used to assign a ranking or position to rows within a result set, based on a specified ORDER BY clause. They differ in how they handle ties (rows with the same value).

• ROW_NUMBER() gives a unique row number without ties.
• RANK() gives the same rank for ties but skips ranks.
• DENSE_RANK() gives the same rank for ties but doesn’t skip ranks.

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM leaderboard;

14 . How can I display the top 3 earners in each department in an HR dashboard?

Use the RANK() or DENSE_RANK() window function with PARTITION BY department and order by salary in descending order.

SELECT employee_id, name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
WHERE status = 'active'

Then, filter using a subquery or CTE

WITH ranked_employees AS (
  SELECT employee_id, name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT * FROM ranked_employees
WHERE salary_rank <= 3;

15. What if I want to show only one unique runner-up (no ties) per leaderboard in a contest?

If you want to strictly show only the 2nd place without any ties, use ROW_NUMBER() because it assigns a unique sequence to each row, even when there are duplicate scores.

WITH leaderboard AS (
  SELECT employee_id, name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
  FROM employees
)
SELECT * FROM leaderboard
WHERE row_num = 2;

• ROW_NUMBER() assigns a unique ranking within each department, ordered by descending salary.
• The WHERE row_num = 2 filters to show only the 2nd highest-paid employee in each department with no ties allowed.

16. How can you dynamically fetch the Nth highest salary in SQL (e.g., 4th, 5th)?

Use a Common Table Expression (CTE) or subquery with DENSE_RANK() and filter where the rank equals N.

DECLARE @N INT = 4;

WITH RankedSalaries AS (
  SELECT employee_id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT *
FROM RankedSalaries
WHERE rnk = @N;

SET @N = 4;

WITH RankedSalaries AS (
  SELECT employee_id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT *
FROM RankedSalaries
WHERE rnk = @N;

-- Use a CTE with parameterized query
PREPARE get_nth_salary(INT) AS
WITH Ranked AS (
  SELECT employee_id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM Ranked WHERE rnk = $1;
EXECUTE get_nth_salary(4);

17. How can you fetch the Nth highest salary using LIMIT and OFFSET in MySQL or PostgreSQL?

You can use DISTINCT, ORDER BY, and LIMIT ... OFFSET to retrieve the Nth highest unique salary.

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- 2nd highest salary

How OFFSET Works

23. Can a table have a UNIQUE constraint and a PRIMARY KEY on overlapping columns?

Yes, technically it's allowed, but it's usually redundant and inefficient.

CREATE TABLE Sample (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  UNIQUE (id, name)  -- Redundant
);

• id is already unique because it's the PRIMARY KEY.
• The UNIQUE(id, name) constraint enforces uniqueness on the combination, but since id is already unique on its own, the pair (id, name) must also be unique. It's making the constraint redundant.

CREATE TABLE Orders (
  order_id INT,
  product_id INT,
  PRIMARY KEY (order_id, product_id),
  UNIQUE (order_id)  -- Not redundant
);

24. When is a UNIQUE constraint not redundant in a table that already has a PRIMARY KEY?

A UNIQUE constraint is not redundant when it enforces additional rules that are not covered by the primary key. This often happens when you need another column or set of columns to be unique, even though it's not the primary identifier of the row. So, a UNIQUE constraint is not redundant if

• It enforces uniqueness on columns not already covered by the PRIMARY KEY.

  Example

  CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE
  );
  

• It enforces independent business rules.

  Example

  CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    SKU VARCHAR(100),
    CategoryID INT,
    UNIQUE (SKU)
  );
  

  Here SKU (Stock Keeping Unit) must be unique across all products — a separate business rule.

• It uses different column combinations than the PK.

  Example

  CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    UNIQUE (CourseID, EnrollmentDate)
  );
  

  These are different combinations of columns, so the UNIQUE constraint is not redundant.

25. Does enforcing UNIQUE or PRIMARY KEY constraints affect performance?

Yes, enforcing these constraints does impact performance, both positively and negatively

Performance Overhead

• INSERT/UPDATE overhead : The database performs an index lookup to ensure no duplicate values exist. This lookup adds latency, especially on large tables or frequently updated keys.
• Extra index maintenance : For every DML operation (INSERT, UPDATE, DELETE) associated unique indexes must be updated, adding to I/O and CPU cost.

Performance Benefits

• Data integrity : Guarantees no duplicate values — a hard guarantee at the database level.
• Faster SELECTs : These constraints automatically create unique indexes, which speed up queries on those columns (especially JOINs, WHERE and lookups).

Note : In write-heavy systems, it's important to balance data integrity vs performance. You can sometimes enforce uniqueness at the application level, but for critical business logic, database-level constraints are essential.

26. What is a CTE in SQL?

A CTE (Common Table Expression) is a temporary, named result set that exists only during the execution of a single SQL statement. It's defined using the WITH clause and can be referenced like a regular table or view within SELECT, INSERT, UPDATE or DELETE statements.

Example :

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT *
FROM cte_name
WHERE column2 > 100;

27. How is a CTE different from a Subquery?

WITH HighEarners AS (
  SELECT * FROM Employees WHERE salary > 100000
)
SELECT name FROM HighEarners WHERE department = 'Engineering';

SELECT name
FROM (
  SELECT * FROM Employees WHERE salary > 100000
) AS HighEarners
WHERE department = 'Engineering';

28. When should I use a CTE over a subquery?

Most of the use cases of CTE (Common Table Expression) over subquery are

1. Breaking Down Complex Logic
   • CTEs allow you to structure large queries step by step, similar to writing modular code.
   • Great for multi-join, aggregated or multi-layered queries.
   WITH Filtered AS (...), Aggregated AS (...) SELECT ... FROM Aggregated;
   

2. Reusing a Result Set
   • You can reference the same CTE multiple times in the main query without repeating logic.
   • Subqueries must be duplicated, making them harder to maintain.
3. Recursive Queries

   CTEs support recursion, which is essential for

   • Tree structures
   • Hierarchical data
   • Graph traversals
   • Bill of materials (BOM)
   WITH RECURSIVE OrgChart AS (...)
   

   Subqueries can not do recursion.

4. Improving Readability & Maintainability
   • CTEs are named, making the logic self-explanatory and the query easier to debug.
   • Especially helpful in team environments and code reviews.

29. Are Common Table Expressions (CTEs) better for performance compared to subqueries or temporary tables?

CTEs are great for readability, modularity and recursion, but they do not always offer performance improvements. In fact, depending on the database engine and how you use them, CTEs can sometimes be slower than subqueries or temp tables.

Tip : CTEs are not inherently faster. Use them for clarity and recursive logic, but always check the execution plan. For performance-critical queries, compare with subqueries or temp tables and benchmark.

31. What is database normalization?

Database normalization is the process of organizing data in a relational database to minimize redundancy and ensure data integrity by dividing large, complex tables into smaller, well-structured tables. Each resulting table represents a single concept or entity and relationships between tables are maintained using foreign keys.

32. What are the differences Between DBMS and RDBMS

Both DBMS (Database Management System) and RDBMS (Relational Database Management System) are systems designed to manage data efficiently, but they differ significantly in terms of structure, functionality and use cases.

Note : All RDBMS are DBMS, but ❌ not all DBMS are RDBMS.

RDBMS follows a strict relational model, while DBMS can be any system that stores and retrieves data.

33. Difference Between INNER JOIN and OUTER JOINs

Joins are used to combine rows from two or more tables based on a related column. Here's how different join types behave:

Tip : Use INNER JOIN when you need only common data, and use OUTER JOINs when you want to retain unmatched records too.

34. Can you explain ACID properties in databases?

ACID is a set of four key properties that ensure reliable and consistent transactions in a database system.

1. Atomicity : A transaction is all-or-nothing. If one part fails, the entire transaction is rolled back. Partial changes are never saved.
2. Consistency : Ensures that a transaction brings the database from one valid state to another. All rules (constraints, triggers, FK) must be satisfied.
3. Isolation : Transactions are executed independently. Multiple transactions can run at the same time without interfering with each other.
4. Durability : Once a transaction is committed, changes are permanent even in case of power loss or crash.

35. When would you use a Stored Procedure vs a Trigger in SQL?

Both stored procedures and triggers are used to encapsulate logic in SQL, but they serve very different purposes

CREATE PROCEDURE sp_update_salary (@emp_id INT, @bonus DECIMAL)
AS
BEGIN
  UPDATE employees
  SET salary = salary + @bonus
  WHERE emp_id = @emp_id;
END;

CREATE TRIGGER trg_audit_employees
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(emp_id, old_salary, new_salary, changed_on)
  VALUES (OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

36. What are constraints in SQL and why use them?

Constraints are rules applied to table columns to make sure the data is valid, accurate and consistent. They help prevent bad or incorrect data from being stored in the database.

• PRIMARY KEY: Uniquely identifies each row
• FOREIGN KEY: Maintains referential integrity (Ensures values match those in another table).
• NOT NULL: Prevents a column from having NULL values.
• UNIQUE: Ensures all values in a column are different i.e. Prevents duplicate values.
• CHECK: Validates that values meet a specific condition
• DEFAULT: Sets a default value if none is provided

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL
);

38. Why to use Indexes?

Indexes make data retrieval much faster and more efficient. Without an index, the database has to perform a full table scan, checking every row one by one to find a match. which can be slow, especially on large tables. With an index, the database can quickly jump to the rows it needs, improving the performance of queries like SELECT, JOIN, WHERE and ORDER BY.

In short : Indexes reduce the amount of work the database has to do, helping your queries run faster and more efficiently, especially on large datasets.

39. What are key Benefits of Using Indexes

• Faster Data Retrieval : Speeds up SELECT queries, especially on large tables.
• Better Use of Joins : Speeds up joins by indexing foreign and primary keys.
• Improved Application Performance : Enhances read operations in applications, reducing response time.
• Enforcing Uniqueness : Unique indexes prevent duplicate entries in a column (like email).
• Efficient Sorting and Filtering : Helps in ORDER BY, GROUP BY and WHERE clause queries.

40. What is Trade-off using Indexs?

While indexes can significantly speed up read operations, they come with certain downsides that you should be aware of

• Slower Write Operations : INSERT, UPDATE and DELETE take longer because indexes need to be updated too.
• Increased Storage : indexes take up extra disk space, especially if there are many or large indexes.
• Maintenance Overhead : More indexes mean more work to manage, monitor and optimize over time.
• Diminishing Returns : Adding too many indexes can actually hurt performance by confusing the query planner or slowing down queries.

Use indexes only where needed - only on columns frequently used in WHERE, JOIN, ORDER BY or GROUP BY clauses. Avoid indexing every column, as that can slow down write performance and waste storage.

• Top 50 SQL Interview Questions and Answers (Beginner to Intermediate)
• SQL Performance Tuning : Advanced Techniques with Examples
• Top SQL Interview Questions for 2025
• C# Interview Questions and Answers
• Java Interview Questions and Answers
• A Structured Approach to Cracking Technical Interviews
• How to Approach HR Interview Questions