MySQL实战-基于emp,dept,salgrade表的34道题
- 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%.
--emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | NULL | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | NULL | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | NULL | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | NULL | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | NULL | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-09-06 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | NULL | 3000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-08-09 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | NULL | 1250.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7566 | 1981-03-12 | 1250.00 | NULL | 30 |
| 7902 | FROD | ANALYST | 7782 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7698 | NULL | 1300.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | NULL | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
--dept表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
--salgrade表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
1、取得每个部门最高薪水的人员名称
--思路：
1、第一步先找出每个部门最大工资数
2、将1中的表当成临时表t,与emp e表进行连接。连接条件是：t.deptno=e.deptno and t.maxsal=e.sal
SELECT e.ename,e.sal,e.deptno 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 | sal | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| FROD | 3000.00 | 20 |
| KING | 5000.00 | 10 |
+-------+---------+--------+
4 rows in set (0.00 sec)
2、哪些人的薪水在部门的平均薪水之上
--思路
1、找出部门平均薪水（按照部门编号分组求平均值）
2、将查询结果当成临时表t,t表与emp e进行表连接 条件：t.deptno=e.deptno and e.sal>t.avgsal
SELECT e.ename,e.sal,t.* 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 |
+-------+---------+--------+-------------+
| JONES | 2975.00 | 20 | 2205.000000 |
| BLAKE | 2850.00 | 30 | 1616.666667 |
| SCOTT | 3000.00 | 20 | 2205.000000 |
| FROD | 3000.00 | 20 | 2205.000000 |
| KING | 5000.00 | 10 | 2916.666667 |
+-------+---------+--------+-------------+
5 rows in set (0.00 sec)
3、取得部门中（所有人的）平均的薪水等级
--将所有人的薪水等级求出来再平均
--将emp e和salgrade s进行表连接，连接条件是e.sal BETWEEN s.losal AND s.hisal。
SELECT e.deptno,avg(s.grade) FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 3.0000 |
| 30 | 2.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
变形：取得部门中（所有人的）平均的 薪水等级。（没有的）
--先求各部门平均薪水，再求对应的等级
--从emp e中取出deptno,avg（sal）并将其看作临时表t
--将t和salgrade s进行表连接，连接条件是t.avgsal BETWEEN s.losal AND s.hisal
SELECT t.*,s.grade FROM salgrade s JOIN
(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) t
ON t.avgsal BETWEEN s.losal AND s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 10 | 2916.666667 | 4 |
| 20 | 2205.000000 | 4 |
| 30 | 1616.666667 | 3 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）
--方法一：将emp表按照sal降序排列，找到第一个工资
SELECT sal FROM emp ORDER BY sal DESC LIMIT 0,1;
--方法二：自连接
select sal from emp where sal not in
(select a.sal from emp a join emp b on a.sal<b.sal); --后面括号里面表格是找出a表中所有比b表中小的工资。
要找最大的，就工资不在这个表的查找范围内即可。
--方法三：用max聚合函数
SELECT max(sal) FROM emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.00 sec)
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
--方法一：emp按照deptno分组并按照平均工资降序排列找出第一个。
SELECT t.deptno FROM
(SELECT avg(sal) avgsal,deptno FROM emp GROUP BY deptno
ORDER BY avgsal DESC LIMIT 0,1) t;
+--------+
| deptno |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
--方法二：
第一步：取得平均薪水最大的值
select avg(sal) avgsal from emp group by deptno
order by avgsal desc limit 1;
第二步：从emp中找出avg(sal)=最大的平均薪水
SELECT deptno FROM emp GROUP BY deptno
having avg(sal)=(select avg(sal) avgsal from emp group by deptno
order by avgsal desc limit 1);
6、取得平均薪水最高的部门的部门名称
--方法一：
把emp表中deptno和avg(sal)查询出来看作临时表t;与dept d表进行表连接，条件是d.deptno=t.deptno；最后将部门名称取出即可。
SELECT d.dname FROM dept d JOIN
(select deptno,avg(sal) avgsal from emp group by deptno
order by avgsal desc limit 1) t
ON d.deptno=t.deptno;
+------------+
| dname |
+------------+
| ACCOUNTING |
+------------+
1 row in set (0.00 sec)
--方法二：having()筛选函数
select deptno,avg(sal) as avgsal from emp group by deptno
having avg(sal)=(select max(t.avgsal) avgsal
from(select avg(sal) avgsal from emp group by deptno) t);
7、求平均薪水的等级最低的部门的部门名称
--先找出最低工资的deptno，然后进行表连接，与5、6题目类似。
SELECT d.dname FROM dept d JOIN
(select deptno,avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) t
ON d.deptno=t.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+
1 row in set (0.00 sec)
8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
--先找出普通员工的最高薪水，取出比最高薪水高的领导人姓名和工资。
SELECT ename,sal FROM emp
WHERE sal>(select max(sal) 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 |
| FROD | 3000.00 |
| KING | 5000.00 |
+-------+---------+
6 rows in set (0.00 sec)
9、取得薪水最高的前五名员工
--按照工资降序排列，取前面5个员工。
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FROD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
10、取得薪水最高的第六到第十名员工
--与第9题一样，注意limit函数的使用，第一个参数是起始位置，第二个参数是选取长度。
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 |
+--------+---------+
5 rows in set (0.00 sec)
11、取得最后入职的5名员工
--与9、10题类似
SELECT ename,hiredate FROM emp ORDER BY hiredate DESC LIMIT 5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| CLARK | 1981-09-06 |
| TURNER | 1981-08-09 |
| JAMES | 1981-03-12 |
| FROD | 1981-03-12 |
| JONES | 1981-02-04 |
+--------+------------+
5 rows in set (0.00 sec)
12、取得每个薪水等级有多少员工
--先找出每个员工工资对应的工资等级，作为临时表t;从t中再查找想要的信息。
SELECT t.grade,count(*) FROM
(SELECT s.grade FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal) t
GROUP BY grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 1 |
| 2 | 5 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+----------+
5 rows in set (0.00 sec)
SELECT s.grade,count(*) FROM salgrade s JOIN emp e
ON e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade;
13、面试题
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
--第一步：从cn表找出‘黎明’老师的授课编号
SELECT cno FROM c WHERE cteacher='黎明';
--第二步：通过sc表查询cno=上面结果的sno,这些sno都是选择黎明老师课程的学号
SELECT sno FROM sc WHERE cno=(SELECT cno FROM c WHERE cteacher='黎明');
--第三步：在s表中查询sno not in 上面结果的数据
SELECT sname FROM s WHERE sno not in
(SELECT sno FROM sc WHERE cno=(SELECT cno FROM c WHERE cteacher='黎明'));
+-------+
| sname |
+-------+
| 学生3 |
| 学生4 |
+-------+
2 rows in set (0.00 sec)
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
--第一步：列出2门以上（含2门）不及格学生姓名t1
select sc.sno,s.sname from sc join s on sc.sno=s.sno
where sc.scgrade<60
group by sc.sno,s.sname
having count(*)>=2;
--第二步：找出每一个学生平均成绩t2
select sno,avg(scgrade) as avgscgrade from sc group by sno;
--第三步：将第一步和第二步联合
SELECT t1.sname,t2.avgscgrade FROM
(select sc.sno,s.sname from sc join s
on sc.sno=s.sno where sc.scgrade<60
group by sc.sno,s.sname having count(*)>=2) t1
JOIN (select sno,avg(scgrade) as avgscgrade from sc group by sno) t2
ON t1.sno=t2.sno;
+-------+------------+
| sname | avgscgrade |
+-------+------------+
| 学生1 | 46 |
+-------+------------+
1 row in set (0.00 sec)
3，即学过1号课程又学过2号课所有学生的姓名。
SELECT s.sname FROM s JOIN sc ON s.sno=sc.sno
WHERE sc.cno=1 AND sc.sno in(select sno from sc where cno=2);
+-------+
| sname |
+-------+
| 学生1 |
| 学生2 |
+-------+
2 rows in set (0.00 sec)
思考：后面那个条件能写成WHERE sc.cno=1 OR sc.cno=2吗？
SELECT s.sname,sc.cno FROM s JOIN sc ON s.sno=sc.sno
WHERE sc.cno=1 OR sc.cno=2;
+-------+------+
| sname | cno |
+-------+------+
| 学生1 | 1 |
| 学生1 | 2 |
| 学生2 | 1 |
| 学生2 | 2 |
| 学生3 | 1 |
+-------+------+
5 rows in set (0.00 sec)
能写成WHERE sc.cno in (1,2)吗？
SELECT s.sname,sc.cno FROM s JOIN sc ON s.sno=sc.sno
WHERE sc.cno in (1,2);
+-------+------+
| sname | cno |
+-------+------+
| 学生1 | 1 |
| 学生1 | 2 |
| 学生2 | 1 |
| 学生2 | 2 |
| 学生3 | 1 |
+-------+------+
5 rows in set (0.00 sec)
14、列出所有员工及领导的姓名
--表的自连接。emp e1<员工表> emp e2<领导表>，不是所有的员工都有领导，最大的领导没有领导，所以左外连接，连接条件是e1.mgr=e2.empno。
SELECT e1.ename empname,e2.ename leadername FROM emp e1 LEFT JOIN emp e2
ON e1.mgr=e2.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| SCOTT | JONES |
| JAMES | JONES |
| ALLEN | BLAKE |
| WARD | BLAKE |
| MARTIN | BLAKE |
| TURNER | BLAKE |
| MILLER | BLAKE |
| FROD | CLARK |
| ADAMS | SCOTT |
| SMITH | FROD |
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
| KING | NULL |
+---------+------------+
14 rows in set (0.00 sec)
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
--和上面题目思路一样，表的自连接
emp a<员工表> a.hiredate
emp b<领导表> b.hiredate
SELECT
a.empno '员工编号',a.ename '员工姓名',a.hiredate '员工入职日期',
b.empno '领导编号',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;
+----------+----------+--------------+----------+----------+--------------+----------+
| 员工编号 | 员工姓名 | 员工入职日期 | 领导编号 | 领导姓名 | 领导入职日期 | dname |
+----------+----------+--------------+----------+----------+--------------+----------+
| 7902 | FROD | 1981-03-12 | 7782 | CLARK | 1981-09-06 | RESEARCH |
+----------+----------+--------------+----------+----------+--------------+----------+
1 row in set (0.00 sec)
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
--由题目看出是外连接
SELECT d.dname,e.* FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| RESEARCH | 7369 | SMITH | CLERK | 7902 | NULL | 800.00 | NULL | 20 |
| SALES | 7499 | ALLEN | SALESMAN | 7698 | NULL | 1600.00 | 300.00 | 30 |
| SALES | 7521 | WARD | SALESMAN | 7698 | NULL | 1250.00 | 500.00 | 30 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | NULL | 20 |
| SALES | 7654 | MARTIN | SALESMAN | 7698 | NULL | 1250.00 | 1400.00 | 30 |
| SALES | 7698 | BLAKE | MANAGER | 7839 | NULL | 2850.00 | NULL | 30 |
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-09-06 | 2450.00 | NULL | 10 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | NULL | 3000.00 | NULL | 20 |
| SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-08-09 | 1500.00 | 0.00 | 30 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | NULL | 1250.00 | NULL | 20 |
| SALES | 7900 | JAMES | CLERK | 7566 | 1981-03-12 | 1250.00 | NULL | 30 |
| RESEARCH | 7902 | FROD | ANALYST | 7782 | 1981-03-12 | 3000.00 | NULL | 20 |
| ACCOUNTING | 7934 | MILLER | CLERK | 7698 | NULL | 1300.00 | NULL | 10 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | NULL | 5000.00 | NULL | 10 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)
17、列出至少有5个员工的所有部门
SELECT d.dname,count(e.ename) FROM emp e JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.dname
HAVING count(e.ename)>=5;
+----------+----------------+
| dname | count(e.ename) |
+----------+----------------+
| RESEARCH | 5 |
| SALES | 6 |
+----------+----------------+
2 rows in set (0.00 sec)
18、列出薪金比"SMITH"多的所有员工信息
--先找出smith的工资，再找出比他高的员工
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | NULL | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | NULL | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | NULL | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | NULL | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-09-06 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | NULL | 3000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-08-09 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | NULL | 1250.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7566 | 1981-03-12 | 1250.00 | NULL | 30 |
| 7902 | FROD | ANALYST | 7782 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7698 | NULL | 1300.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | NULL | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
第一步：先列出job='CLERK'的姓名和所在部门
select e.ename,d.dname from dept d join emp e
on e.deptno=d.deptno
where job='CLERK';
第二步：查找出各个部门总人数
select deptno,count(*) as totalEmp from emp e group by deptno;
第三步：将上面两个步骤合并
select e.ename,d.dname,t.totalEmp from dept d join emp e
on e.deptno=d.deptno
join (select deptno,count(*) as totalEmp from emp e group by deptno) t
on d.deptno=t.deptno
where job='CLERK';
+--------+------------+----------+
| ename | dname | totalEmp |
+--------+------------+----------+
| MILLER | ACCOUNTING | 3 |
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
+--------+------------+----------+
4 rows in set (0.00 sec)
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select min(sal),count(*),job from emp group by job having min(sal)>1500;
+----------+----------+-----------+
| min(sal) | count(*) | job |
+----------+----------+-----------+
| 3000.00 | 2 | ANALYST |
| 2450.00 | 3 | MANAGER |
| 5000.00 | 1 | PRESIDENT |
+----------+----------+-----------+
3 rows in set (0.00 sec)
21、列出在部门"SALES"<销售部>工作的员工的姓名。
SELECT ename FROM emp WHERE deptno=(select deptno from dept where dname='SALES');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.07 sec)
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
emp a <员工表>
emp b <领导表>
dept d <部门表>
salgrade s <工资等级表>
select a.ename empname,d.dname,b.ename leadername,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);
+---------+------------+------------+-------+
| empname | dname | leadername | grade |
+---------+------------+------------+-------+
| SCOTT | RESEARCH | JONES | 4 |
| FROD | RESEARCH | CLARK | 4 |
| JONES | RESEARCH | KING | 4 |
| BLAKE | SALES | KING | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| KING | ACCOUNTING | NULL | 5 |
+---------+------------+------------+-------+
6 rows in set (0.00 sec)
23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
--记得把SCOTT本身排除掉
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 |
+-------+----------+
| FROD | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
SELECT ename,sal FROM emp WHERE sal in
(select distinct sal from emp where deptno=30) AND deptno<>30;
+-------+---------+
| ename | sal |
+-------+---------+
| ADAMS | 1250.00 |
+-------+---------+
1 row in set (0.00 sec)
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
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);
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FROD | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
+-------+---------+------------+
4 rows in set (0.00 sec)
26、列出在每个部门工作的员工数量,平均工资和平均服务期限。
select d.deptno,count(e.ename) totalEmp,ifnull(avg(e.sal),0) avgsal,ifnull(avg(每一个员工的服务期限),0) as avgtime
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno;
每一个员工的服务期限（to_days()函数）
select (to_days(now())-to_days(hiredate))/365 from emp;
27、列出所有员工的姓名、部门名称和工资。(简单)
SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d
ON e.deptno=d.deptno;
28、列出所有部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(e.ename) from emp e
right join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc;
+--------+------------+----------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+------------+----------+----------------+
| 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 * FROM emp WHERE sal in (SELECT min(sal) FROM emp GROUP BY job);
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 |
| FROD | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)
30、列出各个部门的“MANAGER(领导)”的最低薪金
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 yearsal from emp order by yearsal * 12;
32、求出员工领导的薪水超过3000的员工名称与领导名称
SELECT e1.ename empname,e1.sal,e2.ename leadername,e2.sal FROM emp e1 JOIN emp e2
ON e1.mgr=e2.empno
where e2.sal>3000;
+---------+---------+------------+---------+
| empname | sal | leadername | sal |
+---------+---------+------------+---------+
| JONES | 2975.00 | KING | 5000.00 |
| BLAKE | 2850.00 | KING | 5000.00 |
| CLARK | 2450.00 | KING | 5000.00 |
+---------+---------+------------+---------+
3 rows in set (0.00 sec)
33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.
select d.dname,sum(e.sal) as sumsal,count(e.ename) as totalEmp from emp e right join dept d on e.deptno=d.deptno
where d.dname like '%S%'
group by d.dname;
+------------+----------+----------+
| dname | sumsal | totalEmp |
+------------+----------+----------+
| OPERATIONS | NULL | 0 |
| RESEARCH | 11025.00 | 5 |
| SALES | 9700.00 | 6 |
+------------+----------+----------+
3 rows in set (0.03 sec)
34、给任职日期超过30年的员工加薪10%.
create table emp_bak as select * from emp;
update emp_bak set sal=sal*1.1 where 任职日期>30;
--任职日期：(to_days(now())-to_days(hiredate))/365
版权声明：本文为Guiabbey原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。