Top SQL Query Interview Questions and Answers with Examples-Part 1

In this Post, I am writing some examples of SQL queries which is asked when we go for the interviews, these are very useful for Java developer position, QA, BA, supports professional, project manager or any other technical position,depending on experience also.

We have write down some Practice SQL Queries with Answers for Employee and Department Table in different parts, as we do not want to make my post lengthy and boring. Each post will contain some Practice SQL Queries with Solutions For Employee and Dept Table. 




Create Employee Table

CREATE TABLE emp (

empno INT PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT NULL,
hiredate DATETIME,
sal NUMERIC(7,2),
comm NUMERIC(7,2) NULL,
deptNo INT)



Insert records in Employee Table


insert into emp values

    (1,'Ankit','ADMIN',6,'12-17-1990',18000,NULL,4)
insert into emp values
    (2,'Jitendra','MANAGER',9,'02-02-1998',52000,300,3)
insert into emp values
    (3,'Arpit','SALES I',2,'01-02-1996',25000,500,3)
insert into emp values
    (4,'Abhishek','SALES I',2,'04-02-1990',27000,NULL,3)
insert into emp values
    (5,'Naveen','TECH',6,'06-23-1994',22500,1400,4)
insert into emp values
    (6,'Prashant','MANAGER',9,'05-01-1993',54000,NULL,4)
insert into emp values
    (7,'Tarun','TECH',6,'09-22-1997',25000,NULL,4)
insert into emp values
    (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)
insert into emp values
    (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)
insert into emp values
    (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2)
insert into emp values
    (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)
insert into emp values
    (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)
insert into emp values
    (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)
insert into emp values
    (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)
insert into emp values


    (15,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)


Create Department Table


CREATE TABLE dept (

deptno INT NOT NULL,
dname VARCHAR(14),
loc VARCHAR(13))


Insert records in Department Table


insert into dept values (1,'ACCOUNTING','ST LOUIS')

insert into dept values (2,'RESEARCH','NEW YORK')
insert into dept values (3,'SALES','ATLANTA')
insert into dept values (4, 'OPERATIONS','SEATTLE')


Retrive results from both Tables


Select * from emp

Select * from Dept
--------------------------------------------------------------------------------------------------------------------------

Output:


empno
ename
job
mgr
hiredate
sal
comm
deptNo
1
1
Ankit
ADMIN
6
17.12.1990 00:00:00
18000,00
NULL
4
2
2
Jitendra
MANAGER
9
02.02.1998 00:00:00
52000,00
300,00
3
3
3
Arpit
SALES I
2
02.01.1996 00:00:00
25000,00
500,00
3
4
4
Abhishek
SALES I
2
02.04.1990 00:00:00
27000,00
NULL
3
5
5
Naveen
TECH
6
23.06.1994 00:00:00
22500,00
1400,00
4
6
6
Prashant
MANAGER
9
01.05.1993 00:00:00
54000,00
NULL
4
7
7
Tarun
TECH
6
22.09.1997 00:00:00
25000,00
NULL
4
8
8
GRANT
ENGINEER
10
30.03.1997 00:00:00
32000,00
NULL
2
9
9
JACKSON
CEO
NULL
01.01.1990 00:00:00
75000,00
NULL
4
10
10
FILLMORE
MANAGER
9
09.08.1994 00:00:00
56000,00
NULL
2
11
11
ADAMS
ENGINEER
10
15.03.1996 00:00:00
34000,00
NULL
2
12
12
WASHINGTON
ADMIN
6
16.04.1998 00:00:00
18000,00
NULL
4
13
13
MONROE
ENGINEER
10
03.12.2000 00:00:00
30000,00
NULL
2
14
14
ROOSEVELT
CPA
9
12.10.1995 00:00:00
35000,00
NULL
1
15
15
ROOSEVELT
CPA
9
12.10.1995 00:00:00
35000,00
NULL
1


  
deptno
dname
loc
1
1
ACCOUNTING
ST LOUIS
2
2
RESEARCH
NEW YORK
3
3
SALES
ATLANTA
4
4
OPERATIONS
SEATTLE


Question 1: Write SQL Query to find out second highest salary of Employee Table Solution: select MAX(sal) as MaxSalary from Emp WHERE sal NOT IN (select MAX(sal) from Emp ); or select Top 1 sal as MaxSalary from Emp WHERE sal NOT IN (select MAX(sal) from Emp ) order by sal desc; or select Top 1 sal as MaxSalary from Emp WHERE sal IN (select Distinct Top 2 sal from Emp order by sal desc ) order by sal; Output:

  
MaxSalary
1
56000,00


Question 2: Write SQL Query to find out Max Salary from each department.


Answer: We can find the maximum salary for each department by grouping all records by Department Id and then using MAX() function to calculate maximum salary in each department.
Select deptNo,max(Sal) from emp group by deptNo

