SQL查询：统计女学生人数及成绩分布.pdfVIP

(2)

selectCOUNT(*)as女学生人数

fromstudent

whereS=女

(3)selectstudent.sno,sname,s,sdept,cname,

gradefromstudent,course,SCwherestudent.sno=

SC.Snoandcourse.Cno=SC.Cno

(2)

selectCOUNT(*)as女学生人数

fromstudent

whereS=女

(3)selectstudent.sno,sname,s,sdept,cname,grade

fromstudent,course,SC

wherestudent.sno=SC.Snoand

course.Cno=SC.Cno

(4)

selectsname,cname,grade

来自student,course,sc其中student.sno=SC.Sno并且course.cno=

SC.Cno并且Grade并且Grade按Grade降序排列

80==90

(4)

selectsname,cname,grade

fromstudent,course,scwherestudent.sno=SC.Snoandcourse.cno=SC.Cno

and80=GradeandGrade=90

orderbyGradedesc

selectSnamefrom(selectCnamefrom(select*

fromSCwhereGrade=80andgrade)课程

=90

innerjoinSCon课程.cno=Sc.cno)学生inner

joinScon学生.sno=sc.sno(5)

selectSnamefrom(selectCnamefrom

(select*fromSCwhereGrade=80andgrade=90)

courseinnerjoinSConCourse.cno=Sc.cno)

studentinnerjoinSconStudent.sno=sc.sno

(5)

(6)

select*fromstudent

其中Sage=(selectSagefromstudentwhereSname=王小红)

selectsno,AVG(grade)as平均成绩from

SCwhereGradegroupbysno

=80

(6)

select*fromstudent

whereSage=(selectSagefromstudentwhereSname=王小红)

selectsno,AVG(grade)as平均成绩fromSC

whereGrade=80

groupbysno

(7)

更新SC表，设置Grade=0

其中Sno在(选择Sno从student表中，其中Sdept=计算机)

从SC表中选择所有记录

(7)

updateSCsetGrade=0

whereSnoin(selectSnofromstudentwhereSdept=计算机)

select*fromSC