sql 子查询

利用oracle中自带的几个表。
求每个部门的部门名称，员工数，员工平均工资，以及每个部门工资最低的员工姓名。sql如下：

 select dd.dname, tt.deptno, tt.empnum, tt.avgsal, e.ename
   from scott.dept dd,
        (select e.deptno deptno,
                count(e.empno) empnum,
                avg(e.sal) avgsal,
                min(sal) minsal
           from scott.emp e
          group by e.deptno) tt,
        scott.emp e
  where tt.deptno = dd.deptno
    and e.sal = tt.minsal

表的结构如下：
员工表:

create table SCOTT.EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)

-- Create/Recreate primary, unique and foreign key constraints 
alter table SCOTT.EMP
  add constraint PK_EMP primary key (EMPNO) ;
alter table SCOTT.EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references SCOTT.DEPT (DEPTNO);

部门表：

-- Create table
create table SCOTT.DEPT
(
  DEPTNO NUMBER(2) not null,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
)

-- Create/Recreate primary, unique and foreign key constraints 
alter table SCOTT.DEPT
  add constraint PK_DEPT primary key (DEPTNO);