24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
步骤一：找到部门30的所有薪资
mysql> select sal from emp where deptno=30; +---------+ | sal | +---------+ | 1600.00 | | 1250.00 | | 1250.00 | | 2850.00 | | 1500.00 | | 950.00 | +---------+ 6 rows in set (0.00 sec)
步骤二：找到所有员工薪资等同于部门30的薪资的员工
select ename, sal, deptno from emp where (sal in (select sal from emp where deptno=30));
+--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | TURNER | 1500.00 | 30 | | JAMES | 950.00 | 30 | +--------+---------+--------+ 6 rows in set (0.00 sec)
步骤三：过滤掉30部门的员工
select ename, sal, deptno from emp where (sal in (select sal from emp where deptno=30) and deptno!=30);
符合条件的为0
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名称.
步骤一：找到部门30的最高薪资
mysql> select max(sal) from emp where deptno=30; +----------+ | max(sal) | +----------+ | 2850.00 | +----------+ 1 row in set (0.00 sec)
步骤二：找到所有高于部门30最高薪资的人
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);
+-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | JONES | 2975.00 | 20 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 4 rows in set (0.00 sec)
步骤三：部门加进去
select t.ename, t.sal, d.dname from (select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30)) t join dept d on t.deptno = d.deptno;
+-------+---------+------------+ | ename | sal | dname | +-------+---------+------------+ | KING | 5000.00 | ACCOUNTING | | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | +-------+---------+------------+ 4 rows in set (0.00 sec)
答案二：
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal> (select max(sal) from emp where deptno=30);
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
步骤一：找到每个人的部门
select d.dname from emp e join dept d on d.deptno=e.deptno;
步骤二：统计每个部门的人数
select d.dname, count(e.ename) from emp e right join dept d on d.deptno=e.deptno group by e.deptno;
+------------+----------------+ | dname | count(e.ename) | +------------+----------------+ | OPERATIONS | 0 | | ACCOUNTING | 3 | | RESEARCH | 5 | | SALES | 6 | +------------+----------------+ 4 rows in set (0.00 sec)
步骤三：加入平均薪资
select d.dname, count(e.ename), ifnull(round(avg(e.sal), 2), 0) from emp e right join dept d on d.deptno=e.deptno group by e.deptno;
+------------+----------------+---------------------------------+ | dname | count(e.ename) | ifnull(round(avg(e.sal), 2), 0) | +------------+----------------+---------------------------------+ | OPERATIONS | 0 | 0.00 | | ACCOUNTING | 3 | 2916.67 | | RESEARCH | 5 | 2175.00 | | SALES | 6 | 1566.67 | +------------+----------------+---------------------------------+ 4 rows in set (0.00 sec)
步骤四：加入平均服务期限
select d.dname, count(e.ename), ifnull(round(avg(e.sal), 2), 0) avg_sal, ifnull(avg(timestampdiff(YEAR, e.hiredate, now())),0) avg_year from emp e right join dept d on d.deptno=e.deptno group by e.deptno;
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 | +--------+------------+---------+ 14 rows in set (0.00 sec)
28、列出所有部门的详细信息和人数
步骤一：统计每个部门的人数
mysql> select deptno, count(*) num from emp group by deptno; +--------+-----+ | deptno | num | +--------+-----+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+-----+ 3 rows in set (0.00 sec)
步骤二：
select d.*, ifnull(t.num,0) from dept d left join (select deptno, count(*) num from emp group by deptno) t on d.deptno=t.deptno;
+--------+------------+----------+-----------------+ | DEPTNO | DNAME | LOC | ifnull(t.num,0) | +--------+------------+----------+-----------------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | | 40 | OPERATIONS | BOSTON | 0 | +--------+------------+----------+-----------------+ 4 rows in set (0.00 sec)
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 | +-----------+----------+ 5 rows in set (0.00 sec)
步骤二
select e.ename, t.* from emp e join (select job,min(sal) min_sal from emp group by job) t on e.job=t.job and e.sal=t.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 | | KING | PRESIDENT | 5000.00 | | FORD | ANALYST | 3000.00 | +--------+-----------+---------+ 7 rows in set (0.00 sec)
30、列出各个部门的 MANAGER(领导)的最低薪金
步骤一：找出工作为MANAGER的所有人
mysql> select deptno,ename,sal from emp where job='MANAGER'; +--------+-------+---------+ | deptno | ename | sal | +--------+-------+---------+ | 20 | JONES | 2975.00 | | 30 | BLAKE | 2850.00 | | 10 | CLARK | 2450.00 | +--------+-------+---------+ 3 rows in set (0.00 sec)
步骤二
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
+--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 2450.00 | | 20 | 2975.00 | | 30 | 2850.00 | +--------+----------+ 3 rows in set (0.00 sec)
31、列出所有员工的年工资,按年薪从低到高排序
select ename, (sal + ifnull(comm,0))*12 as income from emp order by income;
+--------+----------+ | ename | income | +--------+----------+ | 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 | | FORD | 36000.00 | | SCOTT | 36000.00 | | KING | 60000.00 | +--------+----------+ 14 rows in set (0.00 sec)
32、求出员工领导的薪水超过 3000 的员工名称与领导名称
步骤一：找出员工领导对应表
mysql> select a.ename, b.ename, b.sal -> from emp a -> join emp b -> on a.mgr = b.empno; +--------+-------+---------+ | ename | ename | sal | +--------+-------+---------+ | SMITH | FORD | 3000.00 | | ALLEN | BLAKE | 2850.00 | | WARD | BLAKE | 2850.00 | | JONES | KING | 5000.00 | | MARTIN | BLAKE | 2850.00 | | BLAKE | KING | 5000.00 | | CLARK | KING | 5000.00 | | SCOTT | JONES | 2975.00 | | TURNER | BLAKE | 2850.00 | | ADAMS | SCOTT | 3000.00 | | JAMES | BLAKE | 2850.00 | | FORD | JONES | 2975.00 | | MILLER | CLARK | 2450.00 | +--------+-------+---------+ 13 rows in set (0.00 sec)
步骤二：筛选>3000
select a.ename, b.ename, b.sal from emp a join emp b on a.mgr = b.empno where b.sal>3000; //加筛选
+-------+-------+---------+ | ename | ename | sal | +-------+-------+---------+ | JONES | KING | 5000.00 | | BLAKE | KING | 5000.00 | | CLARK | KING | 5000.00 | +-------+-------+---------+ 3 rows in set (0.00 sec)
33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数
步骤一：找到员工名和部门对照表
select d.dname, e.ename from emp e join dept d on e.deptno=d.deptno;
+------------+--------+ | dname | ename | +------------+--------+ | ACCOUNTING | CLARK | | ACCOUNTING | KING | | ACCOUNTING | MILLER | | RESEARCH | SMITH | | RESEARCH | JONES | | RESEARCH | SCOTT | | RESEARCH | ADAMS | | RESEARCH | FORD | | SALES | ALLEN | | SALES | WARD | | SALES | MARTIN | | SALES | BLAKE | | SALES | TURNER | | SALES | JAMES | +------------+--------+ 14 rows in set (0.00 sec)
步骤二：按照部门分组，统计每组人数
select d.dname, count(e.ename) from emp e join dept d on e.deptno=d.deptno group by d.deptno;
+------------+----------------+ | dname | count(e.ename) | +------------+----------------+ | ACCOUNTING | 3 | | RESEARCH | 5 | | SALES | 6 | +------------+----------------+ 3 rows in set (0.00 sec)
步骤三：加入名字条件
select d.dname, count(e.ename) from emp e right join dept d on e.deptno=d.deptno where d.dname like '%S%' group by d.deptno;
+------------+----------------+ | dname | count(e.ename) | +------------+----------------+ | RESEARCH | 5 | | SALES | 6 | | OPERATIONS | 0 | +------------+----------------+ 3 rows in set (0.00 sec)
步骤四：加入平均薪资
select d.dname, ifnull(sum(e.sal), 0) as sumsal, count(e.ename) from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno;
+------------+----------+----------------+ | dname | sumsal | count(e.ename) | +------------+----------+----------------+ | RESEARCH | 10875.00 | 5 | | SALES | 9400.00 | 6 | | OPERATIONS | 0.00 | 0 | +------------+----------+----------------+ 3 rows in set (0.00 sec)
34、给任职日期超过 30 年的员工加薪 10%
update emp set sal =sal*1.1 where timestampdiff(YEAR, hiredate, now())>30;
Query OK, 14 rows affected (0.00 sec) Rows matched: 14 Changed: 14 Warnings: 0
