Some Queries In Oracle

1. Display MGR and ENAME from EMP table. 2. Display the records where the job is either SALESMAN or DEPT NO =20. 3. Display the record where MGR is blank. 4. Display all the records where names begin with ‘A’. 5. Display the records whose ENAME begins with the letter in between ‘K’ to ‘M’. 6. Display all the records whose ENAME contain 5 characters. 7. Display total number of employees department wise. 8. Display the department that contains maximum employees. 9. Display the employee who getting maximum salary. 10. Display the last 4 character of each employee 11. Display the year and the total number of employee hired during a year. 12. Display the year and the total number of employee for the year in which maximum numbers of employee were hired. 13. Display the record for all employees who have the same job as EMP NO=7838. 14. Display all the records where job contain the letter “E” 15. Display the record where the salary of the employee is greater than the salary obtained by ‘TURNER” 16. Select all employee who are either ‘clerk’ or ‘salesman’ and have salary greater than 1500. 17. Select all the employee whose names start with ‘Th’ or ‘Lh’. 18. Display names ,annual salary ,comm. of those salesperson whose monthly salary >comm.. 19. Select employees who are with the company for more than 25 years 20. Display details of those employees who have join the company on same date. 21. Display details of highest paid employee in the EMP table. 22. Display the 2nd highest paid employee in the EMP table 23. Display DEPT NO which do not have clerk. 24. Update sal of all employees by 2000 who are working in the company for more than 15 years and drawing sal<4000. 25. Display ENAME , HIREDATE in ascending order. 26. Suppose a table contain thousand data and you are needed to select the 2nd last record from the table .write the query. 27. Print the list of employees displaying last salary if exactly 1500 .display on target if less than 1500. 28. Display DEPT NO and the name of the employee who gets minimum salary in each department. 29. Display ENAME ,annual salary and comm. Of every employee in DEPT NO 30 and fill the comm. Field with 0 if it doesn’t contain any value. 30. Display details of that employee who have no manager. 31. Display details of those employees who are clerk and whose salary lies between 1000 and 2000 32. . List the employee by name , salary, and department name for every employee in the company except clerks , sort on salary , displaying the highest salary first. 33. Display Name and Total Remuneration for all employees. 34. List the employee name and salary increased by 20%.

35. Display each employee name with hire date and salary review date .assuming review date is first year after hire date 36. Find out how many managers are there without listing them. 37. Find the average salary and avg Total Remuneration for each job types. Remember Salesman earn comm.. 38. Find the job that was filled in the first half of 1983 and the same job that was filled during the same period of 1984. 39. List all employees by NAME and NUMBER along with their manager’s details. 40. Write the query to display details for any employee who earns a salary greater that the average for their department .Sort in Department Number order. 41. Find out the employee name , salary , dept no, who earns greater than every employee in Department no 30. 42. Find the Department having maximum employees. 43. Find the employee who earns more than ‘MILLER” 44. Find all the person who are not MANAGERS 45. Find the name of person getting same salary in different department . 46. Find the job whose average salary is equal to maximum average salary of any job. 47. Find out the details of all the person who have been assigned same job in different DEPT NO. 48. Find the department which has more than 3 employees. 49. Write a query which will return the day of the week ,for any date entered in format :DD:MM:YY. 50. Check whether all employee number are indeed unique. 51. List lowest paid employees working for each manager. Exclude any group where the min salary is <1000.Sort the output by salary. 52. Show the records of employees working in DALLAS location. 53. List the following details for employees who earn $36,000 a year or who are clerk. 54. List all the employees by name, and number along with their manager’s name and number. 55. Find the employees who joined the company before their managers. 56. Find the job with the highest average salary. 57. Find the employees who have at least one person reporting to them. 58. Find all employees whose department is not in DEPT table. 59. Find Department which has no employees. 60. Display the following information for the department with the highest annual remuneration bill. 61. In which year did most people join the company?Display the year and number of employees.