SQL Interview Questions for Data Analysts

WHERE filters rows before grouping; HAVING filters groups after aggregation. Example: WHERE region = 'East' limits rows, while HAVING SUM(sales) > 1000 keeps only groups whose total exceeds 1000.

• INNER JOIN: only matching rows in both tables.
• LEFT JOIN: all rows from the left table plus matches.
• RIGHT JOIN: all rows from the right plus matches.
• FULL OUTER JOIN: all rows from both, matched where possible.

DISTINCT removes duplicate rows from the result. GROUP BY collapses rows into groups so you can apply aggregates like SUM or COUNT per group. If you only need unique values with no aggregation, use DISTINCT.

• DELETE removes rows (can use WHERE) and is logged/rollback-able.
• TRUNCATE removes all rows quickly, no WHERE, resets identity.
• DROP removes the entire table structure.

NULL means "unknown/absent", not zero or empty string. You cannot test it with =; use IS NULL / IS NOT NULL. Functions like COALESCE(col, 0) replace NULL with a default.

CHAR(n) is fixed length (padded). VARCHAR(n) is variable length up to n (saves space). TEXT stores large variable-length strings. Use VARCHAR for most text columns.

Although you write SELECT first, SQL logically runs: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This is why you cannot use a SELECT alias in WHERE but can in ORDER BY.

Aggregates summarise many rows into one value: COUNT, SUM, AVG, MIN, MAX. Example: SELECT region, SUM(sales) FROM orders GROUP BY region; returns total sales per region.

A subquery is a query nested inside another. Example: find customers who spent above average — SELECT name FROM customers WHERE total > (SELECT AVG(total) FROM customers);. Subqueries can appear in SELECT, FROM or WHERE.

UNION stacks two result sets and removes duplicates (slower). UNION ALL keeps all rows including duplicates (faster). Use UNION ALL when you know there are no duplicates or you want to keep them.

All assign a position within an ordered partition. ROW_NUMBER is always unique. RANK leaves gaps after ties (1,1,3). DENSE_RANK leaves no gaps (1,1,2). Choose based on how you want ties handled.

Group by the columns that define a duplicate and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;. To delete duplicates, a CTE with ROW_NUMBER() is a clean approach.

An index is a data structure that speeds up reads on indexed columns (like a book index). The trade-off: it uses storage and slows down writes (inserts/updates must maintain the index). Index columns used in WHERE/JOIN/ORDER BY, not everything.

Window functions compute across a set of rows related to the current row without collapsing them. Example — rank sales within each region: SELECT region, name, sales, RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rnk FROM orders;. Common ones: ROW_NUMBER, RANK, SUM() OVER(...), LAG/LEAD.

One clean approach uses a window function: SELECT name, salary FROM (SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM emp) t WHERE rnk = 2;. DENSE_RANK handles ties correctly.

A CTE (Common Table Expression) is a named temporary result defined with WITH. It makes complex queries readable and can be referenced multiple times, and (as a recursive CTE) can traverse hierarchies. Example: WITH ranked AS (SELECT ... ) SELECT * FROM ranked WHERE rnk = 1;

Add appropriate indexes on filtered/joined columns, select only needed columns (avoid SELECT *), filter early, avoid functions on indexed columns in WHERE, review the execution plan, and prefer joins over correlated subqueries where possible.

A non-correlated subquery runs once, independently of the outer query. A correlated subquery references the outer query and runs once per outer row (slower). Correlated subqueries can often be rewritten as joins for better performance.

Use a window function: SELECT dt, amount, SUM(amount) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM sales;. The frame clause defines the window of rows summed up to the current row.

ACID guarantees reliable transactions: Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not interfere), and Durability (committed data survives failures).