SQL Interview Queries - Java Backend Developer

1. Basic SQL Queries — 3 to 5 Years

Select

SELECT * FROM employee;

SELECT emp_id, emp_name, salary
FROM employee;

WHERE

SELECT * FROM employee WHERE salary > 80000;

SELECT * FROM employee WHERE department = 'IT';

SELECT * FROM employee
WHERE salary BETWEEN 70000 AND 100000;

LIKE

SELECT * FROM employee
WHERE emp_name LIKE 'J%';

LIKE: J% starts with J, %J ends with J, %J% contains J, _ohn means one character followed by ohn.

ORDER BY

SELECT * FROM employee
ORDER BY salary DESC, emp_name ASC;

Aggregate Functions

SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT COUNT(*) FROM employee;

GROUP BY

SELECT department, COUNT(*) AS employee_count
FROM employee
GROUP BY department;

HAVING

SELECT department, COUNT(*) AS employee_count
FROM employee
GROUP BY department
HAVING COUNT(*) > 5;

WHERE filters rows; HAVING filters groups after GROUP BY.

DISTINCT

SELECT DISTINCT department FROM employee;

2. Important Salary Queries

Second Highest Salary

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Nth Highest Salary

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
) x
WHERE rnk = 5;

Highest Salary Per Department

SELECT *
FROM (
    SELECT e.*,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rnk
    FROM employee e
) x
WHERE rnk = 1;

Second Highest Salary Per Department

SELECT *
FROM (
    SELECT e.*,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rnk
    FROM employee e
) x
WHERE rnk = 2;

Employees Above Average Salary

SELECT *
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

Employees Above Department Average

SELECT e.*
FROM employee e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employee e2
    WHERE e2.department = e.department
);

3. JOIN Queries

INNER JOIN

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employee e
INNER JOIN department d
ON e.dept_id = d.dept_id;

LEFT JOIN

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d
ON e.dept_id = d.dept_id;

Employees Without Department

SELECT e.*
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

Departments Without Employees

SELECT d.*
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

Employee and Manager — Self Join

SELECT e.emp_name AS employee,
       m.emp_name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.emp_id;

Employee Earning More Than Manager

SELECT e.emp_name AS employee,
       e.salary AS employee_salary,
       m.emp_name AS manager,
       m.salary AS manager_salary
FROM employee e
JOIN employee m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

4. Subqueries

Top 3 Employees Per Department

SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employee e
) x
WHERE rn <= 3;

Department With Highest Average Salary

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

Highest Salary Without MAX()

SELECT salary
FROM employee e1
WHERE NOT EXISTS (
    SELECT 1
    FROM employee e2
    WHERE e2.salary > e1.salary
);

5. Duplicate Records

Duplicate Emails

SELECT email, COUNT(*) AS count
FROM employee
GROUP BY email
HAVING COUNT(*) > 1;

Duplicates Across Multiple Columns

SELECT emp_name, department, salary, COUNT(*) AS count
FROM employee
GROUP BY emp_name, department, salary
HAVING COUNT(*) > 1;

Delete Duplicates Using ROW_NUMBER

DELETE FROM employee
WHERE emp_id IN (
    SELECT emp_id
    FROM (
        SELECT emp_id,
               ROW_NUMBER() OVER (
                   PARTITION BY email
                   ORDER BY emp_id
               ) AS rn
        FROM employee
    ) x
    WHERE rn > 1
);

Always verify the rows with SELECT before running a production DELETE. Exact syntax can vary by database.

6. Window Functions

ROW_NUMBER, RANK and DENSE_RANK

SELECT emp_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employee;

Salary	ROW_NUMBER	RANK	DENSE_RANK
100000	1	1	1
100000	2	1	1
90000	3	3	2
80000	4	4	3

Running Total

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

LAG and LEAD

SELECT customer_id, order_date, amount,
       LAG(amount) OVER (
           PARTITION BY customer_id ORDER BY order_date
       ) AS previous_amount,
       LEAD(amount) OVER (
           PARTITION BY customer_id ORDER BY order_date
       ) AS next_amount
FROM orders;

Latest Order Per Customer

SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS rn
    FROM orders o
) x
WHERE rn = 1;

7. CTE and Recursive CTE

WITH department_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY department
)
SELECT e.*
FROM employee e
JOIN department_avg d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

