Mysql基础入门34道作业题

2022-01-31 15:45:23

1、取得每个部门最高薪水的人员名称

1、每个部门最高薪水的人员名称
第一步：得到每个部门的最高薪水
select
    deptno,max(sal) as maxsal
from 
    emp 
group by
    deptno;

第二步：将上表作为临时表t与emp表内连接起来查询
连接条件：[e.deptno=t.deptno and e.sal = t.maxsal;]
select 
    e.ename,e.deptno,maxsal
from
    (select deptno,max(sal) as maxsal from emp group by deptno) t
inner join 
    emp e
on 
    e.deptno=t.deptno and e.sal = t.maxsal;

2、哪些人的薪水在部门的平均薪水之上

第一步：找出每个部门的平均薪水
 select deptno,avg(sal) as avgsal from emp group by deptno;
 +--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
第二步：找出薪水大于平均薪水的人员编号，名称。
将上一张表作为临时表和emp表连接
连接条件是e.deptno=t.deptno and e.sal > t.avgsal
查询的内容有：e.ename,sal
select 
   e.ename,e.sal,t.*
from  
   emp e
inner join 
   (select deptno,avg(sal) as avgsal from emp group by deptno) t
on  
   e.deptno=t.deptno and e.sal > t.avgsal;

3、取得部门中（所有人的）平均的薪水等级

第一步：获得部门中所有人的薪水等级

select
   e.deptno,e.ename,s.grade
from 
   emp e
inner join
   salgrade s
on 
   e.sal between s.losal and s.hisal;
	 +--------+--------+-------+
	| deptno | ename  | grade |
	+--------+--------+-------+
	|     20 | SMITH  |     1 |
	|     30 | ALLEN  |     3 |
	|     30 | WARD   |     2 |
	|     20 | JONES  |     4 |
	|     30 | MARTIN |     2 |
	|     30 | BLAKE  |     4 |
	|     10 | CLARK  |     4 |
	|     20 | SCOTT  |     4 |
	|     10 | KING   |     5 |
	|     30 | TURNER |     3 |
	|     20 | ADAMS  |     1 |
	|     30 | JAMES  |     1 |
	|     20 | FORD   |     4 |
	|     10 | MILLER |     2 |
        +--------+--------+-------+

第二步：对获得的薪水等级按照部门求平均
查询内容：e.ename,avg(grade) as avgsal
连接条件：e.deptno=t.deptno
分组：group by e.deptno
select 
    e.deptno,avg(grade) as avgsal
from 
    emp e
inner join 
    (select e.deptno,e.ename,s.grade from  emp e inner join salgrade s on e.sal between s.losal and s.hisal) t
on
    e.deptno=t.deptno
group by
    e.deptno;
+--------+--------+
| deptno | avgsal |
+--------+--------+
|     10 | 3.6667 |
|     20 | 2.8000 |
|     30 | 2.5000 |
+--------+--------+

老师的方法：

select
   e.deptno,avg(s.grade)
from 
   emp e
inner join
   salgrade s
on 
   e.sal between s.losal and s.hisal
group by 
   deptno;

4、不准用组函数（Max），取得最高薪水

方法1：使用分组函数取得最高薪水
select
   max(sal) as maxsal 
from 
   emp
+---------+
| maxsal  |
+---------+
| 5000.00 |
+---------+

方法2：使用order by按照sal降序排序，然后使用limit输出第一条数据
select
   sal
from 
   emp 
order by
   sal
desc
limit 1;

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

老师的方法：第三种：采用表的自连接

老师的方法：表的自连接
第一步：通过表的自连接找出除了5000之外的所有薪资
select
    distinct e2.sal
from 
    emp e1
join 
    emp e2
on
    e1.sal > e2.sal;
只有5000不会被找到，因为e1表中的sal的数据大于e2表中除5000之外的所有sal，
第二步：查找薪资，not in（上表结果），最终就会把5000取出来
select
   sal as maxsal
from 
   emp
where 
   sal not in (select distinct e2.sal from emp e1 join emp e2 on e1.sal > e2.sal);

5、取得平均薪水最高的部门的部门编号

方法1：

第一步：取得每个部门的平均薪水且降序排序
select 
     deptno,avg(sal) as avgsal
from
     emp
group by
     deptno
order by 
     avgsal
