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.
- 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)
- CREATE TABLE [Department]([Depid] [int] NULL,[DepName] [varchar](250) NULL)
And insert some records into the table.
Department
- INSERT INTO tbl_Department(Depid, DepName)VALUES(1, 'IT Department')
- INSERT INTO tbl_Department(Depid, DepName)VALUES(2, 'HR Department')
- INSERT INTO tbl_Department(Depid, DepName)VALUES(3, 'Developer Department')
- INSERT INTO tbl_Department(Depid, DepName)VALUES(4, 'Tester Department')
- INSERT INTO tbl_Department(Depid, DepName)VALUES(5, 'Creative Department')
- INSERT INTO tbl_Department(Depid, DepName)VALUES(6, 'SEO Department')
- INSERT INTO tbl_Department(Depid, DepName)VALUES(7, 'Admin Department')
Employee
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(1, 'Jitendra' , 'Male', 'Demo Address', 'Agra', 4000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(2, 'Aman' , 'Male', 'Demo Address', 'Ghaziabad', 0, 2)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(3, 'Niman' , 'Male', 'Demo Address', 'Agra', 1000, 2)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(4, 'Rita' , 'Female', 'Demo Address', 'Mathura', 1000, 2)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(5, 'Sita' , 'Female', 'Demo Address', 'Agra', 4000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(6, 'Rohan' , 'Male', 'Demo Address', 'Agra', 5000, 2)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(7, 'Sohan' , 'Male', 'Demo Address', 'Noida', 4000, 1)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(8, 'Mohan' , 'Male', 'Demo Address', 'Agra', 4000, 5)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(9, 'Ram' , 'Male', 'Demo Address', 'Noida', 5000, 1)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(10, 'Shyam' , 'Male', 'Demo Address', 'Delhi', 0, 5)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(11, 'Teena' , 'Female', 'Demo Address', 'Mathura', 3000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(12, 'Aman' , 'Male', 'Demo Address', 'Ghaziabad', 4000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(13, 'Jitendra' , 'Male', 'Demo Address', 'Ghaziabad', 4000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(14, 'Sunil' , 'Male', 'Demo Address', 'Delhi', 2000, 6)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(15, 'Roshan' , 'Male', 'Demo Address', 'Mathura', 3000, 2)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(16, 'Manoj' , 'Male', 'Demo Address', 'Ghaziabad', 2000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(17, 'Rinky' , 'Female', 'Demo Address', 'Mathura', 0, 1)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(18, 'Pooja' , 'Female', 'Demo Address', 'Delhi', 4000, 7)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(19, 'Shivani' , 'Female', 'Demo Address', 'Delhi', 4000, 7)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(20, 'Pankaj' , 'Male', 'Demo Address', 'Agra', 7000, 1)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(21, 'Sendy' , 'Male', 'Demo Address', 'Mathura', 8000, 3)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(22, 'Akash' , 'Male', 'Demo Address', 'Delhi', 6000, 1)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(23, 'Nidhi' , 'Female', 'Demo Address', 'Ghaziabad', 7000, 6)
- INSERT INTO tbl_Emp(Empid, EmpName, Gender, EmpAddress, City, Salary, Fk_DepId)VALUES(24, 'Kartik' , 'Male', 'Demo Address', 'Agra', 6000, 6)
- 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:
The following are the questions.
Question 1: How to find duplicate records in the Employee Table?
Answer:
- Select EmpName, Count(EmpName)as EmpRepeat from tbl_Emp
- Group by EmpName
- having Count(EmpName)>1
Question 2: How to delete duplicate records in the Employee Table?
Answer:
- WITH TempEmpTbl (EmpName,duplicateEmpCount)
- AS
- (
- SELECT EmpName,ROW_NUMBER() OVER(PARTITION by EmpName, Salary ORDER BY EmpName)
- AS duplicateEmpCount
- FROM tbl_Emp
- )
- DELETE FROM TempEmpTbl
- WHERE duplicateEmpCount > 1
Question 3: How to find the highest salary of an Employee?
Answer:
Select MAX(Salary) as Salary from tbl_Emp.
Question 4: How to find the second highest salary of an Employee?
Answer:
- Select MAX(Salary) as Salary from tbl_Emp WHERE Salary NOT IN (select MAX(Salary) from tbl_Emp )
Question 5: How to find the Max Salary from each department?
Answer:
- select D.DepName, MAX(E.Salary) as Salary from tbl_Emp E
- Inner Join tbl_Department D on E.Fk_DepId = D.DepId Group By D.DepName
Question 6: How to find the name of the department with the Max Sum of Salaries of its employees?
Answer:
- select Top 1 D.DepName, Sum(E.Salary) as Salary from tbl_Emp E
- Inner Join tbl_Department D on E.Fk_DepId = D.DepId Group By D.DepName
- Order by sum(salary) desc
Question 7: How to find cities and number of employees whose Salary is greater than 4000.
Answer:
- 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
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:
- SELECT * FROM tbl_Emp WHERE EmpName like '[^a-r]%'
Question 10: How to reverse the gender of each employee in a table?
Answer:
- Select EmpName, Case Gender When 'Male' then 'Female' else 'Male' End As Gender
- from tbl_Emp
I explain various types of SQL Server Queries. I hope this article is useful for everybody. I hope to see some good comments.