本文共 3057 字，大约阅读时间需要 10 分钟。

1:查看工资高于2000的员工

select ename,sal from emp_xiangyoulu where sal>2000; select ename,deptno,sal from emp_xiangyoulu where sal>2000 order by sal desc;

2:查看职位不是"CLERK"的员工

select ename,job from emp_xiangyoulu where job <>'CLERK'; select ename,job from emp_xiangyoulu where job != 'CLERK';

3:查看工资在1000-2500之间的员工 

select ename,sal from emp_xiangyoulu where sal between 1000 and 2500; select ename,deptno,sal from emp_xiangyoulu where sal between 1000 and 2500 order by sal;

4:查看名字是以K结尾的员工

select * from emp_xiangyoulu where ename like '%K'; select ename,deptno,sal from emp_xiangyoulu where ename like '%K';

5:查看20,30号部门的员工

select * from emp_xiangyoulu where deptno=20 or deptno=30; select * from emp_xiangyoulu where deptno in(20,30);

6:查看奖金为NULL的员工

select ename,comm from emp_xiangyoulu where comm is null; select * from emp_xiangyoulu where comm is null order by deptno;

7:查看年薪高于20000的员工

select ename,sal*12 from emp_xiangyoulu where sal*12>20000; select ename,sal*12 totalSal from emp_xiangyoulu where sal*12>20000;

8:查看公司共有多少种职位

select count(distinct job) from emp_xiangyoulu ; --select count(job) from (select ceil(avg(sal)),job from emp_xiangyoulu group by job);

9:按部门号从小到大排列查看员工

select * from emp_xiangyoulu order by deptno asc;--顺序(默认) select * from emp_xiangyoulu order by deptno desc;--倒叙

10:查看每个部门的最高，最低，平均工资，和工资总和

select max(sal),min(sal),ceil(avg(sal)),sum(sal),deptno from emp_xiangyoulu  group by deptno order by deptno;

11:查看平均工资高于2000的部门的最低薪水

--select avg(sal),deptno from emp_xiangyoulu group by deptno where avg(sal)>2000; --报错! select avg(sal),deptno from emp_xiangyoulu group by deptno having avg(sal)>2000; select avg(sal),deptno from emp_xiangyoulu group by deptno having deptno in(   select deptno from emp_xiangyoulu group by deptno having avg(sal)>2000);

12:查看在NEW YORK工作的员工

select * from emp_xiangyoulu e join dept_xiangyoulu d    on e.deptno=d.deptno and d.loc='NEW YORK';  select * from emp_xiangyoulu e,dept_xiangyoulu d where e.deptno=d.deptno and d.loc='NEW YORK';

13:查看所有员工及所在部门信息，若该员工没有部门，则部门信息以NULL显示

select * from emp_xiangyoulu e left outer join dept_xiangyoulu d on e.deptno=d.deptno;

14:查看ALLEN的上司是谁

select e.ename,e.mgr,m.empno,m.ename from emp_xiangyoulu e join emp_xiangyoulu m  on e.mgr=m.empno and e.ename='ALLEN';

15:查看SMITH的上司在哪个城市工作?

select m.ename,d.loc from emp_xiangyoulu e join emp_xiangyoulu m on e.mgr=m.empno join dept_xiangyoulu d on e.deptno=d.deptno  where e.ename='SMITH';

16:查看平均工资高于2000的那些部门名字以及所在城市?

select distinct e.deptno,d.dname,d.loc from emp_xiangyoulu e join dept_xiangyoulu d on e.deptno=d.deptno  and e.deptno in(select deptno from emp_xiangyoulu group by deptno having avg(sal)>2000); SELECT e.deptno,AVG(e.sal),d.dname,d.loc FROM emp_xiangyoulu e ,dept_xiangyoulu d WHERE e.deptno=d.deptno GROUP BY e.deptno,d.dname,d.loc  HAVING AVG(e.sal)>2000;

17:在NEW YORK工作的员工有多少人?

select count(1) from emp_xiangyoulu e join dept_xiangyoulu d on e.deptno=d.deptno and d.loc='NEW YORK';

18:在DALLAS工作的员工的平均工资是多少?

select avg(e.sal) from emp_xiangyoulu e join dept_xiangyoulu d  on e.deptno=d.deptno where d.loc='DALLAS';

转载地址：http://egnp.baihongyu.com/