SQL Project — Working on Employee Database

1. From the employees table return complete information about the employees.

2. From the employees table, find the salaries of all employees. Return salary.

3. From the employees table, find the unique designations of the employees. Return job name.

4. From the employees table, list the employees’ name, increased their salary by 15%, and expressed as number of Dollars.

5. Produce the output of employees as follows.
Employee
JONAS(manager).

6. From the employees table, find those employees with hire date in the format like February 22, 1991. Return employee ID, employee name, salary, hire date.

7. From the employees table, count the number of characters except the spaces for each employee name. Return employee name length.

8. From the employees table, find the employee ID, salary, and commission of all the employees.

9. From the employees table, find the unique department with jobs. Return department ID, Job name.

10. From the employees table, find those employees who do not belong to the department 2001. Return complete information about the employees.

11. From the employees table, find those employees who joined before 1991. Return complete information about the employees.

12. From the employees table, compute the average salary of those employees who work as ‘ANALYST’. Return average salary.

13. From the employees table, find the details of the employee ‘BLAZE’.

14. From the employees table, find those employees whose commission is more than their salary. Return complete information about the employees.

15. From the employees table, find those employees whose salary exceeds 3000 after giving 25% increment. Return complete information about the employees.

16. From the employees table, find the names of the employees whose length is six. Return employee name.

17. From the employees table, find those employees who joined in the month January. Return complete information about the employees.

18. From the employees table, find the name of employees and their manager separated by the string ‘WORKS FOR’.

19. From the employees table, find those employees whose designation is ‘CLERK’. Return complete information about the employees.

20. From the employees table, find those employees whose experience is more than 27 years. Return complete information about the employees.

21. From the employees table, find those employees whose salaries are less than 3500. Return complete information about the employees.

22. From the employees table, find the employee whose designation is ‘ANALYST’. Return employee name, job name and salary.

23. From the employees table, find those employees who have joined in the year 1991.Return complete information about the employees.

24. From the employees table, find those employees who joined before 1st April 1991. Return employee ID, employee name, hire date and salary.

25. From the employees table, find those employees who are not working under a manager. Return employee name, job name.

26. From the employees table, find those employees who joined on 1st May 91. Return complete information about the employees.

27. From the employees table, find those employees working under the manger whose ID is 68319. Return employee ID, employee name, salary, and job age as “Experiences”.

28. From the employees table, find those employees who earn more than 100 as daily salary. Return employee ID, employee name, salary, and job age.

29. From the employees table, find those employees who retired after 31-Dec-99, completion of 8 years of service period. Return employee name.

30. From the employees table, find those employees whose salary is an odd value. Return complete information about the employees.

31. From the employees table, find those employees whose salary contains only three digits. Return complete information about the employees.

32. From the employees table, find those employees who joined in the month of APRIL. Return complete information about the employees.

33. From the employees table, find those employees who joined in the company before 19th of a month. Return complete information about the employees.

34. From the employees table, find those employees who are SALESMAN and experience more than 10 years. Return complete information about the employees.

35. From the employees table, find those employees of department id 3001 or 1001 and joined in the year 1991. Return complete information about the employees.

36. From the employees table, find those employees who are working for the department ID 1001 or 2001.Return complete information about the employees.

37. From the employees table, find those employees whose designation is ‘CLERK’ and work in the department ID 2001. Return complete information about the employees.

38. From the employees table, find those employees where:
a. the employees receive some commission which should not be more than the salary and annual salary including commission is below 34000.
b. Designation is ‘SALESMAN’ and working in the department ‘3001’. Return employee ID, employee name, salary and job name.

39. From the employees table, find those employees who are either CLERK or MANAGER. Return complete information about the employees.

40. From the employees table, find those employees who joined in any year except the month of February. Return complete information about the employees.

41. From the employees table, find those employees who joined in the year 91. Return complete information about the employees.

42. From the employees table, find those employees who joined in the month of June 1991. Return complete information about the employees.

43. From the employees table, find all the employees whose annual salary is within the range 24000 and 50000 (Begin and end values are included.). Return complete information about the employees.

44. From the employees table, find all those employees who have joined on 1st May, 20th Feb, and 3rd Dec in the year 1991. Return complete information about the employees.

45. From the employees table, find those employees working under the managers 63679 or 68319 or 66564 or 69000. Return complete information about the employees.

46. From the employees table, find those employees who joined in 90’s. Return complete information about the employees.

47. From the employees table, find those managers who are in the department 1001 or 2001. Return complete information about the employees.

48. From the employees table, find those employees who joined in the month FEBRUARY with a salary range between 1001 to 2000 (Begin and end values are included.). Return complete information about the employees.

49. From the employees table, find those employees who joined before or after the year 1991. Return complete information about the employees.

50. From the employees and department tables, find employees along with department name. Return employee ID, employee name, job name, manager ID, hire date, salary, commission, department ID, and department name.

51. From the employees, department, and salary_grade tables, find those employees who earn 60000 in a year or not working as an ANALYST. Return employee name, job name, (12*salary) as Annual Salary, department ID, and grade.

52. From the employees table, find those employees whose salary is higher than the salary of their managers. Return employee name, job name, salary, manager ID, manager name, manager’s salary.

53. From the employees and department tables, find those employees whose salary is between 2000 and 5000 (Begin and end values are included.) and location is PERTH. Return employee name, department ID, salary, and commission.

54. From the employees and salary_grade tables, find those employees whose department ID is 1001 or 3001 and salary grade is not 4. They joined the company before 1992–12–31. Return grade, employee name.

