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:
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:
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:
2nd Query Output:
Answer:
Average Salary of all employee:-Select Avg(sal) as averageSalary from emp
Output:
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:
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:
Question 7:Write SQL Query to find duplicate rows and delete from Table?
Answer:
DELETE FROM myTemp WHERE RN > 1;
Duplicate record is getting deleted after executing the query
Answer : Select ename, count(*) as dulicateRowCount from emp group by ename having count(*)>1
Output:
ename
|
dulicateRowCount
| |
1
|
ROOSEVELT
|
2
|
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
6 Comments
Really good blog, shared useful information with example. Also can use below link to get how you can remove duplicate rows.
ReplyDeletehttp://spkichara.blogspot.com/2018/01/remove-duplicate-row-in-sql-query.html
Very Good Post for SQL Interview Queries Questions, Please post more Questions.
ReplyDeleteThanks bro
ReplyDeleteVery intersting stuff thank u sharing ....
data analytics course
data science course
business analytics course
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
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteReally good post for clearing the interview
ReplyDelete