SQL Project — Working on HR Database

HR Database

Queries

1. From the employees table, find those employees whose salary is less than 6000. Return full name (first and last name), and salary.

2. From the employees table, find those employees whose salary is higher than 8000. Return first name, last name and department number and salary.

3. From the employees table, find those employees whose last name is “McEwen”. Return first name, last name and department ID.

4. From the departments table, find the details of ‘Marketing’ department. Return all fields.

5. From the employees table, find those employees whose first name does not contain the letter ‘M’. Sort the result-set in ascending order by department ID. Return full name (first and last name together), hire_date, salary and department_id.

6. From the employees table, find those employees who falls in the following criteria : 1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. 2. : those employees who joined before ‘2003–06–05’ and not included in the department number 40, 120 and 70. Return all fields.

7. From the employees table, find those employees who do not earn any commission. Return full name (first and last name), and salary.

8. From the employees table, find those employees whose salary is in the range 9000,17000 (Begin and end values are included). Return full name, contact details and salary.

9. From the employees table, find those employees whose first name ends with the letter ‘m’. Return the first and last name, and salary.

10. From the employees table, find those employees whose salary is not in the range 7000 and 15000 (Begin and end values are included). Sort the result-set in ascending order by the full name (first and last). Return full name and salary.

11. From the employees table, find those employees who were hired during November 5th, 2007 and July 5th, 2009. Return full name (first and last), job id and hire date.

12. From the employees table, find those employees who works either in department 70 or 90. Return full name (first and last name), department id.

13. From the employees table, find those employees who work under a manager. Return full name (first and last name), salary, and manager ID.

14. From the employees table, find those employees who were hired before June 21st, 2002. Return all fields.

15. From the employees table, find those employees whose managers hold the ID 120 or 103 or 145. Return first name, last name, email, salary and manager ID.

16. From the employees table, find those employees whose first name contains the letters D, S, or N. Return all fields.

17. From the employees table, find those employees whose first name contains a character ‘s’ in 3rd position. Return first name, last name and department id.

18. From the employees table, find those employees who are working in the departments, which are not included in the department number 50 or 30 or 80. Return employee_id, first_name, job_id, department_id.

19. From the employees table, find those employees whose department numbers are included in 30 or 40 or 90. Return employee id, first name, job id, department id.

20. From the job history table, find those employees who worked more than two jobs in the past. Return employee id.

21. From the employees table, count the number of employees, sum of all salary, and difference between the highest salary and lowest salary by each job id. Return job_id, count, sum, salary_difference.

22. From the job history table, find each job ids where two or more employees worked for more than 300 days. Return job id.

23. From the locations table, count the number of cities in each country has. Return country ID and number of cities.

24. From the employees table, count the number of employees worked under each manager. Return manager ID and number of employees.

25. From the jobs table, find all jobs. Sort the result-set in descending order by job title. Return all fields.

26. From the employees table, find all those employees who are either Sales Representative or Salesman. Return first name, last name and hire date.

27. From the employees table, calculate average salary of those employees for each department who get a commission percentage. Return department id, average salary.

28. From the employees table, find those departments where a manager can manage four or more employees. Return department_id.

29. From the employees table, find those departments where more than ten employees work, who got a commission percentage. Return department id.

30. From the job history table, find those employees who have completed their previous jobs. Return employee ID, end_date.

31. From the employees table, find those employees who have no commission percentage and salary within the range 7000, 12000 (Begin and end values are included.) and works in the department number 50. Return all the fields of employees.

32. From the employees table, compute the average salary of each job ID. Exclude those records where average salary is higher than 8000. Return job ID, average salary.

33. From the jobs table, find those job titles where the difference between minimum and maximum salaries is in the range the range 12000, 18000 (Begin and end values are included.). Return job_title, max_salary-min_salary.

34. From the employees table, find those employees whose first name or last name starts with the letter ‘D’. Return first name, last name.

35. From the jobs table, find details of those jobs where minimum salary exceeds 9000. Return all the fields of jobs.

