w3resource
w3resource logo
SQL Exercises

SQL Exercises, Practice, Solution - exercises on employee Database

SQL subqueries on employee Database [77 Exercise with Solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

Structure of employee Database:

employee database structure

1. Write a query in SQL to display all the details of managers.  Go to the editor

Sample table: employees


Click me to see the solution

2. Write a query in SQL to display the employee ID, name, job name, hire date, and experience experience of all the managers. Go to the editor

Sample table: employees


Click me to see the solution

3. Write a query in SQL to list the employee ID, name, salary, department name of all the 'MANAGERS' and 'ANALYST' working in SYDNEY, PERTH with an exp more than 5 years without receiving the commission and display the list in ascending order of location.  Go to the editor

Sample table: employees


Click me to see the solution

4. Write a query in SQL to display the employee ID, name, salary, department name, location, department ID, job name of all the employees working at SYDNEY or working in the FINANCE deparment with an annual salary above 28000, but the monthly salary should not be 3000 or 2800 and who does not works as a MANAGER and whose ID containing a digit of '3' or '7' in 3rd position. List the result in ascending order of department ID and descending order of job name.  Go to the editor

Sample table: employees


Click me to see the solution

5. Write a query in SQL to list all the employees of grade 2 and 3.  Go to the editor

Sample table: employees


Click me to see the solution

6. Write a query in SQL to display all the employees of grade 4 and 5 who are working as ANALYST or MANAGER.   Go to the editor

Sample table: employees


Sample table: salary_grade


Click me to see the solution

7. Write a query in SQL to list the details of the employees whose salary is more than the salary of JONAS.   Go to the editor

Sample table: employees


Click me to see the solution

8. Write a query in SQL to list the employees who works in the same designation as FRANK.   Go to the editor

Sample table: employees


Click me to see the solution

9. List the employees who are senior to ADELYN   Go to the editor

Sample table: employees


Click me to see the solution

10. Write a query in SQL to list the employees of department ID 2001 who works in the designation same as department ID 1001.   Go to the editor

Sample table: employees


Sample table: department


Click me to see the solution

11. Write a query in SQL to list the employees whose salary is same as the salary of FRANK or SANDRINE. List the result in descending order of salary.   Go to the editor

Sample table: employees


Click me to see the solution

12. Write a query in SQL to list the employees whose designation are same as the designation of MARKER or salary is more than the salary of ADELYN.   Go to the editor

Sample table: employees


Click me to see the solution

13. Write a query in SQL to list the employees whose salary is more than the total remuneration of the SALESMAN.  Go to the editor

Sample table: employees


Click me to see the solution

14. Write a query in SQL to list the employees who are senior to BLAZE and working at PERTH or BRISBANE.   Go to the editor

Sample table: employees


Click me to see the solution

15. Write a query in SQL to list the employees of grade 3 and 4 working in the department of FINANCE or AUDIT and whose salary is more than the salary of ADELYN and experience is more than FRANK. List the result in the ascending order of experience.   Go to the editor

Sample table: employees


Sample table: department


Sample table: salary_grade


Click me to see the solution

16. Write a query in SQL to list the employees whose designation is same as the designation of SANDRINE or ADELYN.   Go to the editor

Sample table: employees


Click me to see the solution

17. Write a query in SQL to list any job of department ID 1001 those that are not found in department ID 2001.   Go to the editor

Sample table: employees


Click me to see the solution

18. Write a query in SQL to find the details of highest paid employee.   Go to the editor

Sample table: employees


Click me to see the solution

19. Write a query in SQL to find the highest paid employees in the department MARKETING.   Go to the editor

Sample table: employees


Sample table: department


Click me to see the solution

20. Write a query in SQL to list the employees of grade 3 who have been hired in most recently and belongs to PERTH.   Go to the editor

Sample table: employees


Sample table: department


Sample table: salary_grade


Click me to see the solution

21. Write a query in SQL to list the employees who are senior to most recently hired employee working under KAYLING.   Go to the editor

Sample table: employees


Click me to see the solution

22. Write a query in SQL to list the details of the employees within grade 3 to 5 and belongs to SYDNEY. The employees are not in PRESIDENT designated and salary is more than the highest paid employee of PERTH where no MANAGER and SALESMAN are working under KAYLING.   Go to the editor

Sample table: employees


Sample table: department


Sample table: salary_grade


Click me to see the solution

23. Write a query in SQL to list the details of the senior employees as on year 1991.   Go to the editor

Sample table: employees


Click me to see the solution

24. Write a query in SQL to list the employees who joined in 1991 in a designation same as the most senior person of the year 1991.   Go to the editor

Sample table: employees


Click me to see the solution

25. Write a query in SQL to list the most senior employee working under KAYLING and grade is more than 3.   Go to the editor

Sample table: employees


Sample table: salary_grade


Click me to see the solution

26. Write a query in SQL to list the employee name, and job_name who are not working under a manager.   Go to the editor

Sample table: employees


Click me to see the solution

27. Write a query in SQL to display the total salary of employees belonging to grade 3.   Go to the editor

Sample table: employees


Click me to see the solution

28. Write a query in SQL to list the employees in department 1001 whose salary is more than the average salary of employees in department 2001.   Go to the editor

Sample table: employees


Click me to see the solution

29. Write a query in SQL to list the details of the departments where maximum number of employees are working.   Go to the editor

Sample table: employees


Click me to see the solution

30. Write a query in SQL to list the employees who are retiring after 31-Dec-99 after completion of 8 years of service period.   Go to the editor

Sample table: employees


Click me to see the solution

31. Write a query in SQL to list the employees who are not working in the department MARKETING.   Go to the editor

Sample table: employees


Click me to see the solution

32. Write a query in SQL to list those employees whose salary contain only 3 digits.   Go to the editor

Sample table: employees


Click me to see the solution

33. Write a query in SQL to list the name of the employees who are getting the highest salary of each department.   Go to the editor

Sample table: employees


Click me to see the solution

34. Write a query in SQL to list the employees whose salary is equal to the average of maximum and minimum salary.   Go to the editor

Sample table: employees


Click me to see the solution

35. Write a query in SQL to list the managers whose salary is more than the average salary his employess.   Go to the editor

Sample table: employees


Click me to see the solution

36. Write a query in SQL to list the employees whose salary is less than the salary of his manager but more than the salary of any other manager.   Go to the editor

Sample table: employees


Click me to see the solution

37. Write a query in SQL to list the name and average salary of employees in department wise.   Go to the editor

Sample table: employees


Click me to see the solution

38. Write a query in SQL to find out the least 5 earners of the company.   Go to the editor

Sample table: employees


Click me to see the solution

39. Write a query in SQL to list the managers who are not working under the PRESIDENT.   Go to the editor

Sample table: employees


Click me to see the solution

40. Write a query in SQL to list the name, salary, commission and netpay for those employees whose netpay is more than any other employee.   Go to the editor

Sample table: employees


Click me to see the solution

41. Write a query in SQL to list the name of the departments where highest number of employees are working.   Go to the editor

Sample table: employees


Click me to see the solution

42. Write a query in SQL to list the employees who joined in the year 91.   Go to the editor

Sample table: employees


Click me to see the solution

43. Write a query in SQL to list the employees who joined in the company on the same date.   Go to the editor

Sample table: employees


Click me to see the solution

44. Write a query in SQL to list the name of the departments where more than average number of employees are working.   Go to the editor

Sample table: employees


Click me to see the solution

45. Write a query in SQL to list the name of the managers who is having maximum number of employees working under him.   Go to the editor

Sample table: employees


Click me to see the solution

46. Write a query in SQL to list those managers who are getting salary to less than the salary of his employees.   Go to the editor

Sample table: employees


Click me to see the solution

47. Write a query in SQL to list the details of all the employees who are sub-ordinates to BLAZE.   Go to the editor

Sample table: employees


Click me to see the solution

48. Write a query in SQL to list the employees who are working as managers, using co-related subquery.   Go to the editor

Sample table: employees


Click me to see the solution

49. Write a query in SQL to list the name of the employees for their manager JONAS and also the name of the manager of JONAS.   Go to the editor

Sample table: employees


Click me to see the solution

50. Write a query in SQL to find all the employees who earn the minimum salary for a designation and arrange the list in ascending order on salary.   Go to the editor

Sample table: employees


Click me to see the solution

51. Write a query in SQL to find all the employees who earn the highest salary for a designation and arrange the list in descending order on salary.   Go to the editor

Sample table: employees


Click me to see the solution

52. Write a query in SQL to find the most recently hired emps in each department order by hire_date.   Go to the editor

Sample table: employees


Click me to see the solution

53. Write a query in SQL to list the name,salary, and department id for each employee who earns a salary greater than the average salary for their department and list the result in ascending order on department id.  Go to the editor

Sample table: employees


Click me to see the solution

54. Write a query in SQL to find the name and designation of the employees who earns a commission and salary is the maximum.  Go to the editor

Sample table: employees


Click me to see the solution

55. Write a query in SQL to list the name, designation, and salary of the employees who does not work in the department 1001 but works in same designation and salary as the employees in department 3001   Go to the editor

Sample table: employees


Click me to see the solution

56. Write a query in SQL to list the department id, name, designation, salary, and net salary (salary+commission) of the SALESMAN who are earning maximum net salary.  Go to the editor

Sample table: employees


Click me to see the solution

57. Write a query in SQL to list the department id, name, designation, salary, and net salary of the employees only who gets a commission and earn the second highest earnings.   Go to the editor

Sample table: employees


Click me to see the solution

58. Write a query in SQL to list the name and salary of FRANK if his salary is equal to max_sal of his grade.   Go to the editor

Sample table: employees


Click me to see the solution

59. Write a query in SQL to display the unique department of the employees.   Go to the editor

Sample table: employees


Click me to see the solution

60. Write a query in SQL to list the details of the employees working at PERTH.  Go to the editor

Sample table: employees


Click me to see the solution

61. Write a query in SQL to list the employees of grade 2 and 3 who belongs to the city PERTH.  Go to the editor

Sample table: employees


Click me to see the solution

62. Write a query in SQL to list the employees whose designation is same as either the designation of ADLYNE or the salary is more than salary of WADE.  Go to the editor

Sample table: employees


Click me to see the solution

63. Write a query in SQL to list the employees of department 1001 whose salary is more than the salary of ADELYN.   Go to the editor

Sample table: employees


Click me to see the solution

64. Write a query in SQL to list the managers who are senior to KAYLING and who are junior to SANDRINE.   Go to the editor

Sample table: employees


Click me to see the solution

65.Write a query in SQL to list the ID, name,location,salary, and department of the all the employees belonging to the department where KAYLING works.  Go to the editor

Sample table: employees


Click me to see the solution

66. Write a query in SQL to list the employees whose salary grade are greater than the grade of MARKER.   Go to the editor

Sample table: employees


Click me to see the solution

67. Write a query in SQL to list the employees of the grade same as the grade of TUCKER or experience is more than SANDRINE and who are belonging to SYDNEY or PERTH.   Go to the editor

Sample table: employees


Click me to see the solution

68. Write a query in SQL to list the employees whose salary is same as any one of the employee.   Go to the editor

Sample table: employees


Click me to see the solution

69. Write a query in SQL to list the total remuneration (salary+commission) of all sales person of MARKETING department.   Go to the editor

Sample table: employees


Click me to see the solution

70. Write a query in SQL to list the details of most recently hired employees of department 3001.   Go to the editor

Sample table: employees


Click me to see the solution

71. Write a query in SQL to list the highest paid employees of PERTH of grade 2 who joined before the most recently hired employee.   Go to the editor

Sample table: employees


Click me to see the solution

72. Write a query in SQL to list the highest paid employees working under KAYLING.   Go to the editor

Sample table: employees


Click me to see the solution

73. Write a query in SQL to list the name, salary, and commission for those employees whose net pay is greater than or equal to the salary of any other employee in the company.   Go to the editor

Sample table: employees


Click me to see the solution

74. Write a query in SQL to find out the employees whose salaries are greater than the salaries of their managers.   Go to the editor

Sample table: employees


Click me to see the solution

75. Write a query in SQL to find the maximum average salary drawn for each job name except for PRESIDENT.   Go to the editor

Sample table: employees


Click me to see the solution

76. Write a query in SQL to find the number of employees are performing the duty of a manager.   Go to the editor

Sample table: employees


Click me to see the solution

77. Write a query in SQL to list the department where there are no employees.   Go to the editor

Sample table: employees


Click me to see the solution

Practice Online


 

Structure of employee Database:

employee database structure


Amazon promo codes to get huge discounts for limited period (USA only).