Skip to content

Top 10 SQL Interview Questions and Answers

These are some of the most frequently asked SQL interview questions with explanations and examples.

Table Setup Scripts

Employee Table

CREATE TABLE emp (
  emp_id INT,
  emp_name VARCHAR(20),
  department_id INT,
  salary INT,
  manager_id INT,
  emp_age INT
);

INSERT INTO emp VALUES (1, 'John', 100, 10000, 4, 39);
INSERT INTO emp VALUES (2, 'Michael', 100, 15000, 5, 48);
INSERT INTO emp VALUES (3, 'William', 100, 10000, 4, 37);
INSERT INTO emp VALUES (4, 'James', 100, 5000, 2, 16);
INSERT INTO emp VALUES (5, 'Oliver', 200, 12000, 6, 55);
INSERT INTO emp VALUES (6, 'George', 200, 12000, 2, 14);
INSERT INTO emp VALUES (7, 'Harry', 200, 9000, 2, 13);
INSERT INTO emp VALUES (8, 'Charlie', 200, 5000, 2, 12);
INSERT INTO emp VALUES (9, 'Thomas', 300, 6000, 6, 51);
INSERT INTO emp VALUES (10, 'Edward', 300, 7000, 6, 50);

Department Table

CREATE TABLE department (
  dept_id INT,
  dept_name VARCHAR(10)
);

INSERT INTO department VALUES (100, 'Analytics');
INSERT INTO department VALUES (300, 'IT');

Orders Table

CREATE TABLE orders (
  customer_name CHAR(10),
  order_date DATE,
  order_amount INT,
  customer_gender CHAR(6)
);

INSERT INTO orders VALUES ('Emily', '2020-01-01', 10000, 'Female');
INSERT INTO orders VALUES ('Daniel', '2020-01-02', 12000, 'Male');
INSERT INTO orders VALUES ('Sophia', '2020-01-02', 12000, 'Female');
INSERT INTO orders VALUES ('Henry', '2020-01-03', 15000, 'Male');
INSERT INTO orders VALUES ('Olivia', '2020-01-03', 14000, 'Female');

Q1 — How to Find Duplicates in a Table?

SELECT
    emp_id,
    COUNT(*)
FROM emp
GROUP BY emp_id
HAVING COUNT(*) > 1;

Explanation

  • GROUP BY emp_id groups rows based on employee ID.
  • COUNT(*) counts how many times each employee ID appears.
  • HAVING COUNT(*) > 1 filters only duplicate employee IDs.

This query identifies duplicate records in the table.

Q2 — How to Delete Duplicates?

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY emp_id ORDER BY emp_id) AS rn
    FROM emp
)
DELETE FROM cte
WHERE rn > 1;

Explanation

  • ROW_NUMBER() assigns sequential numbers to rows within each emp_id group.
  • The first occurrence gets rn = 1.
  • Duplicate rows get values greater than 1.
  • The DELETE statement removes duplicate rows.

Always take a backup before running delete operations in production.

Q3 — Difference Between UNION and UNION ALL?

UNION

SELECT * FROM emp
UNION
SELECT * FROM emp;

Key Points

  • Removes duplicate rows.
  • Returns only distinct records.
  • Slightly slower because duplicate elimination requires sorting/hash operations.

UNION ALL

SELECT * FROM emp
UNION ALL
SELECT * FROM emp;

Key Points

  • Keeps duplicate rows.
  • Faster than UNION.
  • Used when duplicates are acceptable or required.

Q4 — Difference Between RANK(), ROW_NUMBER(), and DENSE_RANK()?

RANK()

SELECT
    emp_id,
    emp_name,
    salary,
    RANK() OVER(ORDER BY salary DESC) AS rnk
FROM emp;

Behavior

  • Same rank for tied values.
  • Skips ranks after ties.

Example:

Salary Rank
15000 1
12000 2
12000 2
10000 4

ROW_NUMBER()

SELECT
    emp_id,
    emp_name,
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn
FROM emp;

Behavior

  • Always generates unique row numbers.
  • No duplicate rankings.

DENSE_RANK()

SELECT
    emp_id,
    emp_name,
    salary,
    DENSE_RANK() OVER(ORDER BY salary DESC) AS dr
FROM emp;

Behavior

  • Same rank for tied values.
  • Does not skip ranks.

Example:

Salary Dense Rank
15000 1
12000 2
12000 2
10000 3

Q5 — Employees Not Present in Department Table

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

Explanation

  • LEFT JOIN keeps all employees.
  • Matching department rows are added if available.
  • WHERE d.dept_id IS NULL filters employees without departments.

Q6 — Second Highest Salary in Each Department

SELECT *
FROM (
    SELECT
        emp_name,
        salary,
        department_id,
        DENSE_RANK() OVER(
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rn
    FROM emp
) a
WHERE rn = 2;

Explanation

  • PARTITION BY department_id separates employees department-wise.
  • DENSE_RANK() ranks salaries within each department.
  • WHERE rn = 2 fetches second highest salary records.

Q7 — Find All Transactions Done by Customer Named Sophia

SELECT *
FROM orders
WHERE UPPER(customer_name) = 'SOPHIA';

Explanation

  • UPPER() converts values to uppercase.
  • Enables case-insensitive matching.
  • Useful when database values contain mixed casing.

Q8 — Self Join: Manager Salary Greater Than Employee Salary

SELECT
    e1.emp_id,
    e1.emp_name,
    e2.emp_name AS manager_name,
    e1.salary,
    e2.salary AS manager_salary
FROM emp e1
INNER JOIN emp e2
    ON e1.manager_id = e2.emp_id
WHERE e2.salary > e1.salary;

Explanation

  • A self join joins the same table with itself.
  • e1 represents employees.
  • e2 represents managers.
  • The query finds employees whose managers earn more.

Q9 — Different Types of Joins

INNER JOIN

Returns only matching records from both tables.

SELECT *
FROM emp e
INNER JOIN department d
ON e.department_id = d.dept_id;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table.

SELECT *
FROM emp e
LEFT JOIN department d
ON e.department_id = d.dept_id;

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table.

SELECT *
FROM emp e
RIGHT JOIN department d
ON e.department_id = d.dept_id;

FULL OUTER JOIN

Returns all matching and non-matching rows from both tables.

SELECT *
FROM emp e
FULL OUTER JOIN department d
ON e.department_id = d.dept_id;

CROSS JOIN

Returns Cartesian product of both tables.

SELECT *
FROM emp
CROSS JOIN department;

Q10 — Update Query to Swap Gender

UPDATE orders
SET customer_gender = CASE
    WHEN customer_gender = 'Male' THEN 'Female'
    WHEN customer_gender = 'Female' THEN 'Male'
END;

Explanation

  • CASE applies conditional logic.
  • Male becomes Female.
  • Female becomes Male.
  • Other values remain unchanged.

Additional Interview Tips

Common Follow-Up Questions

  • Difference between WHERE and HAVING
  • Primary Key vs Unique Key
  • Clustered vs Non-Clustered Index
  • Correlated vs Non-Correlated Subquery
  • CTE vs Temporary Table
  • DELETE vs TRUNCATE vs DROP
  • Normalization levels
  • ACID properties
  • Window functions vs aggregate functions

Performance Optimization Basics

  • Use indexes carefully.
  • Avoid SELECT * in production.
  • Filter early using WHERE.
  • Use joins efficiently.
  • Analyze execution plans.
  • Partition large tables when needed.