Question

please help me out with these SQL problems:

1. Create a treewalking list of every employee's last name, his manager's last name, and his position in the company. – The top level manager has position 1, this manager's subordinates position 2, their subordinates position 3, and so on. – Start the listing with employee number 100.

2. create the list of department names and the departmental costs (salaries added up). – Include only departments whose salary costs are between 15000 and 31000, and sort the listing by the cost.

3. Create a list of department names, the manager id, manager name (employee last name) of that department, and the average salary in each department.

4. Show the highest average salary for the departments in the employees table. Round the result to the nearest whole number.

5.– Create a list of department names and their monthly costs (salaries added up). • Tables Used: – Employees, Departments

6. Create a list of department names, and job_ids. Calculate the monthly salary cost for each job_id within a department, for each department, and for all departments added together. • Tables Used: – Employees, Departments

7. Create a list of department names, and job_ids. Calculate the monthly salary cost for each job_id within a department, for each department, for each group of job_ids irrespective of the department, and for all departments added together. (Hint: Cube). • Tables Used: – Employees, Departments

8. Expand the previous list (number 7) to also show if the department_id or job_id was used to create the subtotals shown in the output. (Hint: Cube, Grouping) • Tables Used: – Employees, Departments

9. Create a list that includes the monthly salary costs for each job title within a department. – In the same list, display the monthly salary cost per city. (Hint: Grouping Sets) • Tables Used: – Employees, Departments, Locations

10. Create a list of employee names as shown and department ids. – In the same report, list the department ids and department names. And finally, list the cities. – The rows should not be joined, just listed in the same report. (Hint: Union) . Tables Used: – Employees, Departments, Locations

Answer 1

Sql queries are:

4.Select Round(max(avg(sal))) from emp group by dept_name;

2.select deptname,departmnet cost from departments where sal>15000 and sal<31000 order by sal;