36. From the employees table, find those employees who joined after 7th September 1987. Return all the fields.

37. From the employees and departments tables, find the first name, last name, department number, and department name for each employee.

38. From the employees, departments, and locations tables, find the first name, last name, department, city, and state province for each employee.

39. From the employees and job grades tables, find the first name, last name, salary, and job grade for all employees.

40. From the employees and departments tables, find all those employees who work in department ID 80 or 40. Return first name, last name, department number and department name.

41. From the employees, departments, and locations tables, find those employees whose first name contains a letter ‘z’. Return first name, last name, department, city, and state province.

42. From the employees and departments tables, find all departments including those without any employee. Return first name, last name, department ID, department name.

43. From the employees table, find those employees who earn less than the employee of ID 182. Return first name, last name and salary.

44. From the employees table, find the employees and their managers. Return the first name of the employee and manager.

45. From the departments and locations tables, display the department name, city, and state province for each department.

46. From the employees and departments tables, find those employees who have or not any department. Return first name, last name, department ID, department name.

47. From the employees table, find the employees and their managers. These managers do not work under any manager. Return the first name of the employee and manager.

48. From the employees table, find those employees who work in a department where the employee of last name ‘Taylor’ works. Return first name, last name and department ID.

49. From the employees, departments, jobs, and job history tables, find those employees who joined on or after 1st January 1993 and leave on or before 31 August 2007. Return job title, department name, employee name, and joining date of the job.

50. From the employees and jobs tables, find the difference between maximum salary of the job and salary of the employees. Return job title, employee name, and salary difference.

51. From the employees and departments table, compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.

52. From the employees and jobs tables, compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.

53. From the departments, locations, and countries tables, find the name of the country, city, and departments, which are running there.

54. From the employees and departments tables, find the department name and the full name (first and last name) of the manager.

55. From the employees and jobs tables, compute the average salary of employees for each job title.

56. From the employees and job history tables, find those employees who earn $12000 and above. Return employee ID, starting date, end date, job ID and department ID.

57. From the employees, departments, locations, countries tables, find those departments where at least 2 employees work. Group the result set on country name and city. Return country name, city, and number of departments.

58. From the employees, departments, and locations tables, find the department name, full name (first and last name) of the manager and their city.

59. From the jobs and job history tables, compute the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.

60. From the employees, departments, and locations tables, find full name (first and last name), and salary of those employees who work in any department located in ‘London’ city.

61. From the jobs and job history tables, find full name (first and last name), job title, starting and ending date of last jobs of employees who worked without a commission percentage.

62. From the employees and departments tables, find the department name, department ID, and number of employees in each department.

63. From the employees, departments, locations, countries tables, find the full name (first and last name) of the employee with ID and name of the country presently where he/she is working.

64. From the employees table, find those employees who get higher salary than the employee whose ID is 163. Return first name, last name.

65. From the employees table, find those employees whose designation is the same as the employee whose ID is 169. Return first name, last name, department ID and job ID.

66. From the employees table, find those employees whose salary matches the smallest salary of any of the departments. Return first name, last name and department ID.

67. From the employees table, find those employees who earn more than the average salary. Return employee ID, first name, last name.

68. From the employees table, find those employees who report that manager whose first name is ‘Payam’. Return first name, last name, employee ID and salary.

69. From the employees and departments tables, find all those employees who work in the Finance department. Return department ID, name (first), job ID and department name.

70. From the employees table, find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields.

71. From the employees table, find those employees whose salary is in the range of smallest salary, and 2500. Return all the fields.

72. From the employees and departments tables, find those employees who do not work in those departments where manager ids are in the range 100, 200 (Begin and end values are included.) Return all the fields of the employees.

73. From the employees table, find those employees who get second-highest salary. Return all the fields of the employees.

74. From the employees table, find those employees who work in the same department where ‘Clara’ works. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.

75. From the employees table, find those employees who work in a department where the employee’s first name contains a letter ‘T’. Return employee ID, first name and last name.

