MySql7——34道题目

1、取得每个部门最高薪水的人员名称

a.取得每个部门最高薪水
select max(sal) as ‘maxsal’,deptno from emp group by deptno;

b.emp表与 a步骤的表，进行连接
select e.ename,e.deptno,e.sal,t.maxsal
from emp e
join
(select max(sal) as ‘maxsal’,deptno from emp group by deptno) t
on
t.maxsal=e.sal;

2.哪些人的薪水在部门的平均薪水之上

a. 找出部门的平均薪水
Select avg(sal) as ‘avgsal’, deptno from emp group by deptno;

B.进行表连接？
Select e.ename,e.sal,e.deptno,t.avgsal from emp e
Join
(Select avg(sal) as ‘avgsal’, deptno from emp group by deptno) t
On
E.deptno=t.deptno
Having
E.sal>t.avgsal;

3、取得部门中（所有人的）平均的薪水等级，如下：

A.取得部门每个人的薪水等级
Select e.ename,e.deptno,s.grade from emp e
Join
Salgrade s
On
E.sal Between s.losal and s.hisal;

B.计算各部门内部的 薪水等级平均值
Select a.deptno, avg(a.grade)
From
(Select e.ename,e.deptno,s.grade from emp e
Join
Salgrade s
On
E.sal Between s.losal and s.hisal) a
Group by a.deptno;

4、不准用组函数（Max），取得最高薪水

select sal as ‘maxsal’ from emp order by sal desc limit 1;

5、取得平均薪水最高的部门的部门编号

先取得各部门平均薪水

select deptno, avg(sal) as ‘avgsal’ from emp group by deptno;

再按平均薪水排降序，并取最高的
select deptno from emp group by deptno order by avg(sal) desc limit 1;

6、取得平均薪水最高的部门的部门名称

先取得平均薪水最高的部门的部门编号

select deptno from emp group by deptno order by avg(sal) desc limit 1;

再把dept表与本表连接

select dname from dept
join
(select deptno from emp group by deptno order by avg(sal) desc limit 1) t
on
t.deptno=dept.deptno;

7、求平均薪水的等级最低的部门的部门名称

先取得平均薪水最低的部门的部门编号

select deptno from emp group by deptno order by avg(sal) asc limit 1;

再把dept表与本表连接

select dname from dept
join
(select deptno from emp group by deptno order by avg(sal) asc limit 1) t
on
t.deptno=dept.deptno;

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

1.先取得领导的员工号
select distinct mgr from emp where mgr is not null;

2.再取得普通员工的最高工资
select max(sal) as ‘maxsal’ from emp where empno not in
(select distinct mgr from emp where mgr is not null);

3.最后比较哪个领导的工资比普通员工要高
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));

9、取得薪水最高的前五名员工

select ename , sal from emp
order by sal desc
limit 5;

10、取得薪水最高的第六到第十名员工

select ename , sal from emp
order by sal desc
limit 5,5;

11、取得最后入职的5名员工

select ename,hiredate from emp order by (str_to_date(hiredate,’%Y-%m-%d’)) desc limit 5;

或：select ename,hiredate from emp order by hiredate desc limit 5;

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

1.每个员工的薪水等级

select e.ename , s.grade from
emp e
join salgrade s
on
e.sal between
s.losal and s.hisal;

2.每个等级有多少人数

select t.grade , count(t.grade) from
(
select e.ename , s.grade from
emp e
join salgrade s
on
e.sal between
s.losal and s.hisal) t
group by
t.grade;

13、面试题

有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
3，即学过1号课程又学过2号课所有学生的姓名。
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。

创建表：
create table S (
SNO varchar(10),
SNAME varchar(255)
);
create table C (
CNO varchar(10),
CNAME varchar(255),
CTEACHER varchar(255)
);
create table SC (
SNO varchar(10),
CNO varchar(10),
SCGRADE varchar(10)
);

• 插入数据：

• C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

insert into c (CNO, CNAME, CTEACHER) values (‘1’, ‘Chinese’, ‘zhang’);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘2’, ‘politics’, ‘wang’);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘3’, ‘English’, ‘li’);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘4’, ‘Math’, ‘zhao’);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘5’, ‘physics’, ‘lm’);
commit;

• S（SNO，SNAME）代表（学号，姓名）

