scott自己的练习

#和10部门工资一样的其他部门的员工的信息
select * from emp where sal in(select sal from emp where deptno = 10)and deptno!=10;#<>也对
#列出至少有一个员工的所有部门
#方法一  分组再筛选
select d.* ,COUNT(1)  from dept d right join emp e on d.deptno = e.deptno  group by d.deptno having COUNT(1)>0;
#方法二
select count(1) ,e.deptno,d.dname,d.loc from emp e, dept d where e.deptno = d.deptno group by e.deptno having count(1)>0;
#列出受雇日期早于其直接上级的所有员工 where弄反了  。。。。。。
select e1.empno id,e1.ename 员工,e2.ename 上级 from emp e1,emp e2 where e1.mgr = e2.empno and  e1.hiredate<e2.hiredate order by e1.empno;
#select e1.empno ,e1.hiredate,e2.empno,e2.hiredate from emp e1,emp e2 where e1.mgr = e2.empno and e1.hiredate<e2.hiredate;
#列出最低薪资大于1500的各种工作(查找所有工作-必然有重复-orderby-再筛选 min sal >1500)
select min(sal),job from emp group by job having min(sal)>1500;
#列出薪金高于公司平均薪资的所有员工 avg:2108.928571
select * from emp where sal>(select avg(sal) from emp);
#列出与SCOTT从事相同工作的所有员工
select * from emp where job = (select job from emp where ename ='SCOTT') and ename <>'SCOTT';
#或者
select * from emp e1,(select job from emp where ename='SCOTT') e2 where e1.job = e2.job and e1.ename <>'SCOTT';
#列出在每个部门工作的员工数量、平均工资、和平均服务期限  主要是：平均服务期限
#select count(1),avg(sal) ,avg(hiredate) from emp group by deptno;
select count(1)as 员工数量,avg(sal)as 平均工资 ,avg(year(now())-year(hiredate))as 平均服务期限 from emp group by deptno;
#列出所有部门的详细信息和部门人数 
select d.*,count(1) from dept d, emp e where d.deptno= e.deptno group by d.dname;
select d.*,count(1) from dept d, emp e where d.deptno= e.deptno group by e.deptno;
#查询所有没有奖金的员工信息 
select * from emp where (comm is null or comm = 0);
#查询所有81年之后入职的员工信息 有错误
select * from emp where hiredate >= "TO_DATE"('1981-01-01','yyyy-mm-dd');