1️⃣ What are Window Functions in SQL?
Answer: They perform calculations across rows related to the current row without collapsing the result set.
Example:
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(): doesn't skip numbers
– ROW_NUMBER(): gives unique numbers to each row
Use: Sorting, pagination, leaderboard design
3️⃣ What is the use of COALESCE()?
Answer: Returns the first non-null value in a list.
Example:
SELECT name, COALESCE(nickname, 'No Nick') FROM users;
4️⃣ How does CASE work in SQL?
Answer: It's used for conditional logic.
Example:
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).
Answer: Temporary result set for readable and reusable queries.
Example:
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️⃣ What is the difference between EXISTS and IN?
– EXISTS: stops after finding the first match (more efficient)
– IN: compares a list of values
Use EXISTS for subqueries when checking existence
7️⃣ What are Indexes in SQL?
Answer: They improve read performance but slow down write operations.
– Types: Single-column, Composite, Unique
8️⃣ How to optimize SQL queries?
– Use proper indexes
– Avoid SELECT *
– Use WHERE filters early
– Analyze query plan
💬 Double Tap ❤️ For More!