一、数据表设计图
二、创建语句
二、创建语句
/*==============================================================*/ /* DBMS name: ORACLE Version 9i */ /* Created on: 2008-11-10 23:39:24 */ /*==============================================================*/ alter table "emp" drop constraint FK_EMP_REFERENCE_DEPT; drop table "dept" cascade constraints; drop table "emp" cascade constraints; drop table "salgrade" cascade constraints; /*==============================================================*/ /* Table: "dept" */ /*==============================================================*/ create table dept ( deptno NUMBER(11) not null, dname VARCHAR2(15) not null, loc VARCHAR2(15) not null, constraint PK_DEPT primary key (deptno) ); /*==============================================================*/ /* Table: "emp" */ /*==============================================================*/ create table emp ( empno NUMBER(11) not null, deptno NUMBER(11), ename VARCHAR2(15) not null, sal NUMBER(11) not null, job VARCHAR2(15) not null, mgr NUMBER(11) not null, hirdate DATE not null, comm NUMBER(11) not null, constraint PK_EMP primary key (empno) ); /*==============================================================*/ /* Table: salgrade */ /*==============================================================*/ create table salgrade ( grade NUMBER(11) not null, losal NUMBER(11) not null, hisal NUMBER(11) not null, constraint PK_SALGRADE primary key (grade) ); alter table emp add constraint FK_EMP_REFERENCE_DEPT foreign key (deptno) references dept (deptno); 三、测试要求及语句 /** *公司工资最高的员工列表 子查询 */ select t.ename,t.sal from emp t where t.sal = ( select max(sal) from emp ) /** *查询每一个员工的经理人及自己的名字 */ select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno) /** *查询公司平均薪水的等级 */ select s.grade from salgrade s where (select avg(t.sal) from emp t) between s.losal and s.hisal /** *求部门中那些人的工资最高 */ select d.dname,ename,sal from (select t.deptno,ename,sal from (select deptno,max(sal) as max_sal from emp group by deptno) e join emp t on (e.deptno = t.deptno and t.sal = max_sal) ) et join dept d on (d.deptno = et.deptno) /** *查询部门平均薪水的等级 */ select d.dname,avg_sal,grade from (select deptno,avg_sal,grade from (select deptno,avg(sal) as avg_sal from emp group by deptno) e join salgrade s on (e.avg_sal between s.losal and s.hisal ) ) es join dept d on (es.deptno = d.deptno) /** *求部门的平均薪水等级 */ select deptno,avg(grade) from (select deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t group by t.deptno /** * 求那些人是经理人 */ select ename from emp e where empno in (select distinct mgr from emp) /** *不准用组函数 求薪水的最高值 */ select ename from emp where empno not in (select distinct e1.empno from emp e1 join emp e2 on (e1.sal<e2.sal) ) /** *平均薪水最高的部门编号与名称 */ select d.deptno,dname from (select deptno,avg(sal) avg_sal from emp group by deptno) t1 join dept d on (d.deptno = t1.deptno) where avg_sal = (select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno) t2 ) /** *求平均薪水的等级最低的部门名称 */ select dname from dept d where d.deptno in ( select deptno from ( select deptno,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t1 join salgrade g on (avg_sal between g.losal and g.hisal) ) t2 where t2.grade = ( select min(grade) from ( select deptno,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t1 join salgrade g on (avg_sal between g.losal and g.hisal) ) t3 ) ) /** *求部门经理人中平均薪水最低的部门名称 */ select d.dname,t1.avg_sal from dept d join ( select deptno,avg(sal) avg_sal from ( select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on (e1.mgr = e2.empno) ) t group by deptno ) t1 on (d.deptno = t1.deptno) where avg_sal = ( select min(avg_sal) from ( select deptno,avg(sal) avg_sal from ( select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on (e1.mgr = e2.empno) ) t group by deptno ) ) /** *求必普通员工的最高薪水还要高的经理人名称 */ select ename from ( select e2.ename,e2.empno,e2.sal from emp e1 join emp e2 on (e1.mgr = e2.empno) ) t where t.sal > ( select max(e.sal) from emp e where e.empno not in ( select e1.mgr from emp e1 join emp e2 on (e1.mgr = e2.empno) ) ) /** *求薪水最高的第6名到10名雇员 */ SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM (select e1.ename,e1.sal from emp e1 order by e1.sal desc)) A WHERE ROWNUM <= 10 ) WHERE RN >= 6