Gemini Generated

Ace Your Next Interview: 5 Essential SQL Questions Every Interviewer Asks (With Examples)

Introduction

Whether you are interviewing for a role as a Data Analyst, Data Engineer, or Backend Software Developer, SQL (Structured Query Language) is almost always on the assessment checklist. Interviewers love questions that test both conceptual understanding and practical query-writing capability.

1. WHERE vs. HAVING: What’s the Difference

This is a classic conceptual question designed to test whether you understand the logical order of operations in SQL.

This is a classic conceptual question designed to test whether you understand the logical order of operations in SQL.

• Where clause: Filters rows before any grouping or aggregation takes place. It cannot use aggregate functions (like sum(), avg(), or count()).
• HAVING clause: Filters groups after aggregation has been performed. It is almost always used alongside GROUP BY and can evaluate aggregate functions.

Example

Imagine we have an orders table. If we want to find customers who have spent a total of over $500, but we only want to look at orders placed in 2025, we use both:

Table name - orders

order_id customer_id    order_date    order_amount
 101        1           2025-03-10         $300
 102        1           2025-06-15         $250
 103        2           2024-12-20         $600
 104        2           2025-02-01         $200

SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
WHERE order_date >= '2025-01-01' -- Filters individual rows first
GROUP BY customer_id
HAVING SUM(order_amount) > 500;   -- Filters the aggregated groups next



output:
customer_id      total_spent
    1              $550

2. Finding the Nth Highest Salary

A favorite among interviewers for testing problem-solving logic. The classic version asks for the second-highest salary from an employees table. While you can use limit and offset in some databases, using Window Functions is the cleanest, industry-standard approach.

Example

Using the dense_rank() window function ensures we handle ties correctly (e.g., if multiple employees share the same top salary).

Table name - employees

employee_id   employee_name      salary
  1             Alice           $90,000
  2             Bob             $120,000
  3            Charlie          $120,000
  4            Diana            $75,000

WITH RankedSalaries AS (
    SELECT 
        employee_name, 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
    FROM employees
)
SELECT employee_name, salary
FROM RankedSalaries
WHERE salary_rank = 2;


Output

employee_name       salary
Bob                $120,000
Charlie            $120,000

3. How Do You Find Duplicate Rows in a Table

Data hygiene is a critical skill. Interviewers frequently ask how you would detect or isolate duplicate data within a database table.

Example

Get SULTAN ANSARI’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To find duplicate emails in a users table, you group by the target column and use having count(*)>1.

Table name - email

user_id            email
  1         alice@example.com
  2         bob@example.com
  3         alice@example.com
  4         charlie@example.com
  5         bob@example.com
  6         bob@example.com

SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Output 

    email                    duplicate_count
alice@example.com               2
bob@example.com                 3

4. INNER JOIN vs. LEFT JOIN

Understanding table relationships is core to relational databases. You must clearly articulate how rows are matched or discarded during joins.

• inner join: Returns only the rows that have matching values in both tables.
• left join: Returns all rows from the left table, and the matched rows from the right table. If there is no match, the result for the right table's columns is NULL.

Example

Listing all employees, even if they haven’t been assigned to a department yet.

Table name - employees

employee_name   department_id
Alice             10
Bob               20
Charlie          NULL

Table name - departments

id      department_name
10       Engineering
20       Marketing

SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d 
    ON e.department_id = d.id;


Output 

employee_name     department_name
Alice              Engineering
Bob                Marketing
Charlie            NULL

If an employee has a NULL department ID, they will still appear in the list with a NULL department name.

5. What is a CTE (Common Table Expression) and Why Use It

Modern SQL development relies heavily on readability and modular code. A CTE is a temporary result set defined within the execution scope of a single SELECT, UPDATE, INSERT AND DELETE statement. You declare it using the WITH keyword.

Why use it?

• It replaces messy, hard-to-read nested subqueries.
• It allows you to reference the same temporary dataset multiple times within a main query.
• It enables recursive queries (like organizational hierarchies).

Example

employees and department table

employee_id name  salary   department_id
    1       John   $110,000     100
    2       Sarah  $95,000      100
    3       Mike   $130,000     200

WITH HighEarners AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 100000
)
SELECT department_id, COUNT(*) AS high_earner_count
FROM HighEarners e
JOIN employee_departments ed ON e.employee_id = ed.employee_id
GROUP BY department_id;


Output

department_id    high_earner_count
   100                  1
   200                  1

Final Thoughts

Mastering these five patterns covers a huge percentage of what database interviewers look for. Focus not just on memorizing the syntax, but on understanding when and why you would choose one clause or function over another.