oracle笔记2-多表查询和子查询

--查询出当前用户下的所有表
select table_name from user_tables;

--执行顺序原则： from where group by having select order by
--书写顺序原则： select from where group by having order by
select * from dept;
select * from emp;
select * from bonus;
select * from salgrade;

--查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno

--范例：查询出每个员工的上级领导 --自连接自关联自查询
--(员工编号、员工姓名、员工部门编号、员工工资、领导编号、领导姓名、领导工资)
select e.empno, e.ename, e.deptno, e.sal, m.empno, m.ename, m.sal
from emp e, emp m
where e.mgr = m.empno;

--范例: 查询员工编号、员工姓名、员工部门编号、部门名称,领导编号、领导姓名、领导工资
select e.empno, e.ename, e.deptno, d.dname, m.empno, m.ename, m.sal
from emp e, emp m, dept d
where e.mgr = m.empno
and e.deptno = d.deptno;

--范例：查询出员工编号、员工姓名、员工部门编号、部门名称,员工工资,工资等级,领导编号、领导姓名、领导工资,领导工资等级
--三张表 emp e/m, dept d, salgrade s
select e.empno,
        e.ename,
        e.deptno,
        d.dname,
        e.sal,
        s.grade,
        m.empno,
        m.ename,
        m.sal,
        a.grade
   from emp e, emp m, dept d, salgrade s, salgrade a
where e.mgr = m.empno
    and e.deptno = d.deptno
    and e.sal between s.losal and s.hisal
    and m.sal between a.losal and a.hisal;

--范例：查询出所有员工的上级领导
--方式一:left join on
select e.empno, e.ename, m.empno, m.ename
from emp e
left join emp m
on e.mgr = m.empno;
--方式二: +
select e.empno, e.ename, m.empno, m.ename
from emp e, emp m
where e.mgr = m.empno(+);

--范例：查询出所有的部门下的员工，要求把没有员工的部门也展示出来
--方式一:left join on
select *
from emp e
right join dept d
on e.deptno = d.deptno
order by e.empno;
--方式二: +
select * from emp e, dept d where e.deptno(+) = d.deptno order by e.empno;

--查询比SCOTT工资高的员工
select * from emp where emp.sal >= (select e.sal from emp e where e.ename = 'SCOTT');--自己也查出来了???

--查询职位是经理并且工资比7782号员工高的员工
select *
from emp
where job = 'MANAGER'
and sal > (select sal from emp e where e.empno = '7782');

--查询工资最低的员工
select * from emp where sal = (select min(e.sal) from emp e);

--查询部门最低工资大于 30号部门最低工资的部门
select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 30)

--查询出和scott同部门并且同职位的员工
select * from emp where deptno = (select deptno from emp where ename = 'SCOTT') and
job = (select job from emp where ename = 'SCOTT');

SELECT * FROM emp WHERE (deptno,job) = (SELECT deptno, job FROM emp WHERE ename = 'SCOTT');

--查询每个部门的最低工资和最低工资的雇员和部门名称
SELECT e.*,d.dname
FROM emp e, dept d,
(SELECT deptno, MIN(sal) minsal FROM emp GROUP BY deptno) t
WHERE e.sal = t.minsal AND
e.deptno = d.deptno AND
e.deptno = t.deptno

--查询出不是领导的员工
SELECT *
FROM EMP
WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL)

--查询员工表中工资最高的前三名
SELECT T.*, ROWNUM
FROM (SELECT * FROM EMP ORDER BY SAL DESC) T
WHERE ROWNUM <= 3;

--找到员工表中薪水大于本部门平均薪水的员工
SELECT *
   FROM EMP E,
        (SELECT ROUND(AVG(SAL)) AVGSAL, DEPTNO FROM EMP GROUP BY DEPTNO) T
WHERE E.DEPTNO = T.DEPTNO
    AND E.SAL > T.AVGSAL
ORDER BY E.DEPTNO DESC;

--统计每年入职的员工个数
SELECT count(*) counts, to_char(hiredate,'yyyy') years FROM emp GROUP BY to_char(hiredate,'yyyy')
--行转列
SELECT SUM(counts) "Total",
         SUM(decode(years,'1980',counts)) "1980",
         SUM(DECODE(years,'1981',counts)) "1981",
         SUM(DECODE(years,'1982',counts)) "1982",
         SUM(DECODE(years,'1987',counts)) "1987"
FROM (SELECT count(*) counts, to_char(hiredate,'yyyy') years FROM emp GROUP BY to_char(hiredate,'yyyy'))

--范例：工资大于1500，或者是20号部门下的员工
SELECT * FROM emp WHERE sal > 1500 OR deptno = 20;

--范例：工资大于1500，并且是20号部门下的员工
SELECT * FROM emp WHERE sal > 1500 AND deptno = 20;

--范例：1981年入职的普通员工（不包括总裁和经理）
SELECT * FROM emp WHERE to_char(hiredate,'yyyy') = 1981 AND job NOT IN ('MANAGER','PRESIDENT');

--查询没有员工的部门
SELECT * FROM dept d WHERE NOT EXISTS (SELECT * FROM emp e WHERE e.deptno = d.deptno );