Write a query to display the department number, the job, the number of employees and the average salary within each department for each job. Round your results to the nearest whole number.
computer science
Description
Lab 2
1. Write a
query to display the department number, the job, the number of employees and
the average salary within each department for each job. Round your results to
the nearest whole number.
2. Find the maximum
and average salary for each job except for ‘PRESIDENT’.
Round the result to two decimals.
3. List the
department number and the number of employees where at least two employees are
working the department.
4. Find the department
number, the name, the job and the commission of employees that are working on
that department and are receiving a commission. Sort the output in descending
order of commission.
5. Display the employee name, job and department number
of all employees in each of the department 10 and 30 in alphabetical order by
name.
6. Write a query to display the number of employee and
the average commission for each department number. Label the columns
EMPLOYEE_NUM, AVG_COMM and DEPT_NUM respectively. Include all null values for
commission.
7.
Display the job, the manager number and the salary of
the lowest paid employee for that manager. Exclude anyone whose job is SALESMAN
and manager is not known. Exclude any groups where the minimum salary is in the
range of $1500 and $2850. Sort the output in descending order of salary.
8.
Display the average number of managers without listing
them for each department number. Include the managers that don’t have managers.
Label the column Number of Managers.
9. List the total information of employee with department name and the location
of all the employees working under ‘Accounting’ and ‘Research’ in the ascending
order of deptno.
10. Display the Grade, Number of employees, and maximum salary of each
grade.
