一个SQL

多行子查询，多列：

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

查询自己部门工资高于平均部门平均工资的人的信息；

select a2.ename,a2.sal,a2.deptno from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
select * from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;