Top 12 SQL Query Problems for Coding Interviews (with Solutions) (original) (raw)

Hello guys, if you are looking for SQL query examples from interviews or SQL Query Practice questions to improve your SQL skill or just to prepare for tech interviews then you have come to the right place. Earlier, I have shared best websites to learn SQL and Practice Query online and in this article, I am going to share 12 popular SQL query questions from interviews. SQL is an important skills for both programmers and data scientist, even people from QA and BA stream also need to know SQL to do their job well in this era or data driven world. That's why SQL queries are also quite popular on interviews.

If you have given interviews then you may have come across questions like how to find 2nd highest salary or Nth highest salary or remove duplicates form table. Those are classic SQL query questions and in this article, I am going to share few more question to prepare you for tech interviews.

You can also practice them to test your SQL skills before your technical Interview. If you don't know SQL, a short form of Structured Query Language is one of the essential skills in today's programming world.

No matter whether you are a Java developer, C++ developer or Python developer, you must know how to write SQL queries. Every programming job interview has at least one or two questions that require you to write SQL queries for a given requirement and many developers struggle there.

It's easy to answer theoretical questions like what is the difference between clustered and non-clustered index (see) or what is the difference between correlated and non-correlated subqueries (see), but when it comes time to actually write SQL queries to solve problems, it's not that easy, especially if you haven't done your homework and practice.

In the past, I have also shared frequently asked SQL Interview Questions and recommended a couple of books and websites to improve your SQL query skills but nothing is better than understanding schema, data, and writing your own SQL queries.

In order to learn fast, start with a small table with a few columns which include data types like number, date, and String, which have fewer number data so that you can quickly understand and expect what should be output. Includes some NULL, empty, and out of bound values to really test your queries.

Considering all these together today I am going to share SQL script to create a sample table to practice writing SQL queries for interviews. In this article, you will find an SQL script to create a table and populate it with sample data and then write SQL queries to solve some common problems from Interviews.

12 SQL Query Problems with Solutions for Technical Interview

It's time to write SQL queries now. This section contains 6 SQL query Interview questions that will test many of your SQL skills like joins, grouping, and aggregating data, how you handle nulls in SQL etc. It doesn't test all skills e.g. correlated subqueries, but you can take a look at questions like how to find Nth highest salary of employees to learn that.

This section contains 12 data problems for which you need to write SQL queries, the solution is provided in the next section but I suggest you try to solve these problems first before looking at the solution.

Also we will use classic Employee and Department data model as shown in following ERD Diagram

Employee department table ERD diagram SQL

And, here are the SQL query problems which you need to solve based upon above schema:

1. Can you write an SQL query to show Employee (names) who have a bigger salary than their manager?

2. Write an SQL query to find Employees who have the biggest salary in their Department?

3. Write an SQL query to list Departments that have less than 3 people in it?

4. Write an SQL query to show all Departments along with the number of people there?

5. Can you write an SQL query to show all Employees that don't have a manager in the same department?

6. Can you write SQL query to list all Departments along with the total salary there?

7. Can you write an SQL query to find the second highest salary of Employee? (solution)

8. How to find all duplicate records from a table? (solution)

9. How do you copy all rows of a table using SQL query? (solution)

10. How do you join more than two tables in SQL query? (solution)

11. How to find 2nd highest salary without using a co-related subquery? (solution)

12. There exists an Order table and a Customer table, find all Customers who have never ordered (solution)

Don't scroll down to look at the solution until you try solving all the problems by yourself. Some of the questions are tricky, so please pay special attention to them.

It's not a real interview you can take your time because all the hard work your mind will put now to find answers by its own will always remain there and that's the real learning you will get by doing this exercise.

Top 12 SQL Query Questions from Interviews for Practice with Solutions

SQL Script to create a table and Populate data

In this section, we'll see our SQL script for creating and populating the sample table required for running SQL queries.

I have chosen Employee and Department tables to teach you how to write SQL queries because it is one of the most popular SQL query examples and most of the developers, data scientists, students, and technical guys are familiar with Employee and Department data model.

