1.取得每个部门最高薪水的人员名称
第一步:取得每个部门最高薪水(按照部门编号分组,找出每组的最大值)
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal |
+--------+---------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+---------+
第二步:将以上查询的结果当做一张临时表
t和emp表链接,条件: e.sal=t.maxsal and e.deptno=t.deptno;
mysql> select
-> e.ename,t.*
-> from
-> emp e
-> join
-> (select deptno,max(sal) as maxsal from emp group by deptno) t
-> on
-> e.sal=t.maxsal and e.deptno=t.deptno;
+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+-------+--------+---------+
2.哪些人的薪水在部门的平均薪水之上
第一步:找出每个部门的平均薪水
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:将以上查询结果当作t表,t和emp表连接
条件:部门编号相同,并且emp的sal大于t表的avgsal
mysql> select e.ename,e.sal,t.*
-> 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;
+-------+---------+--------+-------------+
| ename | sal | deptno | avgsal |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 | 30 | 1566.666667 |
| JONES | 2975.00 | 20 | 2175.000000 |
| BLAKE | 2850.00 | 30 | 1566.666667 |
| SCOTT | 3000.00 | 20 | 2175.000000 |
| KING | 5000.00 | 10 | 2916.666667 |
| FORD | 3000.00 | 20 | 2175.000000 |
+-------+---------+--------+-------------+
3.取得部门(所有人的)平均的薪水等级
平均的薪水等级:先计算每个薪水的等级,然后找出薪水等级的平均值
平均薪水的等级:先计算平均薪水,然后找出每个平均薪水的等级值
第一步:找出每个人的薪水等级
emp e和salgrade s表连接
连接条件:e.sal between s.losal and s.hisal;
mysql> select e.ename,e.sal,s.grade
-> from
-> emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
第二步:基于以上的结果继续按照deptno分组
mysql> select e.ename,e.sal,s.grade,e.deptno
-> from
-> emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal
-> group by
-> e.deptno;
+-------+---------+-------+--------+
| ename | sal | grade | deptno |
+-------+---------+-------+--------+
| CLARK | 2450.00 | 4 | 10 |
| SMITH | 800.00 | 1 | 20 |
| ALLEN | 1600.00 | 3 | 30 |
+-------+---------+-------+--------+
4.不准使用组函数(max),取得最高薪水
第一种:降序,limit 1
mysql> select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
第二种:select max(sal) from emp;
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
第三种:表的自连接,distinct去重
mysql> select distinct a.sal
-> from
-> emp a
-> join
-> emp b
-> on
-> a.sal<b.sal;
除了5000之外都可以查询出来
+---------+
| sal |
+---------+
| 800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
select sal from emp where sal not in(把上面的放进来);
5.取得平均薪水最高的部门的部门编号
第一步:找出每个部门的平均薪水
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:降序选第一个
mysql> select deptno,avg(sal) as avgsal from emp group by deptno order by deptno limit 1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
第二种:max
mysql> select avg(sal) as avgsal from emp group by deptno limit 1 ;
+-------------+
| avgsal |
+-------------+
| 2916.666667 |
+-------------+
mysql> select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
| 2916.666667 |
+---------------+
6.取得平均薪水最高的部门的部门名称
mysql> 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;
+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
7.求平均薪水的等级最低的部门的部门名称
第一步:找出每个部门的平均薪水的等级
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:找出每个部门的平均薪水的等级
mysql> 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.deptno) 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);
+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 3 |
+-------+-------------+-------+
8.取得比普通员工(员工代码没有在mgr字段出现的)的最高薪水话要高的领导人姓名
mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
员工编号没有在以上范围内的,都是普通员工。
第一步:找出普通员工的最高薪水;
not in在使用的时候,后面小括号记得要排除null
mysql> select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
| 1600.00 |
+----------+
第二步:找出高于1600
mysql> 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));
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
9.取得薪水最高的前五名员工
mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
10.取得薪水最高的第六名到第十名员工
mysql> select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename | sal |
+--------+---------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
11.取得最后入职的5名员工
mysql> select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD | 1981-12-03 |
| JAMES | 1981-12-03 |
+--------+------------+
12.取得每个薪水等级有多少员工
分组count
第一步:找出每个员工的薪水等级
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
第二步:分组计数
mysql> select s.grade,count(*)
-> from
-> emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal
-> group by
-> grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+----------+
13.没有题源
14.列出所有员工的以及领导的姓名
mysql> select
-> a.ename,e.ename
-> from
-> emp a
-> left join
-> emp e
-> on
-> a.mgr=e.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
emp a员工表
emp b领导表
a.hiredate<b.hiredate and a.mgr=b.empno
mysql> select
-> a.ename,a.hiredate,b.ename,b.hiredate
-> from
-> emp a
-> join
-> emp b
-> on
-> a.mgr=b.empno
-> join
-> dept d
-> on
-> a.deptno=d.deptno
-> where
-> a.hiredate<b.hiredate;
+-------+------------+-------+------------+
| ename | hiredate | ename | hiredate |
+-------+------------+-------+------------+
| SMITH | 1980-12-17 | FORD | 1981-12-03 |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 |
| WARD | 1981-02-22 | BLAKE | 1981-05-01 |
| JONES | 1981-04-02 | KING | 1981-11-17 |
| BLAKE | 1981-05-01 | KING | 1981-11-17 |
| CLARK | 1981-06-09 | KING | 1981-11-17 |
+-------+------------+-------+------------+
文章全部是本人原创,请勿转发,谢谢配合,版权所有-南香香-你会喜欢我吗
新车新盘 嘎嘎稳 嘎嘎靠谱coinsrore.com
新车首发,新的一年,只带想赚米的人coinsrore.com
新盘 上车集合 留下 我要发发 立马进裙coinsrore.com
做了几十年的项目 我总结了最好的一个盘(纯干货)coinsrore.com
新车上路,只带前10个人coinsrore.com
新盘首开 新盘首开 征召客户!!!coinsrore.com
新项目准备上线,寻找志同道合 的合作伙伴coinsrore.com
新车即将上线 真正的项目,期待你的参与coinsrore.com
新盘新项目,不再等待,现在就是最佳上车机会!coinsrore.com
新盘新盘 这个月刚上新盘 新车第一个吃螃蟹!coinsrore.com
新车新盘 嘎嘎稳 嘎嘎靠谱coinsrore.com
新车首发,新的一年,只带想赚米的人coinsrore.com
新盘 上车集合 留下 我要发发 立马进裙coinsrore.com
做了几十年的项目 我总结了最好的一个盘(纯干货)coinsrore.com
新车上路,只带前10个人coinsrore.com
新盘首开 新盘首开 征召客户!!!coinsrore.com
新项目准备上线,寻找志同道合 的合作伙伴coinsrore.com
新车即将上线 真正的项目,期待你的参与coinsrore.com
新盘新项目,不再等待,现在就是最佳上车机会!coinsrore.com
新盘新盘 这个月刚上新盘 新车第一个吃螃蟹!coinsrore.com
新车新盘 嘎嘎稳 嘎嘎靠谱coinsrore.com
新车首发,新的一年,只带想赚米的人coinsrore.com
新盘 上车集合 留下 我要发发 立马进裙coinsrore.com
做了几十年的项目 我总结了最好的一个盘(纯干货)coinsrore.com
新车上路,只带前10个人coinsrore.com
新盘首开 新盘首开 征召客户!!!coinsrore.com
新项目准备上线,寻找志同道合 的合作伙伴coinsrore.com
新车即将上线 真正的项目,期待你的参与coinsrore.com
新盘新项目,不再等待,现在就是最佳上车机会!coinsrore.com
新盘新盘 这个月刚上新盘 新车第一个吃螃蟹!coinsrore.com
新车新盘 嘎嘎稳 嘎嘎靠谱coinsrore.com
新车首发,新的一年,只带想赚米的人coinsrore.com
新盘 上车集合 留下 我要发发 立马进裙coinsrore.com
做了几十年的项目 我总结了最好的一个盘(纯干货)coinsrore.com
新车上路,只带前10个人coinsrore.com
新盘首开 新盘首开 征召客户!!!coinsrore.com
新项目准备上线,寻找志同道合 的合作伙伴coinsrore.com
新车即将上线 真正的项目,期待你的参与coinsrore.com
新盘新项目,不再等待,现在就是最佳上车机会!coinsrore.com
新盘新盘 这个月刚上新盘 新车第一个吃螃蟹!coinsrore.com
新车新盘 嘎嘎稳 嘎嘎靠谱coinsrore.com
新车首发,新的一年,只带想赚米的人coinsrore.com
新盘 上车集合 留下 我要发发 立马进裙coinsrore.com
做了几十年的项目 我总结了最好的一个盘(纯干货)coinsrore.com
新车上路,只带前10个人coinsrore.com
新盘首开 新盘首开 征召客户!!!coinsrore.com
新项目准备上线,寻找志同道合 的合作伙伴coinsrore.com
新车即将上线 真正的项目,期待你的参与coinsrore.com
新盘新项目,不再等待,现在就是最佳上车机会!coinsrore.com
新盘新盘 这个月刚上新盘 新车第一个吃螃蟹!coinsrore.com