SQL for Data Analyst Interviews — Frequently Asked Questions

SELECT
  COUNT(*) AS headcount,
  SUM(salary) AS total_salary,
  ROUND(AVG(salary), 2) AS avg_salary,
  MAX(salary) AS highest,
  MIN(salary) AS lowest
FROM employees;

Interviewers like this to test whether you know multiple aggregates can go in the same SELECT. Always alias your columns for readable output.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
  AND salary < (SELECT MAX(salary) FROM employees)
ORDER BY salary DESC;

Two scalar subqueries in one WHERE clause. Tests whether you can combine subqueries with AND. A common way to identify mid-to-high performers.

SELECT MIN(salary) AS third_highest
FROM (
  SELECT DISTINCT salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 3
) top3;

-- Without LIMIT at all:
SELECT MAX(salary) AS third_highest
FROM employees
WHERE salary NOT IN (
  SELECT DISTINCT salary FROM employees
  ORDER BY salary DESC LIMIT 2
);

Classic trick question. The first approach uses LIMIT inside a subquery. The second uses NOT IN to exclude top 2 then finds MAX of what's left.

-- DELETE: removes specific rows, can use WHERE, logs each row, rollback possible
DELETE FROM employees WHERE department = 'Old Dept';

-- TRUNCATE: removes ALL rows fast, cannot use WHERE, minimal logging, faster
TRUNCATE TABLE temp_data;

-- DROP: removes the table itself (structure + data + indexes)
DROP TABLE temp_data;

DELETE is DML (reversible in a transaction). TRUNCATE is DDL (fast, not row-by-row logged). DROP destroys the table. Know all three — common theory question.

-- SQL execution order (not writing order):
-- 1. FROM (and JOINs) — identify source tables
-- 2. WHERE — filter rows
-- 3. GROUP BY — group remaining rows
-- 4. HAVING — filter groups
-- 5. SELECT — choose columns and expressions
-- 6. DISTINCT — remove duplicates
-- 7. ORDER BY — sort result
-- 8. LIMIT / TOP — restrict output rows

This explains WHY you cannot use a SELECT alias in WHERE (WHERE runs before SELECT) but CAN use it in ORDER BY (ORDER BY runs after SELECT). A favourite interview theory question.