What are Top 20 SQL queries asked in interview ?

In my previous article I have given the details about data validation interview questions as well as the SQL queries for banking domain asked in interview. In this article I would like to give the SQL queries asked in interview . These are nothing but the list of queries which has been asked in interview. These are nothing but the multiple complex sql interview questions as well as some newly added interview questions related to SQL and PL SQL. The following queries are Top 20 queries asked in interview .

Question 1 : How to calculate second highest salary for employee? Kindly give me two ways to do it.

Answer :

Query 1 :

Select distinct Salary from Employees e1 where 2=

Select count(distinct Salary) from Employees e2 where e1.salary<=e2.salary;

Query 2 :

Using Rank function :

SELECT Salary FROM ( SELECT e.Salary, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employees e ) WHERE rn = 2;

Question 2 : How to calculate second highest salary using correlated subquery?

Answer :

SELECT name, salary FROM Employee e1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e2.salary > e1.salary)SELECT name, salary FROM Employee e1 WHERE 2-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2 WHERE e2.salary > e1.salary);

Question 3 : How to find duplicate records in SQL?

Answer :

SELECT    Employee_Num,    COUNT(Employee_Num)FROM    EmployeesGROUP BY Employee_NumHAVING COUNT(Employee_Num) > 1;

Question 4 : What is query to find the the employees whos first name starts with S,P,A?

Answer :

Select * from Employees where name like ‘[SPA]%’;

Question 5 : How to find the first 50% records from Employee table?

Answer :

Select rownum,e.* from Employee E where Rownum <=(Select count(*)/2 from Employee);

Question 6 : How to fetch last record from Employee table?

Answer :

Select * from Employee where Rowid= select max(Rowid) from Employee;

Question 7 : How to fetch common records from Employee and Employee_Master without using joining condition?

Answer :

Select * from Employee

Intersect

Select * from Employee_Master;

Question 8 : What is query to fetch distinct records from Employee table without using distinct keyword?

Answer :

select * from Employee a where  rowid = (select max(rowid) from Employee b where  a.emp_id=b.emp_id);

Question 9 : What is query to find the employees whose name is amit and salary is greater than 50000?

Answer :

Select * from Employee where first_name= ‘Amit’ and salary > 50000;

Question 10 : How to find all users who is using employee database?

Answer :

SELECT Username FROM All_Users  where database_name = ‘Employee’ ORDER BY Username;

Question 11 : How to display the hire_Date of employee in dd-mm-yyyy format?

Answer :

Select to_date (Hire_date,’DD-MON-YYYY’) Date_Format from Employee;

Question 12 : How do you get number of weekends for current month?

Answer :

SELECT count (*) AS Weekends FROM

(SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt

FROM Dual

CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1

)

Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

Question 13 : How to find employee password and replace those passwords by ‘*’?

Answer :

SELECT REPLACE(Password, ‘ ‘, ‘*’) FROM Employees;

Question 14 : How to find the employees who are taking salaries ( Tip : Consider there are two tables – Employee and Employee_Salary)

Answer :

SELECT Emp_id, Employee_name FROM Employees where Emp_ID IN (SELECT EmpId FROM Employee_Salary);

Question 15 : Question 14 : How to find the employees who are not taking salaries ( Tip : Consider there are two tables – Employee and Employee_Salary)

Answer :

SELECT Emp_id, Employee_name FROM Employees where Emp_ID NOT IN (SELECT EmpId FROM Employee_Salary);

Question 16 : How to combine Employee id and manager id together and add that in Employee_master table.

Answer :

insert Emp_man_id in to Employee master as

SELECT CONCAT(EmpId, ManagerId) as NewId FROM Employees;

Question 17 : Suppose there are 3 records in Employee table ‘Amit’ , ‘Amit’ and rohit . IF i want to update record named’ Amit’ to ‘Rohit’ and ‘Rohit’ to ‘Amit’ How to query that?

Answer :

Update Employees set Employee_name = case when Employee_name=’Amit’ then ‘Rohit’ when Employee_name= ‘Rohit’ then ‘Amit’ End;

Question 18 : How to find count of total occurances of letter ‘A’ From Employee table?

Answer :

SELECT Full_Name, LENGTH(Full_Name) – LENGTH(REPLACE(Full_Name, ‘A’, ”)) FROM Employees;

Question 19 : How to find the last day of previous month ?

Answer :

Select LAST_DAY (ADD_MONTHS (SYSDATE,-1)) from dual;

Question 20 : Find the list of employees who joined in 2021?

Answer :

SELECT * FROM Employees WHERE Hire_date BETWEEN ‘2021/01/01’ AND ‘2021/12/31’;

These are above some important SQL queries for interview asked in 2021. I hope you like this article. If you like this article or if you have any issues with the same kindly comment in comments section.