INSERT INTO S ( SNO, SNAME ) VALUES ( ‘1’, ‘stu1’);
INSERT INTO S ( SNO, SNAME ) VALUES ( ‘2’, ‘stu2’);
INSERT INTO S ( SNO, SNAME ) VALUES ( ‘3’, ‘stu3’);
INSERT INTO S ( SNO, SNAME ) VALUES ( ‘4’, ‘stu4’);
commit;

• SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘1’, ‘40’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘2’, ‘30’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘3’, ‘20’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘4’, ‘80’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘1’, ‘5’, ‘60’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘1’, ‘60’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘2’, ‘60’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘3’, ‘60’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘4’, ‘60’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘2’, ‘5’, ‘40’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘3’, ‘1’, ‘60’);
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( ‘3’, ‘3’, ‘80’);
commit;

解决方案：
找出没选过“黎明”老师的所有学生姓名。
1.找出黎明老师的课号
select cno from c where cteacher=‘lm’;

2.找出没选过黎明老师的学生学号
Select distinct a.sno as ‘sno’ from sc a
join
(select cno from c where cteacher=‘lm’) b
on
a.cno != b.cno;

3.找出学号对应的学生名
Select s.sname from s s
join (select distinct a.sno as ‘sno’ from sc a
left join
(select cno from c where cteacher=‘lm’) b
on
a.cno != b.cno) t
on
s.sno=t.sno;

2，列出2门以上（含2门）不及格学生姓名及平均成绩。
1.按学号分组，看不及格人数,不及格科目大于等于2的人
select sno, count(scgrade) as ‘bujige’ from sc where scgrade<60 group by sno having bujige>=2;

2.连接，得到姓名
select a.sname as ‘sname’ , a.sno as ‘sno’, b.bujige
from s a
join
(select sno, count(scgrade) as ‘bujige’ from sc where scgrade<60 group by sno having bujige>=2) b
on a.sno=b.sno;

3，即学过1号课程又学过2号课所有学生的姓名。

1.找出“即学过1号课程又学过2号课”学生的学号
1.1.找出“学过1号课程”学生的学号
select sno from sc where cno=1 group by sno;

1.2.找出“学过1号课程”学生的学号
select sno from sc where cno=2 group by sno;

1.3.连接两个表
select a.sno from
(select sno from sc where cno=1 group by sno) a
join
(select sno from sc where cno=2 group by sno) b
on
a.sno=b.sno;

2.连接表，取出姓名
select x.sname as ‘name’ from s x
join
(select a.sno from
(select sno from sc where cno=1 group by sno) a
join
(select sno from sc where cno=2 group by sno) b
on
a.sno=b.sno) y
on x.sno=y.sno;

14、列出所有员工及领导的姓名(自连接)

（本例中必须要有外连接left）
select a.ename, ifnull(b.ename,‘CEO’)
from emp a
left join emp b
on a.mgr=b.empno;

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

（三张表的连接查询）
1.先找出员工直接上级
select a.ename, ifnull(b.ename,‘CEO’)
from emp a
left join emp b
on a.mgr=b.empno;

2.再比较两者的入职日期

select a.empno , a.ename, d.dname
from emp a
join emp b
on a.mgr=b.empno and a.hiredate<b.hiredate
join dept d
on a.deptno=d.deptno;

16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.（表连接、外连接）

select d.dname , e.*
from dept d
left join emp e
on d.deptno = e.deptno;

17、列出至少有5个员工的所有部门

1.列出部门名称和这些部门的员工信息
select d.dname , e.*
from dept d
left join emp e
on d.deptno = e.deptno;

2.计算每个部门的员工数量

select t.dname as ‘部门’ , count(t.dname) as ‘员工数量’
from
(select d.dname , e.*
from dept d
left join emp e
on d.deptno = e.deptno) t #导入表 t
group by t.dname #按部门名分组
having count(t.dname)>=5; #过滤出员工数量至少为5的部门

18、列出薪金比"SMITH"多的所有员工信息.

1.求出SMITH的薪资
select sal as ‘SMITHsal’ from emp where ename=‘SMITH’;

2.求比SMITH 薪资高的人
select e.*
from emp e
join (select sal as ‘SMITHsal’ from emp where ename=‘SMITH’) t
on e.sal>t.SMITHsal;

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

1.找出工作是clerk的员工
select ename , job
from emp
where job=‘clerk’;

2.找出工作是clerk的员工的部门编号，部门名称
select e.ename,e.job ,e.deptno,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where job =‘clerk’;

