Sql Query Interview Questions And Answers

Introduction

Preparing for SQL query interview questions and answers can feel intimidating, but mastering the fundamentals and practicing a variety of problem types dramatically improves your confidence. This guide walks you through a comprehensive set of interview‑ready SQL questions, explains the reasoning behind each answer, and offers practical tips you can apply during your next technical interview. Whether you’re a fresh graduate or an experienced developer looking to pivot into data‑focused roles, understanding how to construct, optimize, and debug SQL queries is essential.

Common SQL Query Interview Questions and Answers

Basic SELECT Queries

1. Write a query to retrieve all columns for every row in a table named employees.

   SELECT * FROM employees;
   

   Explanation: The * wildcard selects every column. This is useful when you need a complete snapshot of the table.

2. How would you select only the first_name, last_name, and hire_date columns?

   SELECT first_name, last_name, hire_date FROM employees;
   

   Explanation: Listing specific column names improves readability and reduces unnecessary data transfer.

3. Retrieve distinct values of department_id from employees.

   SELECT DISTINCT department_id FROM employees;
   

   Explanation: DISTINCT removes duplicate rows, giving you a unique list of departments.

Filtering and Sorting Data

4. Find all employees hired after January 1, 2020.

   SELECT * FROM employees
   WHERE hire_date > '2020-01-01';
   

   Explanation: The WHERE clause filters rows based on a condition. Date literals are enclosed in single quotes.

5. List employees ordered by salary in descending order, limiting results to the top 10.

   SELECT * FROM employees
   ORDER BY salary DESC
   LIMIT 10;
   

   Explanation: ORDER BY sorts data, DESC specifies descending order, and LIMIT restricts output.

6. Select employees whose last_name starts with ‘S’.

   SELECT * FROM employees
   WHERE last_name LIKE 'S%';
   

   Explanation: The LIKE operator with the wildcard % matches any characters after the initial ‘S’.

Joins and Relationships

7. Write an INNER JOIN to combine employees and departments tables on department_id.

   SELECT e.first_name, d.department_name
   FROM employees e
   INNER JOIN departments d
       ON e.department_id = d.department_id;
   

   Explanation: Only rows with matching department_id appear in the result.

8. Retrieve all employees and their corresponding department names, using a LEFT JOIN.

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

   Explanation: LEFT JOIN returns every row from the left table (employees), showing NULL for departments that have no match.

9. Find employees who have no matching department record.

   SELECT e.first_name
   FROM employees e
   LEFT JOIN departments d ON e.department_id = d.department_id
   WHERE d.department_id IS NULL;
   

   Explanation: The WHERE clause filters for NULL values, identifying orphaned records.

Subqueries and CTEs

10. Select the average salary of each department using a subquery.

    SELECT department_id, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_salary
    FROM employees e
    GROUP BY department_id;
    

    Explanation: A correlated subquery calculates the average per department within the outer query Simple as that..

11. Use a Common Table Expression (CTE) to compute the total salary per department and then list departments where the total exceeds $500,000.

    WITH dept_totals AS (
        SELECT department_id, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department_id
    )
    SELECT department_id, total_salary
    FROM dept_totals
    WHERE total_salary > 500000;
    

    Explanation: CTEs simplify complex calculations and improve readability.

Aggregation and Grouping

12. Count the number of employees in each department.

    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
    

    Explanation: COUNT(*) tallies rows per group defined by department_id.

13. Find the highest salary in each department.

    SELECT department_id, MAX(salary) AS highest_salary
    FROM employees
    GROUP BY department_id;
    

    Explanation: MAX() aggregates the greatest value per group.

14. Calculate the average salary of employees hired after 2021, grouped by job title.

    SELECT job_title, AVG(salary) AS avg_salary
    FROM employees
    WHERE hire_date > '2021-12-31'
    GROUP BY job_title;
    

    Explanation: Filtering precedes grouping, ensuring only relevant rows are aggregated And that's really what it comes down to..

Window Functions

15. Rank employees by salary within each department using RANK().

    SELECT first_name, department_id, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees;
    

    Explanation: PARTITION BY creates separate ranking groups per department; RANK() assigns the same rank to tied salaries.

