SQL Query Interview Questions

2

Beginner

What is the difference between WHERE and HAVING clauses?

Filtering 3 min read

WHERE is used to filter rows before grouping, while HAVING is used to filter groups after the GROUP BY operation. WHERE cannot be used with aggregate functions, but HAVING can.

WHERE Example:

SELECT * FROM employees
WHERE salary > 50000;

HAVING Example:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

Remember:

WHERE filters individual rows
HAVING filters aggregated results
WHERE executes before GROUP BY
HAVING executes after GROUP BY

6

Advanced

Explain the difference between UNION and UNION ALL

Set Operations 4 min read

UNION and UNION ALL are both used to combine results from multiple SELECT queries, but they differ in how they handle duplicates and performance.

UNION Example (removes duplicates):

SELECT employee_name FROM employees_ny
UNION
SELECT employee_name FROM employees_sf;

UNION ALL Example (keeps duplicates):

SELECT employee_name FROM employees_ny
UNION ALL
SELECT employee_name FROM employees_sf;

Key Differences:

UNION: Removes duplicate rows, sorts results (slower)
UNION ALL: Keeps all rows including duplicates, no sorting (faster)
Use UNION when you need distinct results
Use UNION ALL when you need all rows or know there are no duplicates

9

Intermediate

What is the difference between DELETE, TRUNCATE, and DROP?

DML/DDL 4 min read

These three commands are used for removing data, but they work differently in terms of scope, speed, and reversibility.

DELETE Example:

-- Removes specific rows, can be rolled back
DELETE FROM employees WHERE department = 'HR';
-- Removes all rows but table structure remains
DELETE FROM employees;

TRUNCATE Example:

-- Removes all rows, cannot be rolled back, faster than DELETE
TRUNCATE TABLE employees;

DROP Example:

-- Removes entire table including structure
DROP TABLE employees;

Key Differences:

DELETE: DML command, row-by-row removal, can use WHERE, can be rolled back, slower
TRUNCATE: DDL command, removes all rows, faster, resets identity columns, minimal logging
DROP: DDL command, removes table completely including structure, fastest
Use DELETE when you need conditional removal or transaction rollback
Use TRUNCATE when you need to quickly remove all data but keep table structure
Use DROP when you want to remove the table entirely

13

Advanced

What are correlated subqueries and how do they work?

Subqueries 5 min read

A correlated subquery is a subquery that references columns from the outer query. It executes once for each row processed by the outer query.

Correlated Subquery Example:

-- Find employees whose salary is above average in their department
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

Non-Correlated Subquery (for comparison):

-- Find employees earning more than company average (executes once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Key Characteristics:

Correlated: References outer query, executes for each row
Non-Correlated: Independent, executes once
Correlated subqueries can be performance-intensive
Often can be rewritten as JOINs for better performance
Useful for row-by-row comparisons

Correlated subquery as JOIN (better performance):

-- Same query rewritten as JOIN
SELECT e.name, e.department, e.salary
FROM employees e
JOIN (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
) dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;

21

Intermediate

What is the difference between IN and EXISTS operators?

Subqueries 4 min read

Both IN and EXISTS are used for subqueries, but they work differently in terms of performance and use cases.

IN operator example:

-- Find employees in specific departments
SELECT name, department
FROM employees
WHERE department IN ('Sales', 'Marketing', 'Engineering');

-- Using subquery with IN
SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = 'New York'
);

EXISTS operator example:

-- Find customers who have placed orders
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

-- Using NOT EXISTS to find customers without orders
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Key Differences:

IN: Checks if value matches any value in a list/subquery
EXISTS: Checks if subquery returns any rows (returns boolean)
Performance: EXISTS often faster with large datasets
NULL handling: IN returns FALSE if any NULL in list, EXISTS ignores NULLs
Use IN for static lists or small result sets
Use EXISTS for correlated subqueries or when checking for existence
Use NOT EXISTS instead of NOT IN (better NULL handling)

26

Intermediate

What is the CASE statement and how is it used?

Conditional Logic 3 min read

The CASE statement provides conditional logic in SQL queries, similar to if-else statements in programming languages.

Simple CASE (value comparison):

