Master SQL for Your Next Coding Interview: 75+ Questions from Beginner to Advanced

Introduction

If you‘re aiming for a career in data analytics, business intelligence, software engineering or many other technical fields, having strong SQL skills is essential. SQL (Structured Query Language) is the standard language for interacting with relational databases and is used by organizations of all sizes to store, query and manipulate their data.

For many technical job interviews, especially data-related roles, you can expect to be asked questions to test your proficiency with SQL. These SQL coding questions are designed to assess your ability to retrieve and analyze data using SQL queries. The questions may start with the basics but often progress to more complex problems involving multiple tables, subqueries, and advanced functions.

To crack SQL interviews, you need to have a solid grasp of SQL fundamentals as well as exposure to more advanced techniques. In this guide, we‘ll walk through over 75 real-world SQL interview questions ranging from beginner to advanced levels. We‘ll cover the key concepts tested, provide detailed explanations for each question, and share additional tips to help you ace your next SQL interview.

But first, let‘s briefly review some SQL basics that you should be comfortable with before diving into the questions.

SQL Basics to Review

Here are the fundamental SQL concepts you should know:

• SQL Statements: SELECT, INSERT, UPDATE, DELETE to query and modify data
• Clauses: WHERE, GROUP BY, HAVING, ORDER BY to filter, group and sort query results
• Joins: INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN to combine rows from multiple tables
• Aggregations: COUNT, SUM, AVG, MIN, MAX to perform calculations on groups of rows
• Subqueries: Queries nested inside other queries
• Functions: String, date, numeric, and aggregate functions to transform and analyze data
• Data Types: VARCHAR, INT, DATE, BOOLEAN and others to define columns
• Constraints: Primary key, foreign key, UNIQUE, NOT NULL, etc. to enforce data integrity
• Indexes: Used to improve query performance
• Transactions: To group statements and maintain data consistency
• Database Objects: Tables, views, procedures, etc. used to store and interact with data

Alright, now that we‘ve reviewed the fundamentals, let‘s jump into the interview questions! We‘ll start with beginner level and work our way up to more advanced problems.

Beginner SQL Interview Questions

Q1. Write a query to retrieve all columns and rows from a table named "employees".

SELECT * 
FROM employees;

This basic query selects all columns (*) and all rows from the employees table. It‘s the simplest way to retrieve all data from a table.

Q2. Write a query to retrieve only the "first_name" and "last_name" columns from the "employees" table.

SELECT first_name, last_name
FROM employees; 

Here we specify the exact column names we want to retrieve inside the SELECT statement. Only those columns will be returned in the result set.

Q3. Write a query to retrieve the "first_name", "last_name", and "salary" of employees who have a salary greater than $50,000.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

The WHERE clause is used to filter the rows returned based on a condition. In this case, only rows where salary is greater than 50,000 will be included in the results.

Q4. Write a query to calculate the average salary of all employees.

SELECT AVG(salary) AS avg_salary
FROM employees;

The AVG() function calculates the average value of a numeric column. We use an alias (AS avg_salary) to give the result a descriptive name.

Q5. Write a query to find the employee with the maximum salary.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

To find the max salary, we can ORDER BY the salary column in descending order (DESC) and take only the first result with LIMIT 1.

Q6. Write a query to count the number of employees in each department.

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

The COUNT(*) function counts the number of rows. By using GROUP BY department, we get the count for each distinct department value.

Intermediate SQL Interview Questions

Now let‘s look at some more challenging questions that test your knowledge of joins, subqueries, and more advanced functions.

Q1. Write a query to retrieve the name and salary of employees who earn more than the average salary of their department.

SELECT e.first_name, e.last_name, e.salary
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept_avgs ON e.department = dept_avgs.department
WHERE e.salary > dept_avgs.avg_salary;

Here we use a subquery to calculate the average salary per department. We join this with the employees table to compare each employee‘s salary to their department average.

Q2. Write a query to find the top 3 departments with the highest average employee salary.

SELECT department, AVG(salary) AS avg_salary
FROM employees  
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 3;

This query uses GROUP BY to calculate the average salary per department, orders by the average descending, and takes the top 3 results.

Q3. Write a query to find employees who have a higher salary than their manager.

