Interview Mastery - SQL Mastery

Beginner Interview Questions

Start with these foundational questions to build confidence in basic SQL concepts, syntax, and database fundamentals.

1. What is SQL?

▶

SQL (Structured Query Language) is the language used to communicate with relational databases. You declare what you want, the optimizer decides how.

With SQL you can: create tables, insert/read/update/delete data, join related tables, and analyze data.

SELECT * FROM employees;

Returns all columns and rows from employees.

2. What is a database?

▶

An organized collection of data, e.g., Company Database: employees, departments, jobs, job_history. Each database contains related tables, indexes, views, and procedures.

3. What is a table, row, and column?

▶

Table stores data in rows and columns. Row = one record (e.g., 101 | John | 75000). Column = one attribute (e.g., salary).

employee_id	first_name	salary
101	John	75000

4. What is a primary key?

▶

A column (or composite) that uniquely identifies a row. Properties: unique, NOT NULL, one per table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

5. What is a foreign key?

▶

A column that creates a relationship between tables, referencing a primary key in another table.

FOREIGN KEY (department_id) REFERENCES departments(department_id)

Ensures referential integrity — you cannot insert a non-existent department_id.

6. What is NULL?

▶

NULL means unknown, not zero or empty string. Never use = NULL.

SELECT * FROM employees WHERE manager_id IS NULL; -- correct
-- Wrong: WHERE manager_id = NULL

7. What is the difference between DELETE, TRUNCATE, and DROP?

▶

Command	Deletes rows	Deletes structure	WHERE
DELETE	Yes	No	Yes
TRUNCATE	All	No	No
DROP	Yes	Yes	No

DELETE FROM employees WHERE employee_id=101;
TRUNCATE TABLE employees;
DROP TABLE employees;

8. What is the difference between WHERE and HAVING?

▶

WHERE	HAVING
Filters rows before GROUP BY	Filters groups after GROUP BY
No aggregate	With aggregate (AVG>...)

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 75000;

9. What is GROUP BY?

▶

Groups rows sharing same values to calculate aggregates per group.

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id;

One result row per department.

10. What is DISTINCT?

▶

Removes duplicate values, returning only unique rows.

SELECT DISTINCT department_id FROM employees;

Intermediate Interview Questions

These intermediate questions test your ability to join tables, aggregate data, and write subqueries and CTEs for common business problems.

1. Explain INNER JOIN

▶

Returns only matching rows from both tables.

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

With sample tables (Alice/10, Bob/20, Charlie/10, David NULL + HR/10, Engineering/20, Marketing/30) → 3 rows (Alice, Bob, Charlie). David and Marketing excluded.

2. Explain LEFT JOIN

▶

Returns all rows from left table, plus matches from right (NULL if no match).

SELECT d.department_name, e.first_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

Shows all departments, including Marketing (no employees) → first_name NULL.

3. Explain SELF JOIN

▶

A table joins to itself, aliasing as two copies (e and m) — essential for hierarchies.

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Key: e.manager_id = m.employee_id. Use LEFT JOIN to keep employees with no manager.

4. Find the second highest salary

▶

Clarify distinct vs non-distinct. Two methods:

-- Method 1: MAX subquery
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: DENSE_RANK (clearer for distinct)
WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
)
SELECT salary FROM ranked WHERE rnk=2;

5. Find employees earning above company average

▶

SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery calculates average, outer query compares each salary.

6. Find the highest salary in each department

▶

WITH ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk=1;

7. Find duplicate records

▶

SELECT first_name, last_name, email, COUNT(*) 
FROM employees
GROUP BY first_name, last_name, email
HAVING COUNT(*) > 1;

8. Count employees by department

▶

SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id;

9. Find departments with no employees

▶

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

10. Explain subqueries and CTEs

▶

Subquery: query inside another (in WHERE/FROM). CTE (WITH) names a temporary result for readability.

WITH high AS (SELECT * FROM employees WHERE salary>70000)
SELECT * FROM high;

Advanced Interview Questions

Advanced questions challenge you with window functions, execution order, and performance topics that distinguish senior data engineers.

