60+ Essential SQL Interview Questions for Aspiring Data Scientists (2025 Edition)

ByJordan Brown

Introduction

If you aspire to become a data scientist, you absolutely must master SQL. Structured Query Language (SQL) is the primary tool used to interact with databases and extract data for analysis. A recent survey by Burtch Works found that SQL is the most commonly required skill for data science roles, with 98% of positions listing it as a requirement[^1].

SQL proficiency is essential across the entire data science workflow – from querying data to exploring datasets to building features for machine learning models. As a result, SQL questions are ubiquitous in data science interviews at companies of all sizes. From tech giants like Google and Facebook to startups and non-tech companies hiring their first data scientist, you can expect SQL to be a major focus.

To help you prepare, we‘ve compiled over 60 of the most common and important SQL interview questions. Questions are grouped by the key SQL concepts they cover, with detailed explanations and real-world examples. We‘ll also share expert tips on what interviewers are really looking for and how to showcase your SQL skills.

SQL Fundamentals

Before diving into more complex topics, it‘s crucial to have a rock-solid grasp of core SQL concepts. Interviewers will typically start with questions on the fundamentals of SQL syntax and querying. Here are some examples:

Q1. What is SQL primarily used for?

SQL is a domain-specific language used for managing and querying relational databases. It enables users to create, read, update and delete (CRUD) data in database tables. SQL is the primary way data scientists extract and manipulate data for analysis.

Q2. Explain the difference between WHERE and HAVING clauses.

Both WHERE and HAVING are used to filter rows, but they differ in when the filtering occurs:

  • WHERE is used to filter individual rows before any grouping occurs. It can be used with SELECT, UPDATE, and DELETE statements.
  • HAVING is used to filter groups after rows have been grouped together with GROUP BY. It can only be used with SELECT statements.

In practice, you should always use WHERE if you‘re filtering on individual row values. Use HAVING when you need to filter on the results of an aggregate function.

Q3. What are the main data types in SQL?

While specific data types can vary between database systems, most support the following main categories:

  • Numeric types: INTEGER, FLOAT, REAL, DECIMAL, etc.
  • Character and string types: CHAR, VARCHAR, TEXT
  • Date and time types: DATE, TIME, TIMESTAMP
  • Binary types: BINARY, VARBINARY, BLOB
  • Boolean: BOOLEAN

It‘s important to choose appropriate data types when designing tables to ensure data integrity and optimize performance.

Querying and Filtering

The heart of SQL is querying data – selecting, filtering, and transforming data from database tables. Here are some common querying and filtering questions:

Q4. Write a query to return all employees with a salary greater than $50,000, ordered by salary descending.

SELECT * 
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Key concepts:

  • SELECT * returns all columns
  • WHERE filters rows based on a condition
  • ORDER BY sorts the results (default is ascending)
  • DESC keyword for descending order

Q5. Write a query to find the top 5 highest paid employees.

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

Alternatively:

SELECT TOP 5 *
FROM employees
ORDER BY salary DESC;

The LIMIT clause (or TOP keyword in SQL Server) is used to return only a specified number of rows.

Q6. Write a query to calculate the average salary for each department.

SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id  
GROUP BY d.department_name;

This query involves an inner join between the employees and departments tables to get the department name. The AVG() aggregate function computes the average salary. Whenever you use an aggregate function, you must GROUP BY all non-aggregated columns.

JOINs and Subqueries

JOINs are used to combine rows from multiple tables based on a related column. Subqueries enable more complex, multi-step logic. Interviewers love to ask about JOINs and subqueries to test your data modeling skills.

Q7. What are the different types of JOIN in SQL?

The main types of JOIN are:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

The key difference is how they handle unmatched rows. INNER JOIN only includes rows that match the join condition. LEFT/RIGHT JOIN includes all rows from one table plus any matching rows from the other. FULL JOIN includes all rows from both tables, with nulls for unmatched values.

Q8. Write a query to return the names of all employees in the ‘Marketing‘ department along with their manager‘s name.

Assuming we have an employees table with an employee_id and manager_id column:

SELECT e1.first_name AS employee, e2.first_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id
JOIN departments d ON e1.department_id = d.department_id
WHERE d.department_name = ‘Marketing‘;

This query uses a self-join on the employees table to match each employee to their manager. The additional join to the departments table lets us filter for only ‘Marketing‘ employees.

Q9. Write a query to find employees who earn more than the average salary in their department.

SELECT e1.first_name, e1.last_name, e1.salary
FROM employees e1
JOIN (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees 
  GROUP BY department_id
) e2 
ON e1.department_id = e2.department_id
WHERE e1.salary > e2.avg_salary;

This uses a subquery to calculate the average salary per department, which is then joined back to the employees table to find individuals earning above that average.

Window Functions

Window functions perform calculations across a set of rows that are related to the current row. They are a powerful tool for complex analytic queries. Common functions include RANK, ROW_NUMBER, LEAD/LAG, and running totals.

Q10. Write a query to rank employees within each department by salary.

SELECT
  first_name,
  last_name, 
  department_name,
  salary,
  RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS rank
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

RANK() assigns ranks within each department (PARTITION BY department_name) based on descending salary. Employees with the same salary receive the same rank, with the next rank skipped.

Q11. Write a query to find the employee with the second highest salary in each department.

SELECT *
FROM (
  SELECT 
    first_name,
    last_name,
    department_name, 
    salary,
    DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS rank
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
) t
WHERE rank = 2;

This uses DENSE_RANK which always assigns consecutive ranks (1, 2, 2, 3 vs 1, 2, 2, 4 for RANK). We select from this subquery where rank = 2 to get the second highest salary per department.

Recursive Queries

Recursive queries are a more advanced SQL concept that allow you to traverse hierarchical data. They are often used for tasks like bill of materials explosion or organizational chart rollups.

Q12. Given a table of employees with a manager_id column, write a query to return the entire org chart starting from the CEO.

Assuming the CEO has a NULL manager_id:

WITH RECURSIVE org_chart AS ( 
  -- base case
  SELECT employee_id, first_name, last_name, manager_id, 1 AS level 
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive case  
  SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, oc.level + 1 
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT first_name, last_name, level 
FROM org_chart 
ORDER BY level, last_name;

This recursive query has two parts:

  1. The base case selects the CEO (where manager_id is NULL)
  2. The recursive case joins back to the CTE to get all direct reports of the previous level

The result is all employees listed out in a hierarchical fashion with a level showing their distance from the CEO.

Conclusion

SQL is an essential skill for all data scientists. By mastering these core concepts and practicing with real interview questions, you‘ll be well-prepared to showcase your SQL abilities.

Remember, interviewers are looking for more than just correct queries. They want to see your thought process, your approach to problem-solving, and your ability to explain complex topics. Practice walking through your solutions out loud, discussing trade-offs and alternatives.

Beyond the technical skills, employers are also looking for data scientists who can apply SQL to real-world problems. As you prepare, think about examples from your own projects or experience where you‘ve used SQL to extract insights and drive business value. Being able to discuss the impact of your work is just as important as writing flawless queries.

Additional resources for practicing SQL:

With dedication and practice, you‘ll be able to confidently handle any SQL question that comes your way. Good luck with your data science interviews!

[^1]: Burtch Works 2022 Data Science Hiring Report: https://www.burtchworks.com/2022/08/11/2022-data-science-hiring-report/

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

You May Like to Read,

Similar Posts