MySQL-数据分组(GROUP BY)-练习

练习

---

1.显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Max、Min、Sum 和 Avg。四舍五入结果为最近的整数。

SELECT
ROUND(MAX(e.SALARY)) max,
ROUND(MIN(e.SALARY)) min,
ROUND(SUM(e.SALARY)) sum,
ROUND(AVG(e.SALARY)) avg
FROM employees e;

2.写一个查询显示每一工作岗位的人数。

SELECT
e.JOB_ID,COUNT(*)
FROM employees e
GROUP BY e.JOB_ID;

3.确定经理人数，不需要列出他们，列标签是 Number ofManagers。提示：用MANAGER_ID列决定经理号。

SELECT
COUNT(DISTINCT e.MANAGER_ID)
FROM employees e;

4.写一个查询显示最高和最低薪水之间的差。

SELECT
MAX(e.SALARY) - MIN(e.SALARY)
FROM employees e;

5.显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。

SELECT e.MANAGER_ID,MIN(e.SALARY)
FROM employees e
WHERE e.MANAGER_ID is not null
GROUP BY e.MANAGER_ID
HAVING min(e.SALARY) > 6000
ORDER BY min(e.SALARY) desc;

6.写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

SELECT
d.DEPARTMENT_NAME,d.LOCATION_ID,COUNT(*),ROUND(AVG(e.SALARY))
FROM employees e,departments d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME,d.LOCATION_ID;