46 Essential SQL Interview Questions for Data Science Professionals (2025 Edition)

SQL (Structured Query Language) is a critical skill for any data science professional. Whether you are a data analyst, data engineer, or data scientist, you need to know how to effectively retrieve and manipulate data using SQL. Many companies test SQL skills as part of their data science interview process.

In this article, we have compiled a comprehensive list of 46 SQL questions to help you prepare for data science interviews and skill tests. We‘ll cover fundamental concepts like different types of joins, aggregations, and subqueries, as well as more advanced topics like window functions and recursive queries.

We‘ll also take a deep dive into primary keys – a crucial concept to master in SQL. By the end of this article, you‘ll be well-equipped to tackle any SQL questions that come your way. Let‘s get started!

SQL Fundamentals

Before jumping into the questions, let‘s review some key SQL concepts that frequently appear in skill tests and interviews:

• SELECT statement – retrieves data from a database table
• WHERE clause – filters results based on specified conditions
• GROUP BY clause – groups result rows that have the same values in specified columns
• HAVING clause – specifies a search condition for a group or aggregate
• ORDER BY clause – sorts the result set in ascending or descending order
• JOINs – combine rows from two or more tables based on a related column
• Subqueries – a query nested inside another query
• Aggregations – calculate a single result from a set of input values (e.g. COUNT, SUM, AVG, MAX, MIN)
• Window functions – perform calculations across a set of rows that are related to the current row

Make sure you have a solid grasp of these fundamentals before tackling the questions below. Refer to the resources at the end of this article for in-depth tutorials on these topics.

Primary Keys

Now let‘s focus on a critical SQL concept that you are likely to be tested on – primary keys. A primary key is a column or group of columns that uniquely identifies each row in a table.

Here are some important properties of primary keys:

1. A primary key must be unique – no two rows can have the same primary key value
2. A primary key cannot contain NULL values
3. A table can have only one primary key (but it can be a composite key consisting of multiple columns)
4. The primary key value should never change – it is used to uniquely identify rows even when other data changes

Many SQL skill tests and interviews will ask about the differences between primary keys and other types of keys, like unique keys and foreign keys. Here‘s a quick overview:

• Unique keys also require values to be unique, but unlike primary keys, they allow one NULL value per column. A table can have multiple unique keys.
• Foreign keys are used to create relationships between tables. They reference the primary key of another table to enforce referential integrity.

Let‘s look at an example to illustrate these concepts:

Suppose we have two tables – CUSTOMERS and ORDERS:

CUSTOMERS Table:
| customer_id (PK) | customer_name |
|——————|—————|
| 1 | John Smith |
| 2 | Jane Doe |

ORDERS Table:
| order_id (PK) | customer_id (FK) | order_date |
|—————|——————|————|
| 1001 | 1 | 2023-01-15 |
| 1002 | 2 | 2023-02-10 |
| 1003 | 1 | 2023-03-22 |

In the CUSTOMERS table, customer_id is the primary key. It uniquely identifies each customer and cannot be NULL.

In the ORDERS table, order_id is the primary key and customer_id is a foreign key referencing the CUSTOMERS table. This establishes a relationship between the two tables – each order is associated with a single customer. The foreign key constraint ensures that you can‘t insert an order for a customer_id that doesn‘t exist in the CUSTOMERS table.

Now that you understand the basics of primary keys, let‘s practice with some actual SQL interview questions!

Interview Questions

Basic

1. What is a primary key?

Answer: A primary key is a column or set of columns that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values.

2. True or False: A table can have more than one primary key.

Answer: False. A table can have only one primary key, which may consist of single or multiple columns.

3. Can a primary key contain NULL values? Why or why not?

Answer: No, a primary key cannot contain NULL values. One of the requirements of a primary key is that it must have a non-NULL value for every row. Allowing NULLs would violate the uniqueness constraint.

4. What is a composite primary key?

Answer: A composite primary key, also called a compound key, is a primary key that consists of two or more columns. The combination of values from these columns uniquely identifies each row in the table.

5. How is a primary key different from a unique key?

Answer: Both primary keys and unique keys enforce uniqueness on the column(s) where they are defined. However, a primary key cannot allow NULL values, while a unique key can allow one NULL value per column. Also, a table can have only one primary key but multiple unique keys.

Intermediate

6. Explain the difference between a primary key and a foreign key.

Answer: A primary key uniquely identifies each record in a table. A foreign key is a field in one table that refers to the primary key in another table. The foreign key establishes a link between the two tables and enforces referential integrity.