SELECT 
    name,
    department,
    CASE department
        WHEN 'Sales' THEN 'Revenue Department'
        WHEN 'Marketing' THEN 'Brand Department'
        WHEN 'Engineering' THEN 'Tech Department'
        ELSE 'Other Department'
    END as dept_category,
    
    CASE 
        WHEN salary >= 100000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        WHEN salary >= 30000 THEN 'Low'
        ELSE 'Very Low'
    END as salary_grade
FROM employees;

Searched CASE (condition evaluation):

SELECT 
    order_id,
    amount,
    CASE 
        WHEN amount > 1000 THEN 'Large Order'
        WHEN amount > 500 THEN 'Medium Order'
        WHEN amount > 100 THEN 'Small Order'
        ELSE 'Mini Order'
    END as order_size,
    
    CASE 
        WHEN DATEDIFF(day, order_date, shipped_date) > 7 THEN 'Delayed'
        WHEN DATEDIFF(day, order_date, shipped_date) <= 2 THEN 'Express'
        ELSE 'Standard'
    END as shipping_status
FROM orders;

CASE in Aggregate Functions:

-- Count by category
SELECT 
    COUNT(CASE WHEN department = 'Sales' THEN 1 END) as sales_count,
    COUNT(CASE WHEN department = 'Engineering' THEN 1 END) as eng_count,
    COUNT(CASE WHEN salary > 80000 THEN 1 END) as high_earners,
    SUM(CASE WHEN status = 'active' THEN salary END) as active_payroll,
    AVG(CASE WHEN department = 'Marketing' THEN salary END) as marketing_avg
FROM employees;

-- PIVOT-like functionality
SELECT 
    EXTRACT(YEAR FROM hire_date) as hire_year,
    COUNT(CASE WHEN department = 'Sales' THEN 1 END) as sales,
    COUNT(CASE WHEN department = 'Engineering' THEN 1 END) as engineering,
    COUNT(CASE WHEN department = 'Marketing' THEN 1 END) as marketing
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date);

33

Intermediate

What is the difference between UNION and JOIN?

Set Operations 3 min read

UNION combines rows from multiple queries vertically, while JOIN combines columns from multiple tables horizontally.

UNION Example (vertical combination):

-- Combine employees from different cities
SELECT name, 'New York' as location FROM employees_ny
UNION ALL
SELECT name, 'San Francisco' as location FROM employees_sf
UNION ALL
SELECT name, 'Chicago' as location FROM employees_chi;

-- UNION vs UNION ALL
SELECT product_id FROM current_products
UNION  -- Removes duplicates
SELECT product_id FROM discontinued_products;

SELECT product_id FROM current_products
UNION ALL  -- Keeps duplicates
SELECT product_id FROM popular_products;

JOIN Example (horizontal combination):

-- Combine employee info with department info
SELECT 
    e.name,
    e.salary,
    d.department_name,
    m.name as manager_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Multiple JOIN types
SELECT 
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

Key Differences:

UNION: Stacks rows, requires same columns, combines result sets
JOIN: Merges columns, requires relationship, combines tables
Result Shape: UNION adds rows, JOIN adds columns
Use Case: UNION for similar data from different sources, JOIN for related data
Performance: UNION sorts to remove duplicates, JOIN uses indexes

39

Intermediate

What are recursive queries and how are they written?

Recursive Queries 5 min read

Recursive queries use Common Table Expressions (CTEs) to query hierarchical or graph-like data, such as organizational charts or bill of materials.

Organizational Hierarchy Example:

-- Employees table with manager hierarchy
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(employee_id)
);

