SQL——关于bjpowernode.sql的33道经典例题之18-33
目录
18 列出所有“CLERK”（办事员）的姓名和部门名称、部门人数
19 列出最低薪水大于1500的各种工作和此工作的全部雇员人数
21 列出薪资高于公司平均薪资的所有员工，所在部门、上级领导、雇员的工资等级
22 列出与(ename)“SCOTT”从事相同工作的所有员工和部门名称
23 列出薪资等于部门30中工作的员工的薪资的其他员工的姓名和薪资
24 列出薪资高于在部门30中工作的员工的薪资的员工的姓名和薪资、部门名称
32 列出部门名称中带“S”字符的部门员工的工资合计、部门人数
数据代码见上篇。
|
dept：部门表 |
emp：员工表 |
salgrade ：工资等级表 |
|
deptno：部门编号 |
empno：员工编号 |
grade：等级 |
|
dname：部门名称 |
ename:员工名字 |
losal：最低薪资 |
|
loc：部门位置 |
job：工作岗位 |
hisal：最高薪资 |
|
|
mgr：上级领导编号 |
|
|
|
hiredate：入职时间 |
|
|
|
sal：月薪 |
|
|
|
comm：补助/津贴 |
|
|
|
deptno：部门编号 |
|
18 列出所有“CLERK”（办事员）的姓名和部门名称、部门人数
# （1）工作岗位为“CLERK”的信息
select d.deptno,d.dname,e.ename
from emp e
join dept d
on e.deptno=d.deptno
where e.job='CLERK';
+--------+------------+--------+
| deptno | dname | ename |
+--------+------------+--------+
| 20 | RESEARCH | SMITH |
| 20 | RESEARCH | ADAMS |
| 30 | SALES | JAMES |
| 10 | ACCOUNTING | MILLER |
+--------+------------+--------+
# （2）每个部门的员工数量
select e.deptno,count(e.ename) as totalEmp
from emp e
group by e.deptno;
+--------+----------+
| deptno | totalEmp |
+--------+----------+
| 20 | 5 |
| 30 | 6 |
| 10 | 3 |
+--------+----------+
# （3）两张表连接
select t.deptno,t.dname,t.ename,f.totalEmp
from (select d.deptno,d.dname,e.ename from emp e
join dept d on e.deptno=d.deptno where e.job='CLERK') t
join (select e.deptno,count(e.ename) as totalEmp from emp e group by e.deptno) f
on t.deptno=f.deptno;
+--------+------------+--------+----------+
| deptno | dname | ename | totalEmp |
+--------+------------+--------+----------+
| 20 | RESEARCH | SMITH | 5 |
| 20 | RESEARCH | ADAMS | 5 |
| 30 | SALES | JAMES | 6 |
| 10 | ACCOUNTING | MILLER | 3 |
+--------+------------+--------+----------+19 列出最低薪水大于1500的各种工作和此工作的全部雇员人数
# （1）每种工作岗位的最低薪水
select e.job,min(sal) minSal
from emp e
group by e.job;
+-----------+---------+
| job | minSal |
+-----------+---------+
| CLERK | 800.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
# （2）每个工作岗位最低薪水大于1500的工作人数
select e.job,min(sal) minSal,count(e.ename)
from emp e
group by e.job
having minSal>1500;
+-----------+---------+----------------+
| job | minSal | count(e.ename) |
+-----------+---------+----------------+
| MANAGER | 2450.00 | 3 |
| ANALYST | 3000.00 | 2 |
| PRESIDENT | 5000.00 | 1 |
+-----------+---------+----------------+20 列出在部门“SALES”<销售部>工作的员工姓名
（假定不知道销售部门的部门编号）
# （1）部门名为"SALES"的部门编号
select deptno from dept where dname="SALES";
+--------+
| deptno |
+--------+
| 30 |
+--------+
# （2）查询员工姓名
select ename from emp e where deptno=(select deptno from dept where dname="SALES");
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+21 列出薪资高于公司平均薪资的所有员工，所在部门、上级领导、雇员的工资等级
# （1）公司平均薪资
select avg(sal) avgsal from emp;
+-------------+
| avgsal |
+-------------+
| 2073.214286 |
+-------------+
# （2）第一个连接：部门名字
# 第二个左连接：emp表看成两张表，列出员工对应的上级领导
# 第三个连接：工资等级
select d.dname,e.ename,b.ename as leadername,grade
from emp e join dept d on e.deptno=d.deptno
left join emp b on e.mgr=b.empno
join salgrade s on e.sal between s.losal and s.hisal
where e.sal>(select avg(sal) avgsal from emp);
+------------+-------+------------+-------+
| dname | ename | leadername | grade |
+------------+-------+------------+-------+
| RESEARCH | FORD | JONES | 4 |
| RESEARCH | SCOTT | JONES | 4 |
| ACCOUNTING | CLARK | KING | 4 |
| SALES | BLAKE | KING | 4 |
| RESEARCH | JONES | KING | 4 |
| ACCOUNTING | KING | NULL | 5 |
+------------+-------+------------+-------+22 列出与(ename)“SCOTT”从事相同工作的所有员工和部门名称
# （1）查询出“SCOTT”的工作岗位
select job from emp where ename="SCOTT";
+---------+
| job |
+---------+
| ANALYST |
+---------+
# （2）
select d.dname,e.*
from emp e
join dept d
on e.deptno=d.deptno
where e.job=(select job from emp where ename="SCOTT");
+----------+-------+-------+---------+------+------------+---------+------+--------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+----------+-------+-------+---------+------+------------+---------+------+--------+
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+----------+-------+-------+---------+------+------------+---------+------+--------+23 列出薪资等于部门30中工作的员工的薪资的其他员工的姓名和薪资
# （1）部门30中工作的员工的薪资
select distinct sal from emp where deptno=30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
# （2）薪资与以上的相同，排除部门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)24 列出薪资高于在部门30中工作的员工的薪资的员工的姓名和薪资、部门名称
# （1）部门30中的员工最高工资
select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
# （2）高于最高工资的其他员工
select deptno,ename,sal from emp
where sal>(select max(sal) from emp where deptno=30)
and deptno!=30;
+--------+-------+---------+
| deptno | ename | sal |
+--------+-------+---------+
| 20 | JONES | 2975.00 |
| 20 | SCOTT | 3000.00 |
| 10 | KING | 5000.00 |
| 20 | FORD | 3000.00 |
+--------+-------+---------+
# （3）连接dept表
select d.dname,t.ename,t.sal
from (select deptno,ename,sal from emp
where sal>(select max(sal) from emp where deptno=30)
and deptno!=30) t
join dept d
on t.deptno=d.deptno;
+------------+-------+---------+
| dname | ename | sal |
+------------+-------+---------+
| RESEARCH | JONES | 2975.00 |
| RESEARCH | SCOTT | 3000.00 |
| ACCOUNTING | KING | 5000.00 |
| RESEARCH | FORD | 3000.00 |
+------------+-------+---------+25 列出在每个部门工作的员工数量、平均工资和平均服务期限
# （1）当前日期、服务期限
select ename,(to_days(now())-to_days(hiredate))/365 as serveyear from emp;
+--------+-----------+
| ename | serveyear |
+--------+-----------+
| SMITH | 42.3589 |
| ALLEN | 42.1808 |
| WARD | 42.1753 |
| JONES | 42.0685 |
| MARTIN | 41.5781 |
| BLAKE | 41.9890 |
| CLARK | 41.8822 |
| SCOTT | 36.0192 |
| KING | 41.4411 |
| TURNER | 41.6329 |
| ADAMS | 35.9260 |
| JAMES | 41.3973 |
| FORD | 41.3973 |
| MILLER | 41.2575 |
+--------+-----------+
# （2）每个部门的员工数量、平均工资、平均服务期限
select deptno,count(ename),avg(sal) avgsal,
avg((to_days(now())-to_days(hiredate))/365) avgserveyear
from emp group by deptno;
+--------+--------------+-------------+--------------+
| deptno | count(ename) | avgsal | avgserveyear |
+--------+--------------+-------------+--------------+
| 20 | 5 | 2175.000000 | 39.55398000 |
| 30 | 6 | 1566.666667 | 41.82556667 |
| 10 | 3 | 2916.666667 | 41.52693333 |
+--------+--------------+-------------+--------------+26 列出所有员工的姓名、部门名称和工资
# 右连接：员工的姓名、部门名称和工资
select d.dname,e.ename,e.sal
from emp e
right join dept d
on e.deptno=d.deptno;
+------------+--------+---------+
| dname | ename | sal |
+------------+--------+---------+
| ACCOUNTING | MILLER | 1300.00 |
| ACCOUNTING | KING | 5000.00 |
| ACCOUNTING | CLARK | 2450.00 |
| RESEARCH | FORD | 3000.00 |
| RESEARCH | ADAMS | 1100.00 |
| RESEARCH | SCOTT | 3000.00 |
| RESEARCH | JONES | 2975.00 |
| RESEARCH | SMITH | 800.00 |
| SALES | JAMES | 950.00 |
| SALES | TURNER | 1500.00 |
| SALES | BLAKE | 2850.00 |
| SALES | MARTIN | 1250.00 |
| SALES | WARD | 1250.00 |
| SALES | ALLEN | 1600.00 |
| OPERATIONS | NULL | NULL |
+------------+--------+---------+27 列出所有部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(ename) totalename
from emp e
right join dept d
on d.deptno=e.deptno
group by deptno;
+--------+------------+----------+------------+
| deptno | dname | loc | totalename |
+--------+------------+----------+------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+------------+28 列出各种工作的最低工资及从事此工作的雇员姓名
# （1）每个工作岗位的最低工资
select e.job,min(e.sal) minsal
from emp e
group by e.job;
+-----------+---------+
| job | minsal |
+-----------+---------+
| CLERK | 800.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
# （2）匹配每个员工姓名
select e.ename,e.job,e.sal
from emp e
join (select e.job,min(e.sal) minsal from emp e group by e.job) t
on e.job=t.job
where e.sal=t.minsal;
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| 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 |
+--------+-----------+---------+29 列出各个部门MANAGER的最低薪资
# （1） 匹配工作岗位中的MANAGER
select e.deptno,min(sal) minsal
from emp e
where e.job="MANAGER"
group by e.deptno;
+--------+---------+
| deptno | minsal |
+--------+---------+
| 20 | 2975.00 |
| 30 | 2850.00 |
| 10 | 2450.00 |
+--------+---------+30 列出所有员工的年工资，按年薪从低到高排序
select ename,(sal+ifnull(comm,0))*12 as yearsal
from emp order by yearsal asc;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| ADAMS | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD | 21000.00 |
| ALLEN | 22800.00 |
| CLARK | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| KING | 60000.00 |
+--------+----------+31 列出员工领导的薪资超过3000的员工名称和领导名称
select e.ename,b.ename as leadername
from emp e
join emp b
on e.mgr=b.empno
where b.sal>3000;
+-------+------------+
| ename | leadername |
+-------+------------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------------+32 列出部门名称中带“S”字符的部门员工的工资合计、部门人数
select d.dname,sum(e.sal) sumavg,count(e.ename) totalemp
from emp e
join dept d
on e.deptno=d.deptno
where d.dname like '%s%'
group by d.dname;
+----------+----------+----------+
| dname | sumavg | totalemp |
+----------+----------+----------+
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
+----------+----------+----------+33 给任职日期超过30年的员工加薪10%
# 复制一张表
create table emp_bark as 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
# 更新表
update emp_bark set sal =sal*1.1 where(to_days(now())-to_days(hiredate))/365>30;
Query OK, 14 rows affected (0.13 sec)
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+题目来源： 动力节点_MySQL_124_34道经典SQL试题_第1题_哔哩哔哩_bilibili
