SQL Query interview Question.

 

1. Query to find the highest salary.

SELECT MAX(SALARY) FROM EMPLOYEE ORDER BY SALARY DESC

----------------------------------------------------------------------------------------------------

Nth number of highest salary using DENSE_RANK function

SELECT SALARY,

           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK

FROM EMPLOYEE

TO FIND NTH HIGHEST SALARY USING CTE:

select * from(
select ename, sal, dense_rank() 
over(order by sal desc)r from Employee) 
where r=&n;

---------------------------------------------------

How to Get the Highest Salary of Each Department?

Please use the SQL Query below in order to get the highest salary of department:

SELECT DepartmentID, MAX(Salary) 
FROM Employee  
GROUP BY DepartmentID


Now for Printing the name, you must join the Employee table with Department table using key DepartmentID,


SELECT DepartmentName, MAX(Salary) 
FROM Employee e RIGHT JOIN Department d ON e.DepartmentId = d.DepartmentID 
GROUP BY DepartmentName


------------------------------------------------------------------------------

 Write a query to get the current date.
SELECT GETDATE();

-----------------------------------------------------

Write a query to retrieve the first four characters of  EmpLname from the EmployeeInfo table.

SELECT SUBSTRING(EmpLname, 1, 4) FROM EmployeeInfo;

--------------------------------------------------------------

Write a query to find the names of employees that begin with ‘S’

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';

------------------------------------------------------
Write a query to fetch the department-wise count of employees sorted by department’s count in ascending order.

SELECT Department, count(EmpID) AS EmpDeptCount 
FROM EmployeeInfo GROUP BY Department 
ORDER BY EmpDeptCount ASC;

-----------------------------------------------------

Write a query to retrieve duplicate records from a table.

SELECT EmpID, EmpFname, Department COUNT(*) 
FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 
HAVING COUNT(*) > 1;

--------------------------------------------------------------


Write a query to fetch 50% records from the EmployeeInfo table.

SELECT * 
FROM EmployeeInfo WHERE
EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);



------------------------------------------------------

 Write an SQL query to remove duplicates from a table without using a temporary table.

DELETE E1 FROM EmployeeDetails E1
INNER JOIN EmployeeDetails E2 
WHERE E1.EmpId > E2.EmpId 
AND E1.FullName = E2.FullName 
AND E1.ManagerId = E2.ManagerId
AND E1.DateOfJoining = E2.DateOfJoining
AND E1.City = E2.City;


-----------------------------------------------------------------

Ques.35. Write an SQL query to fetch only even rows from the table.
Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-
SELECT * FROM EmployeeDetails 
WHERE MOD (EmpId, 2) = 0;

----------------------------------------------------------------------