-- Recursive CTE to get hierarchy
WITH RECURSIVE org_chart AS (
    -- Anchor member: top-level employees (no manager)
    SELECT 
        employee_id,
        name,
        manager_id,
        1 as level,
        name as path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member: join with previous level
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        oc.level + 1 as level,
        oc.path || ' -> ' || e.name as path
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT 
    employee_id,
    name,
    level,
    path
FROM org_chart
ORDER BY path;

Bill of Materials (BOM) Example:

-- Parts explosion (component hierarchy)
CREATE TABLE bom (
    assembly_id INT,
    component_id INT,
    quantity INT
);

WITH RECURSIVE parts_explosion AS (
    -- Anchor: top-level assemblies
    SELECT 
        assembly_id,
        component_id,
        quantity,
        1 as level,
        CAST(component_id AS VARCHAR) as path
    FROM bom
    WHERE assembly_id = 1000  -- Starting assembly
    
    UNION ALL
    
    -- Recursive: explode components
    SELECT 
        b.assembly_id,
        b.component_id,
        pe.quantity * b.quantity as total_qty,
        pe.level + 1 as level,
        pe.path || '.' || CAST(b.component_id AS VARCHAR)
    FROM bom b
    INNER JOIN parts_explosion pe ON b.assembly_id = pe.component_id
)
SELECT * FROM parts_explosion
ORDER BY level, path;

Finding All Ancestors (Bottom-up):

-- Find all managers above an employee
WITH RECURSIVE manager_chain AS (
    -- Start with specific employee
    SELECT 
        employee_id,
        name,
        manager_id,
        0 as level
    FROM employees
    WHERE employee_id = 123  -- Starting employee
    
    UNION ALL
    
    -- Move up to manager
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        mc.level + 1
    FROM employees e
    INNER JOIN manager_chain mc ON e.employee_id = mc.manager_id
)
SELECT * FROM manager_chain
ORDER BY level DESC;

Recursive CTE Components:

Anchor Member: Initial query, provides starting point
Recursive Member: References CTE itself, continues recursion
Termination Condition: When no more rows are returned
UNION/UNION ALL: Combines anchor and recursive results
WITH RECURSIVE: Keyword to declare recursive CTE

46

Intermediate

What is the difference between ANY, ALL, and SOME operators?

Comparison Operators 4 min read

These operators compare a value to each value in a list or subquery, with different comparison semantics.

ANY/SOME Operator (same meaning):

-- Find employees earning more than ANY manager
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary 
    FROM employees 
    WHERE title = 'Manager'
);
-- Equivalent to: salary > MIN(manager_salaries)

-- Using SOME (synonym for ANY)
SELECT name, salary
FROM employees
WHERE salary > SOME (
    SELECT salary 
    FROM employees 
    WHERE title = 'Manager'
);

-- ANY with IN-like behavior
SELECT product_name
FROM products
WHERE category_id = ANY (SELECT category_id FROM popular_categories);
-- Equivalent to: category_id IN (...)

ALL Operator:

-- Find employees earning more than ALL managers
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary 
    FROM employees 
    WHERE title = 'Manager'
);
-- Equivalent to: salary > MAX(manager_salaries)

-- ALL with comparison
SELECT product_name, price
FROM products
WHERE price <= ALL (
    SELECT price 
    FROM products 
    WHERE category_id = 5
);
-- Finds cheapest product in category 5

-- ALL with NOT
SELECT customer_name
FROM customers
WHERE customer_id != ALL (
    SELECT customer_id 
    FROM orders 
    WHERE order_date > '2024-01-01'
);
-- Customers with no recent orders

Practical Examples:

-- Find departments where ALL employees earn > 50000
SELECT department
FROM employees e1
WHERE 50000 < ALL (
    SELECT salary 
    FROM employees e2 
    WHERE e2.department = e1.department
)
GROUP BY department;

-- Using EXISTS alternative for ALL
SELECT department
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e2 
    WHERE e2.department = e1.department 
    AND e2.salary <= 50000
)
GROUP BY department;

Key Differences:

ANY/SOME: True if comparison holds for AT LEAST ONE value
ALL: True if comparison holds for ALL values
SOME = ANY: They are synonyms
Empty subquery: ANY returns FALSE, ALL returns TRUE
Use ANY for "at least one" conditions
Use ALL for "every" conditions
Often rewritable with MIN/MAX or EXISTS

SQL Query Interview Questions

Filter by Difficulty

What is the difference between WHERE and HAVING clauses?

Remember:

Explain the difference between UNION and UNION ALL

Key Differences:

What is the difference between DELETE, TRUNCATE, and DROP?

Key Differences:

What are correlated subqueries and how do they work?

Key Characteristics:

What is the difference between IN and EXISTS operators?

Key Differences:

What is the CASE statement and how is it used?

What is the difference between UNION and JOIN?

Key Differences:

What are recursive queries and how are they written?

Recursive CTE Components:

What is the difference between ANY, ALL, and SOME operators?

Key Differences:

Browse other topics