sql语言实践之自学SQL网(SQL Lesson10,11)
这是一个目录
自学SQl网链接入口
SQL Lesson 10: 在查询中进行统计I (Pt. 1)
练习 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)
练习 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)