all questions are for Oracle practice

Write down the SQL statements for the following.

1. select all information from emp;
2. list all employee who have salary between 2000 and 5500.
3. List department numbers and names in department name order;
4. Display all the different job types.
5. List the details of the employees in departments 10 and 20 in alphabetical order of name.
6. List names and jobs of all clerks in department 20.
7. Display all employees names which have TH or LL in them.
8. List all employees who have a manager.
9. Display name and total remuneration for all employee.
10. Display all employee who were hired during 1981.
11. Find the minimum salary of all employees.
12. Find the minimum, maximum and average salaries of all employees of department 20.
13. List the minimum and maximum salary for each job type.
14. Find out how many managers there are without listing them.
15. Find the average salary and average total remuneration for each job type. Remember salesmen earn commission.
16. Find out the difference between highest and lowest salaries.
17. Find all departments which have more than 3 employees.
18. List lowest paid employees working for each manager. Exclude any groups where the minimum salary is less then 1000. Sort the output by salary.
19. Display all employee names and their department name, in department name order.
20. Display all employee names, department number and names.
21. Display the name, location and department of employees whose salary is more than 1500 a month.
22. Produce a list showing employees salary grades.
23. Show only employees on Grade 3.
24. Show all employees in ‘Dallas’.
25. List the employee name, job, salary, grade and department name for everyone in the company except clerks. Sort on salary, displaying the highest salary first.
26. List ename, job, annual_sal, deptno, dname, grade from emp, dept and salgrade table who earn 36000 a year or who are ‘clerks’.
27. Display the department that has not employees.
28. List all employees by name and number along with their manager’s name and number.
29. Modify solution to question 2 to display IND who has no manager.
30. Find all employees who joined the company before their manager.
31. Find the job that was filled in the first half of 1983, and the same job that was filled during the same period in 1984.