SELECT e1.first_name, e1.last_name, e1.salary 
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e1.salary > e2.salary;

To compare an employee‘s salary to their manager‘s, we need to join the employees table to itself. The JOIN condition matches each employee to their manager using the manager_id and employee_id columns.

Q4. Write a query to find the 5th highest salary among all employees.

SELECT DISTINCT salary
FROM employees e1
WHERE 5 = (
  SELECT COUNT(DISTINCT salary) 
  FROM employees e2
  WHERE e2.salary >= e1.salary
);

Finding the nth highest salary is a bit tricky. This solution uses a correlated subquery to count the number of distinct salaries greater than or equal to each employee‘s salary. The outer query then selects the salary where this count equals 5.

Q5. Write a query to calculate the cumulative sum of salaries for each department, ordered by the hire date.

SELECT first_name, last_name, hire_date, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_salary
FROM employees;

This uses a window function to calculate a running total of salaries. PARTITION BY department calculates separate cumulative sums for each department. ORDER BY hire_date orders the rows within each department by hire date before calculating the running total.

Advanced SQL Interview Questions

These questions cover complex topics like recursive queries, pivoting data, and database design.

Q1. Write a query to find the management chain for an employee (i.e. the employee‘s manager, their manager‘s manager, etc.).

WITH RECURSIVE employee_hierarchy AS (
  SELECT employee_id, first_name, last_name, manager_id, 1 AS level  
  FROM employees
  WHERE employee_id = 1
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;  

This uses a recursive common table expression (CTE) to traverse the hierarchy of employees. The base case starts with a given employee_id. The recursive case joins the CTE to the employees table to find that employee‘s manager and increments the level. This repeats until no more managers are found.

Q2. Write a query to pivot the counting of employees by department and gender.

SELECT 
  COUNT(CASE WHEN gender = ‘M‘ THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = ‘F‘ THEN 1 END) AS female_count,
  department
FROM employees
GROUP BY department;

To pivot the data, we use conditional aggregation with CASE statements inside COUNT. This counts employees separately based on the gender condition, and the counts are grouped by department.

Q3. Discuss the difference between a clustered and a non-clustered index in a database.

A clustered index determines the physical order of data in a table. Table data can be sorted in only one way, therefore, there can be only one clustered index per table. A non-clustered index is a separate structure from the data rows and contains a sorted list of the indexed key and row pointers to the actual data. A table can have multiple non-clustered indexes.

Q4. What are some best practices for designing a database schema?

Some key principles:

• Normalize data to reduce redundancy and improve data integrity. Aim for 3rd normal form.
• Choose appropriate data types for each column based on the data it will store.
• Define primary keys for each table to uniquely identify each row.
• Use foreign keys to establish relationships between tables.
• Create indexes on columns frequently used for filtering or joining, but avoid over-indexing.
• Consider denormalization for read-heavy workloads if performance is more critical than normalization.

Tips for SQL Interview Preparation

In addition to practicing a variety of SQL problems, here are some other tips to prepare for SQL interviews:

1. Understand the SQL syntax and functions for the specific database system used by the company (e.g. MySQL, PostgreSQL, SQL Server, Oracle).

2. Be comfortable with the different types of JOINs and when to use each one. Many interviewers like to test JOIN knowledge.

3. Know the difference between SQL statements used for data querying (SELECT) vs data manipulation (INSERT, UPDATE, DELETE).

4. Practice explaining your thought process as you work through SQL problems. Interviewers often care more about how you think through the problem than getting the exact right answer.

5. Review database design concepts and be prepared to discuss topics like normalization, ER diagrams, and schema design.

6. Brush up on your knowledge of database optimization techniques, like using indexes effectively, analyzing query execution plans, and avoiding costly operations.

Conclusion

SQL is a critical skill to master for many technical interviews. By practicing questions across beginner, intermediate and advanced levels, you‘ll build a comprehensive understanding of SQL and be well-prepared to tackle any problem an interviewer might pose.

Remember, the key to success is not just memorizing syntax, but understanding the underlying concepts and being able to apply them to new problems. With a solid foundation in SQL fundamentals and plenty of hands-on practice, you‘ll be ready to impress in your next SQL interview.

Best of luck with your SQL interview preparation!