1 取得每个部门最高薪水的人员名称
mysql> select ename,sal,deptno from emp
-> where sal in
-> (select max(sal) from emp group by deptno);
2 找出哪些人的工资在部门平均薪资之上
mysql> select t.* ,e.ename ,e.sal from emp e
-> right join
-> (select deptno , avg(sal) as avgsal from emp group by deptno) t
-> on e.deptno = t.deptno and e.sal > t.avgsal;
3 取得部门中（所有人的）平均的薪水等级
mysql> select deptno,avg(grade) from
-> (select e.ename ,e.deptno ,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t
-> group by deptno;
4 不准用组函数（Max），取得最高薪水（给出两种解决方案）
4.1 降序之后limit 1
select sal from emp order by desc limit 1;
4.2 表的自链接
mysql> select sal from emp
-> where sal not in
-> (select distinct a.sal from emp a join emp b on a.sal < b.sal);
5 取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
1
mysql> select deptno ,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
2
mysql> select deptno,avg(sal) as avgsal
-> from emp
-> group by deptno
-> having
-> avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
6 取得平均薪水最高的部门的部门名称
mysql> select d.dname from
-> dept d
-> join
-> (select deptno ,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) t
-> on d.deptno = t.deptno;
7 求平均薪水的等级最低的部门的部门名称
mysql> select d.dname from
-> dept d
-> join
-> ( select deptno ,avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) t
-> on d.deptno = t.deptno;
8 取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名
mysql>select ename,sal
from emp
where
sal > (select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null));
9 取得薪水最高的前五名员工
mysql> select empno,sal from emp order by sal desc limit 5;
10 取得薪水最高的第六到第十名员工
mysql> select empno ,sal from emp order by sal desc limit 5,5;
11 取得最后入职的 5 名员工
mysql> select ename, hiredate from emp order by hiredate desc limit 5;
12 取得每个薪水等级有多少员工
mysql> select grade , count(grade) from (select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t group by grade;
13 面试题
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
13.1 找出没选过“黎明”老师的所有学生姓名。
mysql> select
sname
from
s
where
sno not in (select
sno
from
sc
where
cno = ( select cno from c where cteacher = '黎明'));
13.2列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
mysql> select
s.sno,s.sname,t.avggrade
from
s
join
(select
sno,avg(scgrade) as avggrade from sc
where
scgrade < 60
group by
sno
having count
(cno) >=2) t
on
s.sno = t.sno;
13.3 即学过 1 号课程又学过 2 号课所有学生的姓名。
mysql> select
sno,sname
from
s
where
sno in (select distinct sno from sc where sno in (select sno from sc where cno =1) and sno in (select sno from sc where cno =2));
14 列出所有员工及领导的姓名
mysql> select a.ename ,ifnull(b.ename,'没有上级')
from emp a
left join
emp b
on a.mgr = b.empno;
15 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
mysql> select t.empno,t.ename,d.dname
from
(select a.empno,a.ename,a.deptno
from emp a
left join emp b
on a.mgr = b.empno
where a.hiredate<b.hiredate) t
join dept d
on t.deptno = d.deptno;
16 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
mysql> select d.*,e.*
from emp e
right join dept d
on e.deptno = d.deptno;
17列出至少有 5 个员工的所有部门
mysql> select d.dname,t.countno
from dept d
join ( select deptno,count(empno) as countno
from emp
group by deptno
having count(empno) >= 5) t
where d.deptno = t.deptno;
18 列出薪金比"SMITH"多的所有员工信息.
mysql> select *
from emp
where
sal > (select sal
from emp
where ename = 'smith');
19 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
mysql> select
t1.ename,t1.dname,t2.deptcount
from
(select
e.ename,e.deptno,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
job = 'clerk') t1
join (select
deptno,count(*) as deptcount
from
emp
group by
deptno) t2
on t1.deptno = t2.deptno;
20 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
mysql> select
job,count(*)
from
emp
group by
job
having
min(sal) > 1500;
21 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部 门编号.
mysql> select
ename
from
emp
where
deptno = (select
deptno
from
dept
where
dname = 'sales');
我起初还以为的deptno直接不能用。
22 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等 级
mysql> select
e.ename as '员工' ,d.dname,l.ename as '领导' ,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"从事相同工作的所有员工及部门名称
mysql> select
e.ename, e.job ,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
job = (select job from emp where ename = 'scott')
and
ename <> 'scott';
24 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
mysql> select
ename,sal
from
emp
where
sal in (select sal from emp where deptno = 30)
and
deptno <> 30;
结果是真没有
25 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名 称.
mysql> select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.sal > (select max(sal) from emp where deptno = 30)
and
e.deptno <> 30;
26 列出在每个部门工作的员工数量,平均工资和平均服务期限.
mysql> select
d.deptno,d.dname ,count(e.empno) ,ifnull(avg(e.sal),0),ifnull(avg(timestampdiff(YEAR,hiredate,now())),0)
from
emp e
right join
dept d
on e.deptno = d.deptno
group by
d.deptno;
如何计算平均服务期限 avg（当前年份 - 入职年份）
mysql中如何计算“年差” ： TimeStampDiff(间隔类型，前一个日期，后一个日期)
27 列出所有员工的姓名、部门名称和工资。
mysql> select
e.ename,d.dname , e.sal
from
emp e
join
dept d
on
e.deptno = d.deptno ;
28 列出所有部门的详细信息和人数
mysql> select
d.*,count(empno)
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
deptno;
29 列出各种工作的最低工资及从事此工作的雇员姓名
mysql> 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(领导)的最低薪金
mysql>select
deptno,min(sal)
from
emp
where
job = 'manager'
group by
deptno;
31 列出所有员工的年工资,按年薪从低到高排序
mysql> select
ename,ifnull(sal,0) * 12 as income
from
emp
order by
income asc;
32 、求出员工领导的薪水超过 3000 的员工名称与领导名称
mysql>select
a.ename,b.ename
from
emp a
join
emp b
on
a.mgr = b.empno
where b.sal > 3000;
33 求出部门名称中,带'S'字符的部门员工的工资合计、部门人数
mysql> select
d.dname,ifnull(sum(e.sal),0),count(e.empno)
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like '%s%'
group by d.dname;
34 给任职日期超过 30 年的员工加薪 10%
mysql> update
emp
set
sal = sal * 1.1
where timestampdiff(YEAR,hiredate ,now()) >30;