oracle宋锋所教学生学号,Oracle DBA练习题

修改.buf文件

ed

--求部门中哪些人的薪水最高

select ename, sal from emp

join (select max(sal) max_sal, deptno from

emp group by deptno) t

on (emp.sal = t.max_sal and emp.deptno =

t.deptno)

--求部门平均薪水的等级

select deptno, avg_sal, grade from

(select deptno, avg(sal) avg_sal from emp

group by deptno) t

join salgrade s on (t.avg_sal between

s.losal and s.hisal)

--求部门平均的薪水等级

select deptno, avg(grade) from

(select deptno, ename, grade from emp join

salgrade s on (emp.sal between s.losal and s.hisal)) t

group by deptno

--雇员中有哪些人是经理人

select ename from emp where empno in (select

distinct mgr from emp);

--不准用组函数，求薪水的最高值(面试题)

(使用自连接将本身和左边薪水小于右边建立连接，如果为最大值，则连接不上)

select distinct e1.sal from emp e1 join emp

e2 on (e1.sal < e2.sal);

正确写法：

select distinct sal from

emp where sal not in

2 (select distinct e1.sal from emp e1 join

emp e2 on (e1.sal < e2.sal));

--求平均薪水最高的部门的部门编号

1.求每个部门的平均薪水

select avg(sal), deptno

from emp group by deptno;

2.求最高值

select

max(avg_sal) from

2

(select avg(sal) avg_sal, deptno from emp group

by deptno);

3.求平均薪水最高的部门的部门编号

select deptno, avg_sal

from

2 (select avg(sal) avg_sal, deptno from emp

group by deptno)

3 where avg_sal =

4 (select max(avg_sal) from

5  (select avg(sal) avg_sal, deptno from emp group

by deptno)

6* )

7  /

--求平均薪水最高的部门的部门名称

select dname from dept where deptno =

(

2  select deptno

from

3 (select avg(sal) avg_sal, deptno from emp

group by deptno)

4 where avg_sal =

5 (select max(avg_sal) from

6  (select avg(sal) avg_sal, deptno from emp group

by deptno)

7 )

8 )

--求平均薪水的等级最低的部门的部门名称

select dname, t1.deptno, grade, avg_sal

from

(

select deptno, grade,

avg_sal from

(select deptno, avg(sal) avg_sal from emp group

by deptno) t

join salgrade s on

(t.avg_sal between s.losal and s.hisal)

) t1

join dept on (t1.deptno = dept.deptno)

where t1.grade = (

select min(grade) from

(select deptno, avg(sal)

avg_sal from emp group by deptno) t

join salgrade s on

(t.avg_sal between s.losal and s.hisal)

)

)

视图(view)：视图就是一张表，一个子查询

视图一般以v$开头

权限问题：

conn system/123456 as

sysdba;

grant create table,

create view to scott;

SQL语句如下：

create

view v$dept_avg_sal_info as

select deptno, grade, avg_sal from

(select deptno, avg(sal) avg_sal from emp group

by deptno) t

join

salgrase s on (t.avg_sal between s.losal and s.hisal);

取数据：

select * from v$_dept_avg_sal_info;

视图叫做虚表(数据仍然存在原来的表中)；

完整代码如下：

conn system/123456 as

sysdba;

grant create table,

create view to scott;

create

view v$dept_avg_sal_info as

select deptno, grade, avg_sal from

(select deptno, avg(sal) avg_sal from emp group

by deptno) t

join

salgrase s on (t.avg_sal between s.losal and s.hisal);

select

dname, t1.deptno, grade, avg_sal from

v$_dept_avg_sal_info t1

join

dept on (t1.deptno = dept.deptno)

where t1.grade =

(

select min(grade) from

v$_dept_avg_sal_info

)

--求部门经理人中平均薪水最低的部门名称(思考题)

--求比普通员工的最高薪水还要高的经理人名称

select ename from emp

where empno in (select distinct mgr from emp

where mgr is not null)

and

sal >

(

select max(sal) from emp

where empno not in

(select distinct mgr

from emp where mgr is not null)

)

--求薪水最高的前5名雇员

rownum 相当于每行上有个行号

select empno, ename from emp where rownum

<= 5;

rownum只能和

select rownum r, ename from emp;

使用=，>,

>=时只能使用子查询

select ename from (select rownum r, ename

from emp) where r > 10;

select ename, sal from

(select ename, sal from emp order by sal

desc) where rownum <= 5;

--求薪水最高的第6到第10名雇员(重点掌握)

select ename, sal from(

select ename, sal , rownum r from

(select ename, sal from

emp order by sal desc)

)

where r >=6 and

r<=10;

此方法可用于论坛等分页显示

--面试题：比较效率

select * from emp where

deptno = 10 and ename like '%A%';

select * from emp where

ename like '%A%' and deptno = 10;

第一种效率高。因为比较数字效率高一些。数据库如果做了优化则不好说。

SQL面试题

有3个表S, C, SC

8(8NO, 8NAME) 代表(学号，姓名)

C(CNO, CNAME, CTEACHER) 代表 (课号，课名，教师)

SC(SNO, CNO, SCGRADE) 代表 (学号，课号成绩)

问题：

1, 找出没选过“黎明”老师的所有学生姓名

2，列出2门以上(含2门)不及格学生姓名及平均成绩

3，既学过1号课程又学过2号课程所有学生的姓名。

请用标准SQL语言写出答案，方言也行(请说明使用什么方言)。

1.select sname from s join sc on(s.sno =

sc.sno) join c (c.cno = sc.cno) where c.cteacher

<> 'liming';

2.select sname where sno in (select sno from

sc where scgrade < 60 group by sno hacing count(*)

>= 2);

3.select sno from sc where cno = 1 and sno

in < select sno from sc where cno = 2);

或者

select sname from s where sno in (select sno

from sc where cno = 1 and cno in (select distinct sno frm sc where

cno = 2));