搜索
实验四SQL查询1一、建立university数据库:CreatetableStudent(Snumchar(6)primarykeycheck(Snumlike's%'),Snamevarchar(10),Sexchar(2)check(sex='男'orsex='女'),deptchar(30),BirthdayDatetime,telephonevarchar(12)check(telephonelike'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))goCreatetablecourse(Cnumchar(6)primarykeycheck(Cnumlike'c%'),Cnamevarchar(20),Creditssmallintcheck(Credits0andCredits8),descrvarchar(10),deptvarchar(20),textbookvarchar(40))goCreatetableSections实验四SQL查询2(Secnumchar(8)primarykeycheck(secnumlike'[0-9][0-9][0-9][0-9][0-9]%'),Cnumchar(6),pnumchar(6)check(pnumlike'p%'),foreignkey(Cnum)referencesCourse(Cnum)onupdatecascade)goCreatetableSC(Snumchar(6),Secnumchar(8)check(secnumlike'[0-9][0-9][0-9][0-9][0-9]%'),scoreSmallintcheck(score=0andscore=100),primarykey(Snum,Secnum),foreignkey(Snum)referencesStudent(Snum)onupdatecascade,foreignkey(Secnum)referencesSections(Secnum)onupdatecascade)go--往student表中插入数据insertintostudentvalues('s001','赵剑','男','计算机','1987/3/25','010-11111111')insertintostudentvalues('s002','王谦','男','交通运输','1986/1/1','021-55555555')insertintostudentvalues('s003','孙启明','男','土木工程','1987/4/1','021-44444444')insertintostudentvalues('s004','宇帆','男','机械工程实验四SQL查询3','1987/9/17','021-33333333')insertintostudentvalues('s005','李晓静','女','生物工程','1988/6/21','030-22222222')insertintostudentvalues('s006','金之林','女','计算机','1988/9/11','040-66666666')insertintostudentvalues('s007','张东晓','男','城市规划','1987/8/2','050-77777777')insertintostudentvalues('s008','海琳','女','城市规划','1988/5/23','070-88888888')go--往Course表中插入数据insertintocoursevalues('c116','大学英语','6','必修课','外语系','《大学英语》，同济大学出版社')insertintocoursevalues('c120','高等数学','6','必修课','数学系','《高等数学》，复旦大学出版社')insertintocoursevalues('c126','大学物理','3','必修课','物理系','《大学物理》，高等教育出版社')insertintocoursevalues('c130','数据库技术','3','限选课','计算机系','《数据库技术与应用》，高等教育出版社')insertintocoursevalues('c132','多媒体技术','3','限选课','计算机系','《多媒体技术与应用》，清华大学出版社')insertintocoursevalues('c135','VB程序设计','3','限选课','计算机系','《VB.NET程序设计》，高等教育出版社')go--往Sections表中插入数据insertintosectionsvalues('11601','c116','p001')insertintosectionsvalues('11602','c116','p002')insertintosectionsvalues('12001','c120','p003')insertintosectionsvalues('12002','c120','p003')insertintosectionsvalues('12601','c126','p004')insertintosectionsvalues('13001','c130','p005')insertintosectionsvalues('13002','c130','p006')insertintosectionsvalues('13201','c132','p007')insertintosectionsvalues('13501','c135','p007')go--往SC表中插入数据insertintoSCvalues('s001','11601',77)insertintoSCvalues('s001','12001',80)insertintoSCvalues('s001','12601',89)insertintoSCvalues('s001','13002',90)insertintoSCvalues('s001','13201',92)insertintoSCvalues('s001','13501',95)insertintoSCvalues('s002','11602',90)实验四SQL查询4insertintoSCvalues('s002','12601',88)insertintoSCvalues('s002','13201',98)insertintoSCvalues('s003','11601',90)insertintoSCvalues('s003','12002',94)insertintoSCvalues('s003','12601',88)insertintoSCvalues('s004','11601',89)insertintoSCvalues('s004','13001',90)insertintoSCvalues('s004','13201',92)insertintoSCvalues('s004','13501',89)insertintoSCvalues('s005','11602',56)insertintoSCvalues('s006','11601',88)insertintoSCvalues('s006','12601',78)insertintoSCvalues('s007','11602',90)insertintoSCvalues('s007','13201',95)insertintoSCvalues('s007','13501',50)insertintoSCvalues('s008','11601',89)insertintoSCvalues('s008','12001',90)insertintoSCvalues('s008','12601',93)go二、完成查询1．列出不及格记录学生名单(学号、姓名、课号、课名、成绩)。selectstudent.snum,student.sname,course.cnum,course.cname,sc.scorefromstudentinnerjoinsconstudent.snum=sc.snuminnerjoinsectionsonsc.secnum=sections.secnuminnerjoincourseonsections.cnum=course.cnumwheresc.score602．列出选修了计算机系课程的学生姓名和年龄。selectdistinctstudent.sname,year(getdate())-year(student.birthday)asage实验四SQL查询5fromstudentinnerjoinsconstudent.snum=sc.snuminnerjoinsectionsonsc.secnum=sections.secnuminnerjoincourseonsections.cnum=course.cnumwherecourse.deptlike'计算机系'3．查询选修了数据库技术课程的学生姓名和系别。selectstudent.sname,student.deptfromstudentinnerjoinsconstudent.snum=sc.snuminnerjoinsectionsonsc.secnum=sections.secnuminnerjoincourseonsections.cnum=course.cnumwherecourse.cnamelike'数据库技术%'4．列出选修所有课程的学生名称。selectsnamefromstudentwheresnumin(selectsnumfromscgroupbysnumhavingcount(*)=(selectcount(*)fromcourse))或selectsnum,sname实验四SQL查询6fromstudentwherenotexists(select*fromcoursewherenotexists(select*fromscjoinsectionsonsc.secnum=sections.secnumwheresc.snum=student.snumandsections.cnum=course.cnum))5．检索每门课程成绩都在80分以上（含80）的学生名单。selectsnum,snamefromstudentwheresnumnotin(selectdistinctsnumfromscwheresc.score80)或selectsnum,snamefromstudentwheresnumin(selectsnumfromscGroupbysnumHavingMIN(score)=80)实验四SQL查询76．检索获奖学金的学生名单：学号、姓名（每门课程在80分及以上，平均分数在90分及以上）。selectsnum,snamefromstudentwheresnumin(selectsnumfromscgroupbysnumhavingavg(score)=90)andsnumnotin(selectdistinctsnumfromscwherescore80)或selectsnum,snamefromstudentwheresnumin(selectsnumfromscgroupbysnumhavingmin(score)=80andavg(score)=90)7．检索选修了大学英语的学生名单（学号、姓名）和成绩，并按成绩从高到低排列。实验四SQL查询8selectstudent.snum,student.sname,sc.scorefromstudent