oracle复杂查询练习题(二)
o t4 values('WhatIs
Java','1','99');
insert into t4 values('水王','76','981');
insert into t4 values('新浪网','65','96');
insert into t4 values('牛人','22','9');
insert into t4 values('中国队','64','89');
insert into t4 values('信息','66','66');
insert into t4 values('太阳','53','66');
insert into t4 values('中成药','11','33');
insert into t4 values('西洋参','257','26');
insert into t4 values('大拿','33','23');
www.2cto.com
/*方案一*/
select * from t4 order by cast(总积分 as int) desc
/*方案二*/
select * from t4 order by to_number(总积分) desc;
www.2cto.com
5.得出所有人（不区分人员）每个月及上月和下月的总收入
Sql代码
create table t5 ( tmonth int,
tname varchar2(10),
income number
)
insert into t5 values('08','a',1000);
insert into t5 values('09','a',2000);
insert into t5 values('10','a',3000);
/*方案一*/
select o.tmonth,sum(o.income) as cur,(select sum(t.income) from t5 t where t.tmonth=(o.tmonth+1) group by t.tmonth) as next,
(select sum(t.income) from t5 t where t.tmonth=(o.tmonth-1) group by t.tmonth) as last
from t5 o where o.tmonth=2 group by o.tmonth
/*方案二*/
select tmonth as 月份 ,tname as 姓名,sum(income) as 当月工资,
(select sum(income)
from t5
where tmonth = to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))-1) AS 上月工资 ,
(select sum(income)
from t5
where tmonth = to_number(substr(to_char(sysdate,'yyyy-mm-dd'),7,1))+1) AS 下月工资
from t5 where tmonth=substr(to_char(sysdate,'yyyy-mm-dd'),7,1)
group by tmonth,tname
6.根据现有的学生表，课程表，选课关系表，查询一。没有修过李明老师的课的学生，查询二，既学过a课程，又学过b课程的学生姓名
www.2cto.com
Sql代码
S表 [SNO,SNAME]--学生表
C表 [CNO,CNAME,CTEATHER] --课程表
SC表 [SNO，CNO，SCGRADE] --选课关系表
查询一：没有修过李明老师的课的学生的姓名
select sname from s where not exists
(select*from sc,c where sc.cno=c.cno and c.cteather='李明' and sc.sno=s.sno)
查询二：既学过a课程，又学过b课程的学生姓名
SELECT S.SNO,S.SNAME
FROM S,(
SELECT SC.SNO
FROM SC,C
WHERE SC.CNO=C.CNO
AND C.CNAME IN('a','b')
GROUP BY SNO
)SC WHERE S.SNO=SC.SNO
www.2cto.com
查询三： 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
SELECT S.SNO,S.SNAME,AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
FROM SC
WHERE SCGRADE <60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME