SQL经典查询练手(二)

10．列出与“SCOTT”从事相同工作的所有员工

select ename from emp where job in(select job from emp where ENAME='SCOTT') and ename !='SCOTT' ;

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

select e1.ename,e1.sal,e1.DEPTNO from emp e1,emp e2 where e1.sal = e2.sal and e2.deptno = '30' and e1.deptno !='30'

select ename,sal from emp where sal=any(select sal from emp where deptno=30) and deptno!=30;

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(max的用法)

select sal,ename from emp where sal>(select max(sal) from emp where deptno=30);

13．列出在每个(每个是关键字,对此group by)部门工作的员工数量、平均工资和平均服务期限。(经典的group by用法)select deptno,count(*),avg(a.sal),avg(sysdate-HIREDATE) from emp a group by deptno;