select语句 linux,select语句练习

练习：

1.哪个部门没有员工

select d.dname from emp e,dept d where e.deptno(+)=d.deptno and e.empno is null;

(外连接效率高)

2) select d.dname from dept d  where  not exists ( select null from emp e where  d.deptno=e.deptno);

(子查询效率低)

这些员工都分布在多少个不同的部门

select count(distinct(e.deptno)) from  emp e;

各个部门平均工资

select d.deptno,max(d.dname),avg(nvl(e.sal,0)) from  emp e,dept d  where e.deptno=d.deptno  group by d.deptno;

各个部门不同工种的平均工资

select d.deptno,max(d.dname),e.job,avg(nvl(e.sal,0)) from  emp e,dept d  where e.deptno=d.deptno  group by d.deptno,e.job;

平均工资大于2000的部门

select d.deptno,max(d.dname),avg(nvl(e.sal,0)) from  emp e,dept d  where e.deptno=d.deptno  group by d.deptno having avg(nvl(e.sal,0))>2000;

谁的工资是最低的

select e.empno,e.ename,e.sal from emp e where e.sal=(select min(sal) from emp);

谁和smith是干一样工作的人

select  e.empno,e.ename,e.job from emp e where e.job=(select job from emp where ename='SMITH') and e.ename<>'SMITH';

哪些人是普通员工

select e.empno,e.ename from emp e where e.empno not in (select mgr from emp where mgr is not null);

哪个部门的平均工资比三十二部门高

select e.deptno,max(d.dname),avg(nvl(sal,0)) from emp e,dept d where e.deptno=d.deptno group by e.deptno  having avg(nvl(sal,0))>(select avg(nvl(sal,0)) from emp where deptno=30);

因为有where deptno=30条件指明了所属部门所以可以省略分组函数group by deptno

哪个部门的平均工资是最低的

select e.deptno,max(d.dname),avg(nvl(e.sal,0)) from emp e,dept d where e.deptno=d.deptno group by e.deptno having avg(nvl(e.sal,0)) =(select min(avg(nvl(sal,0))) from emp group by deptno);

哪些人的工资比本部门的平均工资高

select e.empno,e.ename,e.sal from emp e where e.sal>(select avg(nvl(sal,0)) from emp where emp.deptno=e.deptno );

关联子查询效率低

2)select e.empno,e.ename,e.sal from emp e ,(select deptno,avg(nvl(sal,0)) avgdept from emp group by deptno) d where e.deptno=d.deptno and  e.sal>d.avgdept;

内嵌试图效率高