SQL Query Interview Questions for Experienced

In this post i am sharing frequently asked Basic and Complex SQL Query interview questions.I got to know some most asked queries in a SQL tech interview. So, below are some questions

1 Query to find second highest salary of Employee

SELECT MAX(Salary) FROM employee WHERE Salary NOT IN( SELECT MAX(Salary) FROM employee);

2 Query to display the current date

SELECT GetDate();


3 Query to find the number of employees according to gender whose DOB is between 02/04/1983 to 12/05/2000

SELECT COUNT(*), gender from employees WHERE DOB BETWEEN ‘02/04/1983’ AND ‘12/05/2000’;

4 Query to find an employee whose salary is equal to greater than 10000

SELECT empName FROM employees WHERE Salary>=10000;

5 Query to fetch the count of employees working in a project P1

SELECT COUNT(*) FROM employeeSal WHERE Project = ‘P1’;

6 Query to fetch duplicate records from a table

SELECT empId, project, Salary, COUNT(*)
FROM employeeSalary
GROUP BY empId, project, Salary
Having COUNT(*) > 1;

 7.Can you get the list of employees with same salary?

Queries

-- with where clause

select distinct e.empid,e.empname,e.salary
from employee e, employee e1
where e.salary =e1.salary
and e.empid != e1.empid

-- only Salary columns in count and select if -- use group by

select salary, count(salary)
from employee
group by salary
having count(salary)> 1
and salary is not null

8.Query to fetch only odd rows from a table

SELECT E.empId, E.project, E.Salary
FROM (
  SELECT *, Row_Number() OVER(ORDER BY empId) AS RowNumber
  FROM employeeSalary
) E
WHERE E.RowNumber % 2 = 1
Q.8 Query to fetch only even rows from a table

SELECT E.empId, E.project, E.Salary
FROM (
  SELECT *, Row_Number() OVER(ORDER BY empId) AS RowNumber
  FROM employeeSalary
) E
WHERE E.RowNumber % 2 = 0

9 Query to create an empty table with the same structure as some other table

SELECT * INTO newTable FROM employeeDetails WHERE 1 = 0

10 Query to fetch common records between two tables

SELECT * FROM employeeSalary
INTERSECT
SELECT * FROM ManagerSalary

11.How to find duplicate records?

SELECT EMPID,EMPNAME, SALARY, COUNT(*) AS CNT
FROM EMPLOYEE
GROUP BY EMPID,EMPNAME, SALARY
HAVING COUNT(*)>1

12.How to DELETE DUPLICATE RECORDs?

There are multiple options to perform this operation.

using row count to restrict delete only 1 record

set rowcount 1
DELETE FROM EMPLOYEE WHERE EMPID IN (
SELECT EMPID
FROM EMPLOYEE
GROUP BY EMPID,EMPNAME, SALARY
HAVING COUNT(*)>1
)
set rowcount 0

13. Can you write query to find employees with age greater than 30?

select * from employee
where datediff(year,dob, getdate()) >30

14. Query to fetch top n records

SELECT Top N * FROM employeeSalary ORDER BY Salary DESC

15.Can you find N 'th highest Salary?

SELECT * FROM EMPLOYEE E
WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)
FROM EMPLOYEE E1
WHERE E1.SALARY>E.SALARY)

16. can you show 1 row twice in result from a table ?

Yes. We can use union all or cross join to obtain this.

select deptname from department d where d.deptname='it'
union all
select deptname from department d1 where d1.deptname='it'