1.取得每个部门最高薪水的人员名称①找出每个部门的最高薪水按照部门进行分组求最大值select deptno,max(sal) from emp group by deptno;令上面这个表叫临时表t②与员工表进行表连接select t.*,e.ename from t join emp e on t.deptno e.deptno and t.max(sal) e.sal;③把两个SQL语句相结合select t.*,e.ename from (select deptno,max(sal) as maxsal from emp group by deptno) t join emp e on t.deptno e.deptno and t.maxsal e.sal;2.哪些人的薪水在部门的平均薪水之上①查看部门的平均薪水(根据部门编号分组)select deptno,avg(sal) as avgsal from emp group by deptno;令上表为临时表t②进行表连接select t.*,e.ename,e.sal from (select deptno,avg(sal) as avgsal from emp group by deptno) t join emp e on e.deptno t.deptno and e.sal t.avgsal;3.取得每个部门平均薪水的等级①查看每个部门的平均薪水select deptno,avg(sal) as avgsal from emp group by deptno;令上表为临时表t②进行表连接select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between losal and hisal;4.取得部门中所有人的平均的薪水等级薪水等级的平均值①查看部门中所有人的薪水等级select e.deptno,s.grade,e.ename,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal;②按照部门分组求薪水等级的平均值select e.deptno,avg(s.grade) as avggrade from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;5.不准用组函数Max取得最高薪水给出两种解决方案第一种降序排序取第一个limitselect sal from emp order by sal desc limit 1;第二种maxselect max(sal) from emp;第三种自链接select sal from emp;令上表为a表再复制一份一模一样的为b表条件a.salb.sal那a表的5000肯定就找不到比b表小的查询结果肯定就查不到5000;distinct是去重select distinct a.sal from emp a join emp b on a.sal b.sal;select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal b.sal);6.取得平均薪水最高的部门的部门编号至少给出两种解决方案第一种根据降序取第一个select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;第二种max函数①找出最高的平均薪水select max(avgsal) from (select avg(sal) as avgsal from emp group by deptno) tselect deptno,avg(sal) as avgsal from emp group by deptno having avgsal (select max(avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);7.取得平均薪水最高的部门的部门名称多一个表连接和dept表连接按照部门名称进行分组.select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno d.deptno group by d.dname order by avgsal desc limit 1;8.求平均薪水的等级最低的部门的部门名称①找到最低的平均薪资select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;②找到最低薪资的等级select s.grade from salgrade s join (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) t on t.avgsal between s.losal and s.hisal;③找出每个部门的平均薪水按照部门名称分组求每一组的平均薪水而且还需要关联等级select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno d.deptno group by d.dname;④第三步是临时表t与salgrade s连接(找出每个部门的平均等级)select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno d.deptno group by d.dname)t join salgrade s on t.avgsal between s.losal and s.hisal;⑤最后一步我们再第二步找到最低等级为3级现在把条件加上select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno d.deptno group by d.dname)t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade (select s.grade from salgrade s join (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) t on t.avgsal between s.losal and s.hisal);9.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名①首先比普通员工薪水高的一定是领导人然后我们第一步查询所有领导的编号注意里面有一个nullselect distinct mgr from emp where mgr is not null②找出普通员工的最高薪水select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);③找出薪资比第二步大的select ename from emp where sal(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));10.取得薪水最高的前五名员工select ename,sal from emp order by sal desc limit 5;11.取得薪水最高的第六到第十名员工select ename,sal from emp order by sal desc limit 5,5;12.取得最后入职的5名员工select ename,sal,hiredate from emp order by hiredate desc limit 5;13.取得每个薪水等级有多少员工①查询每个员工的薪水等级emp e和slagrade s 连接select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s,hisal;② 基于以上的查询结果继续按照grade分组计数select s.grade, count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;14.列出所有员工及领导的姓名表的自连接外连接select e.ename as 员工,l.ename as 领导 from emp e left join emp l on e.mgr l.empno;里面的有一个是null我们改成没有领导用ifnull函数select e.ename as 员工,ifnull(l.ename,老板) 领导 from emp e left join emp l on e.mgr l.empno;15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称①先列出员工编号、姓名、部门名称select e.empno,e.ename,d.dname from emp e join dept d on e.deptno d.deptno;再连接领导这张表select e.empno,e.ename,d.dname from emp e join dept d on e.deptno d.deptno join emp l on e.mgr l.empno where e.hiredate l.hiredate;16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门select e.*,d.dname from emp e right join dept d on e.deptno d.deptno;17.列出至少有5个员工的所有部门select deptno from emp group by deptno having count(*) 5;18.列出薪金比SMITH多的所有员工信息①找出Smith的薪资select sal from emp where ename SMITH;②比Smith多的select * from emp where sal (select sal from emp where ename SMITH);19.列出所有CLERK(办事员)的姓名及其部门名称,部门的人数①找出部门人数select d.dname,count(*) as total from emp e join dept d on e.deptno d.deptno group by d.dname;②表连接select e.ename,d.dname,t.total from emp e join dept d on e.deptno d.deptno join (select d.dname,count(*) as total from emp e join dept d on e.deptno d.deptno group by d.dname) t on d.dname t.dname where job CLERK;20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数①按照工作岗位分组select from emp group by job;②最低薪资大于1500select from emp group by job having min(sal) 1500;③从事此工作的全部雇员人数select job,count(*) from emp group by job having min(sal) 1500;21.列出在部门SALES销售部工作的员工的姓名,假定不知道销售部的部门编号①查询部门的部门编号select deptno from dept where dname SALES;②select ename from emp where deptno (select deptno from dept where dname SALES);22.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级emp e连接dept d连接emp l连接salgrade sselect e.ename,d.dname,l.ename,s.grade from emp e join dept d on e.deptno d.deptno left join emp l on e.mgr l.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal(select avg(sal)from emp);23.列出与SCOTT从事相同工作的所有员工及部门名称select e.ename,d.dname from emp e join dept d on e.deptno d.deptno where e.job (select job from emp where ename SCOTT) and e.ename SCOTT;24.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金①30部门员工薪资查询出来select distinct sal from emp where deptno 30;②inselect ename,sal from emp where sal in(select distinct sal from emp where deptno 30) and deptno 30;25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称select e.ename,e.sal from emp e join dept d on e.deptno d.deptno where e.sal (select max(sal) from emp where deptno 30);26.列出在每个部门工作的员工数量,平均工资和平均服务期限select d.deptno,count(e.ename) as total,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(datediff(now(),e.hiredate))/365,0) as avgservice from emp e right join dept d on e.deptno d.deptno group by d.deptno;27.列出所有员工的姓名、部门名称和工资select e.ename,d.dname,e.sal from emp e join dept d on e.deptno d.deptno;28.列出所有部门的详细信息和人数select d.deptno,d.dname,d.loc,count(e.ename) as total from emp e right join dept d on e.deptno d.deptno group by d.deptno,d.dname,d.loc;29.列出各种工作的最低工资及从事此工作的雇员姓名select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job) t on e.job t.job and e.sal t.minsal;30.列出各个部门的MANAGER(领导)的最低薪金找出所有的MANAGER按照部门分钟求最小值select deptno,min(sal) from emp where job MANAGER group by deptno;31.列出所有员工的年工资,按年薪从低到高排序年薪月薪补助*12select ename,((sal ifnull(comm,0))*12) as yearsal from emp order by yearsal asc;32.求出员工领导的薪水超过3000的员工名称与领导名称select e.ename 员工,l.ename 领导 from emp e join emp l on e.mgr l.empno where l.sal 3000;33.求出部门名称中,带S字符的部门员工的工资合计、部门人数select d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename) as total from emp e right join dept d on e.deptno d.deptno where d.dname like %S% group by d.dname;34.给任职日期超过30年的员工加薪10%--先创建一个新表把emp复制到emp_bak create table emp_bak as select * from emp; --更新语句 update emp_bak set sal sal *1.1 where datediff(now(),hiredate)/ 365 30;35.完成以下任务有3个表S学生表C课程表SC学生选课表SSNOSNAME代表学号姓名CCNOCNAMECTEACHER代表课号课名教师SCSNOCNOSCGRADE代表学号课号成绩CREATE TABLE SC ( SNO VARCHAR(200), CNO VARCHAR(200), SCGRADE VARCHAR(200) ); CREATE TABLE S ( SNO VARCHAR(200 ), SNAME VARCHAR(200) ); CREATE TABLE C ( CNO VARCHAR(200), CNAME VARCHAR(200), CTEACHER VARCHAR(200) ); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( 1, 语文, 张); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( 2, 政治, 王); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( 3, 英语, 李); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( 4, 数学, 赵); INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( 5, 物理, 黎明); commit; INSERT INTO S ( SNO, SNAME ) VALUES ( 1, 学生1); INSERT INTO S ( SNO, SNAME ) VALUES ( 2, 学生2); INSERT INTO S ( SNO, SNAME ) VALUES ( 3, 学生3); INSERT INTO S ( SNO, SNAME ) VALUES ( 4, 学生4); commit; INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 1, 1, 40); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 1, 2, 30); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 1, 3, 20); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 1, 4, 80); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 1, 5, 60); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 2, 1, 60); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 2, 2, 60); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 2, 3, 60); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 2, 4, 60); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 2, 5, 40); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 3, 1, 60); INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( 3, 3, 80); commit;①.找出没选过“黎明”老师的所有学生姓名。第一步找出黎明老师教的课select CNO from C where CTEACHER黎明;第二步找出所有选黎明老师的学生编号select sno from sc where cno (select CNO from C where CTEACHER黎明);第三步找出没选过的学生select s.* from s where sno not in(select sno from sc where cno (select CNO from C where CTEACHER黎明));②.列出2门以上含2门不及格学生姓名及平均成绩。第一步找出所有不及格的select sno from sc where scgrade 60 group by sno having count(*) 2;第二步找出每个学生的平均成绩select s.sname,avg(sc.SCGRADE) from s join sc on s.sno sc.sno where s.sno in(select sno from sc where scgrade 60 group by sno having count(*) 2) group by s.sname;③.既学过1号课程又学过2号课所有学生的姓名。--学过1号课的学生 select sno from sc where cno 1; --学过2号课的学生 select sno from sc where cno 2; --既学过1号课又学过2号课 select sno from sc where cno 1 and sno in(select sno from sc where cno 2); select sname from s where sno in(select sno from sc where cno 1 and sno in(select sno from sc where cno 2));