SQL: Interview Questions and Answers

1. SQL Interview Questions for Freshers

Entry-level interviews check that you understand the building blocks: keys, constraints, NULL, and how a basic query runs.

1. What is SQL?

   SQL (Structured Query Language) is the standard language for storing, reading, changing, and deleting data in a relational database.

2. What are the main categories of SQL commands?

   DDL defines structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DQL reads data (SELECT), DCL controls access (GRANT, REVOKE), and TCL manages transactions (COMMIT, ROLLBACK).

3. What is a primary key?

   A column, or set of columns, that uniquely identifies each row. It cannot contain NULL and a table can have only one.

4. What is a foreign key?

   A column that points to the primary key of another table. It stops you from inserting a row that references a record that does not exist.

5. What does NULL mean?

   NULL means the value is unknown or missing. It is not zero and not an empty string, so you test it with IS NULL, never with = NULL.

6. What is a constraint?

   A rule the database enforces on a column or table, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

7. What does DISTINCT do?

   It removes duplicate rows from the result, so each combination of the selected columns appears once.

8. In what order does a SELECT query logically run?

   FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT. This is why a column alias from SELECT cannot be used in WHERE.

2. SQL Interview Questions for Experienced

Senior interviews move from syntax to behavior: transactions, indexing, concurrency, and how you tune a slow query.

1. What are the ACID properties?

   Atomicity (all or nothing), Consistency (rules always hold), Isolation (concurrent transactions do not interfere), and Durability (committed data survives a crash).

2. What is an index and what does it cost?

   An index is a sorted structure, usually a B-tree, that lets the database find rows without scanning the whole table. It speeds up reads but slows down writes and uses extra storage.

3. What are the transaction isolation levels?

   Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each higher level prevents more anomalies (dirty reads, non-repeatable reads, phantom reads) at the price of more locking.

4. What is the difference between a CTE and a subquery?

   Both produce a temporary result. A CTE is named with WITH, can be referenced several times in the same query, and can be recursive, which makes long queries easier to read.

5. What is a window function?

   A function that calculates across a set of rows related to the current row using OVER, without collapsing the rows the way GROUP BY does. Examples are ROW_NUMBER, RANK, LAG, and SUM() OVER.

6. How do you find out why a query is slow?

   Read the execution plan with EXPLAIN. Look for full table scans, missing indexes, functions wrapped around indexed columns, SELECT *, and joins that multiply rows.

7. What is a deadlock?

   Two transactions each hold a lock the other needs, so neither can continue. The database picks one as the victim and rolls it back. Locking tables in the same order in every transaction avoids most deadlocks.

8. What is the difference between a view and a materialized view?

   A view stores only the query and runs it each time it is read. A materialized view stores the result on disk and must be refreshed, which makes reads fast but the data can be stale.

3. SQL Query Interview Questions

In this round you write the query yourself. Say your approach out loud before you type it.

1. How do you find employees who earn more than the company average?

   Compare each salary to a scalar subquery: WHERE salary > (SELECT AVG(salary) FROM employees).

2. How do you find the highest paid employee in each department?

   Use a correlated subquery that returns the MAX(salary) for the same department, or rank rows with DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) and keep rank 1.

3. How do you find duplicate values in a column?

   Group by the column and keep groups with more than one row: GROUP BY email HAVING COUNT(*) > 1.

4. How do you get the top 3 rows by a column?

   Sort and limit: ORDER BY salary DESC LIMIT 3. SQL Server uses SELECT TOP 3 and Oracle uses FETCH FIRST 3 ROWS ONLY.

5. How do you count rows that match a condition without filtering the others out?

   Use conditional aggregation: SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END).

4. SQL Joins Interview Questions

Joins appear in almost every SQL interview. Be ready to say which rows each join keeps and which it drops.

1. What is a join?

   A join combines rows from two or more tables using a related column, normally a foreign key matched to a primary key.

2. What are the types of joins?

   INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and the self join, which joins a table to itself.

3. What is a self join used for?

   Comparing rows inside the same table, for example matching each employee to their manager through manager_id.

4. What does a CROSS JOIN return?

   Every combination of rows from both tables. Two tables with 4 and 8 rows give 32 rows.

5. How do you find rows in one table with no match in another?

   Use an anti-join: LEFT JOIN the second table and keep rows where its key IS NULL, or use NOT EXISTS.

6. What is the difference between ON and WHERE in a LEFT JOIN?

   A condition in ON is applied while matching, so unmatched left rows are still kept. The same condition in WHERE runs after the join and removes those rows, which quietly turns the LEFT JOIN into an INNER JOIN.

5. SQL Interview Questions for Data Analysts

Analyst interviews focus on turning raw rows into metrics: totals over time, comparisons, and percentages.

1. How do you calculate a running total?

   Use a window function: SUM(amount) OVER (ORDER BY order_date).

2. How do you compare a value to the previous period?

   Use LAG(revenue) OVER (ORDER BY month) to bring the previous row onto the current row, then subtract.

