1、取得每个部门最高薪水的人员名称
select e.ename,t.maxsal,e.deptno
from emp e
join (select ename,max(sal) as maxsal,deptno from emp group by deptno) t
on e.deptno=t.deptno and t.maxsal=e.sal;
+-------+---------+--------+
| ename | maxsal | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
2、哪些人的薪水在部门的平均薪水之上
平均薪资
select e.ename,e.deptno,e.sal,t.avgsal
from emp e
join (select avg(sal) as avgsal,ename,deptno from emp group by deptno) t
on e.sal > t.avgsal and e.deptno=t.deptno
order by e.deptno,e.sal;
+-------+--------+---------+-------------+
| ename | deptno | sal | avgsal |
+-------+--------+---------+-------------+
| KING | 10 | 5000.00 | 2916.666667 |
| JONES | 20 | 2975.00 | 2175.000000 |
| SCOTT | 20 | 3000.00 | 2175.000000 |
| FORD | 20 | 3000.00 | 2175.000000 |
| ALLEN | 30 | 1600.00 | 1566.666667 |
| BLAKE | 30 | 2850.00 | 1566.666667 |
+-------+--------+---------+-------------+
3、取得部门中(所有人的)平均的薪水等级
1)每个部分平均薪水的等级
select t.*,s.grade
from (select avg(sal) as avgsal,deptno from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
+-------------+--------+-------+
| avgsal | deptno | grade |
+-------------+--------+-------+
| 2916.666667 | 10 | 4 |
| 2175.000000 | 20 | 4 |
| 1566.666667 | 30 | 3 |
+-------------+--------+-------+
3 rows in set (0.02 sec)
2)每个部门薪水等级的平均值
select t.deptno,avg(t.grade)
from (select e.deptno,e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
order by e.deptno) t
group by deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
4、不准用组函数(Max ),取得最高薪水
select ename,sal
from emp
order by sal desc
limit 0,1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
5、取得平均薪水最高的部门的部门编号
平均薪水
select avg(sal) as avgs,deptno from emp group by deptno order by avgs desc limit 0,1;
+-------------+--------+
| avgs | deptno |
+-------------+--------+
| 2916.666667 | 10 |
+-------------+--------+
1 row in set (0.00 sec)
6、取得平均薪水最高的部门的部门名称
select t.deptno,d.dname,t.avgs
from (select avg(sal) as avgs,deptno from emp group by deptno order by avgs desc limit 0,1) t
join dept d
on d.deptno = t.deptno;
+--------+------------+-------------+
| deptno | dname | avgs |
+--------+------------+-------------+
| 10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+
1 row in set (0.00 sec)
7、求平均薪水的等级最低的部门的部门名称
select t.deptno,d.dname,t.avgs
from (select avg(sal) as avgs,deptno from emp group by deptno order by avgs limit 0,1) t
join dept d
on d.deptno = t.deptno;
+--------+-------+-------------+
| deptno | dname | avgs |
+--------+-------+-------------+
| 30 | SALES | 1566.666667 |
+--------+-------+-------------+
1 row in set (0.00 sec)
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
比“普通员工的最高薪水”还要高的一定是领导!
没毛病!!!!
答:不知所云,不做了。
9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
10、取得薪水最高的第六到第十名员工
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 名员工
日期也可以降序,升序。
select ename,hiredate from emp order by hiredate desc limit 0,5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
12、取得每个薪水等级有多少员工
分组count
select t.grade as 薪资等级 ,count(t.grade) as 对应员工数量
from (select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal) t
group by t.grade;
+--------------+--------------------+
| 薪资等级 | 对应员工数量 |
+--------------+--------------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+--------------+--------------------+
5 rows in set (0.00 sec)
13、面试题:
有 3 个表 S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1,找出没选过“黎明”老师的所有学生姓名。
2,列出 2 门以上(含2 门)不及格学生姓名及平均成绩。
3,即学过 1 号课程又学过 2 号课所有学生的姓名。
14、列出所有员工及领导的姓名
select e.deptno as 部门,e.ename as 员工姓名,a.ename as 领导姓名
from emp e
left join emp a
on e.mgr=a.empno
order by e.deptno;
+--------+--------------+--------------+
| 部门 | 员工姓名 | 领导姓名 |
+--------+--------------+--------------+
| 10 | CLARK | KING |
| 10 | KING | NULL |
| 10 | MILLER | CLARK |
| 20 | SMITH | FORD |
| 20 | JONES | KING |
| 20 | SCOTT | JONES |
| 20 | ADAMS | SCOTT |
| 20 | FORD | JONES |
| 30 | ALLEN | BLAKE |
| 30 | WARD | BLAKE |
| 30 | MARTIN | BLAKE |
| 30 | BLAKE | KING |
| 30 | TURNER | BLAKE |
| 30 | JAMES | BLAKE |
+--------+--------------+--------------+
14 rows in set (0.00 sec)
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select m.ename,m.hiredate,m.dn,n.ename,n.hiredate as mgrhiredate
from (select e.*,d.dname as dn from emp e join dept d on e.deptno = d.deptno) m
join (select e.*,d.dname as dn from emp e join dept d on e.deptno = d.deptno) n
on m.mgr = n.empno and m.hiredate < n.hiredate;
+-------+------------+------------+-------+-------------+
| ENAME | HIREDATE | dn | ENAME | mgrhiredate |
+-------+------------+------------+-------+-------------+
| SMITH | 1980-12-17 | RESEARCH | FORD | 1981-12-03 |
| ALLEN | 1981-02-20 | SALES | BLAKE | 1981-05-01 |
| WARD | 1981-02-22 | SALES | BLAKE | 1981-05-01 |
| JONES | 1981-04-02 | RESEARCH | KING | 1981-11-17 |
| BLAKE | 1981-05-01 | SALES | KING | 1981-11-17 |
| CLARK | 1981-06-09 | ACCOUNTING | KING | 1981-11-17 |
+-------+------------+------------+-------+-------------+
6 rows in set (0.01 sec)
16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
select d.dname,e.*
from dept d
left join emp e
on d.deptno = e.deptno
order by d.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.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 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)
17、列出至少有 5 个员工的所有部门
按照部门编号分组,计数,筛选出 >= 5
1)
select t.deptno,t.c
from ( select count(ename) as c ,deptno from emp group by deptno) t
where t.c >= 5;
+--------+---+
| deptno | c |
+--------+---+
| 20 | 5 |
| 30 | 6 |
+--------+---+
2 rows in set (0.00 sec)
2)
select deptno, count(deptno) as count
from emp
group by deptno
having count(ename) >= 5;
+--------+-------+
| deptno | count |
+--------+-------+
| 20 | 5 |
| 30 | 6 |
+--------+-------+
2 rows in set (0.00 sec)
18、列出薪金比"SMITH" 多的所有员工信息
select ename,sal
from emp
where 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 |
+--------+---------+
13 rows in set (0.01 sec)
19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
select e.ename,d.dname,d.deptno
from emp e
join dept d
on e.deptno=d.deptno and e.job = 'CLERK';
+--------+------------+--------+
| ename | dname | deptno |
+--------+------------+--------+
| SMITH | RESEARCH | 20 |
| ADAMS | RESEARCH | 20 |
| JAMES | SALES | 30 |
| MILLER | ACCOUNTING | 10 |
+--------+------------+--------+
4 rows in set (0.00 sec)
select ename,deptno,count(ename) as c from emp group by deptno;
+--------+--------------+
| deptno | c |
+--------+--------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+--------------+
3 rows in set (0.00 sec)
select t1.*,t2.dcount
from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno and e.job ='CLERK') t1
left join (select deptno,count(*) as dcount from emp group by deptno) t2
on t1.deptno = t2.deptno;
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值。
select job,sal,count(*)
from emp
group by job
having min(sal) > 1500;
+-----------+---------+----------+
| job | sal | count(*) |
+-----------+---------+----------+
| ANALYST | 3000.00 | 2 |
| MANAGER | 2975.00 | 3 |
| PRESIDENT | 5000.00 | 1 |
+-----------+---------+----------+
3 rows in set (0.00 sec)
21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
select ename,job,deptno
from emp
where job like '%SALES%';
select job,ename
from emp
where deptno = (select distinct deptno from emp where job like '%SALES%');
+----------+--------+
| job | ename |
+----------+--------+
| SALESMAN | ALLEN |
| SALESMAN | WARD |
| SALESMAN | MARTIN |
| MANAGER | BLAKE |
| SALESMAN | TURNER |
| CLERK | JAMES |
+----------+--------+
6 rows in set (0.00 sec)
22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
select avg(sal) from emp;,s.grade
select e1.ename,d.dname,e2.ename as leader,s.grade
from emp e1
join dept d
on e1.deptno = d.deptno
left join emp e2
on e1.MGR = e2.empno
join salgrade s
on e1.sal between s.losal and s.hisal
where e1.sal > (select avg(sal) from emp);
+-------+------------+--------+-------+
| ename | dname | leader | grade |
+-------+------------+--------+-------+
| JONES | RESEARCH | KING | 4 |
| BLAKE | SALES | KING | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| KING | ACCOUNTING | NULL | 5 |
| FORD | RESEARCH | JONES | 4 |
+-------+------------+--------+-------+
6 rows in set (0.00 sec)
23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
select job from emp where ename = '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');
+-------+----------+
| ename | dname |
+-------+----------+
| SCOTT | RESEARCH |
| FORD | RESEARCH |
+-------+----------+
2 rows in set (0.00 sec)
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.????这题?????
1)select ename,sal from emp where deptno = '30';
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| TURNER | 1500.00 |
| JAMES | 950.00 |
+--------+---------+
6 rows in set (0.00 sec)
2)select ename,sal from emp where deptno <> '30';
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JONES | 2975.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| ADAMS | 1100.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
select max(sal) from emp group by deptno having deptno = '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 group by deptno having deptno = '30');
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
4 rows in set (0.00 sec)
26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
没有员工的部门,部门人数是0
select deptno,count(*) as d_num from emp group by deptno;
select deptno,avg(sal) d_avg_sal from emp group by deptno;
select deptno, avg(2022-year(hiredate)) as d_avg_hire from emp group by deptno;
服务期限
timestampdiff(year,hiredate,now())
select d.*,t1.d_num,t2.d_avg_sal,t3.d_avg_hire
from dept d
left join (select deptno,count(*) as d_num from emp group by deptno) t1
on d.deptno = t1.deptno
left join (select deptno,avg(sal) d_avg_sal from emp group by deptno) t2
on d.deptno = t2.deptno
left join (select deptno, avg(2022-year(hiredate)) as d_avg_hire from emp group by deptno) t3
on d.deptno = t3.deptno
+--------+------------+----------+-------+-------------+------------+
| DEPTNO | DNAME | LOC | d_num | d_avg_sal | d_avg_hire |
+--------+------------+----------+-------+-------------+------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 2916.666667 | 40.6667 |
| 20 | RESEARCH | DALLAS | 5 | 2175.000000 | 38.8000 |
| 30 | SALES | CHICAGO | 6 | 1566.666667 | 41.0000 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL |
+--------+------------+----------+-------+-------------+------------+
4 rows in set (0.00 sec)
27、 列出所有员工的姓名、部门名称和工资。
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 |
+--------+------------+---------+
14 rows in set (0.00 sec)
28、列出所有部门的详细信息和人数
select d.*,count(e.ename)
from dept d
left join emp e
on d.deptno = e.deptno
group by e.deptno;
+--------+------------+----------+----------------+
| DEPTNO | DNAME | LOC | count(e.ename) |
+--------+------------+----------+----------------+
| 40 | OPERATIONS | BOSTON | 0 |
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
+--------+------------+----------+----------------+
4 rows in set (0.00 sec)
29、列出各种工作的最低工资及从事此工作的雇员姓名
select ename,job, min(sal) from emp group by job;
+-------+-----------+----------+
| ename | job | min(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST | 3000.00 |
| SMITH | CLERK | 800.00 |
| JONES | MANAGER | 2450.00 |
| KING | PRESIDENT | 5000.00 |
| ALLEN | SALESMAN | 1250.00 |
+-------+-----------+----------+
5 rows in set (0.01 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 annsal
from emp
order by annsal;
+--------+----------+
| ename | annsal |
+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.00 sec)
32、求出员工领导的薪水超过3000的员工名称与领导
select e1.ename as 员工,e1.sal as 员工工资,e2.ename as 领导,e2.sal as 领导工资
from emp e1
join emp e2
on e1.mgr = e2.empno
and e2.sal > '3000';
+--------+--------------+--------+--------------+
| 员工 | 员工工资 | 领导 | 领导工资 |
+--------+--------------+--------+--------------+
| 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 t.dname,count(t.ename),sum(t.sal)
from (select d.dname,e.ename,e.sal
from emp e
join dept d
on e.deptno = d.deptno
where d.dname like '%S%') t
group by t.dname;
+----------+----------------+------------+
| dname | count(t.ename) | sum(t.sal) |
+----------+----------------+------------+
| RESEARCH | 5 | 10875.00 |
| SALES | 6 | 9400.00 |
+----------+----------------+------------+
2 rows in set (0.00 sec)
34、给任职日期超过 30 年的员工加薪 10%.
update emp set sal = sal *1.1 where timestampdiff(YEAR,hiredate,now()) > 30;