1、取得每个部门最高薪水的人员名称(1)
1)先取出每个部门的最高薪水 //遇到的问题:t表中的max(sal) 必须重命名
select e.deptno,max(sal) from emp e group by e.deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
2)人员名称查找出来 将最高薪水表看作t表
select
e.ename ,t.*
from
( select e.deptno,max(sal) as maxsal from emp e group by e.deptno) t
join
emp e
on
t.deptno=e.deptno and t.maxsal=e.sal;
+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+-------+--------+---------+
2、哪些人的薪水在部门的平均薪水之上
1).先求出部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+
2).求出哪些人薪水高于平均薪水 设上表为t表 t.deptno=e.deptnoe.sal>avg(sal)
select
e.ename ,e.sal
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
t.deptno=e.deptno and e.sal>t.avgsal;
+-------+---------+
| ename | sal |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
3、取得部门中(所有人的)平均的薪水等级,如下:
1).先取得部门中所有人的薪水等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal >=s.losal and e.sal <=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 |
+--------+---------+-------+
2).求出所有人的薪水等级的平均值 将上表看作t
select
avg(t.grade) as avggrade
from
(select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal >=s.losal and e.sal <=s.hisal)t ;
+----------+
| avggrade |
+----------+
| 2.8571 |
+----------+
4、不准用组函数(Max),取得最高薪水(给出两种解决
方案一:将薪水降序排列 取得第一个就是最大 (但是如果有两个一样的最大则无法区分)
select ename,sal from emp order by sal desc limit 0,1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
方案二:
5、取得平均薪水最高的部门的部门编号(至少给出两种解决方案)
方案一:1).先求出: 取得平均薪水最高的部门
1.先求出:部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
2.求出:取得平均薪水最高的部门 将上表看作t表
select t.* from (select deptno,avg(sal) as avgsal from emp group by deptno)t limit 0,1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
6、取得平均薪水最高的部门的部门名称
1).取得部门平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
2). 降序排列 找到平均薪水最高的部门 将上表看作t表
select
t.deptno,max(t.avgsal)
from
( select deptno,avg(sal) as avgsal from emp group by deptno) t;
+--------+---------------+
| deptno | max(t.avgsal) |
+--------+---------------+
| 10 | 2916.666667 |
+--------+---------------+
3).得到部门名称
select
d.dname ,t.deptno,max(t.avgsal)
from
( select deptno,avg(sal) as avgsal from emp group by deptno) t
join
dept d
on
t.deptno=d.deptno;
+------------+--------+---------------+
| dname | deptno | max(t.avgsal) |
+------------+--------+---------------+
| ACCOUNTING | 10 | 2916.666667 |
7、求平均薪水的等级最低的部门的部门名称
参照第六题
1).取得部门平均薪水的等级
8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
9、取得薪水最高的前五名员工
1).先将薪水按照降序排列
select ename,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
2).取出前五名
select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
10、取得薪水最高的第六到第十名员工
1).先将薪水按照降序排列
select ename,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
2).取出第六到十名
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名员工
1).先按照日期升序排列 然后取出前五名
select ename,hiredate from emp order by hiredate limit 0,5;
+-------+------------+
| ename | hiredate |
+-------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| JONES | 1981-04-02 |
| BLAKE | 1981-05-01 |
+-------+------------+
12、取得每个薪水等级有多少员工
select s.grade,count(e.ename) from salgrade s join emp e on e.sal>=losal and e.sal<=hisal
group by s.grade;
+-------+----------------+
| grade | count(e.ename) |
+-------+----------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+----------------+
13、面试题
14、列出所有员工及领导的姓名
1).先列出员工姓名 看为t表
员工表
select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
领导表 g
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7566 | JONES | 7839 | =
| 7698 | BLAKE | 7839 | =
| 7782 | CLARK | 7839 | =
| 7788 | SCOTT | 7566 | =
| 7839 | KING | NULL | =
| 7902 | FORD | 7566 | =
+-------+--------+------+
员工的领导编号 = 领导的员工编号
select e.ename,g.ename from emp e join emp g on e.mgr=g.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
员工
select empno,ename,mgr, hiredate from emp;
+-------+--------+------+------------+
| empno | ename | mgr | hiredate |
+-------+--------+------+------------+
| 7369 | SMITH | 7902 | 1980-12-17 |
| 7499 | ALLEN | 7698 | 1981-02-20 |
| 7521 | WARD | 7698 | 1981-02-22 |
| 7566 | JONES | 7839 | 1981-04-02 |
| 7654 | MARTIN | 7698 | 1981-09-28 |
| 7698 | BLAKE | 7839 | 1981-05-01 |
| 7782 | CLARK | 7839 | 1981-06-09 |
| 7788 | SCOTT | 7566 | 1987-04-19 |
| 7839 | KING | NULL | 1981-11-17 |
| 7844 | TURNER | 7698 | 1981-09-08 |
| 7876 | ADAMS | 7788 | 1987-05-23 |
| 7900 | JAMES | 7698 | 1981-12-03 |
| 7902 | FORD | 7566 | 1981-12-03 |
| 7934 | MILLER | 7782 | 1982-01-23 |
+-------+--------+------+------------+
领导
+-------+--------+------+------------+
| empno | ename | mgr | hiredate |
+-------+--------+------+------------+
| 7566 | JONES | 7839 | 1981-04-02 | =
| 7698 | BLAKE | 7839 | 1981-05-01 | =
| 7782 | CLARK | 7839 | 1981-06-09 | =
| 7788 | SCOTT | 7566 | 1987-04-19 | =
| 7839 | KING | NULL | 1981-11-17 | =
| 7902 | FORD | 7566 | 1981-12-03 | =
+-------+--------+------+------------+
select e.ename from emp e join emp g on e.mgr=g.empno where e.hiredate<g.hiredate;
+-------+
| ename |
+-------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| BLAKE |
| CLARK |
+-------+
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select d.deptno,e.* from dept d left join emp e on e.deptno=d.deptno order by d.deptno;
+--------+-------+--------+-----------+------+------------+---------+---------+--------+
| deptno | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+--------+-------+--------+-----------+------+------------+---------+---------+--------+
| 10 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 20 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 40 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+-------+--------+-----------+------+------------+
17、列出至少有5个员工的所有部门 count(ename)>5
1)select count(ename) from emp group by deptno;
+--------------+
| count(ename) |
+--------------+
| 3 |
| 5 |
| 6 |
+--------------+
2)将上表看作t表
select e.deptno from emp e join (select deptno,count(ename)as nnn from emp group by deptno) t on t.deptno=e.deptno where t.nnn>=5 group by e.deptno;
+--------+
| deptno |
+--------+
| 20 |
| 30 |
+--------+
18、列出薪金比"SMITH"多的所有员工信息.
1).列出SMITH的薪金
select ename,sal from emp where ename='SMITH';
+-------+--------+
| ename | sal |
+-------+--------+
| SMITH | 800.00 |
+-------+--------+
2).将所有人的薪金与他的对比
select emp.* from emp where sal>800.00;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+-
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
1).列出所有"CLERK"(办事员)的姓名及其部门名称
select e.ename,d.dname from emp e join dept d on job= 'CLERK' where e.deptno=d.deptno ;
+--------+------------+
| ename | dname |
+--------+------------+
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| ADAMS | RESEARCH |
| JAMES | SALES |
+--------+------------+
2).计算各个部门人数
select deptno,count(ename) from emp group by deptno;
+--------+--------------+
| deptno | count(ename) |
+--------+--------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+--------------+
3).将一二步联合
select
e.ename,d.dname ,t.count
from
emp e
join
dept d
on
job= 'CLERK'
join
(select deptno,count(ename) as count from emp group by deptno) t
on
d.deptno=t.deptno
where
e.deptno=d.deptno ;
+--------+------------+-------+
| ename | dname | count |
+--------+------------+-------+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+-------+
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
1).首先列出最低薪金大于1500的各种工作
select job from emp where sal>1500 group by job;
+-----------+
| job |
+-----------+
| ANALYST |
| MANAGER |
| PRESIDENT |
| SALESMAN |
+-----------+
2).在一的基础上计算雇员人数
select job,count(ename) from emp where sal>1500 group by job;
+-----------+--------------+
| job | count(ename) |
+-----------+--------------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
| SALESMAN | 1 |
+-----------+--------------+
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
只能想到知道部门编号的情况
select ename from emp where deptno='30';
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
1).先求出公司平均薪金
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
2).列出薪金高于公司平均薪金的所有员工,所在部门
select e.ename,e.deptno from emp e where (select avg(sal) from emp)<e.sal;
+-------+--------+
| ename | deptno |
+-------+--------+
| JONES | 20 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| FORD | 20 |
+-------+--------+
3).加一个工资等级
select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal>=losal and e.sal<=hisal
where (select avg(sal) from emp)<e.sal;
4).最后加一个上级领导 看作有一个领导表g 员工的领导 编号=领导的员工编号
select
e.ename,e.deptno,s.grade,g.ename
from
emp e
join
salgrade s
on
e.sal>=losal and e.sal<=hisal
left join
emp g
on
e.mgr=g.empno
where
(select avg(sal) from emp)<e.sal;
+-------+--------+-------+-------+
| ename | deptno | grade | ename |
+-------+--------+-------+-------+
| JONES | 20 | 4 | KING |
| BLAKE | 30 | 4 | KING |
| CLARK | 10 | 4 | KING |
| SCOTT | 20 | 4 | JONES |
| KING | 10 | 5 | NULL |
| FORD | 20 | 4 | JONES |
+-------+--------+-------+-------+
23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
1).先列出"SCOTT"从事的工作
select job from emp where ename='SCOTT';
+---------+
| job |
+---------+
| ANALYST |
+---------+
2).列出与"SCOTT"从事相同工作的所有员工
select ename from emp where job='ANALYST';
+-------+
| ename |
+-------+
| SCOTT |
| FORD |
+-------+
3).加上部门 名称
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno
where job='ANALYST';
+-------+----------+
| ename | dname |
+-------+----------+
| SCOTT | RESEARCH |
| FORD | RESEARCH |
+-------+----------+
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
1).先得到部门30中员工的薪金
select sal from emp where deptno=30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
2).列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
select distinct e.ename,e.sal from emp e join (select sal from emp where deptno=30) t
on
e.sal=t.sal
where
e.deptno!=30;
Empty set (0.00 sec)
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.(这题我没有秋部门名称 )
1).先得到部门30中员工的薪金 按照降序排列 取最大
select max(sal) as maxsal from emp where deptno=30 order by sal desc;
+---------+
| maxsal |
+---------+
| 2850.00 |
+---------+
2).列出薪金高于部门30中所有员工的薪金的其他员工的姓名和薪金.部门名称.
select distinct e.ename,e.sal,e.deptno from emp e join
(select max(sal) as maxsal from emp where deptno=30 order by sal desc) t
on
e.sal>t.maxsal
where
e.deptno!=30;
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.(不知道平均 服务期限怎么算)
select deptno,count(ename),avg(sal) from emp group by deptno;
+--------+--------------+-------------+
| deptno | count(ename) | avg(sal) |
+--------+--------------+---------+
| 10 | 3 | 2916.666667 |
| 20 | 5 | 2175.000000 |
| 30 | 6 | 1566.666667 |
+--------+--------------+-------------+
27、列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
+--------+------------+---------+
| ename | dname | sal |
+--------+------------+---------+
| CLARK | ACCOUNTING | 2450.00 |
| KING | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH | RESEARCH | 800.00 |
| JONES | RESEARCH | 2975.00 |
| SCOTT | RESEARCH | 3000.00 |
| ADAMS | RESEARCH | 1100.00 |
| FORD | RESEARCH | 3000.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| TURNER | SALES | 1500.00 |
| JAMES | SALES | 950.00 |
+--------+------------+---------+
28、列出所有部门的详细信息和人数
1).列出所有部门的详细信息
select d.*from dept d;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
2).各个部门的人数
select deptno, count(ename) as coun from emp group by deptno;
+--------+----------------+
| deptno | count(e.ename) |
+--------+----------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+----------------+
3).联合 1 2
select
d.*,t.coun
from
dept d
left join
(select deptno, count(ename) as coun from emp group by deptno) t
on
d.deptno=t.deptno;
+--------+------------+----------+------+
| DEPTNO | DNAME | LOC | coun |
+--------+------------+----------+------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | NULL |
+--------+------------+----------+------+
29、列出各种工作的最低工资及从事此工作的雇员姓名
1).列出各种工作的最低工资
select job,min(sal) as minsal from emp group by job;
+-----------+---------+
| job | minsal |
+-----------+---------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+---------+
2).从事各种工作的人的姓名
select ename ,job from emp;
+--------+-----------+
| ename | job |
+--------+-----------+
| SMITH | CLERK |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| SCOTT | ANALYST |
| KING | PRESIDENT |
| TURNER | SALESMAN |
| ADAMS | CLERK |
| JAMES | CLERK |
| FORD | ANALYST |
| MILLER | CLERK |
+--------+-----------+
3).联合 1 2
select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job) t
on
t.minsal=e.sal;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
30、列出各个部门的MANAGER(领导)的最低薪金
select
e.deptno,min(g.sal) as min from emp e join emp g
on
e.mgr=g.empno
group by
e.deptno;
+--------+---------+
| deptno | min |
+--------+---------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+---------+
31、列出所有员工的年工资,按年薪从高到底排序
select ename,sal*12 as yearsal from emp order by yearsal desc;
+--------+----------+
| ename | yearsal |
+--------+----------+
| KING | 60000.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| JONES | 35700.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| ALLEN | 19200.00 |
| TURNER | 18000.00 |
| MILLER | 15600.00 |
| MARTIN | 15000.00 |
| WARD | 15000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| SMITH | 9600.00 |
+--------+----------+
32、求出员工领导的薪水超过3000的员工名称与领导名称
1).先求出员工及其对应领导领导
select e.ename,g.ename from emp e join emp g on e.mgr=g.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
2).工资大于3000的领导】
select e.ename,g.ename from emp e join emp g on e.mgr=g.empno
where g.sal>3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+-------+
33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.
1).求出部门名称中,带’S’字符的部门
select d.dname from dept d where d.dname like '%S%';
+------------+
| dname |
+------------+
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
2).求出部门名称中,带’S’字符的部门员工的工资合计
select d.dname,sum(e.sal) from emp e right join dept d on d.deptno=e.deptno
where d.dname like '%S%' group by d.dname;
3).求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.
select count(e.deptno),sum(e.sal) from emp e right join dept d on d.deptno=e.deptno
where d.dname like '%S%' group by d.deptno;
+-----------------+------------+
| count(e.deptno) | sum(e.sal) |
+-----------------+------------+
| 5 | 10875.00 |
| 6 | 9400.00 |
| 0 | NULL |
+-----------------+------------+
34、给任职日期超过30年的员工加薪10%.