55. From the employees table, find those employees whose manager name is JONAS. Return employee id, employee name, job name, manager ID, hire date, salary, department ID, employee name.

56. From the employees and salary_grade tables, find the name and salary of the employee FRANK. Salary should be equal to the maximum salary within his or her salary group.

57. From the employees table, find those employees who are working either as a MANAGER or an ANALYST with a salary in the range 2000, 5000 (Begin and end values are included.) without any commission. Return complete information about the employees.

58. From the employees and department tables, find those employees working at PERTH, or MELBOURNE with an experience over 10 years. Return employee ID, employee name, department ID, salary, and department location.

59. From the employees and department tables, find those employees whose department location is SYDNEY or MELBOURNE with a salary range of 2000, 5000 (Begin and end values are included.) and joined in 1991. Return employee ID, employee name, department ID, salary, and department location.

60. From the employees, department, and salary_grade tables, find those employees of MARKETING department come from MELBOURNE or PERTH within the grade 3 ,4, and 5 and experience over 25 years. Return employee ID, employee name, salary, grade, department ID, department name, and department location.

61. From the employees table, list employees in ascending order on department ID and descending order on jobs. Return complete information about the employees.

62. From the employees table, find the entire unique jobs in descending order. Return job name.

63. From the employees table, find the employees in the ascending order of their annual salary. Return employee ID, employee name, monthly salary, salary/30 as Daily_Salary, and 12*salary as Anual_Salary.

64. From the employees table, find those employees who are either ‘CLERK’ or ‘ANALYST’. Sort the result set in descending order on job_name. Return complete information about the employees.

65. From the department and employees tables, find the department location of employee ‘CLARE’. Return department location.

66. From the employees table, find those employees who joined on 01-MAY-91, or 03-DEC-91, or 19-JAN-90. Sort the result-set in ascending order by hire date. Return complete information about the employees.

67. From the employees table, find those employees who draw salary less than 1000. Sort the result-set in ascending order by salary. Return complete information about the employees.

68. From the employees table, list the employees in the ascending order on job name and descending order on employee id. Return complete information about the employees.

69. From the employees table, list the unique jobs of department 2001 and 3001 in descending order. Return job name.

70. From the employees table, list all the employees except PRESIDENT and MANAGER in ascending order of salaries. Return complete information about the employees.

71. From the employees table, find the employees whose annual salary is below 25000. Sort the result set in ascending order of the salary. Return complete information about the employees.

72. From the employees table, list the employees who works as a SALESMAN. Sort the result set in ascending order of annual salary. Return employee id, name, annual salary, daily salary of all the employees.

73. From the employees table, list the employee ID, name, hire date, current date and experience of the employees in ascending order on their experiences.

74. From the employees and department tables, find the location of all the employees working in FINANCE or AUDIT department. Sort the result-set in ascending order by department ID. Return complete information about the employees.

75. From the employees and salary_grade tables, find the employees along with grades in ascending order. Return complete information about the employees.

76. From the employees, department, and salary_grade tables, find the employees according to the department in ascending order. Return name, job name, department, salary, and grade.

77. From the employees, department, and salary_grade tables, find all employees except CLERK and sort the result-set in descending order by salary. Return employee name, job name, salary, grade and department name.

78. From the employees, department, and salary_grade tables, find those employees work in the department 1001 or 2001. Return employee ID, name, salary, department, grade, experience, and annual salary.

79. From the employees table, find the highest salary. Return highest salary.

80. From the employees table, find the average salary and average total remuneration (salary and commission) for each type of job. Return name, average salary and average total remuneration.

81. From the employees table, compute the total annual salary distributed against each job in the year 1991. Return job name, total annual salary.

82. From the employees and department tables, list the employee id, name, department id, location of all the employees.

83. From the employees and department tables, find those employees who work in the department ID 1001 or 2001. Return employee ID, employee name, department ID, department location, and department name.

84. From the employees table, count the number of employees of each designation in each department. Return department id, job name and number of employees.

85. From the employees and salary_grade tables, list the grade, number of employees, and maximum salary of each grade.

86. From the employees, salary_grade, and department tables, find those departments where at least two employees work as a SALESMAN in each grade. Return department name, grade and number of employees.

87. From the employees and department tables, find those departments where less than four employees work. Return department ID, number of employees.

88. From the employees and department tables, find those departments where at least two employees work. Return department name, number of employees.

89. From the employees table, check whether the employees ID are unique or not. Return employee id, number of employees.

90. From the employees table, find number of employees and average salary. Group the result set on department id and job name. Return number of employees, average salary, department ID, and job name.

91. From the employees table, find those employees whose name start with ‘A’ and six characters in length. Return employee name.

92. From the employees table, find those employees whose name is six characters in length and the third character must be ‘R’. Return complete information about the employees.

93. From the employees table, find those employees whose name is six characters in length, starting with ‘A’ and ending with ’N’. Return number of employees.

94. From the employees table, find those employees who joined in the month of where the second letter is ‘A’. Return number of employees.

95. From the employees table, find those employees whose names contain the character set ‘AR’ together. Return complete information about the employees.

96. From the employees table, find those employees whose ID not start with the digit 68. Return employee ID.

97. From the employees table, find those employees whose names contain the letter ‘A’. Return complete information about the employees.

98. From the employees table, find those employees whose name ends with ‘S’ and six characters long. Return complete information about the employees.

99. From the employees table, find those employees who joined in any month, but the month name contain the character ‘A’. Return complete information about the employees.

100. From the employees table, find those employees who joined in any month, but the name of the month contain the character ‘A’ in second position. Return complete information about the 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