✅ Advanced SQL Interview Questions with Answers
High-impact SQL concepts frequently asked in technical interviews 💼🧠
1️⃣ What are Window Functions in SQL?
Window functions perform calculations across rows related to the current row
without collapsing the result set, unlike GROUP BY.
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;2️⃣ Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()
- RANK() – Skips numbers for ties
- DENSE_RANK() – No gaps in ranking
- ROW_NUMBER() – Unique number per row
📌 Use Cases: Sorting results, pagination, leaderboard systems
3️⃣ What is the use of COALESCE()?
COALESCE() returns the first non-NULL value from a list of expressions.
SELECT name,
COALESCE(nickname, 'No Nick') AS display_name
FROM users;4️⃣ How does CASE work in SQL?
The CASE statement is used to apply conditional logic in SQL queries.
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM students;5️⃣ Explain CTE (Common Table Expression)
A CTE is a temporary, named result set that improves query readability and reusability.
WITH TopSales AS (
SELECT emp_id, SUM(sales) AS total_sales
FROM sales
GROUP BY emp_id
)
SELECT *
FROM TopSales
WHERE total_sales > 5000;6️⃣ Difference between EXISTS and IN
- EXISTS – Stops after first match (more efficient)
- IN – Compares against a list of values
📌 Tip: Use EXISTS when checking the existence of rows in subqueries.
7️⃣ What are Indexes in SQL?
Indexes improve read performance but can slow down
INSERT, UPDATE, and DELETE operations.
- Single-column Index
- Composite Index
- Unique Index
8️⃣ How to Optimize SQL Queries?
- Use appropriate indexes
- Avoid using
SELECT * - Apply
WHEREfilters early - Analyze execution / query plans
🚀 Interview Tip: Advanced SQL concepts often separate average candidates from top performers.
Comments (0)
No comments yet
Be the first to share your thoughts!
Leave a Comment