jianping5

V1

2022/04/26阅读:28主题:萌绿

MySQL 34道题

TEST

数据库描述

mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+
mysql> select DEPTNO,DNAME,LOC from dept; 
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
mysql> select * from emp; // 开发中最好不要使用 * (效率低)
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

Test1

# 题意:取得每个部门最高薪水的人员名称
第一步:取得每个部门最高薪水(按部门编号分组,找出每一组的最大值)
select
 deptno,max(sal) as maxsal 
from
 emp
group by
 deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     20 | 3000.00 |
|     30 | 2850.00 |
|     10 | 5000.00 |
+--------+---------+
第二步:将以上查询结果看成一张临时表 t,连接 t 和 emp,
条件:emp.deptno = t.deptno and emp.sal = t.maxsal
从而得到每个部门最高薪水的人员名称。
select
 e.ename, t.deptno, t.maxsal
from
 emp e
join
 (select deptno,max(sal) as maxsal from emp group by deptno) t
on
 e.deptno = t.deptno and e.sal = t.maxsal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

Test2

# 题意:取出薪水在部门的平均薪水之上的人员名称
第一步:找出每个部门的平均薪水
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
第二步:将上述查询结果作为一张临时表 t,连接 t 和 emp e
条件为:e.deptno = t.deptno and e.sal > t.avgsal
select
 e.ename,e.sal,t.deptno,t.avgsal
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 |
+-------+---------+--------+-------------+

Test3

# 取出部门中(所有人)平均的薪水等级
第一步:取出所有人的薪水等级
select
 e.ename,e.deptno,e.sal,t.grade
from
 emp e
join
 salgrade t
on
  e.sal between t.losal and t.hisal;
+--------+--------+---------+-------+
| ename  | deptno | sal     | grade |
+--------+--------+---------+-------+
| SMITH  |     20 |  800.00 |     1 |
| ALLEN  |     30 | 1600.00 |     3 |
| WARD   |     30 | 1250.00 |     2 |
| JONES  |     20 | 2975.00 |     4 |
| MARTIN |     30 | 1250.00 |     2 |
| BLAKE  |     30 | 2850.00 |     4 |
| CLARK  |     10 | 2450.00 |     4 |
| SCOTT  |     20 | 3000.00 |     4 |
| KING   |     10 | 5000.00 |     5 |
| TURNER |     30 | 1500.00 |     3 |
| ADAMS  |     20 | 1100.00 |     1 |
| JAMES  |     30 |  950.00 |     1 |
| FORD   |     20 | 3000.00 |     4 |
| MILLER |     10 | 1300.00 |     2 |
+--------+--------+---------+-------+

第二步:按照部门编号分组,取得每个部门的平均的薪水等级
select e.deptno,avg(t.grade) as avg_grade 
from
 emp e
join
 salgrade t
on
 e.sal between t.losal and t.hisal
group by 
 e.deptno
order by
 e.deptno;
+--------+-----------+
| deptno | avg_grade |
+--------+-----------+
|     10 |    3.6667 |
|     20 |    2.8000 |
|     30 |    2.5000 |
+--------+-----------+

Test4

# 题意:取得最高薪水
方案一:
select
 ename,sal
from
 emp
order by
 sal desc
limit   # limit 需要注意可能存在两个一样的最高薪水(若需要求最高薪水对于的员工名称的话)
 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
