sql 子查询
程序员文章站
2022-07-03 13:26:55
...
利用oracle中自带的几个表。
求每个部门的部门名称,员工数,员工平均工资,以及每个部门工资最低的员工姓名。sql如下:
表的结构如下:
员工表:
部门表:
求每个部门的部门名称,员工数,员工平均工资,以及每个部门工资最低的员工姓名。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);