MySQL练习题

sql文件在文章末尾：

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

1、取得每个部门最高薪
select deptno,max(sal) from emp group by deptno;

2、将上面的结果当成一个表s,与emp表进行连接查询
select s.deptno,s.maxsal,e.ename
from (select deptno,max(sal) maxsal from emp group by deptno) s;
join
emp e
on s.maxsal = e.sal;

那些人的薪水在部门的平均薪水之上

1、先求出部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;

2、

select e.ename,e.deptno,e.sal
from (select deptno,avg(sal) avgsal from emp group by deptno) s
join emp e
on e.deptno=s.deptno and e.sal > s.avgsal 
order by deptno desc;

取得部门中所有人的平均的薪水等级

1、先找出部门中所有人的薪水等级

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

取得部门中所有人的平均薪水等级

select a.deptno,a.avgsal,s.grade
from (select deptno,avg(sal) avgsal from emp group by deptno) a
join salgrade s
on a.avgsal between s.losal and s.hisal

不用组合函数，取得最高薪水

方法一：

1、利用排序和分页

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

方法二：

1、利用表的自连接
a表

b表

将a表中的sal与b表中的做比较，a.sal>b.sal，输出b.sal得到的是除了5000，的所有值，然后利用not in

select sal from emp where sal not in
(select distinct b.sal
from emp a
join (select sal from emp) b
on a.sal > b.sal);

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

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

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

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

1、找出平均薪水的等级最低的部门编号

2、把之前的当成一张表，与等级表进行关联

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

1、找出普通员工的最高薪水

select max(sal)
from emp 
where empno not in(select distinct mgr from emp where mgr is not null);

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

取得最后入职的5名员工

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

面试题

建表语句：

CREATE TABLE SC
(
  SNO      VARCHAR(200),
  CNO      VARCHAR(200),
  SCGRADE  VARCHAR(200)
);

CREATE TABLE S
(
  SNO    VARCHAR(200 ),
  SNAME  VARCHAR(200)
);

CREATE TABLE C
(
  CNO       VARCHAR(200),
  CNAME     VARCHAR(200),
  CTEACHER  VARCHAR(200)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4'); 
commit;
 
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;

有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：
1，找出没选过“黎明”老师的所有学生姓名。

• 先找出“黎明”老师的课程编号
• 
• 找出没选黎明老师的课的学生编号
  
• 找出没选黎明老师的课的学生姓名
  

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

• 先找出不及格的学生
• 
• 找出不及格学生挂科的门数
  
• 最终结果

select b.sname,avg(sc.scgrade)
from
(
select s.sno,s.sname
from
(
select sno,count(*) count
from sc
where scgrade < 60
group by sno
having count>2
) a
join s
on a.sno = s.sno
) b
join sc
on sc.sno=b.sno
group by b.sname;

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

• 找出学过1号和2号课的学生编号
  

请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。

列出所有员工及领导的姓名

• 自关联
  

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

• 找出受雇日期早于其直接上级的所有员工的编号,姓名

select emp.empno,emp.ename,dept.dname
from
(
select distinct emp.empno,emp.hiredate
from 
(select mgr
from emp
where mgr is not null) a
join emp
on a.mgr = emp.empno
) a
join emp
on emp.mgr = a.empno
join dept
on emp.deptno = dept.deptno
where emp.hiredate < a.hiredate;

列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

建表SQL

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;