SQL Queries Interview Questions

Q1. Second Highest salary of employee

select

distinct top 1 *

from

employees

where

Salary < (Select MAX(Salary) from employees)

order by Salary desc

select

distinct top 1 *

from

employees

where

Salary < (Select distinct top 1 salary from employees order by Salary desc)

order by Salary desc

Select

top 1 *

from (

Select distinct top 2 * 

from 

employees

order by Salary desc

) innerQuery

order by salary asc

Select emp.*

from (

Select *, row_number() OVER (Order by salary desc

) as rn from employees) innerQuery

inner join employees emp on emp.id = innerQuery.id

where innerQuery.rn =2

Q2. Select Third Highest Data

select

distinct top 1 *

from

(Select distinct top 3 * from employees order by salary desc) innerQuery

order by salary asc

Q3. Select Duplicate Data

Select * 

from employees

where salary in 

(

Select salary from employees group by salary having count(salary) > 1

)

order by salary desc

select employees.*

from

(

select max(emp1.id) empId

from employees emp1

inner join employees emp2

on emp1.salary = emp2.salary

and emp1.id <> emp2.id

group by emp1.salary

) innerQuery

inner join employees

on innerQuery.empId = employees.id

order by employees.id

Q4. Delete Duplicate Data

Delete

from employees

where id in (

Select max(id) from employees group by salary having count(salary) > 1

)

Delete employees

from

(

select max(emp1.id) empId

from employees emp1

inner join employees emp2

on emp1.salary = emp2.salary

and emp1.id <> emp2.id

group by emp1.salary

) innerQuery

inner join employees

on innerQuery.empId = employees.id

Q5. Fetch monthly salary if annual salary is given

Select *, salary/12 as monthlySalary from employees

Q6. How to fetch top 3 highest salary

select * from 

(Select *, row_number() over (order by salary desc) rn from employees) innerQuery

where innerQuery.rn <=3

select top 3 * from employees order by salary desc

Q7. Select employee details having odd id

select * from employees where id%2=1

Q8. Select employee details having even id

select * from employees where id%2=0

Q9. How Can I create table with same structure of table without data

select * into NEW_TABLE from EXISTING_TABLE where 1=2

Q10. How Can I create table with same structure of table with data

select * into NEW_TABLE from EXISTING_TABLE

Q11. Display last 50% records from Employee table

select * from

(Select *,row_number() over(order by id) rn from employees) innerQuery

where innerQuery.rn >= (select count(id)/2 from employees)

Q12. How do I fetch only common records between 2 tables

select * from employees

intersect

select * from employees_new

Q13. Find Query to get information of Employee where Employee is not assigned to the department

select * from employees where deptid not in (select id from department)

select * from employees

except

select emp.* from employees emp

inner join department dep

on emp.deptid = dep.Id

select emp.* from employees emp

left join department dep

on emp.deptid = dep.Id

where dep.Id is null

Q14. How to get distinct records from the table without using distinct keyword.

select * from employees where id in (select max(id) from employees group by FirstName, LastName)

Q15. Select all records from Employee table whose name is ‘Jitendra’ and ‘Ram’

select * from employees where FirstName in ('Jitendra', 'Ram')

Q16. How to fetch all the records from Employee whose joining year is  2017

select * from employees where substring(convert(varchar,DOJ,103),7,4)='2017'

select * from employees where DOJ like '2017%'

Q17. What is SQL Query to find maximum salary of each department?

select max(emp.Salary), dep.DepartmentName from employees emp

inner join department dep

on emp.deptid = dep.Id

group by dep.DepartmentName

select max(emp.Salary), emp.deptid from employees emp group by emp.deptid

Q18. How Do you find all Employees with its managers?

select emp.FirstName + ' ' + emp.LastName as EmpName, manager.FirstName + ' ' + manager.LastName as ManagerName

from employees emp, employees manager

where emp.ManagerId = manager.id

select emp.FirstName + ' ' + emp.LastName as EmpName, manager.FirstName + ' ' + manager.LastName as ManagerName

from employees emp

inner join employees manager

on emp.ManagerId = manager.id

Q19. How to find count of duplicate rows

select FirstName, count(FirstName) cnt from employees group by FirstName, LastName, Location having count(FirstName) > 1

Q20. How to Find the Joining date of Employee in YYYY-DAY-Date format.

select SUBSTRING(CONVERT(VARCHAR, DOJ, 121), 1, 4) + '-' + SUBSTRING(CONVERT(VARCHAR, DOJ, 100), 1, 3) + '-' + SUBSTRING(CONVERT(VARCHAR, DOJ, 103), 1,2) from employees

Q20. Display current date in different format

select CONVERT(VARCHAR, GETDATE(), 100) '100'

select CONVERT(VARCHAR, GETDATE(), 101) '101'

select CONVERT(VARCHAR, GETDATE(), 102) '102'

select CONVERT(VARCHAR, GETDATE(), 103) '103'

select CONVERT(VARCHAR, GETDATE(), 104) '104'

select CONVERT(VARCHAR, GETDATE(), 105) '105'

select CONVERT(VARCHAR, GETDATE(), 106) '106'

select CONVERT(VARCHAR, GETDATE(), 107) '107'

select CONVERT(VARCHAR, GETDATE(), 108) '108'

select CONVERT(VARCHAR, GETDATE(), 109) '109'

select CONVERT(VARCHAR, GETDATE(), 110) '110'

select CONVERT(VARCHAR, GETDATE(), 111) '111'

select CONVERT(VARCHAR, GETDATE(), 112) '112'

select CONVERT(VARCHAR, GETDATE(), 113) '113'

select CONVERT(VARCHAR, GETDATE(), 114) '114'

select CONVERT(VARCHAR, GETDATE(), 120) '120'

select CONVERT(VARCHAR, GETDATE(), 121) '121'

select CONVERT(VARCHAR, GETDATE(), 126) '126'

select CONVERT(VARCHAR, GETDATE(), 127) '127'

select CONVERT(VARCHAR, GETDATE(), 130) '130'

select CONVERT(VARCHAR, GETDATE(), 131) '131'

Output

May 16 2021 10:41PM

05/16/2021

2021.05.16

16/05/2021

16.05.2021

16-05-2021

16 May 2021

May 16, 2021

22:41:11

May 16 2021 10:41:11:850PM

05-16-2021

2021/05/16

20210516

16 May 2021 22:41:11:850

22:41:11:850

2021-05-16 22:41:11

2021-05-16 22:41:11.850

2021-05-16T22:41:11.850

2021-05-16T22:41:11.850

 5 ???? 1442 10:41:11:850PM

 5/10/1442 10:41:11:850PM