SQL Interview Questions for Freshers (With Answers and Queries)
Last updated (2026-11-02)
These SQL interview questions for freshers cover what campus and new-grad interviews actually test: core SQL, joins, aggregation, window functions, the classic query problems, and the DBMS theory that comes with them. Every answer is short enough to say out loud in an interview.
All query questions use one small sample schema, defined below, so you can run them yourself. Queries are standard SQL, with notes where MySQL, PostgreSQL, SQL Server or Oracle behave differently.
The Sample Schema Used in Every Query
Two tables: employees and departments. Each employee has an optional manager_id that points to another employee, which is what the self join questions use.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2),
dept_id INT REFERENCES departments(dept_id),
manager_id INT REFERENCES employees(emp_id)
);| emp_id | name | salary | dept_id | manager_id |
|---|---|---|---|---|
| 1 | Asha | 90000 | 10 | NULL |
| 2 | Ravi | 70000 | 10 | 1 |
| 3 | Meera | 70000 | 20 | 1 |
| 4 | John | 75000 | 20 | 3 |
| 5 | Sara | NULL | NULL | 2 |
SQL Basics: SQL Interview Questions for Freshers With Answers
Definitions and behavior questions that usually open a SQL round.
1.What is SQL, and what are DDL, DML, DCL and TCL?
SQL (Structured Query Language) is the standard language for defining, querying and changing data in relational databases. Its commands fall into four groups:
- DDL (Data Definition):
CREATE,ALTER,DROP,TRUNCATE. Changes structure. - DML (Data Manipulation):
SELECT,INSERT,UPDATE,DELETE. Works on rows. Some texts putSELECTin a separate DQL group. - DCL (Data Control):
GRANT,REVOKE. Manages permissions. - TCL (Transaction Control):
COMMIT,ROLLBACK,SAVEPOINT.
2.What is the difference between DELETE, TRUNCATE and DROP?
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Selected rows (with WHERE) or all rows | All rows | The whole table and its structure |
| WHERE allowed | Yes | No | No |
| Row triggers fire | Yes | No | No |
| Speed on large tables | Slower, row by row | Fast, deallocates pages | Fast |
Rollback depends on the database: in PostgreSQL and SQL Server TRUNCATE can be rolled back inside a transaction, while MySQL and Oracle commit it implicitly. Say this nuance and you stand out.
3.In what order is a SELECT query logically executed?
Logically: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT/OFFSET. The optimizer may physically run things differently, but the result must match this order.
This explains a common error: a column alias defined in SELECT cannot be used in WHERE (it does not exist yet), but it can be used in ORDER BY.
4.How does NULL behave in comparisons?
NULL means unknown, so any comparison with it, even NULL = NULL, gives UNKNOWN rather than TRUE. WHERE keeps only TRUE rows, so WHERE salary = NULL returns nothing. Use IS NULL or IS NOT NULL.
SELECT name FROM employees WHERE salary IS NULL; -- Sara
SELECT name, COALESCE(salary, 0) AS salary FROM employees;COALESCE returns its first non-NULL argument and is the portable way to substitute a default. MySQL also has IFNULL and SQL Server has ISNULL.
5.What is the difference between CHAR and VARCHAR?
CHAR(n) is fixed length and pads shorter values with spaces, which suits fixed codes like country codes. VARCHAR(n) stores only the characters given (plus a small length prefix), so it is the usual choice for names and emails.
6.What constraints can you put on a column?
NOT NULL: the column must have a value.UNIQUE: no two rows share a value. Most databases allow multiple NULLs here, but SQL Server allows only one.PRIMARY KEY: unique and not null, one per table.FOREIGN KEY: the value must exist in the referenced table.CHECK: a condition each row must satisfy, such assalary > 0. MySQL enforces it only from 8.0.16.DEFAULT: the value used when none is supplied.
7.How do you return only the first N rows? Does the syntax differ?
Yes, this is a classic dialect difference. Always pair it with ORDER BY, otherwise "first" has no defined meaning.
-- MySQL, PostgreSQL, SQLite
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;
-- SQL Server
SELECT TOP 3 name, salary FROM employees ORDER BY salary DESC;
-- Standard SQL: PostgreSQL, Oracle 12c+, Db2
SELECT name, salary FROM employees
ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY;Joins and Set Operations
Joins are the most tested SQL topic. Revise them with the SQL joins notes.
8.What types of joins are there?
- INNER JOIN: only rows with a match in both tables.
- LEFT JOIN: all rows from the left table, NULLs where the right side has no match.
- RIGHT JOIN: the mirror of LEFT JOIN.
- FULL OUTER JOIN: all rows from both sides. MySQL does not support it; emulate it with a LEFT JOIN
UNIONa RIGHT JOIN. - CROSS JOIN: every combination of rows (a Cartesian product).
- SELF JOIN: a table joined to itself using two aliases.
9.What does a LEFT JOIN return for an employee with no department?
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;Sara still appears, with dept_name as NULL. With an INNER JOIN she would disappear, because her dept_id is NULL and NULL never equals anything.
10.Why does a WHERE condition on the right table turn a LEFT JOIN into an INNER JOIN?
Unmatched rows have NULLs in the right table's columns, so a filter like WHERE d.dept_name = 'Sales' is UNKNOWN for them and removes them. To keep every left row, move the condition into the ON clause.
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id AND d.dept_name = 'Sales';11.How do you find departments that have no employees?
Use an anti join: a LEFT JOIN that keeps only unmatched rows, or NOT EXISTS.
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;12.Why can NOT IN return zero rows when you expect results?
If the subquery returns even one NULL, x NOT IN (...) becomes UNKNOWN for every row that is not an explicit match, so nothing is returned. Finding employees who manage nobody shows the trap, because Asha's manager_id is NULL:
-- Returns no rows: the subquery contains a NULL
SELECT name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees);
-- Correct: NOT EXISTS is NULL safe
SELECT e.name FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id
);Prefer NOT EXISTS, or add WHERE manager_id IS NOT NULL inside the subquery.
13.What is the difference between UNION and UNION ALL?
Both stack the results of two queries with the same number of compatible columns. UNION removes duplicate rows, which costs a sort or hash step; UNION ALL keeps every row and is faster. Use UNION ALL unless you actually need deduplication.
14.What are INTERSECT and EXCEPT?
INTERSECT returns rows present in both results; EXCEPT returns rows in the first result but not the second. Both remove duplicates by default.
Support varies: PostgreSQL and SQL Server have both, MySQL only from 8.0.31, and Oracle traditionally uses MINUS instead of EXCEPT (newer versions accept both).
Aggregation, GROUP BY, HAVING and Window Functions
For a refresher on each function, see the aggregate functions notes.
15.What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*)counts rows, including rows full of NULLs. On the sample data it returns 5.COUNT(salary)counts only non-NULL values: 4, because Sara's salary is NULL.COUNT(DISTINCT salary)counts unique non-NULL values: 3 (90000, 70000, 75000).
Other aggregates also skip NULLs: AVG(salary) divides by 4, not 5. SUM over only NULLs returns NULL, not 0.
16.What is the difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after GROUP BY and can.
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary IS NOT NULL
GROUP BY dept_id
HAVING COUNT(*) >= 2;17.Which columns can you SELECT in a GROUP BY query?
Only grouped columns and aggregates. Selecting name while grouping by dept_id is an error in standard SQL, because each group has many names.
MySQL enforces this by default since 5.7 (ONLY_FULL_GROUP_BY). PostgreSQL also allows columns that are functionally dependent on a grouped primary key.
18.What are window functions, and how are they different from GROUP BY?
A window function computes a value over a set of related rows, defined by OVER (PARTITION BY ... ORDER BY ...), without collapsing them. GROUP BY returns one row per group; a window function keeps every row and adds a column.
SELECT name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;MySQL supports window functions from 8.0; PostgreSQL, SQL Server and Oracle have had them for years.
19.What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows in the window's order; they differ on ties. Ordering the sample salaries descending (90000, 75000, 70000, 70000):
| salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90000 | 1 | 1 | 1 |
| 75000 | 2 | 2 | 2 |
| 70000 | 3 | 3 | 3 |
| 70000 | 4 | 3 | 3 |
ROW_NUMBER is always unique, RANK leaves a gap after ties (the next value would be 5), and DENSE_RANK leaves no gap (the next would be 4). Where NULLs sort also differs by database, so filter them out when ranking.
20.How do you calculate a running total?
SELECT emp_id, salary,
SUM(salary) OVER (
ORDER BY emp_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM employees;The explicit ROWS frame matters: the default frame is RANGE, which treats rows with equal ORDER BY values as one step and can give surprising totals on ties.
21.How do you find employees who earn more than their department's average?
Either a correlated subquery or a window function. The window version reads the table once:
SELECT name, dept_id, salary
FROM (
SELECT name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees
) t
WHERE salary > dept_avg;SQL Query Interview Questions With Solutions
The query problems that come up again and again. Write them on paper first, then run them against the sample schema.
22.How do you find the second highest salary?
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);This returns 75000, handles duplicate top salaries, and returns NULL (not an empty result) when no second value exists. A LIMIT version needs DISTINCT, otherwise duplicates break it:
-- MySQL, PostgreSQL
SELECT DISTINCT salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- SQL Server, Oracle 12c+, PostgreSQL
SELECT DISTINCT salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;23.How do you find the Nth highest salary?
Use DENSE_RANK, so ties share a rank and "3rd highest" means the 3rd distinct salary. Replace 3 with N:
WITH ranked AS (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE salary IS NOT NULL
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 3;On the sample data this returns 70000. Without window functions, use LIMIT 1 OFFSET N-1 on distinct salaries.
24.How do you find duplicate rows?
Group by the columns that define a duplicate and keep groups with more than one row:
SELECT name, salary, dept_id, COUNT(*) AS copies
FROM employees
GROUP BY name, salary, dept_id
HAVING COUNT(*) > 1;GROUP BY treats NULLs as equal to each other, so two rows with a NULL dept_id land in the same group.
25.How do you delete duplicates but keep one copy?
Keep the smallest emp_id in each duplicate group and delete the rest:
DELETE FROM employees
WHERE emp_id NOT IN (
SELECT keep_id FROM (
SELECT MIN(emp_id) AS keep_id
FROM employees
GROUP BY name, salary, dept_id
) AS k
);The extra derived table k is needed in MySQL, which refuses to read from the table it is deleting from in a direct subquery. NOT IN is safe here because keep_id is never NULL.
26.How do you find employees who earn more than their manager?
A self join: one alias for the employee, one for the manager.
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;On the sample data this returns John (75000), whose manager Meera earns 70000. Sara is excluded because comparing her NULL salary is UNKNOWN.
27.How do you find the highest paid employee in each department?
SELECT dept_id, name, salary
FROM (
SELECT dept_id, name, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE salary IS NOT NULL
) t
WHERE rnk = 1;RANK returns every employee tied for the top. Use ROW_NUMBER if the question wants exactly one per department. For top 3 per department, change the filter to rnk <= 3.
28.How do you list each department with its employee count, including empty departments?
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;Counting e.emp_id instead of * is the key detail: an empty department gets one row of NULLs from the LEFT JOIN, so COUNT(*) would wrongly show 1 while COUNT(e.emp_id) shows 0.
DBMS Interview Questions: Keys, Normalization, ACID and Indexes
Theory questions that are asked alongside SQL. The DBMS notes cover each topic in more depth.
29.What are the different types of keys?
- Super key: any set of columns that uniquely identifies a row.
- Candidate key: a minimal super key (remove any column and it stops being unique).
- Primary key: the candidate key chosen to identify rows; unique and not null.
- Alternate key: a candidate key that was not chosen as primary.
- Foreign key: a column that references a key in another (or the same) table, like
manager_id. - Composite key: a key made of two or more columns.
More examples are in the keys in the relational model notes.
30.What is normalization? Explain 1NF, 2NF, 3NF and BCNF.
Normalization organizes tables to reduce redundancy and avoid insert, update and delete anomalies.
- 1NF: every column holds atomic values, no repeating groups.
- 2NF: 1NF, and no non-key column depends on only part of a composite key.
- 3NF: 2NF, and no non-key column depends on another non-key column (no transitive dependency).
- BCNF: for every functional dependency X to Y, X is a super key.
Real systems sometimes denormalize on purpose for read speed. See normalisation explained.
31.What are the ACID properties?
- Atomicity: a transaction happens fully or not at all.
- Consistency: a transaction moves the database from one valid state to another, respecting all constraints.
- Isolation: concurrent transactions do not see each other's partial work (to the degree set by the isolation level).
- Durability: once committed, changes survive a crash.
The classic example is a bank transfer: the debit and credit must both happen or neither. More in what is a transaction.
32.What is an index, and what is the difference between clustered and non-clustered indexes?
An index is a separate structure, usually a B-tree, that lets the database find rows without scanning the whole table. It speeds up reads on the indexed columns but costs storage and slows down writes.
- Clustered index: the table's rows are stored in the index order, so a table has at most one. MySQL InnoDB clusters on the primary key.
- Non-clustered index: a separate structure holding key values and pointers to the rows; a table can have many.
PostgreSQL has no automatically maintained clustered index. Details in the SQL indexes notes.
33.What are transaction isolation levels?
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible (per the standard) |
| Serializable | Prevented | Prevented | Prevented |
Defaults differ: PostgreSQL, SQL Server and Oracle default to Read Committed, while MySQL InnoDB defaults to Repeatable Read. Real engines often prevent more than the standard requires.
34.What is a view, and how is it different from a table?
A view is a saved query that behaves like a virtual table; it stores no data of its own and is recomputed when queried. Views simplify complex queries and can hide columns for security. A materialized view (PostgreSQL, Oracle) does store results and must be refreshed.
35.What is the difference between DBMS and RDBMS?
A DBMS is any software that stores and manages data. An RDBMS is a DBMS based on the relational model: data in tables with rows and columns, relationships through keys, and SQL as the query language. MySQL, PostgreSQL, SQL Server and Oracle are RDBMSs.
Put this guide to work
Build a one-page resume, check it against a real job description, then practice your answers out loud.
Practice next
- SQL jobsOpen roles that ask for SQL, updated daily.
- DBMS notesKeys, normalization, transactions, indexes and SQL in one place.
- AI mock interviewPractice explaining your SQL answers out loud.
- Interview questions for freshersEvery question bank for freshers in one hub.
- Technical interview questions for freshersDSA, OOP, OS and networking questions for the technical round.
- Browse jobsFresher and entry level tech jobs in India, the US, the UK and remote.
Frequently asked questions
Which SQL topics are most important for freshers?
Joins, GROUP BY with HAVING, NULL handling, subqueries and basic window functions like ROW_NUMBER and DENSE_RANK. Add the classic query problems such as second highest salary and finding duplicates.
Which database should I practice SQL on?
MySQL or PostgreSQL are both free and widely used. The core SQL is the same; learn the few dialect differences such as LIMIT versus TOP versus FETCH FIRST.
Are DBMS interview questions asked along with SQL?
Usually yes, especially in campus placements. Expect keys, normalization up to BCNF, ACID properties, indexes and transaction isolation levels.
How should I answer SQL query interview questions in a live round?
Restate the expected output, write the query step by step, and say how it handles ties and NULLs. Mention an alternative approach if one exists, such as a subquery versus a window function.
Do I need to know window functions as a fresher?
Yes, at least ROW_NUMBER, RANK, DENSE_RANK and aggregates with OVER and PARTITION BY. They solve the Nth highest salary and top N per group problems cleanly and come up often.