sql query interview questions ~ www.jpindia.com

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