This is also the example many of you have used in your academics so it's quite easy to understand and correlate.

Remember, understanding schema and data is very important not only to write correct SQL queries but also to verify that your SQL query is correct by looking at the output.

The SQL queries are written for Microsoft SQL Server database and tested on the same, but you can easily run on Oracle, MySQL, or any other database of your choice by removing T-SQL code e.g. the one which checks if a table already exists, and then drop and re-create it.

Most of the code is standard ANSI SQL, hence it will run as it is on any other database. If you still face any problems then you can also check this guide to migrate SQL Server queries to Oracle.

SQL scripts to create tables

USE Test GO

-- drop Employee table if already exists IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL BEGIN PRINT 'Employee Table Exists, dropping it now' DROP TABLE Employee; END

-- drop Department table if already exists IF OBJECT_ID('dbo.Department', 'U') IS NOT NULL BEGIN PRINT 'Department Table Exists, dropping it now' DROP TABLE Department; END

-- create table ddl statments CREATE TABLE Employee(emp_id INTEGER PRIMARY KEY, dept_id INTEGER, mngr_id INTEGER, emp_name VARCHAR(20), salary INTEGER); CREATE TABLE Department(dept_id INTEGER PRIMARY KEY, dept_name VARCHAR(20));

-- alter table to add foreign keys ALTER TABLE Employee ADD FOREIGN KEY (mngr_id) REFERENCES Employee(emp_id); ALTER TABLE Employee ADD FOREIGN KEY (dept_id) REFERENCES Department(dept_id);

-- populating department table with sample data INSERT INTO Department (dept_id, dept_name) VALUES (1, 'Finance'), (2, 'Legal'), (3, 'IT'), (4, 'Admin'), (5, 'Empty Department');

-- populating employee table with sample data INSERT INTO Employee(emp_id, dept_id, mngr_id, emp_name, salary) VALUES( 1, 1, 1, 'CEO', 100), ( 2, 3, 1, 'CTO', 95), ( 3, 2, 1, 'CFO', 100), ( 4, 3, 2, 'Java Developer', 90), ( 5, 3, 2, 'DBA', 90), ( 6, 4, 1, 'Adm 1', 20), ( 7, 4, 1, 'Adm 2', 110), ( 8, 3, 2, 'Web Developer', 50), ( 9, 3, 1, 'Middleware', 60), ( 10, 2, 3, 'Legal 1', 110), ( 11, 3, 3, 'Network', 80), ( 12, 3, 1, 'UNIX', 200);

This query runs on the Test database, if you don't have the Test database in your SQL Server instance then either create it or remove the "USE Test" to run on any database of your choice, you can also change the name of the database and keep the "USE".

When you run this script, it will create and populate the data the first time. When you run it again, it will drop and recreate the tables again, as shown in the following output:

Employee Table Exists, dropping it now Department Table Exists, dropping it now

(5 row(s) affected)

(12 row(s) affected)

And, here is how our data looks like after setting up:

SQL query Practice Questions online

And, here is how our Department data will look like:

SQL query examples online

In this script, I have followed the naming convention and tricks which I discussed earlier in my article, a better way to write SQL queries. All the keyword is on the capital case while table names and column names are in small and camel case.

This improves the readability of SQL queries by clearing highlight which ones are keywords and which ones are object names even if syntax highlight is not available.

This example shows that just following some simple SQL best practices can seriously improve the queries you write.

Solution of SQL Query Practice Interview Questions

Here is the solution of all SQL query problems discussed in the last section

1. SQL query to show Employee (names) who have a bigger salary than their manager?

In this problem, you need to compare employees' salaries to their manager's salary. To achieve this, you need two instances of the same table. Also in order to find a Manager you need to compare employee id with manager id, this is achieved by using the self-join in SQL, where two instances of the same table are compared.

-- Employees (names) who have a bigger salary than their manager

SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.salary > b.salary;

You can see that Admin 2, and UNIX has higher salary than their boss, CEO who just earn $100. They key here is use of self join, if you have to compare data from the same table then you can create two instance of same table and join them together using self join. An interesting technique to solve this kind of SQL query problem.