16. Compute the running total of salaries ordered by hire date.

    SELECT first_name, salary, hire_date,
           SUM(salary) OVER (ORDER BY hire_date) AS running_total
    FROM employees;
    

    Explanation: A windowed SUM() accumulates values progressively across rows.

Indexing and Performance Tuning

17. Identify columns that would benefit from an index in a sales table frequently queried by product_id and sale_date.
    Answer: Create indexes on product_id and sale_date. Composite indexes like CREATE INDEX idx_sales_product_date ON sales(product_id, sale_date); can speed up queries that filter on both columns Not complicated — just consistent..

18. Explain the impact of an ORDER BY clause on query performance.
    Answer: Sorting requires additional I/O and CPU resources. If the result set is large, consider adding an index that matches the ORDER BY columns to avoid a filesort.

Advanced Topics

19. Write a transaction that inserts a new employee and updates their department’s head count atomically.

    BEGIN TRANSACTION;
    INSERT INTO employees (first_name, last_name, department_id, salary)
    VALUES ('Jane', 'Doe',
    
    

    10, 75000);
    UPDATE departments
    SET head_count = head_count + 1
    WHERE department_id = 10;
    COMMIT;
    ```
    *Explanation:* Wrapping both statements in a transaction guarantees atomicity—either both succeed or the database rolls back to its prior state, preventing orphaned records or stale counters.  

20. **Implement optimistic locking for a `products` table to prevent lost updates.**  
    ```sql
    ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 1;

    -- Application logic:
    UPDATE products
    SET price = 29.99, version = version + 1
    WHERE product_id = 101 AND version = 5;
    ```
    *Explanation:* The `version` column acts as a concurrency token. If another session modified the row first, the `WHERE` clause fails (zero rows affected), signaling the application to retry with fresh data.  

21. **Create a materialized view for monthly sales summaries and schedule its refresh.**  
    ```sql
    CREATE MATERIALIZED VIEW mv_monthly_sales AS
    SELECT DATE_TRUNC('month', sale_date) AS month,
           product_id,
           SUM(quantity) AS total_qty,
           SUM(amount) AS total_revenue
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date), product_id;

    -- Refresh nightly (PostgreSQL example)
    CREATE EXTENSION IF NOT EXISTS pg_cron;
    SELECT cron.schedule('refresh-monthly-sales', '0 2 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;');
    ```
    *Explanation:* Materialized views trade real-time freshness for read performance. `CONCURRENTLY` avoids locking the view during refresh, and `pg_cron` automates the schedule.  

22. **Demonstrate a recursive CTE to traverse an organizational hierarchy.**  
    ```sql
    WITH RECURSIVE org_chart AS (
        SELECT employee_id, first_name, manager_id, 1 AS level
        FROM employees
        WHERE manager_id IS NULL          -- anchor: top-level executives
        UNION ALL
        SELECT e.employee_id, e.first_name, e.manager_id, oc.level + 1
        FROM employees e
        JOIN org_chart oc ON e.manager_id = oc.employee_id
    )
    SELECT * FROM org_chart ORDER BY level, employee_id;
    ```
    *Explanation:* The anchor member seeds the recursion with root nodes; the recursive member joins each employee to their manager, incrementing `level` until the hierarchy is exhausted.  

---

## Conclusion  

Mastering SQL is less about memorizing syntax and more about internalizing how the optimizer thinks—how indexes shape access paths, how join order influences intermediate result sizes, and how isolation levels balance consistency against throughput. The patterns above—filtering before grouping, window functions for analytical depth, transactions for integrity, and materialized views for read-heavy workloads—form a toolkit that scales from ad-hoc reporting to high-concurrency OLTP systems.  

As you progress, profile relentlessly: `EXPLAIN ANAZE` (or your engine’s equivalent) turns guesswork into measurable decisions. Pair that habit with version-controlled schema migrations, automated testing of query plans, and a disciplined backup/restore strategy, and you’ll transform SQL from a query language into a reliable foundation for data-driven applications.

You'll probably want to bookmark this section.