Master SQL Interviews: 50+ Database Questions for Career Success 2026

Domain Overview

SQL is the language of data. Whether you're a data analyst, backend developer, or DBA, SQL skills are fundamental. In 2026, interviewers expect you to not just write queries, but understand execution plans, indexing strategies, and how to optimize for millions of rows.

What makes SQL interviews tricky is that syntax is easy to learn, but understanding query performance requires knowing how databases work internally—B-trees, query planners, join algorithms, and transaction isolation levels.

Fundamental Questions (Q1-Q15)

1. Explain the different types of JOINs with examples.

Expert Answer:

{`-- Setup: users (id, name) | orders (id, user_id, amount)

-- INNER JOIN: Only matching rows from both tables
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: All rows from left table, matching from right
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Users without orders will have NULL for amount

-- RIGHT JOIN: All rows from right table, matching from left
-- (Less common, can rewrite as LEFT JOIN with swapped tables)

-- FULL OUTER JOIN: All rows from both tables
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN: Cartesian product (every combination)
SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;`}

Key insight: The JOIN type determines which rows appear when there's no match. INNER = only matches. LEFT = all from left. FULL = all from both.

2. What is the difference between WHERE and HAVING?

Expert Answer:

WHERE: Filters rows BEFORE grouping

HAVING: Filters groups AFTER aggregation

{`-- Find categories with more than 10 active products
-- WHERE filters individual rows first
-- HAVING filters the aggregated groups

SELECT category, COUNT(*) as product_count
FROM products
WHERE status = 'active'          -- Filter rows before grouping
GROUP BY category
HAVING COUNT(*) > 10;            -- Filter groups after aggregation

-- You can't use aggregate functions in WHERE:
-- WHERE COUNT(*) > 10  ❌ ERROR!`}

Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

3. How do indexes work? When should you add an index?

Expert Answer:

Indexes are data structures (typically B-trees) that allow the database to find rows without scanning the entire table.

{`-- Without index: Full table scan O(n)
-- With index: B-tree lookup O(log n)

CREATE INDEX idx_users_email ON users(email);

-- Composite index for queries that filter on multiple columns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Important: Column order matters!
-- This index helps: WHERE user_id = 1 AND created_at > '2024-01-01'
-- This index helps: WHERE user_id = 1
-- This index does NOT help: WHERE created_at > '2024-01-01' alone`}

When to add indexes:

• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY
• Foreign keys

When NOT to add:

• Small tables (full scan is fast anyway)
• Columns with low cardinality (few unique values)
• Tables with heavy writes (indexes slow down inserts/updates)

4. What's the difference between UNION and UNION ALL?

Expert Answer:

{`-- UNION: Combines results and removes duplicates
SELECT name FROM customers
UNION
SELECT name FROM suppliers;
-- If "Acme Corp" appears in both, only one row in result

-- UNION ALL: Combines all results, keeps duplicates
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;
-- If "Acme Corp" appears in both, two rows in result

-- Performance: UNION ALL is faster (no deduplication step)
-- Use UNION ALL when you know there are no duplicates or want them`}

5. Explain NULL handling in SQL.

Expert Answer:

NULL represents unknown/missing data. It's NOT the same as empty string or zero.

{`-- NULL comparisons are tricky
SELECT * FROM users WHERE age = NULL;     -- Returns nothing!
SELECT * FROM users WHERE age IS NULL;    -- Correct way

-- NULL in expressions propagates
SELECT 5 + NULL;  -- Returns NULL
SELECT NULL = NULL;  -- Returns NULL (not TRUE!)

-- COALESCE: Returns first non-NULL value
SELECT COALESCE(nickname, first_name, 'Anonymous') as display_name
FROM users;

-- NULLIF: Returns NULL if values are equal
SELECT NULLIF(discount, 0)  -- Avoid division by zero
FROM orders;

-- NULL in aggregations
SELECT AVG(age) FROM users;  -- NULLs are ignored
SELECT COUNT(*) FROM users;  -- Counts all rows
SELECT COUNT(age) FROM users; -- Counts non-NULL ages only`}

6. What is a subquery? When would you use it vs a JOIN?

Expert Answer:

{`-- Subquery in WHERE (correlated)
SELECT * FROM orders o
WHERE amount > (
  SELECT AVG(amount) FROM orders WHERE user_id = o.user_id
);

-- Subquery in FROM (derived table)
SELECT dept_name, avg_salary
FROM (
  SELECT department_id, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department_id
) dept_avg
JOIN departments d ON dept_avg.department_id = d.id;

-- Same query with JOIN (often more efficient)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;`}

Use subquery when:

• You need a scalar value for comparison
• EXISTS/NOT EXISTS checks
• The logic is clearer with nesting

Use JOIN when:

• You need columns from both tables
• Usually better performance (optimizer can handle better)

7-15. More Fundamental Questions:

• 7. What is database normalization? Explain 1NF, 2NF, 3NF.
• 8. What are primary keys and foreign keys?
• 9. Explain the difference between DELETE, TRUNCATE, and DROP.
• 10. What is the difference between VARCHAR and CHAR?
• 11. How do you remove duplicates from a table?
• 12. What is the difference between IN and EXISTS?
• 13. How do you find the Nth highest salary?
• 14. What are constraints in SQL (UNIQUE, CHECK, DEFAULT)?
• 15. Explain the difference between RANK, DENSE_RANK, and ROW_NUMBER.

Intermediate Questions (Q16-Q35)

{`-- Syntax: function() OVER (PARTITION BY ... ORDER BY ...)

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

-- Rank within groups
SELECT
  department,
  employee,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- Moving average (last 3 rows)
SELECT
  date,
  amount,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg
FROM sales;

-- Lag/Lead: Access previous/next row
SELECT
  date,
  amount,
  LAG(amount, 1) OVER (ORDER BY date) as prev_amount,
  amount - LAG(amount, 1) OVER (ORDER BY date) as daily_change
FROM sales;`}

{`EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND created_at > '2024-01-01';

-- Sample output (PostgreSQL):
-- Index Scan using idx_orders_user_date on orders
--   Index Cond: (user_id = 123 AND created_at > '2024-01-01')
--   Rows Removed by Filter: 0
--   Planning Time: 0.5 ms
--   Execution Time: 2.3 ms`}

{`-- CTE: Named temporary result set
WITH high_value_customers AS (
  SELECT user_id, SUM(amount) as total_spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 1000
)
SELECT u.name, hvc.total_spent
FROM users u
JOIN high_value_customers hvc ON u.id = hvc.user_id;

-- Recursive CTE: For hierarchical data
WITH RECURSIVE subordinates AS (
  -- Base case
  SELECT id, name, manager_id, 1 as level
  FROM employees
  WHERE id = 1  -- CEO

  UNION ALL

  -- Recursive case
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;`}

Advanced & Real-World Questions (Q36-Q50)

{`-- Core tables
users (id, email, password_hash, created_at)
products (id, name, description, price, inventory_count)
categories (id, name, parent_id)  -- Hierarchical
product_categories (product_id, category_id)  -- Many-to-many

-- Orders
orders (id, user_id, status, total, created_at)
order_items (id, order_id, product_id, quantity, price_at_time)
-- Note: price_at_time captures price when ordered (prices change)

-- Addresses (users can have multiple)
addresses (id, user_id, type, street, city, country, is_default)

-- Reviews
reviews (id, user_id, product_id, rating, comment, created_at)

-- Key indexes
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_products_category ON product_categories(category_id);`}

Common Mistakes Candidates Make