文章目录
- 1. 取得每个部门最高薪水的人员名称
- 2. 哪些人的薪水在部门的平均薪水之上
- 3. 取得部门中(所有人的)平均的薪水等级
- 4.不准用组函数(Max),取得最高薪水
- 5.取得平均薪水最高的部门的部门编号
- 6. 取得平均薪水最高的部门的部门名称【和第五题类似】
- 7. 求平均薪水的等级最低的部门的部门名称
- 8.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
- 9. 取得薪水最高的前五名员工
- 10.取得薪水最高的第六到第十名员工
- 11.取得最后入职的5名员工
- 12.取得每个薪水等级有多少员工
- 13.面试题
- 14.列出所有员工及领导的姓名
- 15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
- 16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
- 17.列出至少有5个员工的所有部门
- 18.列出薪金比“SMITH”多的所有员工信息
- 19.列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数
- 20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
- 21.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
- 22.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
- 23.列出与"SCOTT"从事相同工作的所有员工及部门名称
- 24.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
- 25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
- 26.列出在每个部门工作的员工数量,平均工资和平均服务期限
- 27.列出所有员工的姓名、部门名称和工资
- 28.列出所有部门的详细信息和人数
- 29.列出各种工作的最低工资及从事此工作的雇员姓名
- 30.列出各个部门的MANAGER(领导)的最低薪金
- 31.列出所有员工的年工资,按年薪从低到高排序
- 32.求出员工领导的薪水超过3000的员工名称与领导
- 33.求出部门名称中,带's'字符的部门员工的工资合计、部门人数
- 34.给任职日期超过30年的员工加薪10%
1. 取得每个部门最高薪水的人员名称
- 第一步:取得每个部门最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
- 第二步:将以上结果当做临时表t, t表和emp e表进行连接,条件是: t.deptno = e.deptno and t.maxsal = e.sal
select
e.ename ,t.*
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表,t 和 emp表连接
条件:部门编号相同,并且emp的sal大于t表的avgsal
select
t.*, e.ename ,e.sal
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno)t
on
e.deptno = t.deptno and e.sal > t.avgsal;
3. 取得部门中(所有人的)平均的薪水等级
平均的薪水等级:先计算每一个薪水的等级,然后找出薪水等级的平均值。
平均薪水的等级:先计算平均薪水,然后找出每个平均薪水的等级值。
- 第一步:找出每个人的薪水等级
emp e 和salgrade s表连接
连接条件:e.sal between s.losal and s.hisal
select
e.enane ,e.sal ,e .deptno ,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal order by deptno;
- 第二步:基于以上的结果继续按照deptno分组,求grade的平均值
select
e.deptno , avg (s.grade)
from
emp e
join
salgrade s
on
e.sal between s.1osal and s.hisal
group by
e.deptno;
4.不准用组函数(Max),取得最高薪水
第一种:降序,limit 1【按照降序排,取第一个】
select ename,sal from emp order by sal desc limit 1;
第二种:select max(sal) from emp;
第三种:表的自连接
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal)
找出a表中比b表中值小的数据
select
distinct a.sal //去重
from
emp a
join
emp b
on
a.sal < b.sal
5.取得平均薪水最高的部门的部门编号
第一种:
第一步:找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步:降序选第一个
select deptno , avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
第二种:max
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
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. 取得平均薪水最高的部门的部门名称【和第五题类似】
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;
7. 求平均薪水的等级最低的部门的部门名称
第一步:找出每个部门的平均薪水
select deptno ,avg (sal) as avgsal from emp group by deptno;
第二步:找出每个部门的平均薪水的等级
以上t表和salgrade表连接,条件是:t.avgsal between s. losal and s.hisal
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语句
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 grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
注意:薪水最低,等级一定最低;但是薪水不是最低,但可能等级也是最低的
8.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
maysql> select distinct mgr from enp;
员工编号没有在以上范围内的都是普通员工第一步:找出普通员工的最高薪水
not in 在使用的时候,后面小括号中记得排除NULL
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
注意:
select distinct mgr from emp where mgr is not null;
第二步:找出高于1600的
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
注意:比普通员工最高工资还要高的肯定是领导
9. 取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limt 5;
10.取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limt 5,5;
11.取得最后入职的5名员工
日期也可以降序、升序
select ename,hiredate from emp order by hiredate desc limit 5;
12.取得每个薪水等级有多少员工
分组count
第一步:找出每个员工的薪水等级
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;
ground by
s.grade;
13.面试题
有3个表s(学生表),c(课程表), sc(学生选课表)
s (SNo, SNAME)代表(学号,姓名)
c (CNO,CNAME,CTEACHER)代表(课号,课名,教师)
sc ( SNo,CNo,SCGRADE)代表(学号,课号,成绩)
问题:
1,找出没选过”黎明”老师的所有学生姓名。
2,列出2门以上(含2门)不及格学生姓名及平均成绩。
3,即学过1号课程又学过2号课所有学生的姓名。
14.列出所有员工及领导的姓名
select
a.enane'员工',b.enane '领导'
from
emp a
left join
emp b
on
a.mgr = b.empno;
15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select
a.ename '员工',a.hiredate,b.ename '领导',b.hiredate,d.dname
from
emp a
join
emp b
on
a.mgr = b.empno
join
dept d
on
a.deptno = d.deptno
where
a.hiredate < b.hiredate
16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select
e.* ,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno ;
17.列出至少有5个员工的所有部门
按照部门编号分组,计数,筛选出>=5
select
deptno
from
emp
group by
deptno
having
count(*) >= 5;
18.列出薪金比“SMITH”多的所有员工信息
select ename,sal from emp where sal > (select sal from emp where ename = ’ SMITH’);
19.列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数
第一步:列出所有“CLERK”(办事员)的姓名
select ename , job from emp where job = "CLERK’;
第二步:列出所有“CLERK”(办事员)的姓名及其部门名称
select
e.enane ,e.job ,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = "CLERK";
第三步:列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数
每个部门的人数:
select deptno,count(*) as deptcount from emp group by deptno;
将两张表进行表连接
select
t1.*,t2.deptcount
from
(select
e.enane ,e.job ,d.dname ,d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'CLERK') t1
join
(select deptno,count(*) as deptcount from emp group by deptno) t2
on
t1.deptno = t2.deptno;
20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值
select job , count(*) from emp group by job having min(sal) >1500;
21.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
select ename., from emp where deptno = (select deptno from dept where dname = ‘SALES’ );
22.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select
e.enane '员工' ,d.dnane ,l.enane '领导',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"从事相同工作的所有员工及部门名称
第一步:列出"SCOTT"从事的工作
select job from emp where ename = ‘SCOTT’;
第二步:列出相同工作的所有员工及部门名称
select
e.ename ,e.job ,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;
第二步:列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select
ename , sal
from
emp
where
sal in (select distinct sal from emp where deptno = 30)
and
deptno <> 30 ;
Empty set (o.oo sec)
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
第一步:列出部门30中员工的最高薪金
select max (sal) from emp where deptno = 30 ;
第二步:列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称
select
e.enane ,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) ;
26.列出在每个部门工作的员工数量,平均工资和平均服务期限
没有员工的部门人数为0
select
d.deptno , count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal,ifnull(avg (timestarapdiff (YEAR,hiredate,now ())),0) as avgservicetime
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno;
注:在mysql当中怎么计算两个日期的"年差”,差了多少年?
TimeStampDiff(间隔类型,前一个日期,后一个日期)【间隔类型可以是年、月、日、分、秒、天、星期、季度】
比如:timestampdiff(YEAR,hiredate, now())
27.列出所有员工的姓名、部门名称和工资
select
e.ename,d.dname,e.sal
from
emp e
join
dept d
on
e.deptno = d.deptno;
28.列出所有部门的详细信息和人数
select
d.deptno,d.dnane,d.loc, count(e.ename)
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno,d.dname, d.loc;
29.列出各种工作的最低工资及从事此工作的雇员姓名
第一步:列出各种工作的最低工资
select
job,min (sal) as minsal
from
emp
group by
job ;
第二步:emp e和以上t连接
select
e.enane , 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(领导)的最低薪金
select
deptno,min (sal)
from
emp
where
job = 'MANAGER"
group by
deptno ;
31.列出所有员工的年工资,按年薪从低到高排序
select
ename , (sal + ifnull (comm , 0))* 12 as yearsal
from
emp
order by
yearsal asc;
32.求出员工领导的薪水超过3000的员工名称与领导
select
a.ename '员工',b.ename'领导'
from
emp a
join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;
33.求出部门名称中,带’s’字符的部门员工的工资合计、部门人数
select
d.deptno ,d.dname ,d.loc,count(e.ename),ifnull (sum(e.sal) ,0) as sumsal
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dnane like '%S%'
group by
d.deptno,d.dnane,d.loc;
34.给任职日期超过30年的员工加薪10%
update
emp
set
sal = sal * 1.1
where
timestampdiff(YEAR,hiredate,now()) > 30;