Wednesday, February 15, 2017

Top 6 SQL Query Interview Questions and Answers

The SQL, 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 which require you to write SQL query for given requirement and many developers struggles 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 recommended a couple of books and websites to improve your SQL query skills but nothing is better than the understanding schema, data and writing your own SQL queries.

In order to learn fast, start with a small table with few columns which include data types like number, date, and String, has less number of 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 SQL script to create a table and populate with sample data and then write SQL queries to solve some common problems from Interviews.




SQL Script to create table and Populate data

In this section, we'll see our SQL script for creating and populating sample table required for running SQL queries. I have chosen Employee and Department table to teach you how to write SQL queries because it is one of the most popular SQL query examples and most of the developers, students, and technical guys are familiar with this scheme.

This is also the example many of you have used in your academics so it's quite easy to understand and correlate. Remember, understanding of 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 2014 and tested on 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 problem 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 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)

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. If you are interested in learning more SQL best practices, I suggest reading SQL Antipatterns, an interesting book for both beginners and experienced programmers.

Top 6 SQL Query Interview Questions and Answers


SQL Query Interview Questions

It's the time write SQL queries now. This section contains 6 SQL query Interview questions which will test many of your SQL skills e.g. 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 6 problems for which you need to write SQL queries, the solution is provided in the next section but I suggest you to try to solve these problems first before looking at the solution.

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 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 you 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.

Btw, if you are interested in more SQL query interview questions, then you can also check Joe Celko's SQL Puzzles and Answers, very interesting and challenging to really test your SQL skills.

Top 6 SQL Interview Questions and Answers



Solution of SQL Query Interview Questions

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

1) In this problem, you need to compare employee's salary to their manager's salary. To achieve this, you need two instances of the same table. Also in order to find 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;
2) 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 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 

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 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;


3) 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;
4) 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;


5) 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;


6) 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;

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

 6 SQL Interview Questions and Answers


That's all in this article about SQL query interview questions. If you are an interviewer, then it's really great way to check SQL skills of a candidate. A defined schema and very clear and simple requirements are what you expect on the short duration of 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 query 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. If you want to do really well, I suggest solving SQL problems from Joe Celko's SQL Puzzels.

SQL Query Interview Questions and Answers


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. If you are interested in general and theory based SQL interview questions which are mostly asked during telephonic interviews, then you can try following questions at your leisure:

  • What is the difference between UNION and UNION ALL in SQL? (answer)
  • What is the difference between WHERE and HAVING clause in SQL? (answer)
  • What is difference between rank(), row_number(), and dense_rank() in SQL? (answer)
  • What is the difference between TRUNCATE and DELETE command in SQL? (answer)
  • How to compare date columns in SQL? (answer)
  • What is the difference between the Primary and Foreign key in SQL? (Answer)
  • What is the difference between view and materialized view? (answer)

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

4 comments :

Raman said...

I think there's some error in the query for adding foreign key constraint
ALTER TABLE Employee ADD FOREIGN KEY (mngr_id) REFERENCES Employee(emp_id);

Because this would imply that the mngr_id and emp_id fields in the Employee table to be same.

gaurav khurana said...

For Q2, we can simplify it using

select emp_id, dept_id, salary from Employee e where
salary = (select MAX(salary) from Employee ee where e.dept_id = ee.dept_id)

Javin Paul said...

@Gaurav, yes, you can do that, unless you don't have to print the department name which is in the department table. In that case you need to join, but since dept_id already exists in Employee table above query is ok.

Vamshi Krishna said...




Very Useful information that i have found. don't stop sharing and Please keep updating us..... Thanks

Post a Comment