Table
Name : Employee
EMPLOYEE_ID
|
FIRST_NAME
|
LAST_NAME
|
SALARY
|
JOINING_DATE
|
DEPARTMENT
|
1
|
John
|
Abraham
|
1000000
|
01-JAN-13 12.00.00 AM
|
Banking
|
2
|
Michael
|
Clarke
|
800000
|
01-JAN-13 12.00.00 AM
|
Insurance
|
3
|
Roy
|
Thomas
|
700000
|
01-FEB-13 12.00.00 AM
|
Banking
|
4
|
Tom
|
Jose
|
600000
|
01-FEB-13 12.00.00 AM
|
Insurance
|
5
|
Jerry
|
Pinto
|
650000
|
01-FEB-13 12.00.00 AM
|
Insurance
|
6
|
Philip
|
Mathew
|
750000
|
01-JAN-13 12.00.00 AM
|
Services
|
7
|
TestName1
|
123
|
650000
|
01-JAN-13 12.00.00 AM
|
Services
|
8
|
TestName2
|
Lname%
|
600000
|
01-FEB-13 12.00.00 AM
|
Insurance
|
Table Name : Incentives
EMPLOYEE_REF_ID
|
INCENTIVE_DATE
|
INCENTIVE_AMOUNT
|
1
|
01-FEB-13
|
5000
|
2
|
01-FEB-13
|
3000
|
3
|
01-FEB-13
|
4000
|
1
|
01-JAN-13
|
4500
|
2
|
01-JAN-13
|
3500
|
1 1. Get all
employee details from the employee table
SELECT * FROM EMPLOYEE
2.
Get First_Name,Last_Name
from employee table
SELECT FIRST_NAME,LAST_NAME FROM
EMPLOYEE
3.
Get
First_Name from employee table using alias
SELECT FIRST_NAME
AS NAME FROM
EMPLOYEE
4.
Get
First_Name from employee table in upper case
SELECT UPPER(FIRST_NAME) AS FIRST_NAME FROM EMPLOYEE
5.
Get
First_Name from employee table in lower case
SELECT LOWER(FIRST_NAME) AS FIRST_NAME FROM EMPLOYEE
6.
Get unique
DEPARTMENT from employee table
SELECT DISTINCT(DEPARTMENT) FROM EMPLOYEE
7.
Select
first 3 characters of FIRST_NAME from EMPLOYEE
SELECT SUBSTRING(FIRST_NAME,1,3) AS FIRST_NAME FROM EMPLOYEE
8.
Get
position of 'o' in name 'John' from employee table
SELECT CHARINDEX('O',FIRST_NAME) AS POSITION FROM
EMPLOYEE WHERE FIRST_NAME='JOHN'
9.
Get
FIRST_NAME from employee table after removing white spaces from right side
SELECT RTRIM(FIRST_NAME) AS FIRST_NAME FROM EMPLOYEE
10. Get FIRST_NAME from employee table after removing white
spaces from left side
SELECT LTRIM(FIRST_NAME) AS FIRST_NAME FROM EMPLOYEE
11. Get length of FIRST_NAME from employee table
SELECT LEN(FIRST_NAME) AS LENGTH FROM EMPLOYEE
12. Get First_Name from employee table after replacing 'o' with
'$'
SELECT REPLACE(FIRST_NAME,'O','$') AS FIRST_NAME FROM EMPLOYEE
13. Get First_Name and Last_Name as single column from employee
table separated by a '_'
SELECT FIRST_NAME
+'_'+ LAST_NAME AS
NAME FROM
EMPLOYEE
14. Get FIRST_NAME ,Joining year,Joining Month and Joining Date
from employee table
SELECT FIRST_NAME,YEAR(JOINING_DATE) AS YEAR,MONTH(JOINING_DATE) AS MONTH,JOINING_DATE FROM EMPLOYEE
15.
Get all
employee details from the employee table order by First_Name Ascending
SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME
16. Get all employee
details from the employee table order by First_Name descending
SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME DESC
17. Get all employee details from the employee table order by
First_Name Ascending and Salary descending
SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME ASC,SALARY DESC
18. Get employee details from employee table whose employee name
is “John”
SELECT * FROM EMPLOYEE WHERE FIRST_NAME ='JOHN'
19. Get employee details from employee table whose employee name
are “John” and “Roy”
SELECT * FROM EMPLOYEE WHERE FIRST_NAME IN('JOHN','ROY')
20. Get employee details from employee table whose employee name
are not “John” and “Roy”
SELECT * FROM EMPLOYEE WHERE FIRST_NAME NOT IN('JOHN','ROY')
21. Get employee details from employee table whose first name
starts with 'J'
SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE
'J%'
22. Get employee details from employee table whose first name
contains 'o'
SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE
'%O%'
23. Get employee details from employee table whose first name
ends with 'n'
SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE
'%N'
24. Get employee details from employee table whose first name
ends with 'n' and name contains 4 letters
SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE
'%N' AND LEN(FIRST_NAME)=4
25. Get employee details from employee table whose first name
starts with 'J' and name contains 4 letters
SELECT * FROM EMPLOYEE WHERE FIRST_NAME LIKE
'J%' AND LEN(FIRST_NAME)=4
26. Get employee details from employee table whose Salary
greater than 600000
SELECT * FROM EMPLOYEE WHERE SALARY >
600000
27. Get employee details from employee table whose Salary less
than 800000
SELECT * FROM EMPLOYEE WHERE SALARY <
800000
28. Get employee details from employee table whose Salary
between 500000 and 800000
SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN
500000 AND 800000
29. Get employee details from employee table whose name is
'John' and 'Michael'
SELECT * FROM EMPLOYEE WHERE FIRST_NAME IN('JOHN','MICHAEL')
30. Get employee details from employee table whose joining year
is “2013”
SELECT * FROM EMPLOYEE WHERE YEAR(JOINING_DATE)=2013
31. Get employee details from employee table whose joining month
is “January”
SELECT * FROM EMPLOYEE WHERE DATENAME(MONTH,JOINING_DATE) ='January'
32. Get employee details from employee table who joined before
January 1st 2013
SELECT * FROM EMPLOYEE WHERE
JOINING_DATE < '1-1-2013'
33. Get employee details from employee table who joined after
January 31st
SELECT * FROM EMPLOYEE WHERE
JOINING_DATE > '1-31-2013'
34. Get Joining Date and Time from employee table
SELECT
JOINING_DATE FROM EMPLOYEE
35. Get Joining Date,Time including milliseconds from employee
table
SELECT CONVERT(DATE,JOINING_DATE,103) AS DATE,CONVERT(time,JOINING_DATE,101) as TIME FROM EMPLOYEE
36. Get difference between JOINING_DATE and INCENTIVE_DATE from
employee and incentives table
SELECT DATEDIFF(DAY,EMP.JOINING_DATE,INS.INCENTIVE_DATE) AS DATE_DIFF FROM
EMPLOYEE EMP INNER JOIN
INCENTIVE INS ON INS.EMPLOYEE_RFF_ID=EMP.EMPLOYEE_ID
37. Get database date
SELECT GETDATE() AS DATE
38. Get names of employees from employee table who has '%' in
Last_Name. Tip : Escape character for special characters in a query.
SELECT FIRST_NAME
+' '+LAST_NAME FROM
EMPLOYEE WHERE LAST_NAME LIKE '%\%%' ESCAPE '\'
39. Get Last Name from employee table after replacing special
character with white space
SELECT REPLACE(REPLACE(REPLACE(REPLACE(LAST_NAME,'%',''),'1',''),'2',''),'3','') FROM EMPLOYEE
40. Get department,total salary with respect to a department
from employee table.
SELECT DEPARTMENT,SUM(SALARY) AS SALARY FROM
EMPLOYEE GROUP BY
DEPARTMENT
41. Get department,total salary with respect to a department
from employee table order by total salary descending
SELECT DEPARTMENT,SUM(SALARY) AS SALARY FROM
EMPLOYEE GROUP
BY DEPARTMENT ORDER
BY SALARY DESC
42. Get department,no of employees in a department,total salary
with respect to a department from employee table order by total
salary descending
SELECT DEPARTMENT,SUM(SALARY) AS SALARY,COUNT(EMPLOYEE_ID) AS EMP_COUNT FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY SALARY DESC
43. Get department wise average salary from employee table order
by salary ascending
SELECT (X.SALARY/X.EMP_COUNT)
AVERAGE_SALARY FROM (SELECT SUM(SALARY) AS SALARY ,COUNT(EMPLOYEE_ID) AS EMP_COUNT FROM EMPLOYEE GROUP BY DEPARTMENT ) AS X ORDER BY X.SALARY
44. Get department wise maximum salary from employee table order
by salary ascending
SELECT MAX(SALARY) AS MAX_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MAX_SALARY
45. Get department wise minimum salary from employee table order
by salary ascending
SELECT MIN(SALARY) AS MIN_SALARY FROM EMPLOYEE GROUP BY DEPARTMENT ORDER BY MIN_SALARY
46. Select no of employees joined with respect to year and month
from employee table
SELECT COUNT(EMPLOYEE_ID) AS EMP_COUNT,YEAR(JOINING_DATE)AS YEAR, MONTH(JOINING_DATE)AS MONTH FROM EMPLOYEE GROUP BY YEAR(JOINING_DATE),MONTH(JOINING_DATE)
47. Select department,total salary with respect to a department
from employee table where total salary greater than 800000 order by
Total_Salary descending
SELECT X.* FROM(SELECT DEPARTMENT,SUM(SALARY) AS TOTAL FROM
EMPLOYEE GROUP BY
DEPARTMENT ) AS
X WHERE X.TOTAL>800000 ORDER BY X.TOTAL DESC
48. Select first_name, incentive amount from employee and
incentives table for those employees who have incentives
SELECT EMP.FIRST_NAME,INS.INCENTIVE_AMOUNT FROM
EMPLOYEE EMP INNER JOIN
INCENTIVE INS ON INS.EMPLOYEE_RFF_ID=EMP.EMPLOYEE_ID
49. Select first_name, incentive amount from employee and
incentives table for those employees who have incentives and incentive amount
greater than 3000
SELECT EMP.FIRST_NAME,INS.INCENTIVE_AMOUNT FROM
EMPLOYEE EMP INNER JOIN
INCENTIVE INS ON INS.EMPLOYEE_RFF_ID=EMP.EMPLOYEE_ID WHERE INS.INCENTIVE_AMOUNT>3000
50. Select first_name, incentive amount from employee and
incentives table for all employes even if they didn't get incentives
SELECT EMP.FIRST_NAME,INS.INCENTIVE_AMOUNT FROM
EMPLOYEE EMP LEFT JOIN
INCENTIVE INS ON INS.EMPLOYEE_RFF_ID=EMP.EMPLOYEE_ID
51. Select first_name,
incentive amount from employee and incentives table for all employees even if
they didn't get incentives and set incentive amount as 0 for those employees
who didn't get incentives.
SELECT EMP.FIRST_NAME,ISNULL(INS.INCENTIVE_AMOUNT,0)AS INCENTIVE_AMOUNT FROM EMPLOYEE EMP LEFT
JOIN INCENTIVE INS ON
INS.EMPLOYEE_RFF_ID=EMP.EMPLOYEE_ID
52. Select first_name,
incentive amount from employee and incentives table for all employees who got
incentives using left join
SELECT EMP.FIRST_NAME,INS.INCENTIVE_AMOUNT FROM
INCENTIVE INS LEFT JOIN
EMPLOYEE EMP ON EMP.EMPLOYEE_ID=INS.EMPLOYEE_RFF_ID
53. Select max incentive
with respect to employee from employee and incentives table using sub query
SELECT X.FIRST_NAME,MAX(X.INCENTIVE_AMOUNT) AS MAX_AMOUND FROM
(SELECT EMP.FIRST_NAME,INS.INCENTIVE_AMOUNT FROM
INCENTIVE INS INNER JOIN
EMPLOYEE EMP ON EMP.EMPLOYEE_ID=INS.EMPLOYEE_RFF_ID) AS X GROUP BY X.FIRST_NAME
54. Select TOP 2 salary
from employee table
SELECT TOP 2 SALARY FROM
EMPLOYEE ORDER BY
SALARY DESC
55. Select TOP N salary
from employee table
DECLARE @N INT = 4
SELECT TOP(@N) SALARY FROM
EMPLOYEE ORDER BY
SALARY DESC
56. Select 2nd Highest
salary from employee table
SELECT MIN(X.SALARY) AS MIN_SALARY FROM(SELECT TOP 2 SALARY FROM
EMPLOYEE) AS X
57. Select Nth Highest
salary from employee table
SELECT X.SALARY FROM
(SELECT EMP.SALARY,RANK() OVER(ORDER BY SALARY DESC) AS ROW FROM EMPLOYEE
EMP) AS X WHERE X.ROW=2
58. Select
First_Name,LAST_NAME from employee table as separate rows
SELECT FIRST_NAME
FROM EMPLOYEE UNION
SELECT LAST_NAME FROM EMPLOYEE
59.
What is
the difference between UNION and UNION ALL ?
UNION
The UNION command is used to select related
information from two tables, much like the JOIN command. However, when using
the UNION command all selected columns need to be of the same data type. With
UNION, only distinct values are selected.
UNION
ALL
The UNION ALL command is equal to the UNION command,
except that UNION ALL selects all values.
The difference between Union and Union all is that
Union all will not eliminate duplicate rows, instead it just pulls all rows
from all tables fitting your query specifics and combines them into a table.
A UNION statement effectively does
a SELECT DISTINCT on the results set. If you know that all the records returned
are unique from your union, use UNION ALL instead, it gives faster results.
60. Select employee
details from employee table if data exists in incentive table ?
select * from EMPLOYEE where EMPLOYEE_ID IN(select DISTINCT(EMPLOYEE_RFF_ID) from INCENTIVE)
61. How to fetch data
that are common in two query results?
SELECT * FROM EMPLOYEE INTERSECT SELECT
TOP 4 * FROM EMPLOYEE
62. Get Employee ID's of
those employees who didn't receive incentives
select * from EMPLOYEE where
EMPLOYEE_ID NOT IN(select DISTINCT(EMPLOYEE_RFF_ID) from INCENTIVE)
63. Select 20 % of salary
from John , 10% of Salary for Roy and for other 15 % of salary from employee
table
SELECT EMP.FIRST_NAME,(CASE EMP.FIRST_NAME WHEN 'JOHN' THEN EMP.SALARY *.20 WHEN 'ROY' THEN EMP.SALARY *.10 ELSE EMP.SALARY *.15 END) AS SALARY FROM EMPLOYEE EMP
64. Select Banking as
'Bank Dept', Insurance as 'Insurance Dept' and Services as 'Services Dept' from
employee table
SELECT (CASE DEPARTMENT
WHEN 'BANKING' THEN 'BANK DEPT' WHEN 'INSURANCE' THEN 'INSURANCE DEPT' WHEN 'SERVICES' THEN 'SERVICES DEPT' END)AS DEPARTMENT FROM EMPLOYEE
65. Delete employee data
from employee table who got incentives in incentive table
DELETE FROM EMPLOYEE WHERE
EMPLOYEE_ID IN (SELECT
EMPLOYEE_RFF_ID FROM INCENTIVE)
66. Insert into employee table Last Name with
" ' " (Single Quote - Special Character)
INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,SALARY,JOINING_DATE,DEPARTMENT)
VALUES('SAM','DEEN''',750000,'2-2-2013','BANKING')
67. Select Last Name from employee table which
contain only numbers
SELECT LAST_NAME FROM EMPLOYEE WHERE
LAST_NAME NOT LIKE
'%[^0-9]%'
68. Write a query to rank employees based on
their incentives for a month
SELECT EMP.FIRST_NAME,INS.INCENTIVE_AMOUNT,RANK() OVER(ORDER BY INS.INCENTIVE_AMOUNT DESC) AS RANK FROM EMPLOYEE
EMP INNER JOIN
INCENTIVE INS ON EMP.EMPLOYEE_ID=INS.EMPLOYEE_RFF_ID