Recursive Employee Hierarchy

WITH RECURSIVE employee_hierarchy AS (
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employee
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.emp_id, e.emp_name, e.manager_id,
           eh.level + 1
    FROM employee e
    JOIN employee_hierarchy eh
    ON e.manager_id = eh.emp_id
)
SELECT * FROM employee_hierarchy;

8. Date Queries

Last 30 Days — MySQL

SELECT *
FROM employee
WHERE joining_date >=
      CURRENT_DATE - INTERVAL 30 DAY;

This Year — MySQL

SELECT *
FROM employee
WHERE joining_date >= DATE_FORMAT(CURRENT_DATE, '%Y-01-01')
AND joining_date < DATE_FORMAT(
    CURRENT_DATE + INTERVAL 1 YEAR, '%Y-01-01'
);

Monthly Sales

SELECT YEAR(order_date) AS year,
       MONTH(order_date) AS month,
       SUM(amount) AS total_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

For indexed date columns, avoid unnecessary functions in WHERE predicates when possible.

9. EXISTS / NOT EXISTS / IN

EXISTS

SELECT *
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS

SELECT *
FROM customer c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

IN

SELECT *
FROM customer
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

Do not say EXISTS is always faster than IN. Actual performance depends on the database, optimizer, indexes, data distribution and query shape.

10. INSERT, UPDATE, DELETE and UPSERT

INSERT INTO SELECT

INSERT INTO employee_backup
SELECT *
FROM employee
WHERE department = 'IT';

UPDATE Using Another Table — MySQL

UPDATE employee e
JOIN department d ON e.dept_id = d.dept_id
SET e.salary = e.salary * 1.10
WHERE d.dept_name = 'IT';

Delete Old Records — MySQL

DELETE FROM employee
WHERE joining_date <
      CURRENT_DATE - INTERVAL 5 YEAR;

PostgreSQL UPSERT

INSERT INTO employee(emp_id, emp_name, salary)
VALUES (101, 'John', 90000)
ON CONFLICT (emp_id)
DO UPDATE SET salary = EXCLUDED.salary;

MySQL UPSERT

