Sql Queries.doc

SQL Queries 1. Display all the information of Emp table. select * from emp

2. Display unique Job from Emp table. select Distinct(job) from emp

3. List the emps in asc order of their Salaries. select * from emp order by sal asc

4. 5. 6. 7. 8.

List the detail of emp in the asc order of the DeptNo and desc of job. Display all the unique job group in descending order. Display all the details of all ‘Mgrs’. List the emps who joined before 1982. list the Empno, Empname ,Sal ,DailySal of all emps in asc order of annsal. 9. Display the Empno ,Empname ,job,HireDate,exp of all Mgrs. 10. (Wrong)List the Empno ,Ename ,Sal of all the emp working for Mgr 7639. 11. Display all the details of the emps whose comm. Is more than their sal. 12. List the emps in the asc order of Designations of those joined Secondhalf of 1981. 13. List the emp along with their Exp and Daily Sal is more than Rs 100. 14. List the Emps who is either Clerk or Analyst in desc order. 15. List the emps who joined on 1 may 81,3dec81 , 17-Dec-81,19-jan180 in asc order of seniority 16. List all the emp who working for the dept no 10, or 20. 17. List the emp s who joined in the year 81 18. List all the employee who joined in the month August 1980. 19. select all the employee whose annual salary ranging from 22000 and 45000. 20. List the Enames those are having five character in their name. 21. List the Enames those are starting with s and with five character. 22. List the emps those are having four character and third character must be r. 23. List the five character name starting with s and ending with h 24. List the employee who joined in jan. 25. List the name of employee who joined in the month of which second character is ‘a’. 26. List the emps whose Sal is four digit number ending with 0. 27. List the emps whose names having a character set ‘ll’ together. 28. List the emps who joined in ‘80’s. 29. List the emps who does not belongs to Deptno 20 30. List all the employees except ‘president’ & ‘MGR’ in asc order of Salaries. 31. List the employee whose joining date before or after 1981. 32. List all the emps whose empno not starting with digit 78.

33. List the emps who are working under ‘MGR’ 34. List all the emps who joined in any year but not belongs to the month of march 35. List all the clerk of Deptno 20. 36. List the emps of Deptno 30 or 10 Joined in the year 1981 37. Display all the details of smith. 38. Display the location of smith. 39. List the total information of emp table along with Dname and Loc of all the emps working under ‘ACCOUNTING’ & ‘RESERCH’ in the asc deptno. 40. List the Empno , Ename, Sal ,Dname of all the ‘MGRS’ and ‘ANALYST’ working in NEW YORK,Dallas with an exp more than 7 yr without receiving the Comm asc order of Loc. 41. Display the Empno , Ename ,Sal ,Dname ,Loc ,Deptno , job of all the emps working at Chicago or working for Accounting dept with Ann sal >2800 but the sal should not be =3000 or 2800 whose no is having a digit ‘7’ or ‘8’ in 3rd positioning asc order of Deptno and desc order of jobs. 42. List the Empno ,Ename ,Sal,Dname,Grade,Exp,Ann sal of emps working for dept 10 or 20. 43. List the detail of the emp whose salaries more than the employee BLAKE 44. List the emps whose jobs are same as ALLEN. 45. List the emp who are the Senior of King. 46. List the emps whose Sal is same is same as Ford or Smith in desc order of sal 47. List the emps whose jobs is are same as MILLER or Sal is more than ALLEN 48. List the emps who are senior to Blake working at CHICHAGO & BOSTON. 49. list the emps whose jobs same as Smith or Allen. 50. Write a Query to display the detail of emps whose sal is same as of (a) Employee Sal of EMP1 table. (b) ¾ Sal of any Mgr of EMP2 table. (c) The sal of any sales person with the exp of 5 years belongs to the Sales dept of emp3 table. (d) Any grade 2 employee of emp4 table. (e) Any grade 2 & 3 employee working for sales dept or Operations dept joined in 89. 51. List the jobs of Deptno 10 those are not found in Deptno 20. 52. Find the highest sal of Emp table. 53. Find the details of the highest paid Employee. 54. Find the highest paid employee of sales dept. 55. List the most recently hired employee of grade 3 belong to location chichago.

