SQL interview questions for Java developers
15 min readUpdated
Java backend interviews almost always include SQL: a few concept questions and one or two queries to write. Every query here runs on the small employees and departments tables below, with its result shown underneath.
The sample tables
Create these two tables to try every query yourself. Sara has no department and HR has no employees, which makes the join questions interesting.
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT REFERENCES departments(id),
salary INT NOT NULL
);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
INSERT INTO employees VALUES
(1, 'Asha', 1, 90000),
(2, 'Ravi', 1, 75000),
(3, 'Meera', 2, 60000),
(4, 'John', 2, 60000),
(5, 'Sara', NULL, 50000);SQL basics
1. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates such as COUNT(*) > 1.
2. In what order is a SELECT statement executed?
FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That is why a column alias from SELECT cannot be used in WHERE.
3. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes chosen rows (with WHERE) and can be rolled back. TRUNCATE removes all rows quickly and resets auto-increment. DROP removes the whole table, structure included.
4. What is the difference between UNION and UNION ALL?
UNION combines results and removes duplicates (extra work). UNION ALL keeps every row and is faster; use it when duplicates are impossible or wanted.
5. How does NULL behave in SQL?
NULL means unknown, so salary = NULL is never true; use IS NULL. Aggregates such as AVG and COUNT(column) skip NULLs, while COUNT(*) counts every row. COALESCE(x, 0) replaces NULL with a default.
Keys and design
6. What is the difference between a primary key and a unique key?
Both reject duplicates. A table has one primary key, which can never be NULL and identifies each row. It can have many unique constraints, which in most databases allow NULL.
7. What is a foreign key?
A column that must match a primary key in another table, such as employees.dept_id → departments.id. The database then refuses orphan rows, and ON DELETE rules decide what happens when the parent row is deleted.
8. What is normalisation?
Organising tables so each fact is stored once. 1NF: one value per cell. 2NF: no column depends on only part of a composite key. 3NF: no column depends on another non-key column. It prevents update anomalies.
9. When would you denormalise?
When reads are far more frequent than writes and joins become the bottleneck, such as reporting tables or a stored order total. You accept duplicated data and must keep it in sync.
10. What is an index, and what does it cost?
A sorted structure (usually a B-tree) that lets the database find rows without scanning the table, like a book index. It speeds up WHERE, JOIN and ORDER BY on its columns but slows down inserts and updates and uses space.
11. Why might an index not be used?
A function on the column (WHERE YEAR(created_at) = 2026), a leading wildcard (LIKE '%son'), a type mismatch, or a query that returns most of the table. Check with EXPLAIN.
Joins
12. What types of joins are there?
INNER JOIN keeps matching rows only. LEFT JOIN keeps every row of the left table, with NULLs where there is no match; RIGHT JOIN does the same for the right table. FULL OUTER JOIN keeps both sides. CROSS JOIN pairs every row with every row.
13. List each employee with their department, including employees without one.
A LEFT JOIN from employees keeps Sara, who has no department, with NULL as the department name.
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;
-- Asha | Engineering
-- Ravi | Engineering
-- Meera | Sales
-- John | Sales
-- Sara | NULL14. Find departments that have no employees.
Join and keep the rows where the employee side is missing, or use NOT EXISTS. Both return HR here.
SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;
-- HR15. What is a self join?
A table joined to itself with two aliases, for example employees with a manager_id column joined to the same table to show each employee next to their manager.
Classic query questions
16. Find the second highest salary.
Take the highest salary below the maximum, or use DISTINCT with LIMIT/OFFSET. Here the answer is 75000.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- 75000
-- or
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;17. Find the Nth highest salary per department.
Use a window function: DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) in a subquery or CTE, then keep the rows where the rank equals N.
WITH ranked AS (
SELECT name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE dept_id IS NOT NULL
)
SELECT dept_id, name, salary
FROM ranked
WHERE rnk = 1
ORDER BY dept_id, name;
-- 1 | Asha | 90000
-- 2 | John | 60000
-- 2 | Meera | 6000018. What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
For salaries 90, 75, 75, 60: ROW_NUMBER gives 1, 2, 3, 4; RANK gives 1, 2, 2, 4 (gap after a tie); DENSE_RANK gives 1, 2, 2, 3 (no gap).
19. Find duplicate values in a column.
Group by the column and keep groups with more than one row. Here two employees earn 60000.
SELECT salary, COUNT(*) AS how_many
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;
-- 60000 | 220. Show the number of employees and the average salary per department.
Join, group by department and aggregate. A LEFT JOIN from departments would also show HR with zero employees.
SELECT d.name, COUNT(e.id) AS employees, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY d.name;
-- Engineering | 2 | 82500
-- Sales | 2 | 6000021. What is a subquery versus a CTE?
Both are queries inside a query. A CTE (WITH name AS (...)) gives the subquery a name at the top, which makes long queries readable and lets you use the result more than once.
Transactions and Java
22. What does ACID mean?
Atomicity (all or nothing), Consistency (rules always hold), Isolation (concurrent transactions do not see each other's half-done work) and Durability (committed data survives a crash).
23. What are the isolation levels?
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (MySQL's default) and SERIALIZABLE. Higher levels prevent more anomalies (dirty reads, non-repeatable reads, phantom reads) but allow less concurrency.
24. What is SQL injection and how do you prevent it in Java?
Building SQL by joining user input into the string lets an attacker change the query (' OR '1'='1). Always use PreparedStatement with ? placeholders, or JPA parameters, so input is sent as data, never as SQL.
String sql = "SELECT id, name FROM employees WHERE name = ?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, nameFromUser); // sent as a value, never as SQL
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt("id") + " " + rs.getString("name"));
}
}
}25. How do you prevent two users from buying the last item at the same time?
Inside one transaction, lock the row with SELECT ... FOR UPDATE, check the stock, then update it; or use optimistic locking with a version column (@Version in JPA) and retry on conflict.
Frequently asked questions
Which SQL queries are asked most in interviews?
Second or Nth highest salary, duplicates with GROUP BY and HAVING, joins including finding rows without a match, counts per group, and top-N per group with window functions.
Which database should I practise SQL on?
MySQL or PostgreSQL. Standard SQL works on both; only a few things differ, such as LIMIT syntax in older databases and some date functions.
Do Java developers need to know SQL if they use JPA?
Yes. JPA writes SQL for you, but you still need to read it, find slow queries, design tables and indexes, and write the queries JPA cannot express well.
Learn it step by step
Practise this, step by step
Short lessons with quizzes, practice and interview questions, from your first program to job-ready.