MySQL题目组二（5.1版本MySQL）

题目组二（MySQL5.1版本）
建表语句
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
EMPNO int(4) NOT NULL,
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
MGR varchar(10) DEFAULT NULL,
HIREDATE date DEFAULT NULL,
SAL int(7) DEFAULT NULL,
COMM int(7) DEFAULT NULL,
DEPTNO int(2) DEFAULT NULL,
PRIMARY KEY (EMPNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
字段中文名字依次是：工号，姓名，工作岗位，部门经理，受雇日期，薪金，奖金，部门编号
insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(‘7369’,‘SMITH’,‘CLERK’,‘7902’,‘1980-12-17’,‘800’,null,‘20’),
(‘7499’,‘ALLEN’,‘SALESMAN’,‘7698’,‘1981-02-20’,‘1600’,‘300’,‘30’),
(‘7521’,‘WARD’,‘SALESMAN’,‘7698’,‘1981-02-22’,‘1250’,‘500’,‘30’),
(‘7566’,‘JONES’,‘MANAGER’,‘7839’,‘1981-04-02’,‘2975’,null,‘20’),
(‘7654’,‘MARTIN’,‘SALESMAN’,‘7698’,‘1981-09-28’,‘1250’,‘1400’,‘30’),
(‘7698’,‘BLAKE’,‘MANAGER’,‘7839’,‘1981-05-01’,‘2850’,null,‘30’),
(‘7782’,‘CLARK’,‘MANAGER’,‘7839’,‘1981-06-09’,‘2450’,null,‘10’),
(‘7788’,‘SCOTT’,‘ANALYST’,‘7566’,‘1987-04-19’,‘3000’,null,‘20’),
(‘7839’,‘KING’,‘PRESIDENT’,null,‘1981-11-17’,‘5000’,null,‘10’),
(‘7844’,‘TURNER’,‘SALESMAN’,‘7698’,‘1981-09-08’,‘1500’,‘0’,‘30’),
(‘7876’,‘ADAMS’,‘CLERK’,‘7788’,‘1987-05-23’,‘1100’,null,‘20’),
(‘7900’,‘JAMES’,‘CLERK’,‘7698’,‘1981-12-03’,‘950’,null,‘30’),
(‘7902’,‘FORD’,‘ANALYST’,‘7566’,‘1981-12-03’,‘3000’,null,‘20’),
(‘7934’,‘MILLER’,‘CLERK’,‘7782’,‘1982-01-23’,‘1300’,null,‘10’);

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
DEPTNO int(2) NOT NULL,
DNAME varchar(14) DEFAULT NULL,
LOC varchar(13) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into dept(DEPTNO,DNAME,LOC) values
(‘10’,‘ACCOUNTING’,‘NEW YORK’),
(‘20’,‘RESEARCH’,‘DALLAS’),
(‘30’,‘SALES’,‘CHICAGO’),
(‘40’,‘OPERATIONS’,‘BOSTON’);

题目与答案
1． 列出至少有4个员工的所有部门编号和名称。
select
a.deptno,dname,count(ename)
from dept as a
left join emp as b
on a.deptno=b.deptno
group by a.deptno
having count(ename)>=4;

2． 列出薪金比“SMITH”多的所有员工。
select
ename,job,sal
from dept as a
left join emp as b
on a.deptno=b.deptno
where sal>(select sal from emp where ename=‘SMITH’);

3． 列出所有员工的姓名及其直接上级的姓名。
select worker.ename as ‘员工’,boss.ename as ‘领导’
from emp as worker left join emp as boss
on worker.deptno=boss.deptno
where worker.mgr=boss.empno;

4． 列出受雇日期早于其直接上级的所有员工。
select worker.ename as ‘受雇日期早于上级’
from emp as worker left join emp as boss
on worker.deptno=boss.deptno
where worker.mgr=boss.empno
and worker.hiredate<boss.hiredate;

5． 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select
a.deptno as ‘有员工的部门’,ename as ‘员工姓名’,job as ‘工作’,dname as ‘部门名称’,
(select dept.deptno from
dept left join
emp on dept.deptno=emp.deptno
where ename is null) as ‘无员工的部门’
from dept as a left join
emp as b on a.deptno=b.deptno;

查询没有员工的部门：
select dept.deptno from
dept left join
emp on dept.deptno=emp.deptno
where ename is null;

6． 列出所有“CLERK”（办事员）的姓名及其部门名称。
select ename as ‘姓名’, dname as ‘部门名称’,job
from emp left join
dept on emp.deptno=dept.deptno
where job=‘CLERK’;

7． 列出最低薪金大于1500的各种工作。
select job,sal from emp where sal>1500;

8． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select
ename,dname
from emp left join
dept on emp.deptno=dept.deptno
where dname=‘sales’;

9． 列出薪金高于公司平均薪金的所有员工。
（1）公司平均薪水
select ename from emp
（2）where sal>(select sum(sal)/count(sal) from emp);
select sum(sal)/count(sal) from emp;

10．列出与“SCOTT”从事相同工作的所有员工。
（1）查询“SCOTT”的工作
select job from emp where ename=‘scott’;
（2）select ename from emp where job=(select job from emp where ename=‘scott’);

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename from emp where sal in (select sal from emp where deptno=30)
and deptno !=30;

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
（1）平均服务期限
select sum((year(now())-year(hiredate)))/count((year(now())-year(hiredate)))
as year from emp;
（2）
select
a.deptno,count(ename) as ‘员工数量’,avg(sal) as ‘平均工资’,
(select sum((year(now())-year(hiredate)))/count((year(now())-year(hiredate)))
as year from emp) as ‘平均服务期限’
from emp as a left join
dept as b on a.deptno=b.deptno
group by a.deptno;

14．列出所有员工的姓名、部门名称和工资。
select
ename,a.deptno,sal,dname
from emp as a
left join dept as b
on a.deptno=b.deptno;

15．列出所有部门的详细信息和部门人数。
select
b.deptno,dname,loc,count(ename) as ‘部门人数’
from emp as a
left join dept as b
on a.deptno=b.deptno
group by b.deptno;

16．列出各种工作的最低工资。
select min(sal) as ‘最低工资’,job as ‘工作’ from emp group by job;

17．列出各个部门的MANAGER（经理）的最低薪金。
select min(sal),job,dept.deptno,dname as ‘部门’
from emp left join dept
on emp.deptno=dept.deptno
where job=‘manager’ group by deptno;

18．列出所有员工的年工资,按年薪从低到高排序。
方式一：
select
sal12 + ifnull(comm,0)
from emp;
方式二：
select
12sal + case when (comm is null) then 0
else comm end
from emp;