scott用户下数据库查询练习及答案

AftQL练习:
1.
求工资高于公司平均工资的员工。

1 SELECT
2 ename,sal
3 from
4 emp
5 where
6* sal>(select avg(nvl(sal,0)) avgsal from emp)
SQL> /

ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000

已选择6行。
-----------------------------------------------------------------------------------

2.
求工资最高的员工姓名和工资（用组函数）。

1 select
2 ename,sal
3 from
4 emp
5 where
6* sal=(select max(nvl(sal,0)) from emp)
SQL> /

ENAME SAL
---------- ----------
KING 5000
-----------------------------------------------------------------------------------

3.
求工资最高的员工姓名和工资（不允许用组函数）。

1 select
2 ename,sal
3 from
4 emp
5 where
6* sal not in(select distinct e.sal from emp e join emp e2 on(e.sal)
SQL> /

ENAME SAL
---------- ----------
KING 5000

----------------------------------------------------------------------------------

4.
求工资高于所在部门平均工资的员工。

1 select
2 e.ename,e.sal
3 from
4 emp e
5 where
6 e.sal>(select
7 avg(nvl(e1.sal,0))
8 from
9 emp e1
10* where e.deptno=e1.deptno)
SQL> /

ENAME SAL
---------- ----------
%allen 1600
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000

已选择6行。
-----------------------------------------------------------------------------------------

5.
求平均工资最高的部门的部门编号和部门名称；

1 select deptno,dname,avgsal from
2 (select e.deptno,d.dname,avg(nvl(sal,0)) avgsal from emp e,dept d where
e.deptno=d.deptno group by e.deptno,d.dname)
3 where
4* avgsal=(select max(avgsal)from(select e.deptno,d.dname,avg(nvl(sal,0)) a
vgsal from emp e,dept d where e.deptno=d.deptno group by e.deptno,d.dname))
SQL> /

DEPTNO DNAME AVGSAL
---------- -------------- ----------
10 ACCOUNTING 2916.66667
-----------------------------------------------------------------------------------------

6.
列出各部门工资最高的员工信息（含部门编号、部门名称、员工姓名、员工工资，并按部门编号排序）。

1 select e.deptno,d.dname,e.ename,e.sal
2 from
3 (select
4 deptno,max(nvl(sal,0)) maxsal
5 from emp
6 where sal is not null
7 group by deptno),emp e,dept d
8 where
9 e.deptno=d.deptno
10 and
11 e.sal=maxsal
12* order by e.deptno
SQL> /

DEPTNO DNAME ENAME SAL
---------- -------------- ---------- ----

------
10 ACCOUNTING KING 5000
20 RESEARCH FORD 3000
20 RESEARCH SCOTT 3000
30 SALES BLAKE 2850
-------------------------------------------------------------------------------

7.
列出各部门平均工资及对应的工资等级。

1 select deptno,avgsal,grade
2 from (select deptno,round(avg(nvl(sal,0)),2) avgsal from emp group by deptn
o),salgrade
3 where
4 avgsal between losal
5 and hisal
6* order by avgsal
SQL> /

DEPTNO AVGSAL GRADE
---------- ---------- ----------
30 1566.67 3
20 2175 4
10 2916.67 4
-------------------------------------------------------------------------------------

8.列出各部门工资平均等级。

1 select deptno,avg(grade) avggrade
2 from
3 (select deptno,sal,grade
4 from emp,salgrade
5 where sal between losal and hisal
6 order by deptno)
7* group by deptno
SQL> /

DEPTNO AVGGRADE
---------- ----------
30 2.5
20 2.8
10 3.66666667
----------------------------------------------------------------------------------

10.
列出经理员工（拥有下级员工的员工）。

1 select
2 distinct e1.mgr,e.ename
3 from
4 (select empno,ename
5 from emp)e,emp e1
6 where
7* e.empno=e1.mgr
SQL> /

MGR ENAME
---------- ----------
7566 JONES
7839 KING
7782 CLARK
7902 FORD
7698 BLAKE
7788 SCOTT

已选择6行。

--------------------------------------------------------------------------------
11.
求经理员工中平均工资的最低的部门名称。


1 with basesql as(select e.deptno,e.ename,e.sal
2 from emp e
3 where e.empno in(select distinct mgr from emp where mgr is not null))
4 select a.deptno,d.dname,avg(a.sal) avgsal
5 from
6 basesql a,dept d
7 where
8 a.deptno = d.deptno
9 group by
10 a.deptno,d.dname
11 having
12 avg(a.sal)=
13 (
14 select min(avg(a.sal)) minsal
15 from
16 basesql a
17 group by deptno
18* )
SQL> /

DEPTNO DNAME AVGSAL
---------- -------------- ----------
30 SALES 2850

------------------------------------------------------------------------------------------------

12.
求平均工资的级别最低的部门名称。

SQL> with basesql as(select b.*,s.grade
2 from(
3 select a.deptno,a.dname,avg(nvl(a.sal,0)) avgsal
4 from(
5 select e.deptno,d.dname,e.empno,e.ename,e.sal
6 from emp e,dept d
7 where e.deptno=d.deptno)a
8 group by a.deptno,a.dname)b,salgrade s
9 where b.avgsal between losal and hisal)
10 select * from basesql
11 where grade=(select min(grade) from basesql);

DEPTNO DNAME AVGSA

L GRADE
---------- -------------- ---------- ----------
30 SALES 1566.66667 3
--------------------------------------------------------------------------------------
13.
求比普通员工最高工资高的经理员工姓名。

1 select a.*
2 from
3 (select empno,ename,sal mgrsal from emp where empno in(select mgr from emp
where mgr is not null))a
4 where
5 mgrsal>
6 (select max(nvl(sal,0)) maxsal from
7* (select empno,ename,sal from emp where empno not in(select mgr from emp whe
re mgr is not null)))
SQL> /

EMPNO ENAME MGRSAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000

已选择6行。

---------------------------------------------------------------------------------------------------
14.
求工资最高的前5名员工姓名。

SQL> select * from (select ename,sal from emp where sal is not null order by sal
desc) where rownum<=5;

ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850

---------------------------------------------------------------------------------------------------
15.
求工资最高的前6-10名员工姓名。

1* select * from (select rownum myrownum,a.* from (select ename,sal from emp w
here sal is not null order by sal desc)a) where myrownum between 6 and 10
SQL> /

MYROWNUM ENAME SAL
---------- ---------- ----------
6 CLARK 2450
7 ALLEN 1600
8 TURNER 1500
9 MILLER 1300
10 WARD 1250