3.找出每个部门的人数
select deptno,count(*) as deptnumber from emp group by deptno;

4.第2、3步的表实现连接

select a.ename,a.dname, b.deptnumber
from
(select e.ename,e.job ,e.deptno,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where job =‘clerk’) a
join (select deptno,count(*) as deptnumber from emp group by deptno) b
on
a.deptno=b.deptno;

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.

1.找出各种工作的最低薪资
select job,min(sal) as ‘minsal’ from emp group by job;

2.找出最低薪资大于1500的工作种类

select job,min(sal) as ‘minsal’ from emp group by job having min(sal)>1500;

3.找出各工作种类的人数

select job , count(job) as ‘jobnum’ from emp group by job;

4.把步骤2.3的表进行连接

select a.job, a.minsal, b.jobnum from
(select job,min(sal) as ‘minsal’ from emp group by job having min(sal)>1500) a
join
(select job , count(job) as ‘jobnum’ from emp group by job) b
on
a.job = b.job;

21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.

一、(假设不知道部门编号)
1.先查’sales’部门的编号
select d.deptno from dept d where d.dname=‘sales’;

2.利用此部门编号在emp表中，查询员工

select e.ename from emp e where e.deptno=(select d.deptno from dept d where d.dname=‘sales’);

二、(假设知道部门编号)

1.emp dept 进行连接
select d.dname as ‘dname’ , e.*
from emp e
join dept d
on
e.deptno=d.deptno;

2.在步骤1的表中选取SALES部门的员工
select t.*
from
(
select d.dname as ‘dname’ , e.*
from emp e
join dept d
on
e.deptno=d.deptno) t
where t.dname=‘SALES’;

22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

1.先求公司的平均薪金

select avg(sal) as ‘avgsal’ from emp;

2.再求高于平均薪金的员工，及其部门编号

select ename,sal ,deptno from emp where sal> (select avg(sal) as ‘avgsal’ from emp);

3.求出所有员工薪金等级

select ename , grade from emp
join salgrade
on sal between losal and hisal;

4.求出所有员工的上级领导

select a.ename as ‘ename’, ifnull(b.ename, ‘无’) as ‘leader’ from emp a
left join emp b
on a.mgr=b.empno;

5.连接2，3，4表

select a.ename,a.sal,b.grade, ifnull(c.leader , ‘无’) as ‘leader’,d.dname

from
(select ename,sal ,deptno from emp where sal> (select avg(sal) as ‘avgsal’ from emp)) a

join
(select ename , grade from emp
join salgrade
on sal between losal and hisal) b
on
a.ename = b.ename

join
(
select a.ename as ‘ename’, b.ename as ‘leader’ from emp a
left join emp b
on a.mgr=b.empno) c
on
a.ename=c.ename
join dept d
on a.deptno=d.deptno;

23、列出与"SCOTT"从事相同工作的所有员工及部门名称.

1.找出SCOTT从事的工作
select job from emp where ename=‘scott’;

2.找出与scott工作相同的员工

select e.empno ,e.ename,e.job, e.deptno from emp e
join (select job from emp where ename=‘scott’) t
on e.job = t.job
where e.ename!=‘scott’;

3.找出此员工的部门名称

select t.ename , d.dname
from
(select e.empno ,e.ename,e.job, e.deptno from emp e
join (select job from emp where ename=‘scott’) t
on e.job = t.job
where e.ename!=‘scott’) t
join dept d
on t.deptno=d.deptno;

24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.

1.部门30的员工薪金

select ename,sal ,deptno from emp where deptno=30;

2.其他部门，薪金在部门30薪金范围的员工(最终结果为空)

select e.ename,e.sal,e.deptno from emp e
join
(select ename,sal ,deptno from emp where deptno=30) t
on
(e.sal = t.sal)
where e.deptno != 30;

25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.

1.部门30的员工最高薪金

select ename,max(sal) as maxsal30 ,deptno from emp where deptno=30;

2.其他部门，薪金比部门30最高薪金还高的员工

select e.ename,e.sal,e.deptno from emp e
join ((select ename,max(sal) as maxsal30 ,deptno from emp where deptno=30)) t
on e.sal>t.maxsal30
where e.deptno != 30;

26、列出在每个部门工作的员工数量,平均工资和平均服务期限.

1.取出每个部门的员工数量，平均工资

