10/7/2017 Top 6 SQL Query Interview Questions and Answers

Javarevisited
Blog about Java programming language, FIX Protocol, Tibco RV

Home core java spring hibernate collections multithreading design patterns interview questions coding data structure OOP java 8 books About Me

W E D N E S D A Y, F E B R U A R Y 1 5 , 2 0 1 7

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.

Create GST Compliant Invoices

Intuit® QuickBooks India Follow by Email

Email address... Submit

Interview Questions

Get Paid Faster with GST-Ready Invoicing. Start Your Free core java interview question (170)
Coding Interview Question (63)
Trial Today!
data structure and algorithm (56)

quickbooks.in/Accounting/Software interview questions (48)

SQL Interview Questions (30)
thread interview questions (30)
design patterns (27)
object oriented programming (27)
collections interview questions (25)
SQL Script to create table and Populate data database interview questions (17)
servlet interview questions (17)
In this section, we'll see our SQL script for creating and populating sample table required for
spring interview questions (13)
running SQL queries. I have chosen Employee and Department table to teach you how to
Programming interview question (7)
write SQL queries because it is one of the most popular SQL query examples and most of the
hibernate interview questions (6)
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
http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 1/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers
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
JACKYLED Pack of 50 Antistatic Bag Resealable …
problem then you can also check this guide to migrate SQL Server queries to Oracle.
$5.99

Best of Javarevisited
Create GST Compliant Invoices How Spring MVC works internally?
How to design a vending machine in Java?
Intuit® QuickBooks India
How HashMap works in Java?
Why String is Immutable in Java?
10 Articles Every Programmer Must Read
How to convert lambda expression to method
Get Paid Faster with GST-Ready Invoicing. Start Your Free reference in Java 8?
Trial Today! 10 Tips to improve Programming Skill
10 OOP design principles programmer should know
quickbooks.in/Accounting/Software How Synchronization works in Java?
10 tips to work fast in Linux
5 Books to improve Coding Skills
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; JACKYLED Pack of 50 Antistatic Bag Resealable …
END
$5.99
-- drop Department table if already exists
Search This Blog
IF OBJECT_ID('dbo.Department', 'U') IS NOT NULL
BEGIN Search
PRINT 'Department Table Exists, dropping it now'
Translate this blog
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),
http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 2/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers
( 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),
JACKYLED PACK OF 50
( 11, 3, 3, 'Network', 80), ANTISTATIC BAG …
( 12, 3, 1, 'UNIX', 200);
$5.99

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 USB TYPE C
CABLE,SNOWKIDS USB …
again, it will drop and recreate the tables again, as shown in the following output: $9.99

Employee Table Exists, dropping it now

Department Table Exists, dropping it now

(5 row(s) affected)
AWINNER MICRO USB 3.0
DATA CABLE FOR …
(12 row(s) affected) $5.78

Java Tutorials
In this script, I have followed the naming convention and tricks which I discussed earlier in my
date and time tutorial (21)
article, a better way to write SQL queries. All the keyword is on the capital case while table
FIX protocol tutorial (16)
names and column names are in small and camel case. This improves the readability of SQL
java collection tutorial (70)
queries by clearing highlight which ones are keywords and which ones are object names even
java IO tutorial (25)
if syntax highlight is not available.
Java JSON tutorial (9)
Java multithreading Tutorials (50)
This example shows that just following some simple SQL best practices can seriously improve
Java Programming Tutorials (23)
the queries you write. If you are interested in learning more SQL best practices, I suggest
Java xml tutorial (12)
reading SQL Antipatterns, an interesting book for both beginners and experienced
programmers.

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.

http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 3/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers
This section contains 6 problems for which you need to write SQL queries, the solution is Subscribe to Download the E-book

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?
Download Building a REST API with
5. Can you write an SQL query to show all Employees that don't have a manager in the same The E-book Spring 4?
department?
Email address... Submit

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

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)
Blog Archive

