9.25课后作业
1、整理博客
https://www.cnblogs.com/dadazunzhe/
2、查询教学部山东人的平均薪资
select dep 部门,AVG(salary) 平均薪资,area 地区
from emp GROUP BY area
HAVING area='山东';
3、查询姓名中包含英文字母n并且居住在上海的人的所有信息
select * FROM emp where area='上海' and name like '%n%';
4、查询姓名中包含英文字母n但不包含数字的人的所有信息
select * from emp where name not regexp '.*[0-9]' and name LIKE '%n%';
5、查看各部门的平均年龄并升序排序
select dep,AVG(age) from emp GROUP BY dep ORDER BY AVG(age);
6、查询各部门中年纪最大的人的姓名与居住地（户籍+区域）
SELECT
NAME,
concat_ws('-', area, PORT),
dep
FROM
emp
WHERE
(
(age, dep) IN (
SELECT
MAX(age),
dep
FROM
emp
GROUP BY
dep
)
);
7、查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资
SELECT
NAME,
salary
FROM
emp
WHERE
(salary, age) IN (
SELECT
MAX(salary),
age
FROM
emp
GROUP BY
age
HAVING
AVG(salary) > 5
);
8、查询每一个部门下的员工们及员工职责
select GROUP_CONCAT(empt.name),work
from empt INNER JOIN dept on empt.dep_id=dept.id
GROUP BY dept.name;
转载于:https://www.cnblogs.com/dadazunzhe/p/11587548.html
相关资源：Classic Menu for Office v9.25 破解版
