同济大学数据库作业lab3.4

同济大学《数据库技术及应用》实验报告实验报告题目：创建数据库和数据表姓名：学号：年级：专业：指导教师：日期：2014年10月27日一．实验目的1.创建数据库2.创建数据表3.学会使用insert，delete，update命令4.掌握数据库的备份，还原二．实验内容（实验题目+运行界面截图+实现代码）实验三1.创建数据库2.创建数据表student，course，sc，sectionsCREATETABLEstudent(snumCHAR(4)NOTNULLPRIMARYKEYCHECK(snumlike's[0-9][0-9][0-9]'),snameVARCHAR(20),sexCHAR(2)CHECK(sexin('男','女')),deptVARCHAR(30),birthdayDATE,telephoneCHAR(13)CHECK(isnumeric(left(telephone,3))=1andisnumeric(right(telephone,8))=1))createtablecourse(cnumchar(4)primarykeycheck(cnumlike'c[0-9][0-9][0-9]'),cnameVARCHAR(30),creditssmallintcheck(creditsbetween0and8),descrvarchar(40),deptVARCHAR(30),textboxvarchar(40))createtablesections(secnumchar(5)primarykeynotnullcheck(secnumlike'[0-9][0-9][0-9][0-9][0-9]'),cnumchar(4)foreignkeyreferences.course,pnumchar(4)notnullcheck(pnumlike'p[0-9][0-9][0-9]'))createtablesc(snumchar(4)notnullforeignkeyreferences.student,secnumchar(5)notnullforeignkeyreferences.sections,scoreintcheck(scorebetween0and100)primarykey(snum,secnum))insertintostudentvalues('s001','赵剑','男','计算机','1994-3-25','010-11111111'),('s002','王谦','男','交通工程','1993-1-1','027-55555555'),('s003','孙启明','男','土木工程','1994-4-1','021-44444444'),('s004','宇帆','男','计算机','1994-9-17','021-33333333'),('s005','李晓静','女','生物工程','1995-6-21','030-22222222'),('s006','金之林','女','计算机','1995-9-11','040-66666666'),('s007','张东晓','男','城市规划','1994-8-2','050-77777777'),('s008','海琳','女','城市规划','1995-5-23','070-88888888')insertintocoursevalues('c116','大学英语','6','必修课','外语系','《大学英语》，同济大学出版社'),('c120','高等数学','6','必修课','数学系','《高等数学》，复旦大学出版社'),('c126','大学物理','3','必修课','物理系','《大学物理》，高等教育出版社'),('c130','数据库技术','3','限选课','计算机系','《数据库技术与应用》，高等教育出版社'),('c132','多媒体技术','3','限选课','计算机系','《多媒体技术与应用》，清华大学出版社'),('c135','VB程序设计','3','限选课','计算机系','《VB.NET程序设计》，高等教育出版社')insertintosectionsvalues('11601','c116','p001'),('11602','c116','p001'),('12001','c120','p002'),('12002','c120','p003'),('12601','c126','p004'),('13001','c130','p005'),('13002','c130','p006'),('13201','c132','p007'),('13501','c135','p007')insertintoscvalues('s001','11601','77'),('s001','12001','80'),('s001','12601','89'),('s001','13002','90'),('s001','13201','92'),('s001','13501','94'),('s002','11602','90'),('s002','12601','88'),('s002','13201','98'),('s003','11601','90'),('s003','12002','94'),('s003','12601','88'),('s004','11601','89'),('s004','13001','90'),('s004','13201','92'),('s004','13501','89'),('s005','11602','56'),('s006','11601','88'),('s006','12601','78'),('s007','11602','90'),('s007','13201','95'),('s007','13501','50'),('s008','11601','89'),('s008','12001','90'),('s008','12601','93')实验四1.不及格记录学生名单selectsnamefromstudent,scwherestudent.snum=sc.snumandsc.score602.选修计算机系课程学生姓名和年龄selectdistinctsname,year(GETDATE())-year(birthday)asagefromstudent,sc,sectionswherestudent.snumin(selectdistinctsnumfromsc,sectionswheresc.secnumin(selectsecnumfromsectionswherecnum='c130'orcnum='c132'orcnum='c135'))3.选修数据库技术学生姓名和系别selectdistinctsname,deptfromstudent,sc,sectionswherestudent.snumin(selectdistinctsnumfromsc,sectionswheresc.secnumin(selectsecnumfromsectionswherecnum='c130'))4.选修了所有课程的学生名单selectsnum,snamefromstudentwheresnumin(selectsnumfromscgroupbysnumhavingcount(*)=(selectcount(*)fromcourse))5.每门课都在80分以上的学生名单selectsnamefromstudent,scwherestudent.snum=sc.snumgroupbystudent.snum,snamehavingmin(score)=806.每门课成绩都在80分以上，平均成绩90分以上的学生名单selectsnamefromstudent,scwherestudent.snum=sc.snumgroupbystudent.snum,snamehavingmin(score)=80andavg(score)=907.选修了大学英语的学生名单和成绩，并按照成绩从高到低排序selectStudent.Snum,Student.Sname,sc.scorefromStudentinnerjoinSConStudent.Snum=sc.snuminnerjoinSectionsonsc.secnum=Sections.Secnuminnerjoincourseonsections.cnum=course.cnumwherecourse.cnamelike'%大学英语'orderbysc.scoredesc8.统计每门课程的选修人数，输出列名为课程号和人数selectcount(snum)as'number',cnumfromsc,sectionswheresc.secnum=sections.secnumgroupbycnumorderbycnumasc9．查询选修了数据库，没选修高等数学的姓名和系别selectdistinctsname,deptfromstudent,sc,sectionswherestudent.snumin(selectdistinctsnumfromsc,sectionswheresc.secnumin(selectsecnumfromsectionswherecnum='c130'))exceptselectdistinctsname,deptfromstudent,sc,sectionswherestudent.snumin(selectdistinctsnumfromsc,sectionswheresc.secnumin(selectsecnumfromsectionswherecnum='c120'))10．使用高等教育出版社的课程名selectcnamefromcoursewheretextboxlike'%高等教育出版社%'11.统计所有课程的最高成绩，最低成绩和平均成绩selectmax(score)asmax,min(score)asmin,avg(score)asavgfromsc,sectionswheresc.secnum=sections.secnumgroupbysections.cnum12.统计每门课程的选课人数和不及格人数selectdistinctcount(sc.snum)as'选课人数',SUM(1-score/60)as'不及格人数',cnumfromsc,sectionswheresc.secnum=sections.secnumgroupbysections.cnum13.查询土木，交通，城市规划的学号和姓名selectsnum,snamefromstudentwheredeptlike'土木工程'unionselectsnum,snamefromstudentwheredeptlike'交通工程'unionselectsnum,snamefromstudentwheredeptlike'城市规划'14.选修了数据库或者多媒体的学生学号selectsnumfromsc,sections,coursewheresc.secnum=sections.secnumandsections.cnum=course.cnumandcnamelike'数据库技术'unionselectsnumfromsc,sections,coursewheresc.secnum=sections.secnumandsections.cnum=course.cnumandcnamelike'多媒体技术'15.计算机系且年龄不大于19的学生所有信息select*fromstudentwheredeptlike'计算机'andyear(GETDATE())-year(birthday