▼ 2017 ( 140 )
11. How to find 2nd highest salary without using a co-related subquery? (solution)
► October ( 1 )
► September ( 4 )
12. There exists an Order table and a Customer table, find all Customers who have never
► August ( 15 )
ordered (solution)
► July ( 13 )
► June ( 16 )
Don't scroll down to look the solution until you try solving all the problems by yourself. Some
► May ( 10 )
of the questions are tricky, so please pay special attention to them. It's not a real interview
► April ( 26 )
you can take your time because all the hard work you mind will put now to find answers by its
► March ( 15 )
own will always remain there and that's the real learning you will get by doing this exercise.
▼ February ( 16 )
Difference between Executor, ExecutorService
Btw, if you are interested in more SQL query interview questions, then you can also check Joe and E...
Celko's SQL Puzzles and Answers, very interesting and challenging to really test your SQL String Deduplication of G1 Garbage collector to
Sa...
skills.
5 Differences between COALESCE and ISNULL in
SQL S...
5 Difference between Constructor and Static
Factor...
Top 5 JavaScript Books to Learn - Best of Lot,
Mus...
10 points about volatile modifier or field in
Java...
Insertion Sort Algorithm in Java with Example
and ...
Difference between Comparison (QuickSort) and
Non-...
Top 5 Core Java Books for Beginners - Learn Best
o...
Top 6 SQL Query Interview Questions and Answers
Top 5 Websites for Practicing Data structures and
...
How to Consume JSON from RESTful Web Service
and C...
17 Examples of Calendar and Date in Java
Top 10 Java wait, notify, Locking and
Synchronizat...
Top 5 Books to learn REST and RESTful
Solution of SQL Query Interview Questions WebServices ...
Difference between JAX-RS, Restlet, Jersey,
RESTEa...

http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 4/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers
Here is the solution of all SQL query problems discussed in the last section ► January ( 24 )

► 2016 ( 166 )
1) In this problem, you need to compare employee's salary to their manager's salary. To ► 2015 ( 126 )
achieve this, you need two instances of the same table. Also in order to find Manager you ► 2014 ( 100 )
need to compare employee id with manager id, this is achieved by using the self-join in SQL, ► 2013 ( 127 )
where two instances of the same table are compared. ► 2012 ( 214 )
► 2011 ( 135 )
► 2010 ( 30 )
-- 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 Pages

WHERE a.salary > b.salary; Privacy Policy

2) This is a little bit complex problem to solve, you first need to find the maximum salary of Copyright by Javin Paul 2010-2017. Powered by
Blogger.
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;
http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 5/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers

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:

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.

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)

http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 6/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers
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.

A Kid’s Book Dwarf Cherry Tree (Prunus 10 Seeds Dwarf Peach Fruit 20 orange seeds dwarf
About...Avocados! Fun avium) Self-Fertile Frui… Tree Indoor/Outdoor Bonsai Mandarin Orange
Facts, Photos, and Recipes Seeds Edible Fruit tree for
(for 6-12 years old) (Plant … $5.99 $1.39 home garden
$0.99 (2) (67) $10.99

Ads by Amazon

You May Like Sponsored Links by Taboola

chennai to hyderabad Flights

Rs.1,022 - yatra.com

Best Online Virtual World - Join Now!

Second Life

India's Cheap Hotel Finder

Save70.com

Talegaon ka Icon Dekha kya? Check the most Premium Project of Talegaon - Apartments
Starting 28.6 Lacs only.
Namrata’s Aikonic

Check Your Name & DOB Compatibility. It Is Very Essential For Your Luck.
Knowastro.com

Join now to watch the single-camera comedy The Mindy Project.

Amazon Prime Video

Check Out the Latest Outfits for Women: upto 30% off
Once upon a trunk

Looking For A Credit Card? Fill This Form to Get A CallBack

Citi Bank Credit Card

Posted by Javin Paul at 6:33:00 AM

Labels: SQL , SQL Interview Questions

4 comments :

http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 7/8
10/7/2017 Top 6 SQL Query Interview Questions and Answers
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.

February 20, 2017 at 5:59 PM

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)

April 23, 2017 at 5:57 PM

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.

April 23, 2017 at 6:45 PM

Vamshi Krishna said...

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

April 29, 2017 at 3:21 AM

Post a Comment

Enter your comment...

Comment as: Select profile...

Publish Preview

Newer Post Home Older Post

Subscribe to: Post Comments ( Atom )

hyderabad to
chennai to tirupati to
bhubaneswar
hyderabad Flights hyderabad Flights
Flights

Rs 2,000 Rs 1,022 Rs 1,710

http://javarevisited.blogspot.in/2017/02/top-6-sql-query-interview-questions-and-answers.html 8/8