SQL Query questions for Tech Interviews

2. SQL query to find Employees who have the biggest salary in their Department?

This is a little bit complex problem to solve, you first need to find the maximum salary of each department, but the department doesn't have the salary, it is the employee who has the salary.

So we need to create a virtual or temp table where we should have both department and salary.

This can be achieved by joining both Employee and Department table on dept_id and then using GROUP by clause to group salary on dept_id. Now, someone can question why we didn't use the self join?

Since we need to print the name of the employee who has the highest salary, we need to compare each employee's salary with the department's highest salary which we have just calculated.

This can be done by keeping the result of the previous query in a temp table and then joining it again with the Employee table.

-- Employees who have the biggest salary in their Department SELECT a.emp_name, a.dept_id FROM Employee a JOIN (SELECT a.dept_id, MAX(salary) as max_salary FROM Employee a JOIN Department b ON a.dept_id = b.dept_id GROUP BY a.dept_id) b ON a.salary = b.max_salary AND a.dept_id = b.dept_id;

SQL Query Problems for Tech Interviews

You can see that CEO, Adm 2, Legal 1, and UNIX has highest salary in their
respective department. You can also print department name as an additional
exercise.

3.SQL query to list Departments that have less than 3 people in it?

This is a rather simple SQL query interview question to solve.
You just need to know how to use the COUNT() function and GROUP BY clause.

-- Departments that have less than 3 people in it SELECT dept_id, COUNT(emp_name) as 'Number of Employee' FROM Employee GROUP BY dept_id HAVING COUNT(emp_name) < 3;

Output:

SQL query GROUP BY and COUNT Example

4. SQL query to show all Departments along with the number of people there?

This is a tricky problem, candidates often use inner join to solve the problem, leaving out empty departments.

-- All Department along with the number of people there SELECT b.dept_name, COUNT(a.dept_id) as 'Number of Employee' FROM Employee a FULL OUTER JOIN Department b ON a.dept_id=b.dept_id GROUP BY b.dept_name;

Output

SQL JOIN Query Examples

5.SQL query to show all Employees that don't have a manager

in the same department?

This is similar to the first SQL query interview question, where we have used self-join to solve the problem. There we compared the salary of employee and here we have compared their department.

-- Employees that don't have a manager in the same department SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.dept_id != b.dept_id;

Output

SQL query for Practice

6.Can you write SQL query to list all Departments along with

the total salary of that department?

This problem is similar to the 4th question in this list. Here also you need to use OUTER JOIN instead of INNER join to include empty departments which should have no salaries.

-- All Department along with the total salary there SELECT b.dept_name, SUM(a.salary) as 'Total Salary' FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id GROUP BY b.dept_name;

Output:

SQL query examples for beginners

And, Here is the output of all these SQL queries when running from SQL Server Management Studio:

SQL Query Interview Questions and Answers

That's all in this article about SQL query examples, practice questions and SQL query interview questions. If you are an interviewer, then it's a really great way to check the SQL skills of a candidate. A defined schema and very clear and simple requirements are what you expect in the short duration of the Interview.

Once the candidate has solved the problem you can even discuss optimization. It's much better than asking him about the difference between left and right joins.

If you are a candidate then it's what you really need to head start your preparation. Many SQL programmers just don't practice SQL queries before going into interviews, which is a big mistake in my opinion.

Even if your core skill is Java or C++, I strongly suggest you brush up your SQL skills before any face-to-face programming interview.

Even though asking candidates to write SQL query is a better way to check his SQL skills, sometimes it also pays to ask theoretical questions just to see if is familiar with essential concepts or not, particularly during phone interviews. I

Other SQL Interview Questions and Answers you may like

If you are interested in general and theory-based SQL interview questions which are mostly asked during telephonic interviews, then you can try the following questions at your leisure:

Thanks for reading this article, if you have liked this article then please share it with your friends and colleagues. If you have tips to improve SQL skills or any interesting SQL query questions from your interview then please share with us via comments.