(1)查詢C01課程成績不為Null的學生的姓名和成績。

分析：因為涉及到課程成績和學生姓名，需要對student表和sc表進行連接。

代碼：

select?sname,grade?from?student,sc?where?student.sno=sc.sno?and?cno='C01'?and?grade?is?not?null;

結果：

SNAME?????????????????????????????????????????????? GRADE

-------------------- ---------------------------------------

葛靈?????????????????????????????????????????????????????68

岳林月?????????????????????????????????????????????????60

姬勝俊?????????????????????????????????????????????????54

馬源?????????????????????????????????????????????????????40

翁印?????????????????????????????????????????????????????98

(2)查詢平均分高于70分的女同學的學號，姓名，平均成績。

select?student.sno?,sname,avg(grade)??from?sc?,studentwhere?student.sno=sc.sno?and?ssex='f'

group?by?student.sno,sname?having?avg(grade)>70;

SNO????????SNAME????????????????AVG(GRADE)----------?--------------------?----------001????????葛靈???????????????????????73.2

(3)查詢ma系學生“英語”課程的最高分，列出姓名和最高分。

select?*?from(select?sname,grade?from?sc,course,student?wheresc.sno=student.sno?and?course.cno=sc.cno?and?sdept='MA'?and?cname='英語'and?grade?is?not?nullorder?by?grade?desc)where?rownum=1;SNAME??????????????????????????????????????????????????GRADE--------------------?---------------------------------------葛靈??????????????????????????????????????????????????????86

(4)查詢總學分在8分以上的學生的平均成績，列出學號，平均成績

select?sc.sno,sum(credit),avg(grade)?from?course,scwhere?course.cno=sc.cno?group?by?sc.sno?having?sum(credit)>8;SNO????????SUM(CREDIT)?AVG(GRADE)----------?-----------?----------001?????????????????18???????73.2002?????????????????15?????????68003?????????????????18?????????70004?????????????????10?65.3333333005??????????????????9?????????67

(5)查詢所有18歲以上學生的選課門數(shù)，列出學號，姓名，年齡，選課門數(shù)。

select?sc.sno,sname,sage,count(cno)?from?sc,studentwhere?student.sno=sc.sno?and?sage>18group?by?sc.sno,sname,sage;SNO????????SNAME????????????????????????????????????????????????SAGE??? COUNT(CNO)----------?--------------------?---------------------------------------?----------001????????葛靈??????????????????????????????????????????????????????19?????????5002????????岳林月????????????????????????????????????????????????????25???????? 4004????????馬源??????????????????????????????????????????????????????20??????????3005????????翁印??????????????????????????????????????????????????????23??????????3

(6)刪除所有MA系不及格的選課信息。

delete?sc?where?sc.sno?in

(select?sc.sno?from?student,sc?where?student.sno=sc.sno?and?sdept='MA'?and?grade<60)and?sc.cno?in

(select?sc.cno?from?student,sc?where?student.sno=sc.sno?and?sdept='MA'?and?grade<60);9?rows?deleted

(7)將平均分不及格的學生成績修改為空。(以70分為例)

update?sc?set?grade=null?where?sno?in(select?sno?from?(select?sno?,avg(grade)g??from?sc?group?by?sno)?where?g<70);10?rows?updated

總結

以上是生活随笔為你收集整理的oracle怎么查询成绩学号,oracle学习（简单查询）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。