5 questions across Easy, Medium, and Hard levels
WHERE filters rows before grouping/aggregation. HAVING filters groups after the GROUP BY clause. WHERE cannot use aggregate functions; HAVING can. Example: SELECT dept, AVG(salary) FROM employees WHERE hire_date > 2020 GROUP BY dept HAVING AVG(salary) > 50000.
INNER JOIN: returns rows where there is a match in both tables. LEFT JOIN: all rows from left table + matching from right (NULL for no match). RIGHT JOIN: all from right + matching from left. FULL OUTER JOIN: all rows from both tables. CROSS JOIN: Cartesian product of both tables. SELF JOIN: joining a table with itself.
Window functions perform calculations across related rows without collapsing them like GROUP BY. They use OVER() clause. Examples: ROW_NUMBER() OVER(ORDER BY salary DESC), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(PARTITION BY dept). Useful for running totals, rankings, and moving averages.
SELECT column1, column2, COUNT(*) as count FROM table GROUP BY column1, column2 HAVING COUNT(*) > 1. To delete duplicates while keeping one: DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY column1, column2). Always backup before deletion.
Key techniques: 1) Use indexes on frequently queried columns. 2) Avoid SELECT * - specify columns. 3) Use EXPLAIN/EXPLAIN ANALYZE to understand query plan. 4) Avoid functions on indexed columns in WHERE clause. 5) Use JOINs instead of subqueries when possible. 6) Limit result sets with WHERE and LIMIT. 7) Normalize database design. 8) Partition large tables.