Tuesday 4 November 2014

sql query interview questions

Write query to get all employee detail from "EmployeeDetail" table?

SELECT * FROM [EmployeeDetail]


Write query to get only "FirstName" column from "EmployeeDetail" table?

SELECT FirstName FROM [EmployeeDetail]


Write query to get FirstName in uppler case as "First Name"?

SELECT UPPER(FirstName) AS [First Name]  FROM [EmployeeDetail]


Write query to get FirstName in lower case as "First Name"?

SELECT LOWER(FirstName) AS [First Name]  FROM [EmployeeDetail]


Write query for combine FirstName and LastName and display it as "Name" (also include white space between first name & last name)?

SELECT FirstName +' '+ LastName AS [Name]  FROM [EmployeeDetail]


Select employee detail whose name is "Vikas"?

SELECT * FROM [EmployeeDetail] WHERE FirstName = 'Vikas'


Get all employee detail from EmployeeDetail table whose "FirstName" start with latter 'a'?

SELECT * FROM [EmployeeDetail] WHERE FirstName like 'a%'



Get all employee details from EmployeeDetail table whose "FirstName" contains 'k'?

 SELECT * FROM [EmployeeDetail] WHERE FirstName like '%k%'



Get all employee details from EmployeeDetail table whose "FirstName" end with 'h'?

 SELECT * FROM [EmployeeDetail] WHERE FirstName like '%h'

  
Get all employee detail from EmployeeDetail table whose "FirstName" start with any single character between 'a-p'?

 SELECT * FROM [EmployeeDetail] WHERE FirstName like '[a-p]%'

Get all employee detail from EmployeeDetail table whose "FirstName" not start with any single character between 'a-p'?

SELECT * FROM [EmployeeDetail] WHERE FirstName like '[^a-p]%'


Get all employee detail from EmployeeDetail table whose "Gender" end with 'le' and contain 4 letters.--The Underscore(_) Wildcard Character represents any single character?

SELECT * FROM [EmployeeDetail] WHERE Gender like '__le' --there are two "_"


Get all employee detail from EmployeeDetail table whose "FirstName" start with 'A' and contain 5 letters?


SELECT * FROM [EmployeeDetail] WHERE FirstName like 'A____' --there are two "_"

Get all employee detail from EmployeeDetail table whose "FirstName" containing '%'. ex:-"Vik%as"?

SELECT * FROM [EmployeeDetail] WHERE FirstName like '%[%]%' --there are two "_"
--According to our table it would return 0 rows, because no name containg '%'


Get all unique "Department" from EmployeeDetail table.

SELECT DISTINCT(Department) FROM [EmployeeDetail]


Get the highest "Salary" from EmployeeDetail table.

SELECT MAX(Salary) FROM [EmployeeDetail]


Get the lowest "Salary" from EmployeeDetail table.

SELECT MIN(Salary) FROM [EmployeeDetail]

Show "JoiningDate" in "dd mmm yyyy" format, ex- "15 Feb 2013"

SELECT CONVERT(VARCHAR(20),JoiningDate,106) FROM [EmployeeDetail]

Show "JoiningDate" in "yyyy/mm/dd" format, ex- "2013/02/15"
SELECT CONVERT(VARCHAR(20),JoiningDate,111) FROM [EmployeeDetail]

Show only time part of the "JoiningDate".

SELECT CONVERT(VARCHAR(20),JoiningDate,108) FROM [EmployeeDetail]


Get only Year part of "JoiningDate".

SELECT DATEPART(YEAR, JoiningDate) FROM [EmployeeDetail] 


Get only Month part of "JoiningDate".

SELECT DATEPART(MONTH,JoiningDate) FROM [EmployeeDetail]

Get system date.

SELECT GETDATE()

Get UTC date.

SELECT GETUTCDATE()

Get the first name, current date, joiningdate and diff between current date and joining date in months.

SELECT FirstName, GETDATE() [Current Date], JoiningDate,
DATEDIFF(MM,JoiningDate,GETDATE()) AS [Total Months] FROM [EmployeeDetail]

Get the first name, current date, joiningdate and diff between current date and joining date in days.

SELECT FirstName, GETDATE() [Current Date], JoiningDate,
DATEDIFF(DD,JoiningDate,GETDATE()) AS [Total Months] FROM [EmployeeDetail]


Get all employee details from EmployeeDetail table whose joining year is 2013.

SELECT * FROM [EmployeeDetail] WHERE DATEPART(YYYY,JoiningDate) = '2013'

Get all employee details from EmployeeDetail table whose joining month is Jan(1).

SELECT * FROM [EmployeeDetail] WHERE DATEPART(MM,JoiningDate) = '1'

Get all employee details from EmployeeDetail table whose joining date between "2013-01-01" and "2013-12-01".

SELECT * FROM [EmployeeDetail] WHERE JoiningDate BETWEEN '2013-01-01' AND '2013-12-01'

Get how many employee exist in "EmployeeDetail" table.

SELECT COUNT(*) FROM [EmployeeDetail]

Select only one/top 1 record from "EmployeeDetail" table.

SELECT TOP 1 * FROM [EmployeeDetail]


