SQL Questions for Data Science Interview

Last Updated : 22 Oct, 2026

SQL is a fundamental tool in data science for querying, transforming, aggregating and analyzing structured data stored in relational databases. These questions cover essential SQL concepts including

  • Data filtering, joins, aggregate functions, window functions, subqueries and common table expressions (CTEs).
  • Database transactions, data manipulation and query optimization for efficient data retrieval and processing.

Basic Question

1. What is SQL?

SQL (Structured Query Language) is a standard language used for managing and manipulating relational databases. It allows users to perform tasks such as querying data, updating records and managing schemas.

2. Explain the difference between SQL and NoSQL databases.

SQL DatabasesNoSQL Databases
Store data in tables with rows and columns.Store data using document, key-value, graph or other data models.
Usually use a predefined schema.Often support flexible schemas.
Suitable for complex queries and structured transactions.Useful for flexible data storage and horizontal scalability.
Examples: MySQL, PostgreSQL, SQL Server.Examples: MongoDB, Cassandra, Redis.

3. What are the main SQL data types?

Common SQL data types include:

  • INT: Integer
  • VARCHAR(length): Variable-length string
  • DATE: Date
  • FLOAT: Floating-point number
  • BOOLEAN: True or False value

4. Write a query to select all columns from a table named employees.

SELECT * FROM employees;

5. How do you filter records in SQL? Provide an example.

Use the WHERE clause to filter records.

SELECT * FROM employees WHERE department = 'Sales';

6. What is the purpose of the GROUP BY clause? Give an example.

GROUP BY is used to group rows that have the same values into summary rows.

SELECT department, COUNT(*) FROM employees GROUP BY department;

7. Explain the difference between INNER JOIN and LEFT JOIN.

INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right table; unmatched rows from the right table are filled with NULLs.

INNER JOIN Example:

SELECT employees.name, departments.department 
FROM employees 
INNER JOIN departments 
ON employees.dept_id = departments.dept_id;

LEFT JOIN Example:

SELECT employees.name, departments.department 
FROM employees 
LEFT JOIN departments 
ON employees.dept_id = departments.dept_id;

8. How do you sort the results of a query?

SELECT * FROM employees ORDER BY salary DESC;

9. What Is the Purpose of the HAVING Clause? How Does It Differ from WHERE?

  • WHERE: Filters individual rows before grouping.
  • HAVING: Filters groups after GROUP BY is applied.

Example:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

This query returns departments whose average salary is greater than 50,000.

10. Write a query to find the top 5 highest salaries from a table named salaries.

SELECT salary FROM salaries ORDER BY salary DESC LIMIT 5;

11. How Do You Calculate the Average of a Column?

Use the AVG() function to calculate the average of a numeric column.

Example:

SELECT AVG(salary) AS average_salary
FROM employees;

This query returns the average salary of employees. AVG() ignores NULL values.

12. What Is the Difference Between COUNT(*) and COUNT(column_name)?

  • COUNT(*): Counts all rows, including rows containing NULL values.
  • COUNT(column_name): Counts only rows where the specified column is not NULL.

Example using COUNT(*):

SELECT COUNT(*)
FROM employees;

Example using COUNT(salary):

SELECT COUNT(salary)
FROM employees;

13. How Do You Find the Maximum and Minimum Values in a Column?

Use the MAX() function to find the highest value and the MIN() function to find the lowest value.

Example using MAX():

SELECT MAX(salary) AS highest_salary
FROM employees;

Example using MIN():

SELECT MIN(salary) AS lowest_salary
FROM employees;

14. Explain How the CASE Statement Works in SQL.

The CASE statement applies conditional logic and returns a value based on the specified conditions.

Example:

SELECT name,
       CASE
           WHEN salary > 50000 THEN 'High'
           ELSE 'Low'
       END AS salary_status
FROM employees;

15. Write a Query to Find the Number of Employees in Each Department.

Use COUNT(*) with GROUP BY to count employees in each department.

Example:

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

16. What Is a Window Function? Provide an Example.

A window function performs calculations across related rows without combining them into a single row per group. It is useful for running totals, rankings and averages.

Example:

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

17. What Is the Difference Between ROW_NUMBER(), RANK() and DENSE_RANK()?

ROW_NUMBER()

RANK()

DENSE_RANK()

Assigns a unique number to each row.

Assigns the same rank to rows with equal values.

Assigns the same rank to rows with equal values.

Does not leave gaps in row numbering.

Leaves gaps after tied ranks.

