SQL Queries Asked in Java Interviews - 15 Must Know Examples

SQL is a non-negotiable skill in Java interviews - whether you are applying for a backend developer role, a full-stack position, or a senior engineer spot. Interviewers consistently test SQL alongside Java because most real-world Java applications talk to a relational database, and knowing how to write efficient queries is just as important as knowing how to write clean Java code.

This guide covers the SQL questions and query problems most commonly asked in Java developer interviews - from basic queries that appear in fresher rounds to complex window functions and optimisation questions that come up at the senior level. Every query is explained with real table examples and the reasoning behind each approach.

The Tables We Will Use Throughout

All examples in this guide use these four tables - a simple but realistic employee management schema:

-- employees table
CREATE TABLE employees (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100),
    department VARCHAR(50),
    salary     DECIMAL(10, 2),
    manager_id INT,           -- references employees.id (self-join)
    join_date  DATE,
    is_active  TINYINT(1) DEFAULT 1
);

-- departments table
CREATE TABLE departments (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50),
    city    VARCHAR(50),
    budget  DECIMAL(15, 2)
);

-- projects table
CREATE TABLE projects (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100),
    start_date  DATE,
    end_date    DATE,
    department  VARCHAR(50)
);

-- employee_projects pivot table
CREATE TABLE employee_projects (
    employee_id INT,
    project_id  INT,
    role        VARCHAR(50),
    PRIMARY KEY (employee_id, project_id)
);

-- Sample data overview:
-- employees: 50 rows across Engineering, Marketing, Sales, HR departments
-- Some employees have no manager (top-level), some share the same manager
-- Some employees have no projects assigned

1. Basic Queries — Always Asked in Any Round

Get all employees with salary greater than a value

-- Get all employees earning more than 50,000
SELECT id, name, department, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Get employees from a specific department

-- Single department
SELECT name, salary
FROM employees
WHERE department = 'Engineering';

-- Multiple departments using IN
SELECT name, department, salary
FROM employees
WHERE department IN ('Engineering', 'Marketing')
ORDER BY department, salary DESC;

Count employees per department

SELECT
    department,
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
WHERE is_active = 1
GROUP BY department
ORDER BY total_employees DESC;

Get employees who joined in a date range

-- Joined in 2024
SELECT name, department, join_date
FROM employees
WHERE join_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY join_date;

-- Joined in the last 90 days
SELECT name, join_date
FROM employees
WHERE join_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);

2. Second Highest Salary — The Most Asked Query

This single query appears in almost every Java developer interview. Know all three approaches:

-- Method 1: Using DENSE_RANK() — cleanest and most readable
SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
    WHERE is_active = 1
) ranked
WHERE rnk = 2;

-- Method 2: Using subquery — classic approach
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: LIMIT with OFFSET — works in MySQL
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- OFFSET 1 skips the highest, returns next

-- For Nth highest salary — generalised version
-- Replace 2 with N in DENSE_RANK approach
SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3; -- third highest

3. Find Duplicate Records

-- Find duplicate names in employees table
SELECT name, COUNT(*) AS count
FROM employees
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY count DESC;

-- Find the actual duplicate rows with all details
SELECT *
FROM employees
WHERE name IN (
    SELECT name
    FROM employees
    GROUP BY name
    HAVING COUNT(*) > 1
)
ORDER BY name;

-- Find employees with duplicate salary (same salary, different person)
SELECT salary, COUNT(*) AS count
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

4. Self Join — Manager and Employee

Self joins are regularly asked when the table has a reference to itself — like an employees table where manager_id references another employee's id.

-- Get each employee with their manager's name
SELECT
    e.name        AS employee,
    e.department,
    e.salary,
    m.name        AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.department, e.name;

-- LEFT JOIN used so employees with no manager (CEO/top level) are included
-- INNER JOIN would exclude them

-- Get all employees managed by a specific manager
SELECT e.name, e.salary, e.department
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE m.name = 'Randhir Kumar';

-- Get managers and how many people they manage
SELECT
    m.name AS manager,
    COUNT(e.id) AS team_size
FROM employees e
JOIN employees m ON m.id = e.manager_id
GROUP BY m.id, m.name
ORDER BY team_size DESC;

5. Employees Who Earn More Than Their Manager

A very commonly asked query that tests your understanding of self joins:

-- Find employees who earn more than their own manager
SELECT
    e.name       AS employee,
    e.salary     AS employee_salary,
    m.name       AS manager,
    m.salary     AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.salary DESC;