7. What does the following SQL statement do?

ALTER TABLE Employees ADD PRIMARY KEY (ID);

Answer: This SQL statement adds a primary key constraint on the "ID" column of the "Employees" table. It ensures that the "ID" column will only allow unique, non-NULL values.

8. Consider the following two tables:

STUDENTS Table:
| student_id (PK) | student_name | major |
|—————–|————–|————–|
| 1001 | John Smith | Biology |
| 1002 | Jane Doe | Computer Sci |
| 1003 | Bob Johnson | English |

ENROLLMENTS Table:
| enrollment_id (PK) | student_id (FK) | course_id | grade |
|——————–|—————–|———–|——-|
| 1 | 1001 | BIO101 | A |
| 2 | 1001 | CHM101 | B |
| 3 | 1002 | CS101 | A |
| 4 | 1002 | CS102 | A |
| 5 | 1003 | ENG101 | C |

To query the names and majors of students who got an "A" grade, which of the following SQL statements is correct?

A)

SELECT student_name, major
FROM STUDENTS
WHERE grade = ‘A‘;

B)

SELECT student_name, major 
FROM STUDENTS
JOIN ENROLLMENTS ON STUDENTS.student_id = ENROLLMENTS.student_id;  

C)

SELECT student_name, major
FROM STUDENTS 
JOIN ENROLLMENTS ON STUDENTS.student_id = ENROLLMENTS.student_id
WHERE grade = ‘A‘;

D)

SELECT student_name, major, grade
FROM ENROLLMENTS
WHERE grade = ‘A‘;  

Answer: C is the correct statement. It performs an inner join between the STUDENTS and ENROLLMENTS tables based on the student_id foreign key, and then selects only the rows where the grade is ‘A‘.

Advanced

9. What is referential integrity and how does it relate to primary and foreign keys?

Answer: Referential integrity is a database concept that ensures the relationships between tables remain consistent. It requires that a foreign key value must match an existing primary key value in the referenced table.

When a foreign key is defined, the database ensures referential integrity by checking that:

• The referenced table exists and has a primary key defined on the referenced column(s)
• Every foreign key value corresponds to an existing primary key value in the referenced table
• Rows with a foreign key reference cannot be deleted from the referenced table if dependent rows exist in the referencing table (unless cascading deletes are enabled)

10. Explain the concept of cascading deletes and updates with respect to foreign keys.

Answer: Cascading deletes and updates are foreign key constraint options that automatically delete or update records in a child table when a record is deleted or updated in the associated parent table.

• ON DELETE CASCADE: When a row is deleted from the parent table, all matching rows in the child table are also deleted.
• ON UPDATE CASCADE: When the primary key of a row in the parent table is updated, the corresponding foreign key values in the child table are also updated.

These options maintain referential integrity between related tables, but they should be used cautiously as they can lead to unintended data loss.

11. Given the STUDENTS and ENROLLMENTS tables from question 8, write a SQL query to find the names of students who are not enrolled in any course.

Answer:

SELECT student_name 
FROM STUDENTS
WHERE student_id NOT IN (
  SELECT student_id 
  FROM ENROLLMENTS
);

This query uses a subquery with the NOT IN operator to find students whose student_id does not appear in the ENROLLMENTS table, indicating that they are not enrolled in any course.

12. Consider the following EMPLOYEES table with a self-referencing foreign key:

Write a SQL query to find the names of employees who are managers (i.e. they have other employees reporting to them).

SELECT DISTINCT m.employee_name
FROM EMPLOYEES e 
JOIN EMPLOYEES m ON e.manager_id = m.employee_id;

Conclusion

Congratulations on making it to the end of this comprehensive guide to SQL interview questions! We hope these questions have helped you gain a deeper understanding of key SQL concepts, especially primary keys.

Remember, the best way to prepare for SQL interviews is to practice writing queries on real datasets. Don‘t just memorize syntax – make sure you understand the underlying logic and can apply your knowledge to solve new problems.

Keep learning and happy querying!

Additional Resources

Want to learn more? Check out these resources:

• SQL Basics Tutorial – An interactive SQL tutorial for beginners
• SQL Joins Explained – A visual guide to SQL joins
• How to Use Subqueries in SQL – A comprehensive tutorial on subqueries
• SQL Window Functions – An introduction to window functions in PostgreSQL

You can also find many sample databases online to practice your SQL skills:

• MySQL Sample Database
• PostgreSQL Sample Database
• SQL Server Sample Databases