Post #2692

✅SQL Interview Questions with Answers 1. What is a window function? A window function computes results over a group ("window") of rows related to the current row, without collapsing them (like GROUP BY). Examples: ROW_NUMBER(), RANK(), SUM() OVER(...) for running totals, rankings, or moving averages. 2. What is the difference between RANK() and ROW_NUMBER()? • ROW_NUMBER(): assigns unique sequential numbers to all rows, even if values are equal. • RANK(): gives same rank to tied values, then skips the next rank (e.g., 1, 1, 3). 3. How do you find the second highest salary? SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) t WHERE rnk = 2; This avoids ties if you want exactly the second‑highest value. 4. What is a recursive CTE? A recursive CTE refers to itself in its WITH definition, usually in the form "anchor + UNION ALL recursive step". It is used for hierarchical data like managers‑employees, org charts, or tree structures. 5. What is the difference between correlated and non-correlated subquery? • Non‑correlated: runs once, independent of the outer query. • Correlated: references columns from the outer query and runs once per outer row (e.g., SELECT ... FROM t1 WHERE col > (SELECT AVG(col) FROM t2 WHERE t2.id = t1.id)). 6. How do you remove duplicates without DISTINCT? Use window functions: DELETE FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) as rn FROM table ) t WHERE rn > 1; Or use GROUP BY and keep one row per group. 7. What is an INDEX and when do you use it? An index speeds up data retrieval on specified columns (used in WHERE, JOIN, ORDER BY). Use it on columns that are frequently filtered or joined; avoid on very small tables or columns updated often. 8. Explain self-join with example. A self‑join joins a table to itself using aliases. Example: SELECT e1.name as employee, e2.name as manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.id; Useful for parent‑child relationships. 9. What is the difference between DELETE, DROP, and TRUNCATE? • DELETE: removes rows (can be filtered by WHERE), can be rolled back. • TRUNCATE: removes all rows quickly, resets storage; often not logged per row. • DROP: removes entire table (structure + data); cannot be rolled back. 10. How do you pivot/unpivot data in SQL? • Pivot: turns rows into columns (e.g., sales per month as columns) using PIVOT or conditional aggregation (MAX(CASE WHEN ... END)). • Unpivot: turns columns into rows (e.g., multiple month columns → one month column) using UNPIVOT or UNION ALL/VALUES. 11. What is LAG() and LEAD()? • LAG(col, n): value of col from n rows before current row. • LEAD(col, n): value from n rows after. Used for time‑series analysis (MoM change, prior/next values). 12. How do you handle NULL in aggregates? Most aggregates (SUM, AVG, MAX, MIN) ignore NULL. • COUNT(col) ignores NULL; COUNT(*) counts all rows. • Use COALESCE() or ISNULL() to replace NULL before aggregating. 13. What is the difference between VIEW and MATERIALIZED VIEW? • VIEW: virtual table; query runs every time you select. • MATERIALIZED VIEW: stores result physically and refreshes periodically; faster reads, slower updates. 14. Explain ACID properties. • Atomicity: transaction is "all or nothing". • Consistency: valid state before and after. • Isolation: concurrent transactions don't interfere. • Durability: committed changes survive crashes. 15. How do you optimize a slow query? • Add proper indexes on WHERE, JOIN, ORDER BY columns. • Remove unnecessary SELECT *, DISTINCT, or functions on indexed columns. • Check execution plan and avoid large scans; use LIMIT or partitioning if possible. 16. What is the difference between INNER JOIN and EXISTS? • INNER JOIN: returns combined columns from both tables where keys match. • EXISTS: checks if a subquery returns any rows; usually faster when you only care about existence (e.g., filtering with WHERE EXISTS).