select deptno,count(deptno) as ‘enumber’,avg(sal) as ‘avgsal’ ,avg(timestampdiff(year,hiredate,now())) as ‘workyear’
from emp
group by deptno;

2.对应部门名称

select d.dname as ‘dname’, t.enumber,t.avgsal,t.workyear
from (select deptno,count(deptno) as ‘enumber’,avg(sal) as ‘avgsal’ ,avg(timestampdiff(year,hiredate,now())) as ‘workyear’
from emp
group by deptno) t
join dept d
on d.deptno=t.deptno;

27、列出所有员工的姓名、部门名称和工资。

select e.ename as ‘ename’,d.dname as ‘dname’,e.sal as ‘sal’
from emp e join dept d
on e.deptno=d.deptno;

28、列出所有部门的详细信息和人数

1.找出每个部门的人数
select deptno , count(deptno) as ‘enumber’ from emp group by deptno;

2.与dept表连接

select d.deptno as ‘deptno’ ,d.dname as ‘dname’ , d.loc as ‘loc’ ,t.enumber
from dept d
join (select deptno , count(deptno) as ‘enumber’ from emp group by deptno) t
on d.deptno=t.deptno;

29、列出各种工作的最低工资及从事此工作的雇员姓名

1.找出每种工作的最低工资
select job,min(sal) as ‘minsal’ from emp group by job;

2.找出从事该工作，且工资最低的所有人

第1种方法：
select e.* 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);

第2种方法：
select e.* from emp e
join (select job,min(sal) as ‘minsal’ from emp group by job) t
on (e.job=t.job)
where (e.sal=t.minsal);

30、列出各个部门的MANAGER(领导)的最低薪金

1.找出各个部门领导的员工编号
select distinct mgr from emp;

2.找出各个部门领导的工资

select distinct(t.empno),t.deptno,t.sal from (select e.deptno as ‘deptno’ , e.ename as ‘ename’ , e.empno as ‘empno’ , e.sal as ‘sal’ from emp e
join emp b on e.empno = b.mgr) t;

3.找出每个部门最低的

select a.deptno , min(a.sal) from
(select distinct(t.empno),t.deptno,t.sal from (select e.deptno as ‘deptno’ , e.ename as ‘ename’ , e.empno as ‘empno’ , e.sal as ‘sal’ from emp e
join emp b on e.empno = b.mgr) t) a
group by a.deptno;

31、列出所有员工的年工资,按年薪从低到高排序

1.计算每个员工的年薪

select ename , (sal+ifnull(comm,0))*12 as ‘Yincome’ from emp;

2.按升序排列

select ename , (sal+ifnull(comm,0))*12 as ‘Yincome’ from emp order by ((sal+ifnull(comm,0))*12 ) asc;

32、求出员工领导的薪水超过3000的员工名称与领导名称

1.员工与领导的自连接

select a.ename as ‘employee’ , b.ename as ‘leader’
from emp a
left join emp b
on a.mgr=b.empno
where b.sal>3000;

33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.

1.求出各部门人数
select deptno , count(deptno) as ‘enumber’ from emp group by deptno;

2.接入部门名

select d.dname , ifnull(a.enumber,0) as ‘enumber’ from dept d
left join (select deptno , count(deptno) as ‘enumber’ from emp group by deptno) a
on d.deptno=a.deptno;

3.找出部门名带‘S’的

select t.* from (select d.dname , ifnull(a.enumber,0) as ‘enumber’ from dept d
left join (select deptno , count(deptno) as ‘enumber’ from emp group by deptno) a
on d.deptno=a.deptno) t
where t.dname like ‘%S%’;

3.2.找出部门名最后一个字母带‘S’的

select t.* from (select d.dname , ifnull(a.enumber,0) as ‘enumber’ from dept d
left join (select deptno , count(deptno) as ‘enumber’ from emp group by deptno) a
on d.deptno=a.deptno) t
where t.dname like ‘%S’;

3.3.找出部门名第3个字母带‘S’的

select t.* from (select d.dname , ifnull(a.enumber,0) as ‘enumber’ from dept d
left join (select deptno , count(deptno) as ‘enumber’ from emp group by deptno) a
on d.deptno=a.deptno) t
where t.dname like ‘__S%’;

34、给任职日期超过30年的员工加薪10%.

update emp set sal = sal * 1.1 where timestampdiff(year,hiredate,now()) >30;