Oracle数据库scott用户查询语句笔记

1、找出员工所对应的职务（等值链接）

92年老版本写法

select ename,dname from emp e,dept d
where e.deptno=d.deptno；

99年新版本写法

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

2、找出最高薪水人的姓名

select ename,sal from emp
join(select max(sal)max_sal, deptno from emp group by deptno)t
on(emp.sal = t.max_sal and emp.deptno = t.deptno);

3、找出emp表中的员工对应的经理人(自链接)

select e1.ename,e2.ename from emp e1
join emp e2 on e1.mgr=e2.empno;

4、找出名字，职位以及薪水等级，并且名字第二个字母不能是A

select ename,dname,grade from emp e
join dept d on(e.deptno=d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%';

5、找出薪水大于1500进行分组倒序排列

select avg(sal) from emp
where sal>1200 group by deptno
having avg(sal)>1500 order by avg(sal) desc;

6、指定符号为转义字符

select ename from emp
where ename like '%$%%' escape '$';

7、找出员工名字（ename）入职日期（hiredate）大于1981-2-20 12:34:56

select ename,hiredate from emp
where hiredate> to_date('1981-2-20 12:34:56','YYYY-MM-DD HH:MI:SS');

8、找出薪水（sal）大于1250

select sal from emp
where sal>to_number('$1,250.00','$9,999.99');

9、对入职日期转换成YYYY-MM-DD HH:MI:SS

select to_char(hiredate,'YYYY-MM-DD HH:MI:SS')from emp;

10、找出每个员工对应的经理人，并且显示出全部员工（左外链接）

select e1.ename,e2.ename from emp e1 left
join emp e2 on e1.mgr = e2.empno;

11、找出员工对应的职务（右外链接）

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

12、找出部门编号(DEPTNO)、平均薪水(AVG_SAL)、薪水等级（GRADE）

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

13、找出部门编号、姓名、薪水等级

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

14、找出部门、平均薪水等级

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

15、找出经理人(mgr)

select ename from emp
where empno in (select distinct mgr from emp);

16、使用组函数求出最高薪水

select max(sal) from emp;

17、不用组函数求出最高薪水
思路：
步骤（1）首先emp表自链接去重，然后俩者小于比较

select distinct e1.sal from emp e1
join emp e2 on (e1.sal<e2.sal);

步骤（2）由于俩者在小于比较时，当比较到最高薪水找不到比它更大的数据，最高薪水就无法显示出来。针对没有显示出来的数据可以通过not in把最高薪水找出来！

select sal from emp
where sal not in
    (
    select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal)
    );

18、找出平均薪水最高的部门名称

写法1：
步骤（1）找出平均薪水以及部门编号

select avg(sal),deptno from emp group by deptno;

步骤（2）找出平均薪水最大值，重复步骤1将步骤1看作一张表

select max(avg_sal) from
    (select avg(sal)avg_sal,deptno from emp group by deptno);

步骤（3） 找出平均薪水最大值的部门编号，重复步骤2将步骤2看作成一张表即最大值

select deptno from
    (select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
    (
    select max(avg_sal) from
        (select avg(sal)avg_sal,deptno from emp group by deptno)
    );

写法2：

select deptno from
    (select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal =
    (select max(avg(sal)) from emp group by deptno);

步骤（4）找出平均薪水最高的部门名称

select dname from dept where deptno =
    (
    select deptno from
        (select avg(sal)avg_sal,deptno from emp group by deptno)
    where avg_sal=
        (
        select max(avg_sal) from
             (select avg(sal)avg_sal,deptno from emp group by deptno)
        )
    );

19、找出平均薪水的等级最低的部门的部门名称

写法1：
步骤（1）找出部门平均薪水

select deptno,avg(sal)avg_sal from emp group by deptno;

步骤（2）找出部门的等级平均薪水

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

步骤（3）找出部门最低等级

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

步骤（4）找出平均薪水的等级最低的部门的部门名称
select dname,t1.deptno,grade,avg_sal from
    (
    select deptno,avg_sal,grade from
        (select deptno,avg(sal)avg_sal from emp group by deptno)t
    join salgrade s on (t.avg_sal between s.losal and s.hisal)
    )t1
join dept on (t1.deptno = dept.deptno) where t1.grade =
    (
    select min(grade) from
        (
        select deptno,avg_sal,grade from
            (select deptno,avg(sal)avg_sal from emp group by deptno)t
        join salgrade s on (t.avg_sal between s.losal and s.hisal)
        )
    );

写法2（前提是创建了view视图表格）：

select dname,t1.deptno,grade,avg_sal from
    (v$_deptno_grade_avg_sal_info) t1
join dept on (t1.deptno = dept.deptno) where t1.grade =
    (select min(grade) from v$_deptno_grade_avg_sal_info);

20、比普通员工的最高薪水还要高的经理人名称

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

21、找出姓名10以后的名字（rownum）
步骤（1）使用rownum语句对应员工姓名

select rownum r,ename from emp;

步骤（2）找出姓名10以后的名字（rownum）

select ename from
    (select rownum r,ename from emp)
where r > 10;

22、找出薪水最高的第6到第10个人

select ename,sal,r from
    (
    select ename,sal,rownum r from
        (select ename,sal from emp order by sal desc)
    )
where r >= 6 and r <= 10

23、返回上一步

rollback;

24、备份

create table emp2 as select * from emp;