MySQL面试笔试题（基础题）

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

select ename from emp e,(
    select deptno,max(sal) max_sal from emp group by deptno
) each_dept_max_sal
where e.deptno=each_dept_max_sal.deptno and
e.sal=each_dept_max_sal.max_sal;

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

select ename,sal from emp e,(
    select deptno,avg(sal) avg_sal from emp group by deptno
) d where e.deptno=d.deptno and
e.sal>d.avg_sal;

3、取得部门中（所有有人的）评价薪水等级

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

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

select sal from emp order by sal desc limit 0,1;

 5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

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

select deptno from emp group by deptno having avg(sal) = (
    select max(avg_sal) from (
        select avg(sal) avg_sal from emp group by deptno
    ) d
);

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

select dname from dept where deptno =(
    select deptno from emp group by deptno order by avg(sal) desc limit 0,1
);

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

select dname from dept where deptno =(
    select deptno from emp group by deptno order by avg(sal) limit 0,1
);

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

select ename,sal from emp where empno in(
    select ifnull(mgr,0) mgr from emp
) and sal > (
    select max(sal) from emp where empno not in (
        select ifnull(mgr,0) mgr from emp
    )
);

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

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

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

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

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

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

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

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

13、使用资料2的题：

有三个表，表S（学生表），C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO,CNAME,CTEACHER）代表（课号，课名，老师）
SC（SNO,CNO,SCGRADE）代表（学号，课号，成绩）
问题：
1.找出没有选过’‘黎明’'老师的所有学生姓名

select sname from s where sno not in(
    select sno from s where sno in(
        select sno from sc where cno in(
            select cno from c where cteacher='黎明'
        )
    )
);

2.列出2门以上（含2门）不及格学生姓名及平均成绩

select sname,sc.avg_scgrade from s,(
    select sno,avg(scgrade) avg_scgrade from sc where scgrade<60 group by sno
    having count(cno)>=2
) sc where s.sno=sc.sno;

3.既学过1号课程又学过2号课程的所有的学生姓名

select sname from s where sno in (
    select sc1.sno from sc sc1,sc sc2 where sc1.sno=sc2.sno
    and sc1.cno=1 and sc2.cno=2
);

14、列出所有员工及领导的名字

select e1.ename 员工,e2.ename 领导 from emp e1 left join emp e2
on e1.mgr=e2.empno;

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

select e1.ename 员工,e2.ename 领导 from emp e1, emp e2
where e1.mgr=e2.empno and e1.hiredate<e2.hiredate;

16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

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

17、列出只是有5个员工的所有部门

select d.deptno from dept d,emp e
where e.deptno=d.deptno
group by d.deptno
having count(empno)>=5;

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

select ename,sal from emp where ename!='SMITH' and sal > (
    select sal from emp where ename='SMITH'
);

19、列出所有"CLERK"（办事员）的姓名及其部门名称，部门的人数

select ename,dname,count from emp e,dept d,(
    select deptno,count(empno) count from emp
    group by deptno
) c
where e.deptno=d.deptno and d.deptno=c.deptno and JOB='CLERK';

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

select job,count(empno) count from emp group by job having min(sal)>1500;

21、列出在部门"SALES"（销售部）工作的员工的姓名，假设不知道销售部的部门编号

select ename from emp where deptno =(
    select deptno from dept where dname='SALES'
);

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

select e.ename,dname,m.ename mgr_ename,grade from
emp e left join emp m on e.mgr=m.empno
join dept d on e.deptno=d.deptno
join salgrade s on e.sal between s.losal and s.hisal
where e.sal > (
    select avg(sal) avg_sal from emp
);

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

select ename,dname from emp e,dept d where e.deptno=d.deptno and ename!='SCOTT'
and job in (
    select job from emp where ename='SCOTT'
);

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

select ename,sal from emp where deptno!=30 and sal in (
    select distinct sal from emp where deptno=30
);

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

select ename,sal,dname from emp e,dept d
where e.deptno=d.deptno and sal > all(
    select distinct sal from emp where deptno=30
);

 26、列出在每个部门工作的员工数量，平均工作和平均服务期限，没有员工的部门，部门人数是0

select d.deptno,count(empno) count,ifnull(avg(sal),0) avg_sal,
TimeStampDiff(YEAR,ifnull(hiredate,now()),now()) avg_time
from dept d left join emp e on e.deptno=d.deptno
group by d.deptno;

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

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

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

select d.*,ifnull(temp.count,0) 人数 from dept d left join (
    select deptno,count(empno) count from emp group by deptno
) temp on d.deptno=temp.deptno;

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

select e.* from emp e,(
    select job,min(sal) min_sal from emp group by job
) j where e.job=j.job and e.sal=j.min_sal;

 30、列出各个部门的MANAGER（领导）的最低薪水

select deptno,min(sal) from emp where empno in (
    select mgr from emp
) group by deptno;

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

select ename,sal*12+ifnull(comm,0) year_sal from emp order by year_sal;

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

select e.ename,m.ename from emp e,(
    select empno,ename from emp where empno in (
        select mgr from emp
    ) and sal > 3000
) m where e.mgr=m.empno;

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

select dname,ifnull(sum(sal),0) sum_sal,count(empno) from dept d left join emp e
on e.deptno=d.deptno where dname like '%S%'
group by dname;

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

update emp set sal=sal+sal*0.1 where TimeStampDiff(YEAR,ifnull(hiredate,now()),now())>30;

资料1： 

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
 
CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;
 
CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );
 
 
 
 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
  
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
  
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

资料2： 

drop table if exists s;
drop table if exists c;
drop table if exists sc;
 
create table s(
		sno int(10) primary key,-- 学号
		sname varchar(14)-- 姓名
		);
		
create table c(
		cno int(10) primary key,-- 课号
		cname varchar(14),-- 课名
		cteacher varchar(14)-- 成绩
		);
		
create table sc(
		sno int(10),-- 外键，学号
		cno int(10),-- 外键，课号
		scgrade int(10),-- 成绩
		primary key(sno,cno),-- 联合主键
		CONSTRAINT sc_s_fk FOREIGN KEY(sno) REFERENCES s(sno),-- 外键约束
		CONSTRAINT sc_c_fk FOREIGN KEY(cno) REFERENCES c(cno)-- 外键约束
		);
		
insert into s(sno,sname) values(1,'a');
insert into s(sno,sname) values(2,'b');
insert into s(sno,sname) values(3,'c');
insert into s(sno,sname) values(4,'d');
 
insert into c(cno,cname,cteacher) values(1,'java','王老师');
insert into c(cno,cname,cteacher) values(2,'C++','张老师');
insert into c(cno,cname,cteacher) values(3,'C#','李老师');
insert into c(cno,cname,cteacher) values(4,'mysql','周老师');
insert into c(cno,cname,cteacher) values(5,'oracle','黎明');
 
insert into sc(sno,cno,scgrade) values(1,1,50);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,50);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,70);
insert into sc(sno,cno,scgrade) values(2,4,59);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,61);
insert into sc(sno,cno,scgrade) values(3,3,99);
insert into sc(sno,cno,scgrade) values(3,4,100);
insert into sc(sno,cno,scgrade) values(3,5,52);
insert into sc(sno,cno,scgrade) values(4,3,82);
insert into sc(sno,cno,scgrade) values(4,4,99);
insert into sc(sno,cno,scgrade) values(4,5,40);
	
select * from s;
select * from c;
select * from sc;