desc;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
第二步：
输出上表中的第一条数据中的deptno
select 
   t.deptno
from (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc) t
limit 1;

方法2：

	在第一种方法的基础上，直接输出第一条数据的deptno，就是平均薪资最高的部门编号
	select
	   deptno
	from
	   emp
	group by
	   deptno
	order by
	   avg(sal)
	desc
	limit 1;

老师的方法：第三种：采用表的自连接

老师的方法：
第一步：找到avg(sal)，找出每个部门的平均薪水
select
   deptno,avg(sal) as avgsal
from 
   emp
group by
   deptno;
第二步：将上表作为临时表t，在其中找最大的avgsal
select
  t.deptno,max(t.avgsal) as maxavgsal
from 
   (select deptno,avg(sal) as avgsal from emp group by deptno) t;

6、取得平均薪水最高的部门的部门名称

6、取得平均薪水最高的部门的部门名称
第一步：找出每个部门的平均薪水，降序排列并且使用limit 1生成临时表t，
此时t中只包含排名最低的部门编号和其对应的平均薪资。然后将t表与dept表联合查询，
d.dept=t.deptno
select 
    deptno,avg(sal) as avgsal
from
   emp
group by
   deptno
order by 
   avgsal
desc
limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
接下来找出dept=10所在的部门名称即可。
第二步：t表和dept表联合查询问
查询内容是：d.dname
查询条件是：t.deptno=d.deptno
select
    d.dname
from 
   (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) t
inner join 
   dept d
on
   t.deptno=d.deptno;
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+

老师的方法：


----------------------------------------------------------
老师的方法：
第一步：在上一题第一步的基础上直接修改
select 
    d.dname,avg(e.sal) as avgsal
from
    emp e
join 
    dept d
on
    d.deptno=e.deptno
group by
    e.deptno
order by 
    avgsal
desc
limit 1;
----------------------------------------------------------

7、取得平均薪水最低的部门的部门名称

第一步：找出每个部门的平均薪水，升序排列并且使用limit 1生成临时表t，
此时t中只包含排名最低的部门编号和其对应的平均薪资。然后将t表与dept表联合查询，
d.dept=t.deptno
select 
    deptno,avg(sal) as avgsal
from
   emp
group by
   deptno
order by 
   avgsal
asc
limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
接下来找出dept=30所在的部门名称即可。
第二步：t表和dept表联合查询问
查询内容是：d.dname
查询条件是：t.deptno=d.deptno
select
    d.dname
from 
   (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) t
inner join 
   dept d
on
   t.deptno=d.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+

老师的方法：

----------------------------------------------------------

第一步：找出每个部门的平均薪水
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
| RESEARCH   | 2175.000000 |
| SALES      | 1566.666667 |
+------------+-------------+
第二步：找出每个部门的平均薪水的等级
和salgrade连接查询
select
   t.*,s.grade
from 
   (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno) t
join 
   salgrade s
on 
   t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
第三步：找出最低平均薪资
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
+-------------+
| avgsal      |
+-------------+
| 1566.666667 |
+-------------+
第四步：找出最低薪资的等级
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;
+-------+
| grade |
+-------+
|     3 |
+-------+
第五步：在第二步的表中找第四步中对应的最低薪资的等级[在第二步的步骤中加where条件]
select
   t.*,s.grade
from 
   (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.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字段上出现的)的最高薪水还要高的*姓名

老师的方法：

----------------------------------------------------------

第一步：找出领导,终于想明白了，在mgr出现的都是领导，
但是又重复（几个员工的领导是同一个），所以要去重
select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
第二步：因为上表中又null，所以要排除null
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
第三步：找出普通员工
select empno from emp where empno not in(select distinct mgr from emp where mgr is not null);
+-------+
| empno |
+-------+
|  7369 |
|  7499 |
|  7521 |
|  7654 |
|  7844 |
|  7876 |
|  7900 |
|  7934 |
+-------+
上表中的就是普通员工的empno
第四步：找出普通员工的最高薪水
select 
    max(e.sal) as maxsal
from 
    (select empno from emp where empno not in(select distinct mgr from emp where mgr is not null)) t
join 
    emp e
on
    e.empno=t.empno;
+---------+
| maxsal  |
+---------+
| 1600.00 |
+---------+
第五步：找出比上面maxsal还要高的领导姓名
select
   ename,sal