方案二:
step1: select max(sal) as max_sal from emp;
+---------+
| max_sal |
+---------+
| 5000.00 |
+---------+
step2: select ename,sal from emp where sal = (select max(sal) as max_sal from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
方案三:(自连接)
step1: 
select distinct a.sal from emp a join emp b on a.sal < b.sal;
+---------+
| sal     |
+---------+
| 1300.00 |
|  950.00 |
| 1100.00 |
| 1500.00 |
| 1250.00 |
|  800.00 |
| 2450.00 |
| 2850.00 |
| 1600.00 |
| 2975.00 |
| 3000.00 |
+---------+
step2:
select ename,sal from emp
where
 sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

Test5

# 题意:找出平均薪水最高的部门编号
# 方案一:
step1:
select deptno,avg(sal) as avg_sal from emp group by deptno;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
step2:
select deptno,avg(sal) as avg_sal from emp 
group by deptno 
order by avg_sal desc
limit 1;  # 需要注意可能存在两个薪水一样且都是最高
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
# 方案二:
step1:
select max(t.avg_sal) from (select deptno,avg(sal) as avg_sal from emp group by deptno) t;
调用每个派生表都必须有自己的别名!!!
ERROR 1248 (42000): Every derived table must have its own alias
+--------------+
| max(avg_sal) |
+--------------+
|  2916.666667 |
+--------------+
step2:
select
 deptno,avg(sal) avg_sal
from
 emp
group by
 deptno
having
 avg_sal = (select max(t.avg_sal) from (select deptno,avg(sal) as avg_sal from emp group by deptno) t);

+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

Test6

# 题意:取得平均薪水最高的部门的名称
select d.dname,avg(e.sal) as avg_sal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname
order by avg_sal desc
limit 1# 需要注意平均薪水最高的部门可能有多个且它们的平均薪水一样,找出最高再用 where 筛选
+------------+-------------+
| dname      | avg_sal     |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

Test7

# 题意:求平均薪水的等级最低的部门的名称

两个点:
1.平均薪水最低等级一定最低
2.平均薪水不是最低,但其等级可能是最低

第一步:找出每个部门的平均薪水(按部门名称分类)
select d.dname,avg(e.sal) as avg_sal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname;
+------------+-------------+
| dname      | avg_sal     |
+------------+-------------+
| RESEARCH   | 2175.000000 |
| SALES      | 1566.666667 |
| ACCOUNTING | 2916.666667 |
+------------+-------------+

第二步:得到每个部门对应的平均薪水的等级
select
 t.dname,t.avg_sal,s.grade
from
 (select d.dname,avg(e.sal) as avg_sal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
 salgrade s
on
 t.avg_sal between losal and hisal;
+------------+-------------+-------+
| dname      | avg_sal     | grade |
+------------+-------------+-------+
| RESEARCH   | 2175.000000 |     4 |
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
+------------+-------------+-------+

第三步:找出最低平均薪资所对应的等级
step1: 找出最低薪资
select avg(sal) as avg_sal from emp group by deptno order by avg_sal asc limit 1
+-------------+
| avg_sal     |
+-------------+
| 1566.666667 |
+-------------+
step2: 找出最低薪资对应的等级
select grade from salgrade
where
 (select avg(sal) as avg_sal from emp group by deptno order by avg_sal asc limit 1between losal and hisal;
+-------+
| grade |
+-------+
|     3 |
+-------+

第四步:找出平均薪水的等级最低的部门的名称
select t.dname,t.avg_sal,s.grade
from
 (select d.dname,avg(e.sal) as avg_sal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
 salgrade s
on
 t.avg_sal between losal and hisal
where
 s.grade = (select grade from salgrade where
(select avg(sal) as avg_sal from emp group by deptno order by avg_sal asc limit 1between losal and hisal);
+-------+-------------+-------+
| dname | avg_sal     | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+

Test8

# 题意:取得比普通员工(员工代码没有再 mgr 字段上出现的)的最高薪水还要高的领导姓名
第一步:找出领导(不在此区间的则为普通员工)
select distinct mgr from emp where mgr is not null# 需要排除空值
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
第二步:找出普通员工的最高薪水
select max(sal) as maxsal from emp
where empno not in(select distinct mgr from emp where mgr is not null);
+---------+
| maxsal  |
+---------+
| 1600.00 |
+---------+
第三步:找出比其最高薪水高的领导人姓名(需要注意:比普通员工的最高薪水还要高的一定是领导)
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));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

Test9

# 取得薪水最高的前五名员工(不考虑相等)
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 |
+-------+---------+

Test10

# 取得薪水最高的第六到第十名员工
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 |
| WARD   | 1250.00 |
+--------+---------+

Test11

# 取得最后入职的 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 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+

Test12

# 取得每个薪水等级有多少个员工

第一步:取得每个员工的薪水等级
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 |
+--------+---------+-------+

第二步:取得每个薪水等级对应的员工个数(按薪水等级分组)
# select s.grade,count(*)  # count(*) 只要一行记录中有一个不为 NULL 的都会被记录(即总记录数)

select s.grade,count(empno) as emp_count # 拿 empno 来计算员工的个数 因为 empn 为主键,不能为 NULL 
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by s.grade
order by s.grade;
+-------+-----------+
| grade | emp_count |
+-------+-----------+
|     1 |         3 |
|     2 |         3 |
|     3 |         2 |
|     4 |         5 |
|     5 |         1 |
+-------+-----------+

Test13

# 题意:
有三个表 S(学生表)、C(课程表)、SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAEM,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1. 找出没选过‘黎明’老师的所有学生姓名
2. 列出 2 门以上(含 2 门)不及格学生姓名及平均成绩
3. 找出学过 1 号课程又学过 2 号课程所有学生的姓名

Test14

# 题意:列出所有员工及领导的姓名
select
 a.ename '员工',b.ename '领导'
from
 emp a
left (outerjoin # 左外连接
 emp b
on
 a.mgr = b.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 |
+--------+-------+

Test15

# 题意:列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select a.empno,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;
+-------+-------+------------+-------+------------+------------+
| empno | 员工  | hiredate   | 领导  | hiredate   | dname      |
+-------+-------+------------+-------+------------+------------+
|  7369 | SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
|  7499 | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
|  7521 | WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
|  7566 | JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
|  7698 | BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
|  7782 | CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
+-------+-------+------------+-------+------------+------------+

Test16

# 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select e.*,d.dname
from emp e
right join dept d  # 右外连接 以部门表为主表(因为题意需要列出没有员工的部门)
on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

Test17

# 题意:列出至少有 5 个员工的部门
select d.dname,count(*)
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname
having count(*) >= 5;
+----------+----------+
| dname    | count(*) |
+----------+----------+
| RESEARCH |        5 |
| SALES    |        6 |
+----------+----------+

Test18

# 题意:列出薪资比‘SMITH’多的所有员工信息
第一步:找出'SMITH'的薪资
select sal from emp where ename = 'SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

第二步:找出比其薪资大的所有员工的信息
select * from emp where sal > (select sal from emp where ename = 'SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

Test19

# 题意:列出所有'CLERK'(办事员)的姓名及其部门名称,部门的人数

第一步:找出办事员的姓名及其部门名称
select e.ename,e.job,d.dname,d.deptno
from emp e
join dept d
on e.deptno = d.deptno
where e.job = 'CLERK';
+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
| MILLER | CLERK | ACCOUNTING |     10 |
+--------+-------+------------+--------+

第二步:找出部门名称及其人数()
select d.dname,count(*) as emp_count
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname;
+------------+-----------+
| dname      | emp_count |
+------------+-----------+
| RESEARCH   |         5 |
| SALES      |         6 |
| ACCOUNTING |         3 |
+------------+-----------+

第三步:列出所有办事员的姓名及其部门名称,部门的人数
select a.ename,a.job,a.dname,a.deptno,b.emp_count
from 
 (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where e.job = 'CLERK') a
join
 (select d.dname,count(*) as emp_count from emp e join dept d on e.deptno = d.deptno group by d.dname) b
on
 a.dname = b.dname;
+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | emp_count |
+--------+-------+------------+--------+-----------+
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
+--------+-------+------------+--------+-----------+

Test20

# 题意:列出最低薪资大于 1500 的各种工作及从事此工作的全部雇员人数

第一步:找出最低薪资大于 1500 的各种工作
select job,min(sal) as min_sal from emp group by job having min(sal) > 1500
+-----------+---------+
| job       | min_sal |
+-----------+---------+
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+

第二步:列出最低薪资大于 1500 的各种工作及其对应的全部雇员人数
select
 job,min(sal) as min_sal,count(*) as emp_count 
from
 emp
group by 
 job
having
 min(sal) > 1500
+-----------+---------+-----------+
| job       | min_sal | emp_count |
+-----------+---------+-----------+
| MANAGER   | 2450.00 |         3 |
| ANALYST   | 3000.00 |         2 |
| PRESIDENT | 5000.00 |         1 |
+-----------+---------+-----------+

Test21

# 题意:列出在部门'SALES'<销售部> 工作的员工的姓名,假定不知道销售部的部门编号

第一步:找出部门'SALES'的部门编号
select deptno from dept where dname = 'SALES';

第二步:找出在部门'SALES'的员工姓名
select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where e.deptno = (select deptno from dept where dname = 'SALES');
+--------+-------+
| ename  | dname |
+--------+-------+
| ALLEN  | SALES |
| WARD   | SALES |
| MARTIN | SALES |
| BLAKE  | SALES |
| TURNER | SALES |
| JAMES  | SALES |
+--------+-------+

Test22

# 题意:列出薪资高于公司平均薪资的所有员工,所在部门,上级领导,雇员的工资等级

第一步:找出公司的平均薪资
select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
第二步:列出薪资高于公司平均薪资的所有员工,所在部门,上级领导,雇员的工资等级
select a.ename '员工',a.deptno,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 losal and hisal
where a.sal > (select avg(sal) from emp);
+-------+--------+------------+-------+-------+
| 员工  | deptno | dname      | 领导  | grade |
+-------+--------+------------+-------+-------+
| FORD  |     20 | RESEARCH   | JONES |     4 |
| SCOTT |     20 | RESEARCH   | JONES |     4 |
| CLARK |     10 | ACCOUNTING | KING  |     4 |
| BLAKE |     30 | SALES      | KING  |     4 |
| JONES |     20 | RESEARCH   | KING  |     4 |
| KING  |     10 | ACCOUNTING | NULL  |     5 |
+-------+--------+------------+-------+-------+

Test23

# 题意:列出与'SCOTT'(员工名)从事相同工作的所有员工及部门名称

第一步:找出 'SCOTT' 的工作
select job from emp where ename = 'SCOTT';
+---------+
| job     |
+---------+
| ANALYST |
+---------+

第二步:列出与'SCOTT'(员工名)从事相同工作的所有员工及部门名称
select e.ename,e.deptno,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 | deptno | dname    |
+-------+--------+----------+
| FORD  |     20 | RESEARCH |
+-------+--------+----------+

Test24

# 题意:列出薪资等于部门 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 sal from emp where deptno = 30
and deptno <> 30;

Empty set (0.00 sec)

Test25

# 题意:列出薪资高于在部门 30 工作的所有员工的薪资的员工的姓名,薪资和部门名称

第一步:找出在部门 30 工作的员工的最高薪资
select max(sal) from emp where deptno = 30;

select max(sal) from emp group by deptno having deptno = 30# 这种方法效率较低

第二步:列出薪资高于在部门 30 工作的所有员工的薪资的员工的姓名,薪资和部门名称
select e.ename,e.sal,e.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where e.sal > (select max(sal) from emp where deptno = 30);
+-------+---------+--------+------------+
| ename | sal     | deptno | dname      |
+-------+---------+--------+------------+
| JONES | 2975.00 |     20 | RESEARCH   |
| SCOTT | 3000.00 |     20 | RESEARCH   |
| KING  | 5000.00 |     10 | ACCOUNTING |
| FORD  | 3000.00 |     20 | RESEARCH   |
+-------+---------+--------+------------+

Test26

# 题意:列出在每个部门工作的员工数量,平均工资和平均服务期限
count 自动忽略 NULL
timestampdiff(间隔类型,开始时间,结束时间)
间隔类型有:year,quarter,month,week,day,hour,minute,second

以部门表为主表:
select
 d.deptno,d.dname,count(e.empno) as emp_count, ifnull(avg(e.sal),0as avg_sal,
 avg(ifnull(timestampdiff(day,e.hiredate,now()),0)) as avg_servtime
from emp e
right join dept d
on e.deptno = d.deptno
group by d.dname;
+------------+-----------+-------------+--------------+
| dname      | emp_count | avg_sal     | avg_servtime |
+------------+-----------+-------------+--------------+
| ACCOUNTING |         3 | 2916.666667 |   14798.3333 |
| RESEARCH   |         5 | 2175.000000 |   14078.2000 |
| SALES      |         6 | 1566.666667 |   14907.3333 |
| OPERATIONS |         0 |    0.000000 |       0.0000 |
+------------+-----------+-------------+--------------+

Test27

# 题意:列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+

Test28

# 题意:列出所有部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(e.empno) as emp_count
from emp e
right join dept d
on e.deptno  = d.deptno
group by d.deptno,d.dname,d.loc;
+--------+------------+----------+-----------+
| deptno | dname      | loc      | emp_count |
+--------+------------+----------+-----------+
|     10 | ACCOUNTING | NEW YORK |         3 |
|     20 | RESEARCH   | DALLAS   |         5 |
|     30 | SALES      | CHICAGO  |         6 |
|     40 | OPERATIONS | BOSTON   |         0 |
+--------+------------+----------+-----------+

Test29

# 题意:列出各种工作的最低工资及从事此工作的员工姓名

第一步:列出各种工作的最低工资
select job,min(sal) as min_sal
from emp
group by job;
+-----------+---------+
| job       | min_sal |
+-----------+---------+
| CLERK     |  800.00 |
| SALESMAN  | 1250.00 |
| MANAGER   | 2450.00 |
| ANALYST   | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+

第二步:列出各种工作的最低工资及从事此工作的员工姓名
select e.ename,a.job,a.min_sal
from emp e
join (select job,min(sal) as min_sal from emp group by job) a
on a.job = e.job and a.min_sal = e.sal
order by a.min_sal;
+--------+-----------+---------+
| ename  | job       | min_sal |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| FORD   | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
+--------+-----------+---------+

Test30

# 题意:列出各个部门的 MANAGER(领导)的最低薪资

第一步:简略列出各个部门 MANAGER 的最低薪资
select d.deptno,d.dname,min(e.sal) as min_sal
from emp e
join dept d
on e.deptno = d.deptno
where job = 'MANAGER'
group by d.deptno,d.dname
order by d.deptno;
+--------+------------+---------+
| deptno | dname      | min_sal |
+--------+------------+---------+
|     10 | ACCOUNTING | 2450.00 |
|     20 | RESEARCH   | 2975.00 |
|     30 | SALES      | 2850.00 |
+--------+------------+---------+

第二步:详细列出各个部门 MANAGER 的最低薪资
select
 e.ename,e.job,t.deptno,t.dname,t.min_sal
from
 emp e
join 
 (select d.deptno,d.dname,min(e.sal) as min_sal from emp e join dept d on e.deptno    = d.deptno where job = 'MANAGER' group by d.deptno,d.dname order by d.deptno) t
on e.sal = t.min_sal; 
+-------+---------+--------+------------+---------+
| ename | job     | deptno | dname      | min_sal |
+-------+---------+--------+------------+---------+
| JONES | MANAGER |     20 | RESEARCH   | 2975.00 |
| BLAKE | MANAGER |     30 | SALES      | 2850.00 |
| CLARK | MANAGER |     10 | ACCOUNTING | 2450.00 |
+-------+---------+--------+------------+---------+

Test31

# 题意:列出所有员工的年工资,按年薪从低到高排序
select empno,ename,(sal+ifnull(comm,0))*12 as year_sal
from emp
order by year_sal asc;
+-------+--------+----------+
| empno | ename  | year_sal |
+-------+--------+----------+
|  7369 | SMITH  |  9600.00 |
|  7900 | JAMES  | 11400.00 |
|  7876 | ADAMS  | 13200.00 |
|  7934 | MILLER | 15600.00 |
|  7844 | TURNER | 18000.00 |
|  7521 | WARD   | 21000.00 |
|  7499 | ALLEN  | 22800.00 |
|  7782 | CLARK  | 29400.00 |
|  7654 | MARTIN | 31800.00 | 
|  7698 | BLAKE  | 34200.00 |
|  7566 | JONES  | 35700.00 |
|  7788 | SCOTT  | 36000.00 |
|  7902 | FORD   | 36000.00 |
|  7839 | KING   | 60000.00 |
+-------+--------+----------+

Test32

# 题意:求出员工领导的薪水超过 3000 的员工名称与领导名称
select a.ename '员工',b.ename '领导',b.sal '领导的薪水'
from emp a
join emp b
on a.mgr = b.empno
where b.sal > 3000;
+-------+------+------------+
| 员工  | 领导 | 领导的薪水 |
+-------+------+------------+
| JONES | KING |    5000.00 |
| BLAKE | KING |    5000.00 |
| CLARK | KING |    5000.00 |
+-------+------+------------+

Test33

# 题意:求出部门名称中,带'S'字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,d.loc,ifnull(sum(e.sal),0) sum_sal,count(e.empno) emp_count
from emp e
right join dept d
on e.deptno = d.deptno
where d.dname like '%S%'
group by d.deptno,d.dname,d.loc;
+--------+------------+---------+----------+-----------+
| deptno | dname      | loc     | sum_sal  | emp_count |
+--------+------------+---------+----------+-----------+
|     20 | RESEARCH   | DALLAS  | 10875.00 |         5 |
|     30 | SALES      | CHICAGO |  9400.00 |         6 |
|     40 | OPERATIONS | BOSTON  |     0.00 |         0 |
+--------+------------+---------+----------+-----------+

Test34

# 题意:给任职日期超过 30 年的员工加薪 %10

第一步:查询认知日期超过 30 年的员工,并列出其涨薪后的薪资
select ename,timestampdiff(year,hiredate,now()) as work_year,sal*1.1 as up_sal 
from emp
where timestampdiff(year,hiredate,now()) > 30;
+--------+-----------+---------+
| ename  | work_year | up_sal  |
+--------+-----------+---------+
| SMITH  |        41 |  880.00 |
| ALLEN  |        41 | 1760.00 |
| WARD   |        41 | 1375.00 |
| JONES  |        41 | 3272.50 |
| MARTIN |        40 | 1375.00 |
| BLAKE  |        40 | 3135.00 |
| CLARK  |        40 | 2695.00 |
| SCOTT  |        35 | 3300.00 |
| KING   |        40 | 5500.00 |
| TURNER |        40 | 1650.00 |
| ADAMS  |        34 | 1210.00 |
| JAMES  |        40 | 1045.00 |
| FORD   |        40 | 3300.00 |
| MILLER |        40 | 1430.00 |
+--------+-----------+---------+

第二步:更新到表格中
update emp set sal = sal*1.1
where ifnull(timestampdiff(year,hiredate,now()),0) > 30;

分类:

后端

标签:

数据库

作者介绍

jianping5
V1