Select all employee detail with First name "Vikas","Ashish", and "Nikhil".

SELECT * FROM [EmployeeDetail] WHERE FirstName IN('Vikas','Ashish','Nikhil')


Select all employee detail with First name not "Vikas","Ashish", and "Nikhil".

SELECT * FROM [EmployeeDetail] WHERE FirstName NOT IN('Vikas','Ashish','Nikhil')


Select first name from "EmployeeDetail" table after removing white spaces from right side

SELECT RTRIM(FirstName) AS [FirstName] FROM [EmployeeDetail]


Select first name from "EmployeeDetail" table after removing white spaces from left side

SELECT LTRIM(FirstName) AS [FirstName] FROM [EmployeeDetail]



Display first name and Gender as M/F.(if male then M, if Female then F)

SELECT FirstName, CASE  WHEN Gender = 'Male' THEN 'M'
WHEN Gender = 'Female' THEN 'F'
END AS [Gender]
FROM [EmployeeDetail]

Select first name from "EmployeeDetail" table prifixed with "Hello "

SELECT 'Hello ' + FirstName FROM [EmployeeDetail]

Get employee details from "EmployeeDetail" table whose Salary greater than 600000

SELECT * FROM [EmployeeDetail] WHERE Salary > 600000

Get employee details from "EmployeeDetail" table whose Salary less than 700000

SELECT * FROM [EmployeeDetail] WHERE Salary < 700000


Get employee details from "EmployeeDetail" table whose Salary between 500000 than 600000

SELECT * FROM [EmployeeDetail] WHERE Salary BETWEEN 500000 AND 600000


Select second highest salary from "EmployeeDetail" table.

SELECT TOP 1 Salary FROM
(
      SELECT TOP 2 Salary FROM [EmployeeDetail] ORDER BY Salary DESC
) T ORDER BY Salary ASC



Write the query to get the department and department wise total(sum) salary from "EmployeeDetail" table.

SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail]
GROUP BY Department

Write the query to get the department and department wise total(sum) salary, display it in ascending order according to salary.

SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail]
GROUP BY Department ORDER BY SUM(Salary) ASC

Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.

SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail]
GROUP BY Department ORDER BY SUM(Salary) DESC
  
Write the query to get the department, total no. of departments, total(sum) salary with respect to department from "EmployeeDetail" table.

SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeDetail]
GROUP BY Department

Get department wise average salary from "EmployeeDetail" table order by salary ascending
  
SELECT Department, AVG(Salary) AS [Average Salary] FROM [EmployeeDetail]
GROUP BY Department ORDER BY AVG(Salary) ASC


Get department wise maximum salary from "EmployeeDetail" table order by salary ascending
  
SELECT Department, MAX(Salary) AS [Average Salary] FROM [EmployeeDetail]
GROUP BY Department ORDER BY MAX(Salary) ASC



Get department wise minimum salary from "EmployeeDetail" table order by salary ascending

SELECT Department, MIN(Salary) AS [Average Salary] FROM [EmployeeDetail]
GROUP BY Department ORDER BY MIN(Salary) ASC


Write down the query to fetch Project name assign to more than one Employee

Select ProjectName,Count(*) [NoofEmp] from [ProjectDetail] GROUP BY ProjectName HAVING COUNT(*)>1


Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for those employee which have assigned project already.

SELECT FirstName,ProjectName FROM [EmployeeDetail] A INNER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName


Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee even they have not assigned project.

SELECT FirstName,ProjectName FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName

Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee if project is not assigned then display "-No Project Assigned".

SELECT FirstName, ISNULL(ProjectName,'-No Project Assigned') FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName

Get all project name even they have not matching any employeeid, in left table, order by firstname from "EmployeeDetail" and "ProjectDetail".

SELECT FirstName,ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName


Get complete record(employeename, project name) from both tables([EmployeeDetail],[ProjectDetail]), if no match found in any table then show NULL.

SELECT FirstName,ProjectName FROM [EmployeeDetail] A FULL OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName


Write a query to find out the employeename who has not assigned any project, and display "-No Project Assigned"( tables :- [EmployeeDetail],[ProjectDetail]).

SELECT FirstName, ISNULL(ProjectName,'-No Project Assigned') AS [ProjectName] FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID
WHERE ProjectName IS NULL


Write a query to find out the project name which is not assigned to any employee( tables :- [EmployeeDetail],[ProjectDetail]).

SELECT ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B
ON A.EmployeeID = B.EmployeeDetailID
WHERE FirstName IS NULL

Write down the query to fetch EmployeeName & Project who has assign more than one project.

Select EmployeeID, FirstName, ProjectName from [EmployeeDetail] E INNER JOIN [ProjectDetail] P
ON E.EmployeeID = P.EmployeeDetailID
WHERE EmployeeID IN (SELECT EmployeeDetailID FROM [ProjectDetail] GROUP BY EmployeeDetailID HAVING COUNT(*) >1 )

Write down the query to fetch ProjectName on which more than one employee are working along with EmployeeName.

Select FirstName, ProjectName from [EmployeeDetail] E INNER JOIN [ProjectDetail] P
ON E.EmployeeID = P.EmployeeDetailID


18 comments: