大三门必修数据库三次上机答案.pdfVIP

--IntermediateSQL-1

/*

1.Findthesectionswhichhavetheminimumenrollmentamong

sectionsregisteredbystudents.Foreachsectionassuch,

informationdisplayedshouldinvolve:

#Identifierofsection(i.e.theprimarykeyforsection)

#Coursenamecorrespondingtothesection

#Enrollmentofthesection(enrollmentisthealiasfor

thenumberofstudentswhoregisteredforthesection)

#TOPkeywordinSQLServerisdenied.

*/

selectT.course_id,T.sec_id,T.semester,T.year,T.NUM,course.title

from(selectdistinctcourse_id,sec_id,semester,year,count(ID)asNUM

fromtakes

groupbycourse_id,sec_id,semester,year)asT,

(selectMin(NUM)asNUM2

fromcoursejoin(selectdistinct

course_id,sec_id,semester,year,count(ID)asNUM

fromtakes

groupbycourse_id,sec_id,semester,year)asQ

onQ.course_id=course.course_id

)asS,

course

whereT.NUM=NUM2andT.course_id=course.course_id

selectH.course_id,sec_id,title,take_num

from(selectcourse_id,sec_id,take_num

from(selectcourse_id,sec_id,semester,year,count(*)astake_num

fromtakes

groupbycourse_id,sec_id,semester,year)asH

wheretake_numin(selectmin(take_num)from(select

course_id,sec_id,semester,year,count(*)astake_num

fromtakes

groupbycourse_id,sec_id,semester,year)as

T))asH

leftjoincourseonH.course_id=course.course_id

withtemp(course_id,sec_id,semester,year,num,title)as(

selecttakes.course_id,takes.sec_id,takes.semester,takes.year,

count(ID)asnum,course.title

fromtakes,course

wheretakes.course_id=course.course_id

groupbytakes.course_id,takes.sec_id,takes.semester,

takes.year,course.title

)

selectcourse_id,sec_id,semester,year,num,title

fromtempwherenum=(

selectmin(num)fr