1，获取各个部门最高薪水的人员
使用连接查询
select e.department,e.`name`,t.dept,t.maxMoney from (select e.department as dept,max(e.money) as maxMoney from employee e group by e.department)t join employee e on t.dept = e.department where e.money = t.maxMoney;
2，哪些人的薪水在部门的平均薪水之上
select e.department,e.`name`,t.`平均薪水`,e.money from (select e.department as dept,avg(e.money) as 平均薪水 from employee e group by e.department)t join employee e on e.department = t.dept where e.money > t.`平均薪水`;
3，求各个部门平均薪水的等级
表：
select t.`部门` as 部门,t.`平均薪水` as 平均薪水,m.grade from (select e.department as 部门,avg(e.money) as 平均薪水 from employee e group by e.department)t join moneygrade m on t.`平均薪水` between m.low and m.high;
4, 不使用组函数max()，获取最高薪水
方案一：
select e.name,e.money from employee e order by e.money desc limit 1;
方案二:
第一步
select distinct e.name ,e.money from employee e join employee b on e.money < b.money;
可以发现除了最大值，其他的值都符合要求
第二步
select e.name,e.money from employee e where e.money not in (select distinct e.money from employee e join employee b on e.money < b.money);
5，求平均薪水最低的等级的部门名称
select t.department from (select e.department ,avg(e.money) as 平均薪水 from employee e group by e.department)t join moneygrade m on t.平均薪水 between m.low and m.high where m.grade = ( select min(m.grade) from (select t.department,t.`平均薪水`,m.grade from (select e.department ,avg(e.money) as 平均薪水 from employee e group by e.department)t join moneygrade m on t.平均薪水 between m.low and m.high)m);
6,三个表，学生表（S），课程表( C)，学生选课表(SC)
课程表：分别对应课号（cno），课名（cname），老师（cteacher）
学生表：学号(sno)，姓名(sname)
课程表：学号(sno)，课号(cno)，成绩(scgrade)
学号，课号分别为外键
向学生表中添加数据
课程表添加数据
选课表添加数据
问题1:找出没选过郑老师的所有学生的姓名
select sname from s where sno not in (select sno from sc where cno = (select cno from c where cteacher = '郑老师'));
问题2：列出两门（含两门）以上不及格学生姓名及平均成绩
#分组先求每个人成绩小于60分的门数 select sc.sno , s.sname ,count(*) as studentSum from sc join s on s.sno = sc.sno where sc.scgrade < 60 group by sc.sno having studentSum >= 2;
问题3：即学过1号课程和2号课程所有学生的姓名
select s.sname from sc join s on sc.sno = s.sno where cno = 1 and sc.sno in( select sno from sc where cno = 2 );
7，新建两个表，分别为dept部门信息表和emp员工表
然后呢插入数据，emp表如下
dept表如下
1，列出所有的员工及领导的名字
select e.ename, b.ename as leadername from emp e left join emp b on e.mgr = b.empno;
并出现结果
外连接查询的条数永远大于等于内连接
2，列出受雇日期早于其上级的所有的员工编号，姓名，部门名称
#列出受雇日期早于其上级的所有的员工编号，姓名，部门名称 select d.dname,e.empno,e.ename from emp e join emp b on e.mgr = b.empno join dept d on e.deptno = d.deptno where e.hiredate < b.hiredate;
最终的数据为
3，列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.dname,e.* from emp e right join dept d on e.deptno = d.deptno;
4,列出至少有5个员工的所有部门
select e.deptno,count(e.ename) as totalEmp from emp e group by e.deptno having totalEmp >= 5;
5,列出在部门"SALES"<销售部>工作的员工的姓名，假定不知道销售部门的部门编号
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
6,列出薪资高于公司平均水平的所有员工，所在部门，上级领导
select e.ename, d.dname, b.ename from emp e join dept d on e.deptno = d.deptno left join emp b on e.mgr = b.empno where e.sal > (select avg(sal) as angsal from emp);
7,列出薪水高于在部门30狗熊的所有员工的薪金的员工姓名和薪金，部门名称
select d.dname, e.ename, e.sal FROM emp e join dept d on e.deptno = d.deptno where e.sal > (select max(sal) as maxSal from dept);
8,列出在每个部门工作的员工数量，平均工资
select d.dname,count(*) as '人数',avg(e.sal) as '平均工资' from emp e join dept d on d.deptno = e.deptno group by e.deptno;
9,列出所有的员工的姓名，部门名称和工资
select e.ename as '员工姓名', d.dname as '部门名称', e.sal as '工资' from emp e join dept d on e.deptno = d.deptno
10,列出所有的部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(e.ename) as totalEmp from emp e right join dept d on e.deptno = d.deptno group by d.deptno,d.dname,d.loc;
11,列出各种工作的最低工资以及从事此工作的雇员姓名
select distinct e.job,t.minSal,e.ename from emp e join (select e.job,min(e.sal) as minSal from emp e join dept d group by e.job)t on e.sal = t.minSal
12,列出各个部门MANAGER的最低薪金
select w.deptno,min(w.sal) as minSal from emp w where w.job = 'MANAGER' group by w.deptno;
13,求出员工领导超过3000的员工名称和领导名称
select e.ename, b.ename as leadername from emp e join emp b on e.mgr = b.empno where b.sal > 3000;
14,名字中带S字符的部门的工资合计和部门人数
select d.dname, sum(e.sal) as sumMoney, count(e.ename) as sumPeople from emp e join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.dname
15,给任职期间超过30年的员工加薪
update emp set sal = sal * 1.1 where (to_days(now()) - to_days(hiredate))/365 > 30;