3. How do you calculate each row's share of the total?

   Divide by a window total: 100.0 * amount / SUM(amount) OVER ().

4. How do you pivot rows into columns?

   Use conditional aggregation, one SUM(CASE WHEN ...) per output column, grouped by the row label.

5. How do you handle NULL values in a report?

   Replace them with COALESCE(column, default). Remember that COUNT(column) and AVG(column) skip NULLs, while COUNT(*) does not.

6. What is a cohort analysis?

   Grouping users by a shared starting point, such as signup month, then tracking what each group does in later periods to measure retention.

6. DBMS Interview Questions

These questions test database theory rather than query writing: design, keys, and normalization.

1. What is a DBMS and how is an RDBMS different?

   A DBMS is software that stores and manages data. An RDBMS is a DBMS that stores data in related tables and enforces keys and constraints, such as MySQL, PostgreSQL, and SQL Server.

2. What is normalization?

   Organizing tables to remove repeated data. 1NF requires atomic values, 2NF removes partial dependencies on a composite key, and 3NF removes dependencies between non-key columns.

3. What is denormalization?

   Deliberately adding redundant data back to reduce joins and speed up reads, common in reporting and analytics databases.

4. What is the difference between a candidate key and a super key?

   A super key is any set of columns that uniquely identifies a row. A candidate key is a super key with no unnecessary columns. One candidate key is chosen as the primary key.

5. What is a schema?

   The structure of a database: its tables, columns, data types, relationships, and constraints.

6. What is an ER diagram?

   An Entity-Relationship diagram shows entities (tables), their attributes (columns), and how they relate: one-to-one, one-to-many, or many-to-many.

7. What is the difference between OLTP and OLAP?

   OLTP systems handle many small, fast transactions, like placing an order. OLAP systems run large analytical queries over historical data, like yearly sales reports.

7. Difference-based Questions

"What is the difference between X and Y?" is the most common interview format. Answer with two or three clear contrasts.

DELETE vs TRUNCATE vs DROP

All three remove data, but at different levels. SQLite has no TRUNCATE; a DELETE without WHERE does the same job.

WHERE vs HAVING

WHERE filters rows before they are grouped. HAVING filters the groups afterwards.

UNION vs UNION ALL

Both stack the results of two queries. UNION removes duplicates; UNION ALL keeps every row.

Primary Key vs Unique Key

Both prevent duplicate values. A primary key also rejects NULL and there is only one per table.

INNER JOIN vs OUTER JOIN

An inner join returns only rows that match in both tables. An outer join also keeps the rows that have no match.

RANK vs DENSE_RANK

Both give tied rows the same rank. RANK skips the next numbers after a tie; DENSE_RANK does not.

CHAR vs VARCHAR

CHAR always stores the full declared length. VARCHAR stores only what you put in. SQLite treats both as TEXT, so the difference matters in MySQL, PostgreSQL, and SQL Server.

Clustered vs Non-Clustered Index

A clustered index decides the physical order of the table rows. A non-clustered index is a separate lookup structure that points back to them.

8. SQL Cheat Sheet

A one-page reference of the syntax you are most likely to need in an interview.

9. SQL Practice Exercises

Write each query in the editor before opening the solution. Every example on this page starts with a fresh database.

Employees Dataset

1. List employees hired after 1 January 2020, newest first.

   Show solution

   SELECT name, hire_date
   FROM employees
   WHERE hire_date > '2020-01-01'
   ORDER BY hire_date DESC;

2. Show the total payroll for each department name, highest first.

   Show solution

   SELECT d.name, SUM(e.salary) AS payroll
   FROM departments d
   JOIN employees e ON e.department_id = d.id
   GROUP BY d.name
   ORDER BY payroll DESC;

3. Find the highest paid employee in each department.

   Show solution

   SELECT name, department_id, salary
   FROM employees e
   WHERE salary = (
       SELECT MAX(salary)
       FROM employees
       WHERE department_id = e.department_id
   );

4. List each manager with the number of people who report to them.

   Show solution

   SELECT m.name AS manager, COUNT(*) AS reports
   FROM employees e
   JOIN employees m ON m.id = e.manager_id
   GROUP BY m.name;

Shop Dataset

5. List the products that are out of stock.

   Show solution

   SELECT name, category
   FROM products
   WHERE stock = 0;

6. Find customers who have never placed an order.

   Show solution

   SELECT c.name, c.email
   FROM customers c
   LEFT JOIN orders o ON o.customer_id = c.id
   WHERE o.id IS NULL;

7. Find the best-selling product by total quantity sold.

   Show solution

   SELECT p.name, SUM(oi.quantity) AS units_sold
   FROM order_items oi
   JOIN products p ON p.id = oi.product_id
   GROUP BY p.name
   ORDER BY units_sold DESC
   LIMIT 1;

8. Count the orders in each status.

   Show solution

   SELECT status, COUNT(*) AS orders
   FROM orders
   GROUP BY status
   ORDER BY orders DESC;