1. List the emps who joined in January.

Level1 SQL Assignments

2. List the emps who joined in the month of which second character is a. 3. List the emps whose sal is 4 digit number ending with zero. 4. List the emps whose names having a character set ll together. 5. List the emps those who joined in 80s. 6. List the emps who does not belong to deptno 20. 7. List the emps who joined in before or after 1981. 8. List all the emps except president & Mgr in asc order of salaries. 9. List the emps whose empno not starting with digit 78. 10. List the emps who are working under Mgr. 11. List the emps who joined in any year but not belongs to the month of March. 12. List all the clerks of deptno 20. 13. List the emps of deptno 30 or10 joined in the year 1981. 14. Display the details of Smith. 15. Display the location of Smith. 16. Display the total information of the emps along with grades in the asc order. 17. List all the grade2 and grade 3 emps. 18. Display all grade 4,5 Analyst and Mgr. 19. Find the highest Sal of the Emp table. 20. Find the total sal given to the MGR. 21. Display the total sal of emps belong to Grade 3. 22. Display the avg salaries of all CLERKS. 23. List the Mgr no & no. of emps working for those Mgrs in the asc Mgrno. 24. List the emps name, job and Manager who are without Manager. 25. List the no of emps in each dept where the number is more than 3. 26. Find out least 5 earners of the company. 27. List those emps whose sal is odd value. 28. List the emps whose sal contain 3 digits. 29. List the emps whose deptno is available in his salary. 30. List the emps whose first 2 chars from hiredate = last 2 chars of salary. 31. List the emps whose 10% of sal is equal to year of Joining. 32. List first 50% of chars of ename in lower case and remaining are upper case. 33. List the details of the emps whose grade is equal to one tenth of sales dept. 34. Produce the output of EMP table EMP_AND_JOB for ename and job. 35. Produce the following output from EMP

EMPLOYEE

----------------

SMITH(clerk)

ALLEN(salesman)

36. List the no of emps and avg salary within each dept for each job. 37. Find the max avg salary drawn for each job except for PRESIDENT. 38. List the deptno, name, job, salary and sal+comm. Of the salesman. 39. List the empno, ename, sal, grade of all emps. 40. List the grade 2&3 emp of Chicago. 41. List the details of the depts along with empno, ename or without the emps. 42. Display the number of emps for each job group deptno wise. 43. List the dept details where at least two emps are working. 44. Display the grade, number of emps, max sal of each grade. 45. Display dname, grade, no of emps where atleast two emps are CLERKS. 46. List the emps with their dept names. 47. List the emps name, job, salary, grade and dname except CLERKs and sort on the

basis of highest salary. 48. List the names of depts. Where at least 3 emps are working in each dept. 49. List the details of emps working at CHICAGO. 50. List the empno, ename,deptno, loc of all emps. 51. List the emps of deptno 20 whose jobs are same as deptno 10. 52. List the emps whose Sal is same as FORD or SMITH in desc order of Sal. 53. List the emps whose Jobs are same as MILLER or Sal is more than ALLEN. 54. List the emps whose Sal is > the total remuneration of the SALESMAN. 55. List the emps whose jobs same as ALLEN Or SMITH. 56. List the jobs of Deptno 10 those are not found in dept 20. 57. List the emps of Emp1 who are not found in deptno 20. 58. Find the details of highest paid employee. 59. List the most recently hired emp of grade 3 belongs to the Loc CHICAGO. 60. List the emps who are senior to most recently hired emp who is working under Mgr

KING. 61. List the name and salary of FORD if his salary is equal to hisal of his Grade. 62. List the emps whose salary is equal to average of maximum and minimum. 63. List the name, sal, comm. And net pay is more than any other employee. 64. List the emps those who joined in company before 15th of the month. 65. List the emps who are working as managers. 66. List the emps who are working as managers using co-related sub-query. 67. Find out how many managers are there in the company. 68. Find all the emps who earn minimum sal for each job wise in asc order of sal. 69. Find out the most recently hired emps in each dept order by hiredate. 70. List the emp name, sal and deptno for each emp who earns a sal greater than the

avg for their dept order by deptno. 71. List the deptno where there are no emps 72. Find the name and job of the emps who earn Max salary and Commission. 73. List the name, job and salary of the emps who are not belonging to the dept 10 but

who have the same job and salary as the emps of dept 10. 74. List out the name, job, sal of the emps in the department with the highest avg sal. 75. List the emps who joined in the second half of 82. 76. List the emps joined in Jan with salary ranging from 1500 to 4000.

77. List the empno, ename, sal, job of emps with the ann sal < 34000 but receiving some comm., Which should not be > sal and designation should be salesman working for dept 30

78. List the emps whose job is same as smith. 79. List the Mgrs who are senior to king & who are junior to smith. 80. List the emps whose sal is same as ford or blake. 81. List the highest paid emp. 82. List the details of most recently hired emp of dept 30. 83. List the highest paid emp working under king. 84. List the second highest paid emp. 85. List the emps who are Senior to BLAKE working at CHICAGO & BOSTON. 86. List empno, ename,sal, deptno of dept 10 emps in the asc order of sal. 87. Find the highest paid employee of sales dept. 88. List the details of emp belongs to New York with the Grade 3 to 5 except

PRESIDENT whose sal > the highest paid emp of CHICAGO in Group where there is MANAGER & ANALYST not working for Mgr KING.

89. List the details of the dept where the max no of emps are working. 90. List the name, job, dname, Manager, salary, Grade dept wise. 91. List the names of emps who are getting the highest salary dept wise. 92. List the managers whose salary is more than his emps avg sal. 93. List the emps whose sal < his Manager but more than other Manager. 94. List the employee names and his annual salary dept wise. 95. List the name of the dept where more than avg. no of emps are working. 96. List the emps who are working as managers using co-related sub-query. 97. List the emps whose sal > blakes. 98. List the emps who belongs to dept 10 and sal > allens sal. 99. List the emps who are senior to miller 100. List the emps who are working as either mgr or analyst with sal ranging from

2000 and 5000 and without comm..