oracle初学篇 （01）

 

 

员工表                                                                                           工作地

工资等级表

---查询员工姓名，工作，工作所在地

SELECT e.ename , e.job ,e.deptno, d.loc , d.dname
from emp e , dept d
WHERE e.deptno = d.deptno

--查询出来每个人的上级领导
SELECT e1.empno ,e1.ename , e2.empno , e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno

--查询出来每个人的上级领导和部门名称
SELECT e1.empno ,e1.ename , e2.empno , e2.ename ,d.dname
FROM emp e1, emp e2 , dept d
WHERE e1.mgr = e2.empno and e1.deptno = d.deptno

--查询出来每个人的上级领导和部门名称以及工资等级
SELECT e1.empno ,e1.ename , e2.empno , e2.ename ,d.dname ,decode(s.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') ,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级')
FROM emp e1, emp e2 , dept d , salgrade s ,salgrade s1
WHERE e1.mgr = e2.empno and e1.deptno = d.deptno and e1.sal between s.losal and s.hisal
and e2.sal between s1.losal and s1.hisal

--查询每个部门的人数
select*
from dept d, emp e
where d.deptno = e.deptno(+) order by d.deptno

SELECT e1.empno ,e1.ename , e2.empno , e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno(+)

--1999sql
--笛卡尔积
select*from emp e cross join dept d
--去除笛卡尔积
select*from emp e natural join dept d

select*from emp e join dept d using(deptno) where deptno = 20
--外连接
select*from emp e join dept d on e.deptno = d.deptno

select*from emp e right join dept d on e.deptno = d.deptno

SELECT COUNT(*) FROM emp

select min(sal)最少工资 from emp

select max(sal) 最高工资 from emp

select avg(sal) 平均工资 from emp

select sum(sal) 员工总工资 from emp where emp.deptno = 20

select* from emp order by deptno

SELECT deptno, COUNT(empno) FROM emp group by deptno

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

select count(*),deptno , job from emp group by deptno,job order by deptno