(1)求每个雇员的姓名，工资，工资等级
SQL> select ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between
salgrade.losal and salgrade.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
ADAMS 1100 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
ALLEN 1600 3
TURNER 1500 3
JONES 2975 4
BLAKE 2850 4
CLARK 2450 4
ENAME SAL GRADE
---------- ---------- ----------
SCOTT 3000 4
FORD 3000 4
KING 5000 5
已选择14行。
(2)求每个员工的姓名，部门名，部门号
SQL> select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
ENAME DEPTNO DNAME
---------- ---------- --------------
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
已选择14行。
(3)求每个人的名字和他的经理人的名字，用外连接把KING也取出来
SQL> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+);
ENAME ENAME
---------- ----------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
KING
TURNER BLAKE
ADAMS SCOTT
ENAME ENAME
---------- ----------
JAMES BLAKE
FORD JONES
MILLER CLARK
已选择14行。
(4)求公司中谁的薪水最高
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
----------
KING
(5)求薪水在部门的平均薪水之上的雇员姓名和及薪水
SQL> select ename,sal from emp where sal>=(select avg(sal) from emp);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
已选择6行。
(6)求每个部门中薪水最高的雇员编号，雇员姓名，部门编号，部门名
SQL> select e.empno,e.ename,d.deptno,d.dname,t.maxsal from emp e,dept d,(select
max(sal) maxsal,deptno from emp group by deptno) t where e.sal=t.maxsal and t.de
ptno=d.deptno and e.deptno=t.deptno;
EMPNO ENAME DEPTNO DNAME MAXSAL
---------- ---------- ---------- ---
----------- ----------
7839 KING 10 ACCOUNTING
5000
7788 SCOTT 20 RESEARCH 3000
7902 FORD 20 RESEARCH 3000
7698 BLAKE 30 SALES 2850
(7)求部门平均薪水的等级
SQL> select grade,deptno from salgrade s,(select avg(sal) avgsal,deptno from emp
group by deptno) t where t.avgsal between s.losal and s.hisal;
GRADE DEPTNO
---------- ----------
4 10
4 20
3 30
(8)求部门平均的薪水等级
SQL> select avg(s.grade),e.deptno from emp e,salgrade s where e.sal between s.lo
sal and s.hisal group by e.deptno;
AVG(S.GRADE) DEPTNO
------------ ----------
3.66666667 10
2.8 20
2.5 30
(9)雇员中有哪些人是经理人
SQL> select distinct b.ename from emp a,emp b where a.mgr=b.empno;
ENAME
----------
BLAKE
CLARK
FORD
JONES
KING
SCOTT
已选择6行。
(10)求部门经理人中平均薪水最低的部门名称
SQL> create view yy as select avg(sal) asal,d.dname dname from dept d,(select di
stinct b.ename,b.deptno,b.sal from emp a,emp b where a.mgr=b.empno) t where d.de
ptno=t.deptno group by t.deptno,d.dname;
视图已建立。
SQL> select dname from yy where asal=(select min(asal) from yy);
DNAME
--------------
SALES