6. Department Wise Highest Paid Employee

-- Method 1: Using window function — most efficient
SELECT name, department, salary
FROM (
    SELECT
        name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
    WHERE is_active = 1
) ranked
WHERE rnk = 1;

-- Method 2: Using subquery with GROUP BY
SELECT e.name, e.department, e.salary
FROM employees e
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
) dept_max ON dept_max.department = e.department
          AND dept_max.max_salary  = e.salary
ORDER BY e.department;

-- Note: if two employees tie for highest in a department
-- Method 1 returns both (RANK gives same rank to ties)
-- Method 2 also returns both via the JOIN

7. Employees With No Projects Assigned

Tests your understanding of LEFT JOIN with NULL check vs NOT IN vs NOT EXISTS:

-- Method 1: LEFT JOIN with NULL check — most common and readable
SELECT e.id, e.name, e.department
FROM employees e
LEFT JOIN employee_projects ep ON ep.employee_id = e.id
WHERE ep.employee_id IS NULL;

-- Method 2: NOT EXISTS — often fastest on large tables
SELECT id, name, department
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employee_projects ep
    WHERE ep.employee_id = e.id
);

-- Method 3: NOT IN — avoid on large datasets, slow with NULL values
SELECT id, name, department
FROM employees
WHERE id NOT IN (
    SELECT DISTINCT employee_id FROM employee_projects
);
-- ⚠️ If employee_projects.employee_id has any NULL values
-- NOT IN returns no rows — use NOT EXISTS instead

8. Rolling Total and Running Average

-- Running total of salaries ordered by join date
SELECT
    name,
    join_date,
    salary,
    SUM(salary) OVER (ORDER BY join_date) AS running_total,
    AVG(salary) OVER (ORDER BY join_date) AS running_avg,
    COUNT(*)    OVER (ORDER BY join_date) AS employee_count
FROM employees
WHERE is_active = 1
ORDER BY join_date;

-- Running total per department (resets for each department)
SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_running_total
FROM employees
ORDER BY department, salary DESC;

9. Find Employees Hired in the Same Month

-- Employees who joined in the same month and year as each other
SELECT
    e1.name      AS employee1,
    e2.name      AS employee2,
    e1.join_date AS joined_on
FROM employees e1
JOIN employees e2
    ON  MONTH(e1.join_date) = MONTH(e2.join_date)
    AND YEAR(e1.join_date)  = YEAR(e2.join_date)
    AND e1.id < e2.id -- avoid duplicates and self-match
ORDER BY e1.join_date;

-- Count of new hires per month
SELECT
    DATE_FORMAT(join_date, '%Y-%m') AS month,
    COUNT(*) AS new_hires
FROM employees
GROUP BY DATE_FORMAT(join_date, '%Y-%m')
ORDER BY month;

10. Cumulative Salary — Top 3 Earners Per Department

-- Top 3 highest paid employees in each department
SELECT name, department, salary, dept_rank
FROM (
    SELECT
        name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS dept_rank
    FROM employees
    WHERE is_active = 1
) ranked
WHERE dept_rank <= 3
ORDER BY department, dept_rank;

11. Delete Duplicate Rows — Keep One

-- Find which IDs to delete (keep the lowest ID for each duplicate name)
SELECT id, name
FROM employees
WHERE id NOT IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY name
);

-- Delete duplicates — keep row with lowest ID for each name
DELETE FROM employees
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) AS min_id
        FROM employees
        GROUP BY name
    ) AS to_keep
);

-- Verify — should return 0 rows after deletion
SELECT name, COUNT(*) AS count
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

12. Find Gaps in Sequential IDs

Occasionally asked to test window function knowledge:

-- Find missing IDs in a sequence (gaps in auto-increment)
SELECT
    id + 1 AS gap_start,
    next_id - 1 AS gap_end
FROM (
    SELECT
        id,
        LEAD(id) OVER (ORDER BY id) AS next_id
    FROM employees
) gaps
WHERE next_id - id > 1;

-- Simpler approach — find IDs that don't exist
SELECT seq.id AS missing_id
FROM (
    SELECT (a.id + b.id * 10) AS id
    FROM employees a, employees b
    LIMIT 1000
) seq
LEFT JOIN employees e ON e.id = seq.id
WHERE e.id IS NULL AND seq.id BETWEEN 1 AND (SELECT MAX(id) FROM employees)
ORDER BY missing_id;