1. ROW_NUMBER vs RANK vs DENSE_RANK

▶

Function	Ties	Gaps
ROW_NUMBER	No shared rank	No
RANK	Shared rank	Yes (1,1,3)
DENSE_RANK	Shared rank	No (1,1,2)

SELECT salary, ROW_NUMBER() OVER(ORDER BY salary DESC), RANK() OVER(ORDER BY salary DESC), DENSE_RANK() OVER(ORDER BY salary DESC) FROM employees;

2. Explain LAG and LEAD

▶

LAG = previous row, LEAD = next row in ordered partition.

SELECT customer_id, transaction_date, amount,
       LAG(amount) OVER(PARTITION BY customer_id ORDER BY transaction_date) AS prev_amount,
       LEAD(amount) OVER(PARTITION BY customer_id ORDER BY transaction_date) AS next_amount
FROM transactions;

3. Employees earning more than their manager

▶

SELECT e.first_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Classic SELF JOIN on e.manager_id = m.employee_id.

4. Employees above department average

▶

SELECT * FROM (
  SELECT *, AVG(salary) OVER(PARTITION BY department_id) AS dept_avg
  FROM employees
) x WHERE salary > dept_avg;

5. Previous transaction for each customer (LAG)

▶

SELECT customer_id, transaction_date, amount,
       LAG(amount) OVER(PARTITION BY customer_id ORDER BY transaction_date) AS prev_amount
FROM transactions;

6. Next transaction (LEAD)

▶

SELECT customer_id, transaction_date, amount,
       LEAD(amount) OVER(PARTITION BY customer_id ORDER BY transaction_date) AS next_amount
FROM transactions;

7. Calculate running totals

▶

SELECT customer_id, transaction_date, amount,
       SUM(amount) OVER(PARTITION BY customer_id ORDER BY transaction_date) AS running_total
FROM transactions;

8. Remove duplicate records safely

▶

WITH dup AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY first_name, last_name, email ORDER BY employee_id) AS rn
  FROM employees
)
DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM dup WHERE rn>1);
-- Or SELECT * FROM dup WHERE rn>1 to preview

9. Explain SQL execution order

▶

Logical order: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This explains why WHERE annual_salary > 100000 fails if alias defined in SELECT.

SELECT salary*12 AS annual FROM employees WHERE annual > 100000; -- fails
-- Fix with CTE
WITH a AS (SELECT salary*12 AS annual FROM employees) SELECT * FROM a WHERE annual>100000;

10. Explain indexes

▶

Indexes speed up reads (like book index) but slow writes and use storage.

CREATE INDEX idx_dept ON employees(department_id);
EXPLAIN SELECT * FROM employees WHERE department_id=10;

11. Explain partitioning

▶

Split large table into smaller parts (e.g., by year: 2023, 2024) for faster queries and easier maintenance. Different from sharding (across servers).

12. Explain OLTP vs OLAP

▶

OLTP: many small transactions, writes, operational (banking). OLAP: large analytical reads, aggregations, historical (reporting, warehouse).

13. Explain Star vs Snowflake schema

▶

Star: central fact + denormalized dimensions, fewer joins, simpler. Snowflake: normalized dimensions, more tables/joins, less redundancy.

Star	Snowflake
Simpler, fewer joins	More normalized, more joins

SQL Problem-Solving Framework

Use this 8-step framework to systematically break down any SQL interview problem from output to final query.

Understand output — What columns?
Identify tables — Where columns come from?
Relationships — PK/FK
JOIN? — Multiple tables?
Filtering? — WHERE
Aggregation? — GROUP BY
Keep rows? — Window Function
Multiple steps? — CTE

Decision Tree

Do I need multiple tables? → JOIN
One result per group? → GROUP BY
Keep rows + group calc? → WINDOW
One query depends on another? → SUBQUERY/CTE

SQL Cheat Sheet

Filtering

WHERE, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL

Sorting

ORDER BY ASC/DESC

Aggregation

COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING

Joins

INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS

Advanced

WITH, OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

Performance

Index, EXPLAIN, Partitioning, Sharding

Bookmarks