from 
   emp
where 
   sal>(select max(e.sal) as maxsal from (select empno from emp where empno not in(select distinct mgr from emp where mgr is not null)) t join emp e on e.empno=t.empno);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
----------------------------------------------------------

9、取得薪水最高的前五名员工

查询ename，sal按照sal降序排列,使用limit输出前五条
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、取得薪水最高的第六到第十名员工

查询ename，sal按照sal降序排列,使用limit输出第6到10条
select 
   ename,sal
from 
   emp
order by
   sal 
desc
limit 5,5;//从下标5开始，长度为5.
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+

11、取得最后入职的5名员工

找出员工ename和hiredate，按照hiredate升序排序，使用limit得到最后5个
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

----------------------------------------------------------

第一步：取得每个员工的薪水等级
select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
+--------+-------+
| ename  | grade |
+--------+-------+
| SMITH  |     1 |
| ALLEN  |     3 |
| WARD   |     2 |
| JONES  |     4 |
| MARTIN |     2 |
| BLAKE  |     4 |
| CLARK  |     4 |
| SCOTT  |     4 |
| KING   |     5 |
| TURNER |     3 |
| ADAMS  |     1 |
| JAMES  |     1 |
| FORD   |     4 |
| MILLER |     2 |
+--------+-------+
第二步按照上表中的grade分组。count员工名字
select
    t.grade,count(ename) as counts
