--求部门的平均薪水的等级
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)