SQL Server Query Related Questions and Answers

1. Write an SQL query to print the first three characters of  FIRST_NAME from Worker table.

Select substring(FIRST_NAME,1,3) from Worker;

2. Write an SQL query to find the position of the alphabet (‘a’) in the first name column ‘Amitabh’ from Worker table.

Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Amitabh';

3. Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.

Select REPLACE(FIRST_NAME,'a','A') from Worker;

4. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.

Select * from Worker where FIRST_NAME in ('Vipul','Satish');

5. Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker table.

Select * from Worker where FIRST_NAME not in ('Vipul','Satish');

6. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin”.

Select * from Worker where DEPARTMENT like 'Admin%';

7. Write an SQL query to fetch the no. of workers for each department in the descending order.

SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers 

FROM worker 

GROUP BY DEPARTMENT 

ORDER BY No_Of_Workers DESC;

8. Write an SQL query to determine the nth (say n=5) highest salary from a table.

SELECT TOP 1 Salary

FROM (

 SELECT DISTINCT TOP n Salary

 FROM Worker 

 ORDER BY Salary DESC

 ) as temp

ORDER BY Salary ASC;

9. Write an SQL query to fetch nth max salaries from a table.

SELECT distinct Salary from worker a WHERE n >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;

10. Write an SQL query to show the second highest salary from a table.

Select max(Salary) from Worker 

where Salary not in (Select max(Salary) from Worker);

11. Write an SQL query to fetch the list of employees with the same salary.

Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary 

from Worker W, Worker W1 

where W.Salary = W1.Salary 

and W.WORKER_ID != W1.WORKER_ID;

12. Write an SQL query to fetch the first 50% records from a table.

SELECT * FROM WORKER

WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker);

13. Write an SQL query to fetch the departments that have less than five people in it.

SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' 

FROM Worker 

GROUP BY DEPARTMENT 

HAVING COUNT(WORKER_ID) < 5;

14. Write an SQL query to show all departments along with the number of people in there.

SELECT DEPARTMENT, COUNT(DEPARTMENT) as 'Number of Workers' 

FROM Worker 

GROUP BY DEPARTMENT;

15. Write an SQL query to print the name of employees having the highest salary in each department.

SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by DEPARTMENT) as TempNew 

Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT 

 and TempNew.TotalSalary=t.Salary;

16. Delete duplicate rows from a SQL Server Table?

DELETE FROM [Employee]

    WHERE ID NOT IN

    (

        SELECT MAX(ID) AS MaxRecordID

        FROM [Employee]

        GROUP BY [FirstName],[LastName],[Country]

    );

Using CTE:

WITH CTE([FirstName],[LastName],[Country],DuplicateCount)

AS (SELECT [FirstName],[LastName],[Country],

           ROW_NUMBER() OVER(PARTITION BY [FirstName],[LastName],[Country]

           ORDER BY ID) AS DuplicateCount

    FROM [Employee])

DELETE FROM CTE

WHERE DuplicateCount > 1;