Oracle复习之五

--求部门的平均薪水的等级
select deptno,avg(grade) from
(select deptno,ename,grade from emp
join salgrade s on emp.sal between s.losal and s.hisal)
group by deptno;

DEPTNO     AVG(GRADE)
30     2.5
20     2.8
10     3.66666667
--雇员中有哪些是经理人
select ename from emp where empno in(select mgr from emp);
或select ename from emp where empno in(select distinct mgr from emp);更有效

ENAME
FORD
BLAKE
KING
JONES
SCOTT
CLARK

--不准用组函数，求薪水最高值
select distinct sal from emp where sal not in
(
select distinct e1.sal from emp e1
join emp e2 on (e1.sal < e2.sal )
);

SAL
5000

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

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 hisal)
   ) t1
  join dept on (t1.deptno =dept.deptno)
where t1.grade=
(
  select min(grade) 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 hisal)
)
)

DNAME     DEPTNO     GRADE     AVG_SAL
SALES     30     3     1566.66667

--薪水最高的高的第3到6名雇员

select * from
(
select rownum rn,empno, ename ,sal from
(
select e.empno,ename,sal from emp  e
join  (select empno from emp where empno not in
       (select mgr from emp where mgr is not null)) t
     on e.empno= t.empno
   order by sal desc
)
)where rn >=3 and rn <6;

RN  EMPNO  ENAME  SAL 
3  7934  MILLER  1300 
4  7521  WARD  1250 
5  7654  MARTIN  1250 

rownum只能用于<,<=这么用
select rownum from
(
select rownum r ,ename from emp
) where r>10;

求薪水最高的前5人
select ename,sal from
(
select ename,sal from emp order by sal desc
)where rownum<=5;
ENAME     SAL
KING     5000
SCOTT     3000
FORD     3000
JONES     2975
BLAKE     2850

薪水最高的5-10
select ename,sal from
(
select rownum r,ename,sal from emp order by sal desc
)where r<=10 and r>==5;

ENAME     SAL
KING     5000
SCOTT     3000
BLAKE     2850
CLARK     2450
TURNER     1500
MARTIN     1250

 

SQL面试题：
有3个表S,C,SC
S(SNO,SNAME)代表(学号，姓名)
C(CNO,CNAME,CTEACHER)代表(课号，课名，教师)
SC(SNO,CNO,SCGRADE)代表(学号，课号成绩)

问题:
1.找出没选过"黎明"老师的所有学生姓名。

select SNAME from
S join SC on (S.SNO = SC.SNO) join
C on (C.CNO = SC.CNO) where Teacher <> '黎明';
/** 我写的
select SNAME from
(S join SC on (S.SNO = SC.SNO) ) T
where T.CNO in (select CNO from c where CTEACHER <> '黎明')
*/

2.列出2门以上(含2门)课不及格的学生的姓名及成绩。
select sname from s where sno in
(
select sno,count(*) from sc
where scgrade<60
group by sno having count(*) >= 2
);

3.即学过1号课程又学过2号课程的所有学生的姓名。
select sname from S
wehre sno in
(select sno from sc cno=1 t1
join seclec son from sc cno=2 t2 on t1.sno=t2.sno)