Does not leave gaps after tied ranks.

Example: 1, 2, 3, 4

Example: 1, 1, 3, 4

Example: 1, 1, 2, 3

18. How Do You Perform Date Arithmetic in SQL?

Date arithmetic allows you to add or subtract intervals from dates. The syntax varies across database systems.

Example in MySQL:

SELECT DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AS past_date;

This query returns the date 30 days before the current date.

Subqueries & Joins

19. What Is a CROSS JOIN and When Would You Use It?

A CROSS JOIN returns the Cartesian product of two tables, combining every row from the first table with every row from the second table. It is useful when you need all possible combinations of rows.

Example:

SELECT *
FROM employees
CROSS JOIN departments;

20. Write a Query to Calculate the Running Total of Sales by Month.

Use the SUM() window function to calculate a cumulative total over time.

Example:

SELECT month, sales,
       SUM(sales) OVER (
           ORDER BY month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales_data;

21. What Is a Subquery? Provide an Example.

A subquery is a query nested inside another query. It can be used to filter results based on the output of another query.

Example:

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

22. How Do You Use CASE WHEN in SQL?

The CASE WHEN statement applies conditional logic in SQL. It is useful for categorizing records, creating labels and calculating conditional values.

Example:

SELECT name, salary,
       CASE
           WHEN salary >= 50000 THEN 'High'
           WHEN salary >= 30000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

This query classifies employees into salary categories based on their salaries.

23. What Is a Correlated Subquery? Provide an Example.

A correlated subquery references a column from the outer query. Its result depends on the current row being evaluated, although the database optimizer may execute it differently.

Example:

SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

This query finds employees earning more than their department's average salary.

24. Explain the Difference Between UNION and UNION ALL.

  • UNION: Combines the results of two queries and removes duplicate rows.
  • UNION ALL: Combines the results without removing duplicates.

Example using UNION:

SELECT name FROM employees
UNION
SELECT name FROM managers;

Example using UNION ALL:

SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

25. How Do You Find the Second-Highest Salary in SQL?

You can use the MAX() function with a subquery to find the highest salary below the maximum salary.

Example:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

This query returns the second-highest distinct salary. If there is no second distinct salary, it returns NULL.

26. How Do You Join Three Tables? Provide a Query Example.

Use multiple JOIN operations to combine data from three related tables.

Example:

SELECT e.name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
INNER JOIN projects p
    ON e.project_id = p.project_id;

This example assumes the employees table contains both department_id and project_id.

27. What Is a Self-Join and When Would You Use It?

A self-join joins a table with itself. It is useful for representing hierarchical relationships, such as employees and their managers.

Example:

SELECT e1.name AS employee,
       e2.name AS manager
FROM employees e1
LEFT JOIN employees e2
    ON e1.manager_id = e2.employee_id;

28. How Do You Handle Duplicate Records in a Query Result?

Use the DISTINCT keyword to return unique combinations of the selected columns.

Example:

SELECT DISTINCT department
FROM employees;

This removes duplicate department values from the query result without deleting records from the table.

29. What Is the Purpose of the EXISTS Clause?

The EXISTS operator checks whether a subquery returns at least one row. It returns TRUE when a matching row exists.

Example:

SELECT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

30. Write a Query to Find Customers Who Have Made More Than Three Orders.

Use GROUP BY to group orders by customer and HAVING to filter customers with more than three orders.

Example:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

31. How Do You Insert a New Record into a Table?

Use the INSERT INTO statement to add a new record to a table.

Example:

INSERT INTO employees (name, department, salary)
VALUES ('John Doe', 'Marketing', 60000);

32. What Is the Purpose of the UPDATE Statement?

The UPDATE statement modifies existing records in a table. Use the WHERE clause to specify which records to update.

Example:

UPDATE employees
SET salary = 70000
WHERE name = 'John Doe';

33. How Do You Delete Records from a Table?

Use the DELETE FROM statement to remove records that match a specified condition.

Example:

DELETE FROM employees
WHERE name = 'John Doe';

34. Explain the Concept of Transactions in SQL.

A transaction is a group of database operations treated as a single unit of work. Transactions help maintain data consistency by allowing changes to be committed or rolled back.

35. What Are COMMIT and ROLLBACK?

  • COMMIT: Saves the changes made in the current transaction.
  • ROLLBACK: Undoes uncommitted changes in the current transaction.

Example using COMMIT:

COMMIT;

Example using ROLLBACK:

ROLLBACK;

36. How Do You Handle Errors During Transactions?

Use ROLLBACK to undo uncommitted changes when an operation fails. Error-handling methods vary by database system and application.

Example:

BEGIN;

UPDATE employees
SET salary = 70000
WHERE employee_id = 101;

-- If an error occurs, roll back the transaction.
ROLLBACK;

37. What Are the ACID Properties in SQL?

ACID represents four properties that help ensure reliable database transactions:

  • Atomicity: All operations succeed together or the transaction is rolled back.
  • Consistency: Transactions preserve database rules and constraints.
  • Isolation: Concurrent transactions are controlled to prevent unwanted interference.
  • Durability: Committed changes persist according to the database's guarantees.

38. How Can You Prevent SQL Injection Attacks?

Use parameterized queries or prepared statements to treat user input as data rather than executable SQL. Avoid building queries by directly concatenating untrusted input.

39. How Would You Compare Conversion Rates Between Two Groups in an A/B Test?

An A/B test compares two groups to determine whether a change affects user behavior. SQL can calculate conversion rates for each group.

Example:

SELECT variant,
       COUNT(*) AS users,
       AVG(converted) * 100 AS conversion_rate
FROM experiments
GROUP BY variant;

Assuming converted contains 1 for converted users and 0 otherwise, this query calculates each group's conversion rate. A statistical test is needed to assess whether the difference is significant.

Performance & Optimization

40. How Do You Use LAG() and LEAD() in SQL?

LAG() retrieves a value from a previous row, while LEAD() retrieves a value from a subsequent row. Both are useful for comparing sequential records.

Example:

SELECT month, sales,
       LAG(sales) OVER (ORDER BY month) AS previous_sales,
       LEAD(sales) OVER (ORDER BY month) AS next_sales
FROM monthly_sales;

This query displays each month's sales alongside the previous and next month's sales.

41. What Is an Index? Why Is It Important?

An index is a database structure that helps retrieve data more efficiently. It can improve query performance, especially for frequently filtered or joined columns. However, indexes require storage and can slow down insert, update and delete operations.

42. How Do You Create an Index on a Table?

Use the CREATE INDEX statement to create an index on one or more columns.

Example:

CREATE INDEX idx_salary
ON employees (salary);

43. How Do You Find Customers Who Have Never Placed an Order?

You can use a LEFT JOIN to identify customers who do not have matching records in the orders table.

Example:

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

44. Explain the Difference Between Clustered and Nonclustered Indexes.

In database systems that support this distinction:

  • Clustered index: Organizes table data around the index key.
  • Nonclustered index: Stores a separate index structure that points to the corresponding rows.

The exact implementation and number of clustered indexes allowed depend on the database system.

45. How Do You Calculate Customer Cohort Retention in SQL?

Cohort retention measures how many customers from an initial group return in subsequent periods. Customers can be grouped by their signup month or first purchase month.

Example:

SELECT cohort_month,
       activity_month,
       COUNT(DISTINCT customer_id) AS active_customers
FROM customer_activity
GROUP BY cohort_month, activity_month;

46. How Do You Handle Large Datasets and Improve Query Performance?

Common strategies include:

  • Using appropriate indexes.
  • Partitioning tables when suitable for the workload.
  • Optimizing queries to reduce unnecessary data processing.
  • Reviewing execution plans and keeping database statistics up to date.
  • Using data compression where supported and beneficial.

47. What Is Normalization and Why Is It Important?

Normalization is the process of organizing data into related tables to reduce unnecessary duplication and prevent data anomalies. It helps maintain data consistency and integrity.

48. Explain the Concept of Denormalization with an Example.

Denormalization introduces controlled redundancy or stores precomputed results to improve read performance. For example, a summary table can store total sales by department.

Example:

CREATE TABLE sales_summary AS
SELECT department, SUM(sales) AS total_sales
FROM sales
GROUP BY department;

This creates a snapshot of the totals. The summary may need to be refreshed when the source data changes and the exact syntax varies by database system.

49. What Are the Potential Drawbacks of Over-Normalization?

Over-normalization can require many joins, making queries more complex and potentially affecting performance. It can also make the database schema harder to understand and maintain.

50. How Do You Identify and Remove Duplicate Records in SQL?

The ROW_NUMBER() function can identify duplicate records and help retain only one record for each key.

Example:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY created_at DESC
           ) AS rn
    FROM customers
)
SELECT *
FROM ranked
WHERE rn = 1;
Comment

Explore