SQL remains one of the most tested skills in data and backend interviews. Whether you're interviewing for a data analyst, data engineer, backend developer, or business intelligence role, you'll almost certainly face SQL questions. Here are the 30 most commonly asked SQL interview questions with detailed answers.
WHERE filters rows before aggregation. HAVING filters groups after GROUP BY. Use WHERE to filter individual rows; use HAVING to filter on aggregate values like COUNT(*) or SUM(amount).
A PRIMARY KEY uniquely identifies each row in a table. It cannot be NULL and must be unique. A table can only have one primary key, which can be composed of multiple columns (composite key).
INNER JOIN returns rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right (NULL if no match). RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both tables.
DELETE removes specific rows and can use WHERE; it is logged and can be rolled back. TRUNCATE removes all rows quickly without logging individual rows; it cannot be rolled back in most systems.
NULL represents a missing or unknown value. NULL is not the same as 0 or an empty string. Use IS NULL / IS NOT NULL to check for NULLs — NULL = NULL evaluates to NULL, not TRUE.
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; — Or using a subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1; — This returns any email that appears more than once.
UNION combines result sets and removes duplicates. UNION ALL combines result sets and keeps all duplicates. UNION ALL is faster because it skips the deduplication step.
A subquery is a query nested inside another query. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses. Correlated subqueries reference the outer query and run once per row.
GROUP BY groups rows that share the same value in specified columns. It is used with aggregate functions (COUNT, SUM, AVG, MIN, MAX) to compute metrics per group.
Window functions perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does. Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER (PARTITION BY ...).
A Common Table Expression (WITH clause) defines a temporary named result set. CTEs make complex queries readable by breaking them into steps. Recursive CTEs can traverse hierarchical data like org charts.
RANK() leaves gaps after ties (1, 2, 2, 4). DENSE_RANK() does not leave gaps (1, 2, 2, 3). Use DENSE_RANK() when you need consecutive rankings even after ties.
Key steps: 1) Run EXPLAIN to see the query plan. 2) Add indexes on columns used in WHERE, JOIN ON, and ORDER BY. 3) Avoid SELECT * — fetch only needed columns. 4) Move filtering logic earlier. 5) Avoid N+1 patterns — use JOINs or subqueries.
A self-join joins a table to itself. Common use: finding an employee's manager (employees table with manager_id referencing id in the same table). SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id.
Run all the queries from this article in our free SQL playground.
Open SQL Playground →