数据库实验作业6.docxVIP

1.

selectstudent.sno,sname,avg(score)

fromstudent,sc

wherestudent.sno=sc.sno

groupbysno

havingavg(score)80;

2.

selectsno

fromsc

wherecno=any

(selectcno

fromsc

wheresno)

groupbysno

havingcount(cno)=(selectcount(cno)fromscwheresno;

3.

selectstudent.sno,sname,sc.cno,cname,score

fromstudent,course,sc

wherestudent.sno=sc.snoand

sc.cno=101and

sc.cno=course.cnoandscorein(selectmax(score)

fromsc

wheresc.cno=101

groupbycno);

4.

selectstudent.sno,student.sname,sc1.score,sc2.score,sc1.score-sc2.score

fromscassc1,scassc2,student

wheresc1.scoresc2.scoreand

sc1.sno=sc2.snoand

student.sno=sc1.snoand

student.sno=sc1.snoand

sc1.cno=101and

sc2.cno=102;

5.

selectstudent.sno,sname,score

fromstudent,sc

wherestudent.sno=sc.snoand

cno=(selectcno

fromcourse

wherecname=形势与政策1)

groupbysno

orderbysnodesc;

6.

selectstudent.sno,sname,min(sc.score),course.cname

fromstudent,sc,course

wherestudent.sno=sc.snoand

course.cno=sc.cno

groupbystudent.sno

orderbystudent.snodesc;

7.

selectstudent.sno,sname

fromstudent,sc

wherestudent.sno=sc.snoand

cno=(selectcno

fromtc

wheretno=001);

8.

selectstudent.sno,sname,min(score)

fromstudent,sc

wherestudent.sno=sc.sno

groupbystudent.sno

orderbystudent.snodesc;

9.

selectstudent1.sno,student1.sname,student2.sno,student2.sname,sc1.score-sc2.score

fromscassc1,scassc2,studentasstudent1,studentasstudent2

wheresc1.scoresc2.scoreand

sc1.sno!=sc2.snoand

student1.sno=sc1.snoand

student2.sno=sc2.snoand

sc1.cno=101and

sc2.cno=101;

10.

selectstudent.sno,sname,cno,score

fromstudent,sc

wherestudent.sno=sc.snoand

ssex=男and

104notin(selectcno

fromsc

wheresno=student.sno)and

105notin(selectcno

fromsc

wheresno=student.sno);

11.

fromstudent,sc

wherestudent.sno=sc.snoand

score=(selectmax(score)

fromsc

wherecno=101

)and

cno=101;

12.

selectstudent.sno,sname,count(cno),avg(score)

fromstudent,sc

wherestudent.sno=sc.sno

groupbystudent.sno

havi