76. From the employees table, find those employees who earn more than the average salary and work in a department with any employee whose first name contains a character a ‘J’. Return employee ID, first name and salary.

77. From the employees, departments, and locations tables, find those employees whose department located at ‘Toronto’. Return first name, last name, employee ID, job ID.

78. From the employees table, find those employees whose salary is lower than any salary of those employees whose job title is ‘MK_MAN’. Return employee ID, first name, last name, job ID.

79. From the employees table, find those employees whose salary is more than any salary of those employees whose job title is ‘PU_MAN’. Exclude job title ‘PU_MAN’. Return employee ID, first name, last name, job ID.

80. From the employees table, find those employees whose salary is more than average salary of any department. Return employee ID, first name, last name, job ID.

81. Display the employee id, name ( first name and last name ), salary and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.

82. Display the employee id, name ( first name and last name ), SalaryDrawn, AvgCompare (salary — the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.

83. From the employees and departments tables, find all those departments where at least one or more employees work. Return department name.

84. From the employees, departments, locations, countries tables, find those employees who work in departments located at ‘United Kingdom’. Return first name.

85. From the employees table, find those employees who earn more than average salary and who work in any of the ‘IT’ departments. Return last name.

86. From the employees table, find all those employees who earn more than an employee whose last name is ‘Ozer’. Sort the result in ascending order by last name. Return first name, last name and salary.

87. From the employees, departments, and locations tables, find those employees who work under a manager based in ‘US’. Return first name, last name.

88. From the employees table, find those employees whose salary is greater than 50% of their department’s total salary bill. Return first name, last name.

89. From the employees and departments tables, find those employees who are managers. Return all the fields of employees table.

90. From the employees, departments, and locations tables, find those employees who get such a salary, which is the maximum of salaried employee, joining within January 1st, 2002 and December 31st, 2003. Return employee ID, first name, last name, salary, department name and city.

91. From the departments and locations tables, find those departments, located in the city ‘London’. Return department ID, department name.

92. From the employees table, find those employees who earn more than the maximum salary of a department of ID 40. Return first name, last name and department ID.

93. From the departments table, find departments for a particular location. The location matches the location of the department of ID 30. Return department name and department ID.

-

94. From the employees table, find those employees who work in that department where the employee works of ID 201. Return first name, last name, salary, and department ID.

95. From the employees table, find those employees whose salary matches to the salary of the employee who works in that department of ID 40. Return first name, last name, salary, and department ID.

96. From the employees and departments tables, find those employees who work in the department ‘Marketing’. Return first name, last name and department ID.

97. From the employees table, find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID.

98. From the employees table, find those employees who joined after the employee whose ID is 165. Return first name, last name and hire date.

99. From the employees table, find those employees who earn less than the minimum salary of a department of ID 70. Return first name, last name, salary, and department ID.

100. From the employees table, find those employees who earn less than the average salary, and work at the department where the employee ‘Laura’ (first name) works. Return first name, last name, salary, and department ID.

101. From the employees, departments, and locations tables, find those employees whose department is located in the city ‘London’. Return first name, last name, salary, and department ID.

102. From the employees, departments, and locations tables, find the city of the employee of ID 134. Return city.

103. From the employees, departments, and job history tables, find those departments where maximum salary is 7000 and above. The employees worked in those departments have already completed one or more jobs. Return all the fields of the departments.

104. From the employees and departments tables, write a SQL query to find those departments where starting salary is at least 8000. Return all the fields of departments.

105. From the employees table, find those managers who supervise four or more employees. Return manager name, department ID.

106. From the employees table, find those employees who earn second-lowest salary of all the employees. Return all the fields of employees.

107. From the employees and departments tables, find those departments managed by ‘Susan’. Return all the fields of departments.

108. From the employees table, find those employees who earn highest salary in a department. Return department ID, employee name, and salary.

109. From the employees and job history tables, find those employees who did not have any job in the past. Return all the fields of employees.

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store