INSERT INTO employee(emp_id, emp_name, salary)
VALUES (101, 'John', 90000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

11. Indexing

Simple Index

CREATE INDEX idx_employee_email
ON employee(email);

Composite Index

CREATE INDEX idx_emp_dept_salary
ON employee(department, salary);

Index order should be selected from actual predicates, selectivity, ordering requirements, workload and execution plans.

Covering Index

SELECT emp_id, salary
FROM employee
WHERE department = 'IT';

CREATE INDEX idx_emp_dept_empid_salary
ON employee(department, emp_id, salary);

A covering index contains the columns needed by a query and may allow index-only access, depending on the database.

12. EXPLAIN and Query Optimization

EXPLAIN
SELECT *
FROM employee
WHERE department = 'IT';

MySQL EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT *
FROM employee
WHERE department = 'IT';

Production Slow Query Investigation

Slow Query
 ↓
Execution Plan
 ↓
Indexes
 ↓
Row Counts / Data Distribution
 ↓
Joins / Filters
 ↓
Sorting / Grouping
 ↓
Locks / Concurrency
 ↓
Optimize
 ↓
Measure Again

Avoid SELECT *

SELECT emp_id, emp_name, salary
FROM employee;

13. Pagination

OFFSET Pagination

SELECT *
FROM employee
ORDER BY emp_id
LIMIT 20 OFFSET 1000;

Keyset / Cursor Pagination

SELECT *
FROM employee
WHERE emp_id > 100000
ORDER BY emp_id
LIMIT 20;

Keyset pagination is often preferable for very large datasets because it avoids repeatedly skipping large numbers of earlier rows.

14. Transactions and ACID

BEGIN;

UPDATE account
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE account
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

ROLLBACK;

ACID	Meaning
Atomicity	Operations succeed together or are rolled back according to transaction semantics.
Consistency	Database constraints and invariants are preserved.
Isolation	Concurrent transactions are isolated according to the selected isolation level.
Durability	Committed changes survive failures according to database guarantees.

15. Transaction Isolation Levels

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Common Anomalies

Dirty ReadNon-Repeatable ReadPhantom Read

Exact behavior depends on the database engine and implementation.

16. Deadlocks and Locking

Transaction T1:
locks A → waits for B

Transaction T2:
locks B → waits for A

Result: Deadlock

Possible Mitigations

Consistent lock ordering
Short transactions
Avoid unnecessary locks
Appropriate indexes
Retry handling where appropriate
Analyze database deadlock reports

Optimistic Locking

UPDATE account
SET balance = 9000, version = version + 1
WHERE id = 101 AND version = 5;

Pessimistic Locking

SELECT *
FROM account
WHERE id = 101
FOR UPDATE;

17. Java + Spring Data JPA + Hibernate + SQL

Java
 ↓
Spring Boot
 ↓
Spring Data JPA
 ↓
Hibernate
 ↓
SQL
 ↓
Database

N+1 Query Problem

List<Order> orders =
    orderRepository.findAll();

for (Order order : orders) {
    order.getCustomer().getName();
}

With lazy relationships, this can conceptually become 1 query for orders plus N queries for customers.

Fetch Join

@Query("SELECT o FROM Order o JOIN FETCH o.customer")
List<Order> findOrdersWithCustomers();

Find Orphan Orders

SELECT o.*
FROM orders o
LEFT JOIN customer c
ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

18. Top 20 SQL Queries / Concepts to Prepare

#	Topic	Importance
1	Second highest salary	★★★★★
2	Nth highest salary	★★★★★
3	Highest salary per department	★★★★★
4	Second highest salary per department	★★★★★
5	Top 3 employees per department	★★★★★
6	Duplicate records	★★★★★
7	Employees without department	★★★★☆
8	Customers without orders	★★★★★
9	Employee-manager self join	★★★★★
10	Employees earning more than manager	★★★★★
11	Employees above department average	★★★★★
12	ROW_NUMBER()	★★★★★
13	RANK() vs DENSE_RANK()	★★★★★
14	LAG() / LEAD()	★★★★☆
15	Running total	★★★★☆
16	CTE	★★★★☆
17	EXISTS vs IN	★★★★★
18	Pagination / Keyset pagination	★★★★★
19	EXPLAIN / Indexing	★★★★★
20	Transactions / Isolation / Deadlocks	★★★★★

19. Preparation by Experience

3–5 Years

SELECT, WHERE, ORDER BY
GROUP BY, HAVING
DISTINCT, LIKE, BETWEEN, IN
NULL, CASE, COALESCE
Aggregate functions
INNER/LEFT/SELF JOIN
Subqueries
Second highest salary
Duplicate records

5–8 Years

Correlated subqueries
EXISTS / NOT EXISTS
CTEs
Window functions
ROW_NUMBER, RANK, DENSE_RANK
LAG, LEAD
Top N per group
Running totals
Date queries
Conditional aggregation
UPSERT

8–12 Years

Execution plans and EXPLAIN
Index design
Composite and covering indexes
Query optimization
Pagination
Transactions and ACID
Isolation levels
Deadlocks and locking
Optimistic/pessimistic locking
N+1 query problem

12–15 Years / Senior / Architect

Large-table query optimization
Production slow-query troubleshooting
Deadlock investigation
Concurrent update strategies
Large-scale pagination
Zero-downtime database migrations
Safe production index creation
Connection pool/database saturation
Partitioning concepts
Read replicas and consistency
Database CPU/I/O troubleshooting
JPA/Hibernate query performance

20. Recommended SQL Interview Study Order

1. SELECT / WHERE
 ↓
2. GROUP BY / HAVING
 ↓
3. JOINs
 ↓
4. Subqueries
 ↓
5. Self JOIN
 ↓
6. Aggregate Functions
 ↓
7. Second / Nth Highest Salary
 ↓
8. Duplicate Records
 ↓
9. Window Functions
 ↓
10. CTE
 ↓
11. EXISTS / NOT EXISTS
 ↓
12. Date Queries
 ↓
13. Transactions / ACID
 ↓
14. Isolation Levels
 ↓
15. Locks / Deadlocks
 ↓
16. Indexes
 ↓
17. EXPLAIN / Execution Plans
 ↓
18. Query Optimization
 ↓
19. Pagination
 ↓
20. JPA / Hibernate + SQL
 ↓
21. Production Database Troubleshooting

Senior Java Backend Focus: Be ready to explain not only the query, but why it is correct, how it may execute, which indexes may help, how concurrency affects it, and how you would troubleshoot it in production.