from 
    (select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t
group by
    grade;
+-------+--------+
| grade | counts |
+-------+--------+
|     1 |      3 |
|     2 |      3 |
|     3 |      2 |
|     4 |      5 |
|     5 |      1 |
+-------+--------+
也可以将上面两步结合起来
select 
   s.grade,count(e.ename) as counts
from 
  emp e 
join 
  salgrade s 
on e.sal between s.losal and hisal
group by
  grade;
----------------------------------------------------------

13、面试题

14、列出所有员工及领导的姓名

左外连接，e1作为员工表，e2作为领导表，找出员工对应的领导姓名

select 
     e1.ename,e2.ename
from 
     emp e1
left join 
     emp e2
on
    e1.mgr = e2.empno;
+--------+-------+
| 员工      | 领导    |
+--------+-------+
| 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、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称【不会做】

----------------------------------------------------------
老师的方法：
第一步：自连接。找出受雇日期早于其直接上级的所有员工的编号,姓名,部门编号
select
    a.empno,a.ename,a.deptno
from 
    emp a
join 
    emp b
on
    a.hiredate < b.hiredate and a.mgr= b.empno;
+-------+-------+--------+
| empno | ename | deptno |
+-------+-------+--------+
|  7369 | SMITH |     20 |
|  7499 | ALLEN |     30 |
|  7521 | WARD  |     30 |
|  7566 | JONES |     20 |
|  7698 | BLAKE |     30 |
|  7782 | CLARK |     10 |
+-------+-------+--------+

第二步：上表和dept联合查询部门名称,其实也可以直接从第二步开始。一步到位
select
    a.empno '员工',a.hiredate,a.ename '领导',b.hiredate,d.dname '部门名称'
from 
    emp a
join 
    emp b
on
    a.hiredate < b.hiredate and a.mgr= b.empno
join
    dept d
on 
    d.deptno=a.deptno;
+------+------------+-------+------------+------------+
| 员工    | hiredate   | 领导    | hiredate   | 部门名称           |
+------+------------+-------+------------+------------+
| 7782 | 1981-06-09 | CLARK | 1981-11-17 | ACCOUNTING |
| 7369 | 1980-12-17 | SMITH | 1981-12-03 | RESEARCH   |
| 7566 | 1981-04-02 | JONES | 1981-11-17 | RESEARCH   |
| 7499 | 1981-02-20 | ALLEN | 1981-05-01 | SALES      |
| 7521 | 1981-02-22 | WARD  | 1981-05-01 | SALES      |
| 7698 | 1981-05-01 | BLAKE | 1981-11-17 | SALES      |
+------+------------+-------+------------+------------+
----------------------------------------------------------

16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.

老师的方法：外连接

----------------------------------------------------------

select
    e.*,d.dname
from  
    emp e
join 
    dept d
on 
    e.deptno=d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

----------------------------------------------------------

17、列出至少有5个员工的所有部门【做错了，想复杂了】

第一步：列出所有部门的deptno,dname
select 
     deptno,dname
from 
     dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
第二步：列出所有部门的deptno,dname，并统计每个部门的员工数量作为临时表t
select 
     d.dname,count(e.ename) as counts
from 
     dept d
join 
     emp  e
on 
     d.deptno=e.deptno
group by 
     deptno;
+--------+------------+--------+
| deptno | dname      | counts |
+--------+------------+--------+
|     10 | ACCOUNTING |      3 |
|     20 | RESEARCH   |      5 |
|     30 | SALES      |      6 |
+--------+------------+--------+
第三步：再临时表中搜索counts大于5的输出
select
    t.*
from
    (select d.dname,count(e.ename) as counts from dept d join emp e on d.deptno=e.deptno group by e.deptno) t
where 
    t.counts >= 5;
+----------+--------+
| dname    | counts |
+----------+--------+
| RESEARCH |      5 |
| SALES    |      6 |
+----------+--------+

老师的方法

----------------------------------------------------------
老师的方法：group by having
第一步：列出所有的部门编号
select
   deptno
from 
   emp 
group by 
   deptno
+--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
第二步：对上面的结果使用having进行筛选

select
   deptno,count(*) as counts
from 
   emp 
group by 
   deptno
having
   count(*) >= 5;
+--------+--------+
| deptno | counts |
+--------+--------+
|     20 |      5 |
|     30 |      6 |
+--------+--------+
第三步：扩展，还可以列出相应的部门名称
select
   e.deptno,d.dname,count(*) as counts
from 
   emp e
join
   dept d
on 
   e.deptno=d.deptno
group by 
   e.deptno
having
   count(*) >= 5;
+--------+----------+--------+
| deptno | dname    | counts |
+--------+----------+--------+
|     20 | RESEARCH |      5 |
|     30 | SALES    |      6 |
+--------+----------+--------+
----------------------------------------------------------

18、列出薪金比"SMITH"多的所有员工信息.

第一步：找出smith的薪资
select
    sal
from 
   emp
where 
   ename='SMITH';
第二步：结合，使用where后面嵌套子句

select
    e.ename,e.sal
from 
    emp e
where 
    e.sal > (select sal from emp where ename='SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数【不会做】

老师的方法

----------------------------------------------------------
老师的方法：
第一步：找出所有的办事员姓名以及job
select ename,job from emp where job='clerk';
+--------+-------+
| ename  | job   |
+--------+-------+
| SMITH  | CLERK |
| ADAMS  | CLERK |
| JAMES  | CLERK |
| MILLER | CLERK |
+--------+-------+
第二步：找出对应的部门名称以及部门编号，和dept表联合查询
select 
   e.ename,e.job,d.dname,e.deptno
from 
   emp e
join 
   dept d
on 
   e.deptno= d.deptno
where job='clerk';
+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+
第三步:找出每个部门的人数
select
   deptno,count(*) as deptcount
from 
   emp
group by 
   deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+
第四步：将第二第三步的表连接查询
select
    t1.*,t2.deptcount
from 
    (select e.ename,e.job,d.dname,e.deptno from emp e join dept d on e.deptno= d.deptno where job='clerk') t1
right join
    (select deptno,count(*) as deptcount from emp group by deptno) t2
on
     t1.deptno=t2.deptno;
+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+
----------------------------------------------------------

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.【不会做】
第一步：列出最低薪金大于1500的各种工作
select job from emp group by job having min(sal) > 1500;
+-----------+
| job       |
+-----------+
| ANALYST   |
| MANAGER   |
| PRESIDENT |
+-----------+
第二步：加count(*)计数
select job,count(*) as counts from emp group by job having min(sal) > 1500;
+-----------+--------+
| job       | counts |
+-----------+--------+
| ANALYST   |      2 |
| MANAGER   |      3 |
| PRESIDENT |      1 |
+-----------+--------+

21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.

第一步：列出最低薪金大于1500的各种工作
select job from emp group by job having min(sal) > 1500;
+-----------+
| job       |
+-----------+
| ANALYST   |
| MANAGER   |
| PRESIDENT |
+-----------+
第二步：加count(*)计数
select job,count(*) as counts from emp group by job having min(sal) > 1500;
+-----------+--------+
| job       | counts |
+-----------+--------+
| ANALYST   |      2 |
| MANAGER   |      3 |
| PRESIDENT |      1 |
+-----------+--------+
===================================================================================
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
第一步：emp与dept联合查询找出所有的部门编号,
select e.ename,d.deptno,d.dname from emp e join dept d on e.deptno= d.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| CLARK  |     10 | ACCOUNTING |
| KING   |     10 | ACCOUNTING |
| MILLER |     10 | ACCOUNTING |
| SMITH  |     20 | RESEARCH   |
| JONES  |     20 | RESEARCH   |
| SCOTT  |     20 | RESEARCH   |
| ADAMS  |     20 | RESEARCH   |
| FORD   |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| TURNER |     30 | SALES      |
| JAMES  |     30 | SALES      |
+--------+--------+------------+
第二步：使用where进行筛选，输出销售部的信息
select e.ename,d.deptno,d.dname from emp e join dept d on e.deptno= d.deptno where d.dname='sales';
+--------+--------+-------+
| ename  | deptno | dname |
+--------+--------+-------+
| ALLEN  |     30 | SALES |
| WARD   |     30 | SALES |
| MARTIN |     30 | SALES |
| BLAKE  |     30 | SALES |
| TURNER |     30 | SALES |
| JAMES  |     30 | SALES |
+--------+--------+-------+

方法2：
第一步:找出销售部的部门编号,
select deptno from dept where dname='sales';
+--------+
| deptno |
+--------+
|     30 |
+--------+
第二步：找出部门编号是30的员工姓名
select ename from emp where deptno=(select deptno from dept where dname='sales');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

第一步：找出公司的平均薪资
select avg(sal) as avgsal from emp;
+-------------+
| avgsal      |
+-------------+
| 2073.214286 |
+-------------+
第二步：找出薪资大于avgsal的ename，deptno，mgr，
select
   ename,deptno,sal,mgr
from 
   emp
where 
   sal>(select avg(sal) as avgsal from emp);
+-------+--------+---------+------+
| ename | deptno | sal     | mgr  |
+-------+--------+---------+------+
| JONES |     20 | 2975.00 | 7839 |
| BLAKE |     30 | 2850.00 | 7839 |
| CLARK |     10 | 2450.00 | 7839 |
| SCOTT |     20 | 3000.00 | 7566 |
| KING  |     10 | 5000.00 | NULL |
| FORD  |     20 | 3000.00 | 7566 |
+-------+--------+---------+------+
第三步：找出上级领导姓名
select
   e1.ename,e1.deptno,e1.sal,e1.mgr,e2.ename as '上级领导'
from 
   emp e1
left join 
   emp e2
on
   e1.mgr=e2.empno
where 
   e1.sal>(select avg(sal) as avgsal from emp);
+-------+--------+---------+------+----------+
| ename | deptno | sal     | mgr  | 上级领导      |
+-------+--------+---------+------+----------+
| JONES |     20 | 2975.00 | 7839 | KING     |
| BLAKE |     30 | 2850.00 | 7839 | KING     |
| CLARK |     10 | 2450.00 | 7839 | KING     |
| SCOTT |     20 | 3000.00 | 7566 | JONES    |
| KING  |     10 | 5000.00 | NULL | NULL     |
| FORD  |     20 | 3000.00 | 7566 | JONES    |
+-------+--------+---------+------+----------+
第四步:找出上表的工资的工资等级
select
   t.*,s.grade as '工资等级'
from
   (select e1.ename,e1.deptno,e1.sal,e1.mgr,e2.ename as '上级领导' from 
	   emp e1
	left join 
	   emp e2
	on
	   e1.mgr=e2.empno
	where 
	   e1.sal>(select avg(sal) as avgsal from emp)) t
join
    salgrade s
on
    t.sal between s.losal and s.hisal;
+-------+--------+---------+------+----------+----------+
| ename | deptno | sal     | mgr  | 上级领导      | 工资等级       |
+-------+--------+---------+------+----------+----------+
| JONES |     20 | 2975.00 | 7839 | KING     |        4 |
| BLAKE |     30 | 2850.00 | 7839 | KING     |        4 |
| CLARK |     10 | 2450.00 | 7839 | KING     |        4 |
| SCOTT |     20 | 3000.00 | 7566 | JONES    |        4 |
| KING  |     10 | 5000.00 | NULL | NULL     |        5 |
| FORD  |     20 | 3000.00 | 7566 | JONES    |        4 |
+-------+--------+---------+------+----------+----------+

老师的方法：

----------------------------------------------------------

select
   a.ename '姓名',d.dname '部门',b.ename '领导',s.grade '工资等级'
from
   emp a
join
   dept d
on 
   a.deptno=d.deptno
left join
   emp b
on 
   a.mgr=b.empno
join
   salgrade s
on
   a.sal between s.losal and s.hisal
where 
   a.sal > (select avg(sal) from emp);
+-------+------------+-------+----------+
| 姓名      | 部门           | 领导    | 工资等级       |
+-------+------------+-------+----------+
| JONES | RESEARCH   | KING  |        4 |
| BLAKE | SALES      | KING  |        4 |
| CLARK | ACCOUNTING | KING  |        4 |
| SCOTT | RESEARCH   | JONES |        4 |
| KING  | ACCOUNTING | NULL  |        5 |
| FORD  | RESEARCH   | JONES |        4 |
+-------+------------+-------+----------+
----------------------------------------------------------

23、列出与"SCOTT"从事相同工作的所有员工及部门名称.

注意使用where ename<>'scott'将scott排除出去

第一步：找出"SCOTT"从事的工作job。,注意使用where ename<>'scott'将scott排除出去
select job from emp where ename='scott';
+---------+
| job     |
+---------+
| ANALYST |
+---------+
第二步：找出工作为analyst的所有员工和部门名称
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';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+

24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金【不会】

注意部门编号不等于30(因为要在其他部门中找)

----------------------------------------------------------
老师的方法：
第一步:找出30部门的薪资
select distinct sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
第二步：在整张表中找员工姓名和薪资,但是注意部门编号不等于30(因为要在其他部门中找)
select
   ename,sal
from 
   emp 
where 
   sal in(select distinct sal from emp where deptno=30)
and 
   deptno <> 30;

Empty set (0.00 sec)
----------------------------------------------------------

25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.

第一步：找出30部门的最高薪资
select max(sal) as maxsal from emp where deptno=30;
+---------+
| maxsal  |
+---------+
| 2850.00 |
+---------+
第二步：查询员工的ename、sal、dname。条件是where sal > 2850
select
   e.ename,e.sal,d.dname
from 
  emp e
join
  dept d
on 
  e.deptno=d.deptno
where 
  sal > (select max(sal) as maxsal from emp where deptno=30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

26、列出在每个部门工作的员工数量,平均工资和平均服务期限.

第一步：找出每个部门的员工数量和平均工资
select 
  deptno,count(*) as counts ,avg(sal) as avgsal
from 
   emp
group by
   deptno;
+--------+--------+-------------+
| deptno | counts | avgsal      |
+--------+--------+-------------+
|     10 |      3 | 2916.666667 |
|     20 |      5 | 2175.000000 |
|     30 |      6 | 1566.666667 |
+--------+--------+-------------+
第二步：找出平均服务期限【？】

第一步：找出每个部门的员工数量和平均工资,注意使用ifnull
select 
  d.*,count(e.ename) as counts ,ifnull(avg(sal),0) as avgsal
from 
   emp e
right join
   dept d
on
   e.deptno=d.deptno
group by
   d.dname;
+--------+------------+----------+--------+-------------+
| DEPTNO | DNAME      | LOC      | counts | avgsal      |
+--------+------------+----------+--------+-------------+
|     10 | ACCOUNTING | NEW YORK |      3 | 2916.666667 |
|     40 | OPERATIONS | BOSTON   |      0 |    0.000000 |
|     20 | RESEARCH   | DALLAS   |      5 | 2175.000000 |
|     30 | SALES      | CHICAGO  |      6 | 1566.666667 |
+--------+------------+----------+--------+-------------+
第二步：找出平均服务期限【系统当前年份-入职年份】
先找到函数，计算年份差timestampdiff(year,hiredate,now())
 select timestampdiff(year,hiredate,now()) as avgyear from emp;
+------+
| avgyear |
+------+
|   40 |
|   40 |
|   40 |
|   40 |
|   39 |
|   39 |
|   39 |
|   33 |
|   39 |
|   39 |
|   33 |
|   39 |
|   39 |
|   39 |
+------+
第三步：在后面直接加avg(timestampdiff(year,hiredate,now()))
select 
  d.*,count(e.ename) as counts ,ifnull(avg(sal),0) as avgsal,ifnull(avg(timestampdiff(year,hiredate,now())),0) as avgyear
from 
   emp e
right join
   dept d
on
   e.deptno=d.deptno
group by
   d.dname;
+--------+------------+----------+--------+-------------+---------+
| DEPTNO | DNAME      | LOC      | counts | avgsal      | avgyear |
+--------+------------+----------+--------+-------------+---------+
|     10 | ACCOUNTING | NEW YORK |      3 | 2916.666667 | 39.0000 |
|     40 | OPERATIONS | BOSTON   |      0 |    0.000000 |  0.0000 |
|     20 | RESEARCH   | DALLAS   |      5 | 2175.000000 | 37.0000 |
|     30 | SALES      | CHICAGO  |      6 | 1566.666667 | 39.3333 |
+--------+------------+----------+--------+-------------+---------+

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、列出所有部门的详细信息和人数

按照部门编号分组计数，注意group by分组之后才可以在select后面写，并且count（e.ename）是正确的，不能写count（*）

select
   d.deptno,d.dname,d.loc,count(e.ename) as '人数'
from
   emp e
right join
   dept d
on
   e.deptno=d.deptno
group by
   d.deptno,d.dname,d.loc;
+--------+------------+----------+------+
| deptno | dname      | loc      | 人数 |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |    3 |
|     20 | RESEARCH   | DALLAS   |    5 |
|     30 | SALES      | CHICAGO  |    6 |
|     40 | OPERATIONS | BOSTON   |    0 |
+--------+------------+----------+------+

29、列出各种工作的最低工资及从事此工作的雇员姓名

第一步：找出各种工作的最低工资
select job,min(sal) from emp group by job;
+-----------+----------+
| job       | min(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |   800.00 |
| MANAGER   |  2450.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1250.00 |
+-----------+----------+
第二步:将emp表与上表连接
	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;
+--------+-----------+---------+
| 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 
  deptno,min(sal) as minsal 
from 
   emp 
where 
   job='manager'
group by
   deptno;
+--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+

31、列出所有员工的年工资,按年薪从低到高排序

31、列出所有员工的年工资,按年薪从低到高排序。注意使用ifnull判断comm
select
   ename,sal * 12+ifnull(comm,0) as '年薪'
from 
   emp
order by
   sal 
asc;
+--------+----------+
| ename  | 年薪        |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| WARD   | 15500.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN  | 19500.00 |
| CLARK  | 29400.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

32、求出员工领导的薪水超过3000的员工名称与领导名称

第一步：要会找出领导
select distinct mgr from emp;
第二步：自连接找出员工与对应的领导。找出薪水超过3000的领导
select
   a.ename '员工',b.ename '领导'
from
   emp a
left join
   emp b
on
   a.mgr=b.empno
where 
   b.sal>3000;
+-------+------+
| 员工     | 领导   |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+

33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.

第一步：找出部门名称中带‘s’的部门名字,右连接保证operation出现，且去重
select 
    distinct d.dname
from
    emp e 
right join 
    dept d
on
   e.deptno=d.deptno
where
   d.dname like '%S%';
第二步:
select 
    distinct d.dname,count(sal) as num,sum(sal) as counts
from
    emp e 
right join 
    dept d
on
   e.deptno=d.deptno
where
   d.dname like '%S%'
group by
   d.deptno;

+------------+-----+----------+
| dname      | num | counts   |
+------------+-----+----------+
| RESEARCH   |   5 | 10875.00 |
| SALES      |   6 |  9400.00 |
| OPERATIONS |   0 |     NULL |
+------------+-----+----------+

34、给任职日期超过30年的员工加薪10%【不会做】

第一步：如何判断任职日期超过30年
update emp set sal = sal * 1.1 where (任职日期超过30年)
第二步:判断任职日期超过30年
TimeStampDiff(year, hiredate, now()) > 30
合并起来：
update emp set sal = sal * 1.1 where TimeStampDiff(year, hiredate, now()) > 30;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  880.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1760.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1375.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 3272.50 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1375.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 3135.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2695.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3300.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5500.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1650.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1210.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 1045.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3300.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1430.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

码农公寓

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%【不会做】

相关文章