oracle作业二,Oracle作业

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

第五次

---1

select * from(select studno,avg(studscore) avgscore,count(*) countcourse

from studscoreinfo

group by studno

order by avgscore desc)

where rownum<=10

---2

select studno,studname,studsex,si.classid,classname from studinfo si,classinfo ci

where si.classid=ci.classid and studno in

(select studno from (select studno,avg(studscore) avescore

from studscoreinfo

group by studno

having count(*)>20

order by avescore desc)

where rownum<=10)

---3 不完整

select avg(studscore) avgscore,sum(studscore) sumscore,count(*) classcount,max(studscore) maxscore,min(studscore) minscore

from studscoreinfo si,courseinfo ci

where si.courseid=ci.courseid and coursetype in ('A','B','C','D11','D12')

---4

create view V_StudAvgScore

as

select si.studno,studname,

avg(studscore) as avgscore,

sum(studscore) as sumscore,

max(studscore) as maxscore,

min(studscore) as minscore,

count(*) countcourse

from studinfo si,studscoreinfo ss

where si.studno=ss.studno

group by si.studno

---5

select sas.studno,sas.studname,classname,avgscore,countcourse from V_studavgscore sas, classinfo ci,studinfo si

where sas.studno=si.studno and si.classid=ci.classid and(avgscore >=80 and avgscore<=85 or

avgscore >=60 and avgscore<=70)

---6错

select sas.studno,sas.studname,studsex,studbirthday,classname,avgscore,coursecount from V_studavgscore sas,studinfo si

where sas.studno=si.studno and studsex='男'

group by avgscore>80 and countcourse>30

---7

create sequence seq_newstudno

increment by 1

start with 10001

maxvalue 99999

nocache

nocycle;

select seq_newstudno.nextval from dual;

select seq_newstudno.currval from dual;

---8

create table studinfosequence

as

select seq_newstudno.nextval as studno,si.studname,studsex,studbirthday,ci.classname

from studinfo si,classinfo ci

where si.classid=ci.classid and classname='农经01'

---9

create view V_studavgscorecoursecountup20

as

select si.studno,studname,

sum(studscore) as sumscore,

max(studscore) as maxscore,

min(studscore) as minscore,

count(*) countcourse,

case when

count(*)>20 then (sum(studscore)-max(studscore)-min(studscore))/(count(*)-2)

else

sum(studscore)/count(*)

end as avgscore,

from studinfo si,studscoreinfo ss

where si.studno=ss.studno

group by si.studno,studname

---10

select si.studno,studname,studsex,studbirthday,avg(studscore) avgscore,

case when studscore>=90 then '优秀'

when studscore>=80 then '良好'

when studscore>=70 then '中等'

when studscore>=60 then '及格'

else '不及格' end as scorelevel

from studinfo si,studscoreinfo ss

where si.studno=ss.studno

group by si.studNo,studname,studSex,studscore,studbirthday;

---11 不对

select studscore,Case When avg(StudScore)>=90 then'优秀'

When avg(StudScore)>=80 then'良好'

When avg(StudScore)>=70 then'中等'

When avg(StudScore)>=60 then'及格'

else '不及格'

End AS 等级,

COUNT(*) 人数

from StudScoreInfo ss,courseinfo ci

where ss.courseid=ci.courseid and coursename='可视化程序设计'

group by Studscore;

---12

1.like

select * from studscoreinfo

where studno in(

select studno from(select * from studinfo

where studname like'__丽'))

2.substr

select * from studscoreinfo

where studno in(

select studno from(select * from studinfo

where substr(studname,3,1)='丽'))

---13

select studname,'牛'|| substr(studname,2)

from studinfo

where studname like '刘%';

---14

select studno,studsex,studbirthday from studinfo

group by studno,studsex,studbirthday

having count(*)>1;

---15

select avg(studscore) avgsocre from studscoreinfo

where studno in(

select studno from(select * from studinfo

where studbirthday=null

group by studno))

---16

select s.studno,studname,avg(studscore) avgscore

from studinfo s,studscoreinfo ssi

where s.studno=ssi.studno and studname like'王%'

group by s.studno,studname

having avg(studscore)>(

select avg(studscore)

from studscoreinfo ssi,studinfo s

where s.studno=ssi.studno and studname like '李%')