56. List the emps who are the senior to most recently hired employee who is working under MGR King. 57. List the detail of emp belongs to NewYork with grade 3 to 5 except ‘president ‘ whose sal > highest paid emp of ‘CHICAGO’ in group where there is Manager and ‘ANALYST’ not working for MGR king . 58. Display the detail of most senior employee belongs to 1981 59. List the emps whose joined in81 with job same as the most senior person of year 81. 60. List the most senior emp working under King grade is more than 3. 61. Find the total Sal given to the MANAGER 62. Find the Total Annual Salary to distribute Job wise in the yerar 81 63. Display the Average Sal of all Clerks 64. List the emps in dept 20 whose Sal is > the Average sal of Deptno 10 emp. 65. List the MGR no ,empno working for those Mgrs in the asc Mgrno 66. List the dept details where at least two emps are working. 67. Display the grade ,number of emps ,max sal of each grade. 68. Display Dname,Grade, No of emps where atleast two emps are CLERK 69. List the details of the dept where the maximum no of emps are working. 70. Display the emps whose Mgr name is jones 71. List the emps whose salary is more than 3000 after giving 20% increment. 72. List the emps with their Dept name 73. List the emps Name,Dept, Sal,& comm. For those sal is between 2000 and 5000. 74. List the employee who are not working for sales dept. 75. List the grade ,employees Name for deptno 10 or 30 but the sal grade is not 4 while they joined the company before’31-dec-82’ 76. List the name ,job ,Dname,loc for those employee who are working as manager. 77. List the employee s whose manager is jones also list their manager name. 78. List the emps Name,,job,sal who are without MANAGER. 79. List the emps Name,job,salary,grade,DName except ‘CLERK’ and sort on the basis of Salary. 80. List the names of emps who are getting the highest salary deptwise. 81. List the employee whose salary is equal to average of maximum and minimum 82. List the names of in each dept where number is more than 3. 83. List the names of depts. Where at least 3 employee are working in each dept.

84. List the Managers whose sal is more than his emps Average salary. 85. List the employee Names and his annual salary Deptwise 86. Find out least 5 earner of the company. 87. Find out the no of employees whose sal is greater than their manager salary. 88. List the Manager who are not working under ‘President’ but working under other manager. 89. List the records from emp whose depptno is not in dept 90. List the Name ,sal,Comm,Netpay is more than any other employees. 91. List the ename who are retiring after 31-dec-89 the max job period is 20 yr. 92 List those employee whose sal is odd 93 List the employees whose Salary contain 3 digit. 94 List the employees who joined in the month DEC. 95 .List the emps whose names contains ‘A’. 96 List the employees whose deptno is available in his salary. 97 .List the emps whose first 2 chars from Hiredate = last to character of Salary. 98 .List the employee name whose 10% of salary is equal to year of joining 99 List the Dname whose No of employees is = to number of char s in the Dname. 100 List the no of chars of which is no .of employee’s in any other dept. 101 . List the employees who are working as a manager. 102 Count the no of employees working As manager.

103. List the no of employees who joined in the company on the same date. 104. List the details of emps whose grade is equal to one tenth of sales department. 105. 106. 107. 108.

List the Manager name who is having max no.of emps working under him. List the ename and sal is increased by 15% and expressed as no of Dollars. Produce the output of emp table ‘EMP AND JOB’ for Ename and Job. Produce the following output from emp EMPLOYEE SMITH (clerk) ALLEN(Salesman) 109. List the EMPLOYEES with the HIREDATE in format JUNE 4,1988. 110. Write query will return the day of the week for any date entered in format ’DDMM-YY’ 111. Count the no of character with out considering spaces for each name. 112. List those Managers who are getting less than his emps salary. 113. Print the details of all the emps who are sub-ordinate of Blake. 114. List the emp who are working as Manager using co-related sub-Query. 115. List the emp whose Mgr name is Jones and also with his Manager Name. 116. Define a variable representing the expressionused to calculate on emps total annual remuneration use the expression used to calculate on empswho can earn 30000 a year or more. 117. Find average salary and average total remuneration for each Job type. Remember Salesman earn Commission. 118. List emps who are drawing less than 1000 Sort the output by Salary. 119. List the employee name ,job,Annualsalary,Deptno,Department name who earn 36000 a year or not Clerk. 120. Find out the job that was filled in the first half of 1983 and same Job that was filled during the same period of 1984, 121. Find out the emps who joined in the company before their Manager. 122. Find all the emps who earn minimum salary for each job wise in asc order of sal. 123. List the department no, there are no emp. 124. Find the gross sal of emp where T.A 30%,DA 40%,HRA 50% in asc order. 125. List all the employee who are clerk or manager. 126. Display the unique dept with jobs. 127. Find the union of Deptno,ename and dname. 128. Find the Intersection of Deptno,ename,dname

.