sql语言实践之自学SQL网(SQL Lesson10,11)

SQL Lesson 10: 在查询中进行统计I (Pt. 1)

SQL lesson 10链接入口

练习 do it — 请完成如下任务

1.【统计】找出就职年份最高的雇员(列出雇员名字+年份）

--请输入sql
SELECT Name,Years_employed
FROM employees
GROUP BY Years_employed
ORDER BY Years_employed desc
LIMIT 1
;

2.【分组】按角色(Role)统计一下每个角色的平均就职年份

SELECT Role,AVG(Years_employed)
FROM employees
GROUP BY Role
;

3.【分组】按办公室名字总计一下就职年份总和

SELECT Building,SUM(Years_employed)
FROM employees
GROUP BY Building
;

4.【难题】每栋办公室按人数排名,不要统计无办公室的雇员

SELECT Building,COUNT(Name)
FROM employees
WHERE Building IS NOT NULL
GROUP BY Building
;

SQL Lesson 11: 在查询中进行统计II (Pt. 2)

SQL lesson 11链接入口

练习 do it — 请完成如下任务

1.【统计】统计一下Artist角色的雇员数量

SELECT COUNT(Name)
FROM employees
GROUP BY Role
HAVING Role = 'Artist'
;

2.【分组】按角色统计一下每个角色的雇员数量

SELECT Role,COUNT(Name)
FROM employees
GROUP BY Role
;

3.【分组】算出Engineer角色的就职年份总计

SELECT SUM(Years_employed)
FROM employees
GROUP BY Role
HAVING Role = 'Engineer'
;

4.【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

SELECT COUNT(Name) as 'count',Role,0 as 'bn'
FROM employees
WHERE Building IS NULL
GROUP BY Role
UNION
SELECT COUNT(Name) as 'count',Role,1 as 'bn'
FROM employees
WHERE Building
GROUP BY Role
;

结尾 (其它参考答案)

sql语言实践之自学SQL网(SQL Lesson12)
sql语言实践之自学SQL网(SQL Lesson10,11)
sql语言实践之自学SQL网(SQL Lesson8,9)
sql语言实践之自学SQL网(SQL Lesson 0-7)

----------EOF----------