sql例题学习
2018-12-04 本文已影响32人
Frank_8942
分享一波sql测试题及自己写的sql答案, 如果有更好的sql写法, 欢迎批评指正
部门表 dept：
DEPTNO DNAME LOC
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
员工表 emp:
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL,COMM,DEPTNO
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-02,3000,null,20
7934,MILLER,CLERK,7782,1982-01-23,1300,null,10
需求:
- 查询出 JONES 的领导是谁（JONES 向谁报告）。
- JONES 领导谁。（谁向JONES 报告）。
- 查询各职位的员工工资的最大值，最小值，平均值，总和
- 选择具有各个job 的员工人数(提示:对job 进行分组)
- 查询员工最高工资和最低工资的差距,列名为DIFFERENCE；
- 查询各个管理者属下员工的最低工资，其中最低工资不能低于 800，没有管理者的员工不计算在内
- 查询所有部门的部门名字dname，所在位置loc，员工数量和工资平均值；
- 查询和scott 相同部门的员工姓名ename 和雇用日期hiredate
- 查询工资比公司平均工资高的所有员工的员工号 empno，姓名ename 和工资sal。
- 查询和姓名中包含字母u 的员工在相同部门的员工的员工号 empno 和姓名ename
- 查询在部门的loc 为 newYork 的部门工作的员工的员工姓名ename，部门名称 dname 和岗位名称job
- 查询管理者是king 的员工姓名 ename 和工资sal
- 显示Operations 部门有哪些职位
- 各个部门中工资大于1500的员工人数
- 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
- 所在部门平均工资高于1500的员工名字
- 列出各个部门中工资最高的员工的信息：名字、部门号、工资
- 哪个部门的平均工资是最高的，列出部门号、平均工资
=======================
数据准备
create table if not exists dept(
deptno int,
dname varchar(20),
loc varchar(20)
);
insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');
create table if not exists emp(
empno int,
ename varchar(20),
job varchar(20),
mgr int,
hiredate date,
sal double,
comm double,
deptno int
);
insert into emp values ( 7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30 );
insert into emp values ( 7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values ( 7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp values ( 7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values ( 7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10 );
insert into emp values ( 7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into emp values ( 7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp values ( 7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp values ( 7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30 );
insert into emp values ( 7902,'FORD','ANALYST',7566,'1981-12-02',3000,null,20);
insert into emp values ( 7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);
==================================
答案
1. 查询出 JONES 的领导是谁（JONES 向谁报告）。
select ename
from emp
where empno in ( select mgr from emp where ename = 'JONES' );
2. JONES 领导谁。（谁向JONES 报告）。
select ename
from emp
where mgr in ( select empno from emp where ename = 'JONES' );
3. 查询各职位的员工工资的最大值，最小值，平均值，总和
SELECT job,min(sal) min, max(sal) max, avg(sal) avg, sum(sal) sum
from emp
group by job ;
4. 选择具有各个 job 的员工人数(提示:对job 进行分组)
SELECT job,count(distinct empno) count
from emp
group by job ;
5. 查询员工最高工资和最低工资的差距,列名为 DIFFERENCE ；
select max(sal)-min(sal) as DIFFERENCE
from emp ;
6. 查询各个管理者属下员工的最低工资，其中最低工资不能低于 800 ，没有管理者的员工不计算在内
select a.empno, a.ename, b.min
from
emp a
inner join
(
SELECT mgr, min(sal) min
from emp
where mgr is not null
group by mgr
having min(sal) >= 800
) b
on a.empno = b.mgr ;
7. 查询所有部门的部门名字 dname ，所在位置 loc ，员工数量和工资平均值；
select min(a.dname) dname, min(a.loc) loc, count(b.empno) count, avg(b.sal) avg
from
dept a left join emp b
on a.deptno = b.deptno
group by a.deptno ;
8. 查询和 scott 相同部门的员工姓名 ename 和雇用日期 hiredate
select a.deptno,a.ename,a.hiredate
from
emp a
join
(
select deptno
from emp
where emp.ename = 'scott'
) b
on a.deptno = b.deptno and a.ename != 'scott';
9. 查询工资比公司平均工资高的所有员工的员工号 empno，姓名ename 和工资sal
select a.empno,a.ename,a.sal,b.sal
from
emp a
join
( select avg(sal) sal from emp ) b
on a.sal > b.sal ;
10. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号 empno 和姓名ename
select a.empno,a.ename,a.deptno
from
emp a
inner join
(
select ename,deptno
from emp
where ename like '%u%'
) b
on a.deptno = b.deptno and a.ename != b.ename ;
11. 查询在部门的loc 为 newYork 的部门工作的员工的员工姓名ename，部门名称 dname 和岗位名称job
select
a.ename, b.dname, a.job
from
emp a
join
(
select deptno, dname
from dept
where loc = 'NEW YORK'
) b
on a.deptno = b.deptno ;
12. 查询管理者是 king 的员工姓名 ename 和工资sal
SELECT
a.ename, a.sal
from
emp a
join
(
select empno from emp where ename = 'king'
) b
on a.mgr = b.empno;
13. 显示 Operations 部门有哪些职位
select
a.job
from
emp a
join
( select deptno from dept where dname = 'Operations' ) b
on a.deptno = b.deptno
group by a.job ;
14. 各个部门中工资大于1500的员工人数
select
b.deptno, count(distinct a.ename) count
from
dept b
left join
(select ename,deptno from emp where sal > 1500 ) a
on a.deptno = b.deptno
group by b.deptno ;
15. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
select
a.ename, a.sal, b.avgsal
from
emp a
join
(select avg(sal) avgsal from emp ) b
on a.sal > b.avgsal
order by a.sal desc ;
16. 所在部门平均工资高于2000的员工名字
select
a.ename, a.sal , a.deptno
from
emp a
join
(select deptno from emp group by deptno having avg(sal) > 2000 ) b
on a.deptno = b.deptno ;
17. 列出各个部门中工资最高的员工的信息：名字、部门号、工资
select
a.deptno, a.ename, a.sal
from
emp a
join
(select deptno,max(sal) sal from emp group by deptno ) b
on a.deptno = b.deptno and a.sal = b.sal ;
18. 哪个部门的平均工资是最高的，列出部门号、平均工资
select
deptno, avg(sal) avgsal
from emp
group by deptno
order by avgsal desc
limit 1 ;
