sql语句2

select * from emp,dept where EMP.DEPTNO=DEPT.DEPTNO;

select E.ENAME 姓名,E.JOB 工作职位, E1.ENAME 领导,D.DNAME 部门
from emp e ,emp e1,dept d
where E.MGR=E1.EMPNO
and E.DEPTNO=D.DEPTNO
and E1.DEPTNO=D.DEPTNO;

select E1.ENAME 姓名,E1.SAL 工资, D.DNAME 部门,
decode(S.GRADE,1,'第五工资等级',2,'第四工资等级',3,'第三工资等级',4,'第二工资等级',5,'第一工资等级') 工资等级,
E2.ENAME 领导姓名, decode(S2.GRADE,1,'第五工资等级',2,'第四工资等级',3,'第三工资等级',4,'第二工资等级',5,'第一工资等级') 领导工资等级
from emp e1,dept d,salgrade s ,emp e2, salgrade s2
where E1.DEPTNO=D.DEPTNO
and E1.SAL between S.LOSAL and S.HISAL
and E1.MGR = E2.EMPNO
and E2.SAL between S2.LOSAL and S2.HISAL

select E.ENAME 姓名,D.DNAME 部门 from emp e,dept d where E.DEPTNO(+)=D.DEPTNO

select E1.ENAME,E1.EMPNO,E2.ENAME,E2.EMPNO from emp e1,emp e2 where E1.MGR=E2.EMPNO(+);

//交叉连接
select count(*) from emp cross join dept;

//自然连接
select * from emp natural join dept;

//using 直接关联的操作列
select * from emp e join dept d using(deptno)

//on 子句，用户自己编写连接的条件
select * from emp e join dept d on(e.deptno=d.deptno)

//left join 左连接
select * from emp e left outer join dept d on(e.deptno=d.deptno)

//right join 右连接
select * from emp e right outer join dept d on(e.deptno=d.deptno)

//组函数及分组统计---------------------------------------------------------------------------------------

//count() 求出全部的记录数
select count(*) from emp;

//max() 求出一组中的最大值
select * from emp order by sal desc;
select max(sal) from emp;

//min() 求出最小值
select * from emp order by sal asc;
select min(sal) from emp;

//avg() 求出平均值
select avg(sal) from emp where deptno=20;

//sum() 求和
select sum(sal) from emp where deptno=20;

//group by 分组统计
//各部门的雇员数量
select E.DEPTNO 部门编号,count(E.EMPNO) from emp e group by E.DEPTNO;
//各部门的平均工资
select E.DEPTNO 部门编号 , avg(E.SAL) 平均工资 from emp e group by E.DEPTNO;
//按部门分组，并显示部门的名称，及每个部门的员工数
select D.DNAME 部门名称,count(E.EMPNO) 员工数 from emp e , dept d
where e.deptno=d.deptno group by D.DNAME;
//having 分组条件 要求显示出平均工资大于2000的部门编号和平均工资
select E.DEPTNO ,avg(e.sal) from emp e group by E.DEPTNO having avg(e.sal)>2000;
select E.JOB 工作名称,sum(e.sal) su from emp e where job <>'SALESMAN' group by E.JOB having sum(e.sal) >5000 order by su desc
//平均工资最高的部门工资
select max(avg(sal)) from emp group by deptno ;

//子查询-----------------------------------------------------------------------------------------------------------------
//要求查出比7654工资要高并且工作和7788相同的全部员工的信息
select * from emp where sal > (select sal from emp where empno='7654') and job = (select job from emp where empno='7788')
//工资最低的员工信息
select * from emp where sal = (select min(sal) from emp);

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

select D.DNAME,g.c, g.a ,e.ename from dept d,emp e,
(select deptno d, count(empno) c,avg(sal) a,min(sal) m from emp group by deptno) g
where D.DEPTNO = G.d and g.m=E.SAL

//in 操作符，指定一个查询的范围
select * from emp where sal in(select min(sal) from emp group by deptno ) order by sal desc;

//=any 与 in 操作符功能完全一样
select * from emp where sal=any(select min(sal) from emp group by deptno ) order by sal desc;

//>any 比里面最小的值要大
select * from emp where sal>any(select min(sal) from emp group by deptno ) order by sal desc;

//<any 比最大的值要小
select * from emp where sal<any(select min(sal) from emp group by deptno ) order by sal desc;

//>all 比最大的要大
select * from emp where sal>all(select min(sal) from emp group by deptno ) order by sal desc;

//<all 比最小的要小
select * from emp where sal<all(select min(sal) from emp group by deptno ) order by sal desc;

//多列子查询
select * from emp where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where deptno=20);