13. Pivot — Count Employees Per Department in One Row

-- Vertical (normal GROUP BY result)
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department;

-- Horizontal pivot — each department as a column
SELECT
    SUM(CASE WHEN department = 'Engineering' THEN 1 ELSE 0 END) AS engineering,
    SUM(CASE WHEN department = 'Marketing'   THEN 1 ELSE 0 END) AS marketing,
    SUM(CASE WHEN department = 'Sales'       THEN 1 ELSE 0 END) AS sales,
    SUM(CASE WHEN department = 'HR'          THEN 1 ELSE 0 END) AS hr
FROM employees
WHERE is_active = 1;

14. Common Table Expressions (CTE) — Recursive Example

-- Non-recursive CTE — clean way to break complex queries into steps
WITH
active_employees AS (
    SELECT id, name, department, salary, manager_id
    FROM employees
    WHERE is_active = 1
),
dept_stats AS (
    SELECT department,
           AVG(salary) AS avg_salary,
           COUNT(*)    AS headcount
    FROM active_employees
    GROUP BY department
)
SELECT
    e.name,
    e.department,
    e.salary,
    d.avg_salary,
    ROUND(e.salary - d.avg_salary, 2) AS diff_from_avg
FROM active_employees e
JOIN dept_stats d ON d.department = e.department
ORDER BY diff_from_avg DESC;

-- Recursive CTE — traverse employee hierarchy (manager tree)
WITH RECURSIVE org_chart AS (
    -- Base case: top-level employees (no manager)
    SELECT id, name, manager_id, 1 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees who have a manager in the result set
    SELECT e.id, e.name, e.manager_id, oc.level + 1, CONCAT(oc.path, ' → ', e.name)
    FROM employees e
    JOIN org_chart oc ON oc.id = e.manager_id
)
SELECT id, name, level, path
FROM org_chart
ORDER BY path;

15. Performance Query — Using EXPLAIN

Senior Java interviews often include questions about query optimisation. Knowing how to use EXPLAIN shows production readiness:

-- Always EXPLAIN before optimising
EXPLAIN SELECT e.name, e.salary
FROM employees e
WHERE e.department = 'Engineering'
  AND e.salary > 60000;

-- Look for:
-- type = ALL   → full table scan, bad for large tables
-- type = ref   → index used, good
-- key  = NULL  → no index used
-- rows = high  → scanning too many rows

-- Add an index to fix it
CREATE INDEX idx_employees_dept_salary
ON employees (department, salary);

-- Run EXPLAIN again — should now show type=range or ref

-- Another common pattern — avoid functions on indexed columns
-- ❌ Bad — function on column defeats the index
WHERE YEAR(join_date) = 2024

-- ✅ Good — range condition uses index
WHERE join_date >= '2024-01-01'
  AND join_date  < '2025-01-01'

Quick Reference — Window Functions Cheat Sheet

Most Common SQL Interview Mistakes Java Developers Make

• Using NOT IN when the subquery can return NULLs — always use NOT EXISTS as a safe alternative. This is a production bug waiting to happen.
• Wrapping indexed columns in functions — any function applied to an indexed column in a WHERE clause prevents index usage and causes a full table scan.
• Forgetting DISTINCT in NOT IN subqueries — without DISTINCT, the subquery may return the same value many times, making the query slower than needed.
• Using HAVING without GROUP BY — HAVING without GROUP BY treats the entire result as one group. Usually a mistake.
• Not knowing the difference between WHERE and HAVING — WHERE filters rows before grouping. HAVING filters groups after grouping. You cannot use aggregate functions in WHERE.
• Selecting all columns with SELECT * — in interviews and in production, always select only the columns you need. It shows awareness of performance and makes queries more maintainable.

Final Thought

SQL in Java interviews rewards preparation more than most topics. The same queries appear again and again — second highest salary, duplicates, self joins, employees with no projects, top N per group. Practise writing these from memory until they feel natural.

More importantly, understand the reasoning behind each query — why NOT EXISTS is safer than NOT IN, why window functions are cleaner than correlated subqueries, why functions on indexed columns kill performance. That understanding is what makes you able to adapt when the interviewer changes the question slightly.

For more interview preparation check out our related guides:

• → Java Interview Questions for 6+ Years Experience
• → SQL Interview Questions for Senior Developers
• → Array Coding Interview Problems
• → String Coding Interview Problems