Thursday, September 15, 2022

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;
----------------------------------------------------------------------




No comments:

Post a Comment

How to use SQL pagination using LIMIT and OFFSET

  How to extract just a portion of a larger result set from a SQL SELECT. LIMIT n is an alternative syntax to the FETCH FIRST n ROWS ONLY. T...