Some Simple and Important SQL Server Query Based Interview Questions

If you are working in a company for one to three years on any project then definitely the interviewer will ask these questions. I share the commonly asked SQL Server Query questions asked by interviewers. I hope this will help you to clarify things for you for the SQL Server Query Questions.

Now first create the following table in the database.

  1. CREATE TABLE tbl_Emp(Empid int NULL,EmpName varchar(250) NULL, Gender varchar(20), EmpAddress varchar(500) NULL,City varchar(250) NULL,Salary int NULL, Fk_DepId int)  
  2. CREATE TABLE [Department]([Depid] [intNULL,[DepName] [varchar](250) NULL)  
And insert some records into the table.

Department
  1. INSERT INTO tbl_Department(Depid, DepName)VALUES(1, 'IT Department')  
  2. INSERT INTO tbl_Department(Depid, DepName)VALUES(2, 'HR Department')  
  3. INSERT INTO tbl_Department(Depid, DepName)VALUES(3, 'Developer Department')  
  4. INSERT INTO tbl_Department(Depid, DepName)VALUES(4, 'Tester Department')  
  5. INSERT INTO tbl_Department(Depid, DepName)VALUES(5, 'Creative Department')  
  6. INSERT INTO tbl_Department(Depid, DepName)VALUES(6, 'SEO Department')  
  7. INSERT INTO tbl_Department(Depid, DepName)VALUES(7, 'Admin Department')  
Employee
  1. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(1, 'Jitendra' , 'Male''Demo Address''Agra', 4000, 3)  
  2. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(2, 'Aman' , 'Male''Demo Address''Ghaziabad', 0, 2)  
  3. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(3, 'Niman' , 'Male''Demo Address''Agra', 1000, 2)  
  4. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(4, 'Rita' , 'Female''Demo Address''Mathura', 1000, 2)  
  5. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(5, 'Sita' , 'Female''Demo Address''Agra', 4000, 3)  
  6. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(6, 'Rohan' , 'Male''Demo Address''Agra', 5000, 2)  
  7. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(7, 'Sohan' , 'Male''Demo Address''Noida', 4000, 1)  
  8. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(8, 'Mohan' , 'Male''Demo Address''Agra', 4000, 5)  
  9. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(9, 'Ram' , 'Male''Demo Address''Noida', 5000, 1)  
  10. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(10, 'Shyam' , 'Male''Demo Address''Delhi', 0, 5)  
  11. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(11, 'Teena' , 'Female''Demo Address''Mathura', 3000, 3)  
  12. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(12, 'Aman' , 'Male''Demo Address''Ghaziabad', 4000, 3)  
  13. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(13, 'Jitendra' , 'Male''Demo Address''Ghaziabad', 4000, 3)  
  14. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(14, 'Sunil' , 'Male''Demo Address''Delhi', 2000, 6)  
  15. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(15, 'Roshan' , 'Male''Demo Address''Mathura', 3000, 2)  
  16. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(16, 'Manoj' , 'Male''Demo Address''Ghaziabad', 2000, 3)  
  17. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(17, 'Rinky' , 'Female''Demo Address''Mathura', 0, 1)  
  18. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(18, 'Pooja' , 'Female''Demo Address''Delhi', 4000, 7)  
  19. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(19, 'Shivani' , 'Female''Demo Address''Delhi', 4000, 7)  
  20. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(20, 'Pankaj' , 'Male''Demo Address''Agra', 7000, 1)  
  21. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(21, 'Sendy' , 'Male''Demo Address''Mathura', 8000, 3)  
  22. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(22, 'Akash' , 'Male''Demo Address''Delhi', 6000, 1)  
  23. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(23, 'Nidhi' , 'Female''Demo Address''Ghaziabad', 7000, 6)  
  24. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(24, 'Kartik' , 'Male''Demo Address''Agra', 6000, 6)  
  25. INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(25, 'Ashish' , 'Male''Demo Address''Ghaziabad', 5000, 3)  
Now the Select statement of the table:

select query

Select Statement

The following are the questions.

Question 1: How to find duplicate records in the Employee Table?

Answer:
  1. Select EmpName, Count(EmpName)as EmpRepeat from tbl_Emp  
  2. Group by EmpName  
  3. having Count(EmpName)>1  
Find duplicate records in Employee Table

Question 2: How to delete duplicate records in the Employee Table?

Answer:
  1. WITH TempEmpTbl (EmpName,duplicateEmpCount)  
  2. AS  
  3. (  
  4. SELECT EmpName,ROW_NUMBER() OVER(PARTITION by EmpName, Salary ORDER BY EmpName)  
  5. AS duplicateEmpCount  
  6. FROM tbl_Emp  
  7. )  
  8. DELETE FROM TempEmpTbl  
  9. WHERE duplicateEmpCount > 1  
Question 3: How to find the highest salary of an Employee?

Answer:

Select MAX(Salary) as Salary from tbl_Emp.

find highest salary of Employee

Question 4: How to find the second highest salary of an Employee?

Answer:
  1. Select MAX(Salary) as Salary from tbl_Emp WHERE Salary NOT IN (select MAX(Salary) from tbl_Emp )  
find second highest salary of Employee

Question 5: How to find the Max Salary from each department?

Answer:
  1. select D.DepName, MAX(E.Salary) as Salary from tbl_Emp E  
  2. Inner Join tbl_Department D on E.Fk_DepId = D.DepId Group By D.DepName  
find Max Salary from each department

Question 6: How to find the name of the department with the Max Sum of Salaries of its employees?

Answer:
  1. select Top 1 D.DepName, Sum(E.Salary) as Salary from tbl_Emp E  
  2. Inner Join tbl_Department D on E.Fk_DepId = D.DepId Group By D.DepName  
  3. Order by sum(salary) desc  
 
Question 7: How to find cities  and number of employees whose Salary is greater than 4000.

Answer:
  1. select City, Count(City) as CityCount from tbl_Emp where Salary > 4000 group by City Having Count(City) > 1  


Question 8: How to find the name of an employee whose name contains "ha" and the length is greater than 5?

Answer:

Select EmpName from tbl_emp where EmpName Like '%ha%' and Len(EmpName) > 5

 Length greater than 5

Question 9: How to get all the employee details from the Employee table who's "EmpName" does not start with any single character between "a" and "r"?

Answer:
  1. SELECT * FROM tbl_Emp WHERE EmpName like '[^a-r]%'  
single character between

Question 10: How to reverse the gender of each employee in a table?

Answer:

  1. Select EmpName, Case Gender When 'Male' then 'Female' else 'Male' End As Gender  
  2. from tbl_Emp  
change the gender of the employee

I explain various types of SQL Server Queries. I hope this article is useful for everybody. I hope to see some good comments.