21. How do you handle NULLs in queries?
Use IS NULL, IS NOT NULL, COALESCE(), or IFNULL() to manage NULLs.
Example:
SELECT name FROM users WHERE email IS NULL;
22. What is COALESCE() in SQL?
It returns the first non-NULL value from a list.
SELECT COALESCE(phone, 'Not Provided') FROM customers;
23. What are aggregate functions? 📊
Functions that perform calculations on multiple rows:
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
24. What is GROUP BY and how does it work?
It groups rows that have the same values and is used with aggregate functions.
SELECT department, COUNT(*) FROM employees GROUP BY department;
25. What is the difference between COUNT(\*) and COUNT(column)?
- COUNT(\*): Counts all rows, including those with NULLs.
- COUNT(column): Counts non-NULL values in that column.
26. What are window functions? 🪟
They perform calculations across rows related to the current row without collapsing results.
Examples:
ROW_NUMBER(), RANK(), SUM() OVER()27. Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()
- RANK(): Skips ranks on ties (1, 1, 3)
- DENSE_RANK(): No gaps in ranking (1, 1, 2)
- ROW_NUMBER(): Unique sequence for each row (1, 2, 3)
28. What is the use of LAG() and LEAD()?
They access previous (LAG) or next (LEAD) row values in the result set.
SELECT name, salary, LAG(salary) OVER (ORDER BY id) AS prev_salary FROM employees;
29. What is a CASE statement?
It's used for conditional logic in queries.
SELECT name,
CASE
WHEN salary > 5000 THEN 'High'
ELSE 'Low'
END AS salary_level
FROM employees;
30. What is the difference between CHAR and VARCHAR?
- CHAR(n): Fixed-length, always reserves
n characters. (Padding with spaces if shorter)- VARCHAR(n): Variable-length, uses space based on actual content. (More efficient for varying lengths)
💬 Double Tap ❤️ For Part-4