-if require department name instead of deptid then we need to apply join
Select d.dname,max(Sal) from emp e right join dept d on e.deptNo=d.deptNo group by d.dname

-we have used RIGHT OUTER JOIN because we need the name of the department from Department table which is on the right side of JOIN clause, even if there is no reference of dept_id on Employee table.


Output:


Ist Query Output:


  
deptNo
(No column name)
1
1
35000,00
2
2
56000,00
3
3
52000,00
4
4
75000,00

2nd Query Output:

  
dname
(No column name)
1
ACCOUNTING
35000,00
2
OPERATIONS
75000,00
3
RESEARCH
56000,00
4
SALES
52000,00


Question 3: find all employees which are also manager? .
Answer : we can use self join to solve the problem


SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno;
 or
SELECT e.ename, m.ename FROM emp e join emp m on e.mgr = m.empno;

Output:
  
ename
ename
1
Ankit
Prashant
2
Jitendra
JACKSON
3
Arpit
Jitendra
4
Abhishek
Jitendra
5
Naveen
Prashant
6
Prashant
JACKSON
7
Tarun
Prashant
8
GRANT
FILLMORE
9
FILLMORE
JACKSON
10
ADAMS
FILLMORE
11
WASHINGTON
Prashant
12
MONROE
FILLMORE
13
ROOSEVELT
JACKSON


We use left outer join, this will show  employees without managers.

SELECT e.ename as Employee, m.ename as Manager FROM emp e left outer join emp m on e.mgr = m.empno;


Employee
Manager
1
Ankit
Prashant
2
Jitendra
JACKSON
3
Arpit
Jitendra
4
Abhishek
Jitendra
5
Naveen
Prashant
6
Prashant
JACKSON
7
Tarun
Prashant
8
GRANT
FILLMORE
9
JACKSON
NULL
10
FILLMORE
JACKSON
11
ADAMS
FILLMORE
12
WASHINGTON
Prashant
13
MONROE
FILLMORE
14
ROOSEVELT
JACKSON
15
ROOSEVELT
JACKSON


Question 4 : Find the employee name whose salary is greater then  all employee average Salary

Answer:
Average Salary of all employee:-Select Avg(sal) as averageSalary from emp


Select * from emp where sal > (Select Avg(sal) from emp)

Output:
  
empno
ename
job
mgr
hiredate
sal
comm
deptNo
1
2
Jitendra
MANAGER
9
02.02.1998 00:00:00
52000,00
300,00
3
2
6
Prashant
MANAGER
9
01.05.1993 00:00:00
54000,00
NULL
4
3
9
JACKSON
CEO
NULL
01.01.1990 00:00:00
75000,00
NULL
4
4
10
FILLMORE
MANAGER
9
09.08.1994 00:00:00
56000,00
NULL
2

Question 5:Write SQL Query to find duplicate value in a column(ename)of Table?
Answer : Select ename, count(*) as dulicateRowCount from emp group by ename having count(*)>1 

Output:                                             
  
ename
dulicateRowCount
1
ROOSEVELT
2
Question 6:Write SQL Query to find duplicate rows in a Table?
Answer:

   SELECT ename,ROW_NUMBER() OVER (PARTITION BY ename, job, mgr, hiredate,sal,comm,deptNo ORDER BY empno) RN FROM emp 

output:

  
ename
RN
1
Abhishek
1
2
ADAMS
1
3
Ankit
1
4
Arpit
1
5
FILLMORE
1
6
GRANT
1
7
JACKSON
1
8
Jitendra
1
9
MONROE
1
10
Naveen
1
11
Prashant
1
12
ROOSEVELT
1
13
ROOSEVELT
2
14
Tarun
1
15
WASHINGTON
1

Question 7:Write SQL Query to find duplicate rows and delete from Table?

Answer:

WITH myTemp
AS ( SELECT ROW_NUMBER() OVER (PARTITION BY ename,job,mgr,hiredate, sal,comm,deptNo ORDER BY empno) RN FROM emp)
DELETE FROM myTemp WHERE RN > 1;


Duplicate record is getting deleted after executing the query

Post a Comment

6 Comments

  1. Really good blog, shared useful information with example. Also can use below link to get how you can remove duplicate rows.

    http://spkichara.blogspot.com/2018/01/remove-duplicate-row-in-sql-query.html

    ReplyDelete
  2. Very Good Post for SQL Interview Queries Questions, Please post more Questions.

    Thanks bro

    ReplyDelete
  3. Such an excellent and interesting information in your blog, it is awesome to read and do post like this with more informations. Salesforce Training Dallas   

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Really good post for clearing the interview

    ReplyDelete
Emoji
(y)
:)
:(
hihi
:-)
:D
=D
:-d
;(
;-(
@-)
:P
:o
:>)
(o)
:p
(p)
:-s
(m)
8-)
:-t
:-b
b-(
:-#
=p~
x-)
(k)