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.
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 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