数据库实验报告方案

数据库实验报告方案

//实验内容与要求请有选择地实践以下各题。（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表达下列查询：①检索年龄大于23岁的男学生的学号和姓名；SELECTSno,SnameFROMStudentWHERESsex=’男’ANDSage>23;②检索至少选修一门课程的女生姓名；SELECTSnameFROMStudentWHERESsex=’女’ANDSnoIN(SELECTSnoFROMSCGROUPBYSnoHAVINGcount(*)>=1;);③检索王同学不学的课程的课程号；SELECTCno;FROMCourseWHERECnoNOTIN(SELECTCnoFROMStudent,SCWHERESnamelike’王%’ANDStudent.Sno=SC.Sno);④检索至少选修两门课程的学生学号；SELECTDISTINCTSnoFROMSCGROUPBYSnoHAVINGcount(*)>=2;⑤检索全部学生都选修的课程的课程号与课程名；SELECTCno,CnameFROMCourseWHERENOTEXISTS(SELECT*FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESC.Sno=Student.SnoANDSC.Cno=Course.Cno));⑥检索选修了所有3学分课程的学生学号；n//SELECTDISTINCTSnoFROMSCXWHERENOTEXISTS(SELECT*FROMCourseWHERECcredit=3ANDNOTEXISTS(SELECT*FROMSCYWHEREX.Sno=Y.SnoANDCourse.Cno=Y.Cno));（2）基于“教学管理”数据库jxgl，使用SQL的查询语句表达下列查询：①统计有学生选修的课程门数；SELECTcount(DISTINCTCno)FROMSC;②求选修4号课程的学生的平均年龄；SELECTAVG(Sage)FROMStudent,SCWHERECno=4ANDStudent.Sno=SC.Sno;③求学分为3的每门课程的学生平均成绩；SELECTAVG(Grade)FROMCourse,SCWHERECcredit=3ANDCourse.Cno=SC.CnoGROUPBYSC.Cno;④统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数,查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECTCno,count(Sno)FROMSCGROUPBYCnoHAVINGcount(Sno)>3ORDERBYcount(Sno)DESC,CnoASC;⑤检索学号比“王菲”同学大而年龄比他小的学生姓名；SELECTSnameFROMStudentXWHERESno>(SELECTSnoFROMStudentYWHERESname=’王菲’ANDSage>(SELECTSageFROMStudentZWHERESname=’王菲’ANDX.Sno=Z.SnoANDY.Sno=Z.Sno));⑥检索姓名以“王”打头的所有学生的姓名和年龄；n//SELECTSname,SageFROMStudentWHERESnameLIKE‘王%’;⑦在SC中检索成绩为空置的学生学号和课程号；SELECTSno,CnoFROMSCWHEREGradeisNULL;⑧求年龄大于女同学平均年龄的男学生姓名和年龄；SELECTSname,SageFROMStudentXWHERESsex=’男’ANDSage>(SELECTAVG(Sage)FROMStudentWHERESsex=’女’ANDX.Sno=Y.Sno);⑨求年龄大于所有女同学年龄的男同学姓名和年龄；SELECTSname,SageFROMStudentXWHERESsex=’男’ANDSage>(SELECTMAX(Sage)FROMStudentYWHERESsex=’女’ANDX.Sno=Y.Sno);⑩检索所有比“王华”年龄大的学生姓名，年龄和性别；SELECTSname,Sage,SsexFROMStudentXWHERESage>(SELECTSageFROMStudentYWHERESname=’王华’ANDX.Sno=Y.Sno);①检索选修“2”课程的学生中成绩最高的学生和学号；SELECTSname,SC.SnoFROMStudent,SCWHERECno=2ANDStudent.Sno=SC.Sno;②检索学生姓名和其所选修课程的课程号和成绩；SELECTSname,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.SnoGROUPBYSname;③检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来；SELECTSno,SUM(Grade)FROMSCXn//WHEREGrade>=60ANDSnoIN(SELECTSnoFROMSCYWHEREX.Sno=Y.SnoGROUPBYSnoHAVINGcount(Cno)>4)`ORDERBYSUM(Grade)DESC;（3）设有表4-1~表4-4的4个基本表（表结构于表内容是假设的），请先创建数据库及根据表内容创建表结构，并添加表记录，写出实现以下各题功能的SQL语句：创建以下4各表：CREATETABLESTUDENT(SNOCHAR(6)PRIMARYKEY,SNAMECHAR(20)UNIQUE,SEXCHAR(2),AGESMALLINT,CLASSCHAR(4));CREATETABLETEACHER(TNOCHAR(3)PRIMARYKET,TNAMWCHAR(20)UNIQUE,SEXCHAR(2),AGESMALLINT,PROFCHAR(10),DEPTCHAR(10));CREATETABLECOURSE(CNOCHAR(4)PRIMARYKEY,CNAMECHAR(20)UNIQUE,TNOCHAT(3),FOREIGNKEYTNOREFERENCESTEACHER(TNO));CREATETABLESC(SNOCHAR(6),CNOCHAR(4),GRADESMALLINT,PRIMARYKEY(SNO,CNO),FOREIGNKEYSNOREFERENCESSTUDENT(SNO),FOREIGNKEYCNOREFERENCESCOURSE(CNO));n//插入数据：INSERTINTOSTUDENTVALUES(‘980101’,’李华’，‘男’，19，’9801’);......同上方法依次插入数据①查询选修课程“8105”且成绩在80到90之间的所有记录；SELECT*FROMSCWHERECNO=’8105’ANDGRADEBETWEEN80AND90;②查询成绩为79，89或99的记录；SELECT*FROMSCWHEREGRADEIN(79,89,99);③查询“9803”班的学生人数；SELECTcount(SNO)FROMSTUDENTWHERESNOLIKE‘9803%’;④查询至少有20名学生选修的并且课程号以8开头的课程及平均成绩；SELECTCNO,AVG(GRADE)FROMSCWHERECNOLIKE‘8%’GROUPBYCNO;HAVINGcount(SNO)>=20;⑤查询最低分大于80，最高分小于95的SNO与平均分；SELECTSNO,AVG(GRADE)FROMSCGROUPBYSNOHAVINGMIN(GRADE)>80ANDMAX(GRADE)<95;⑥查询“9803”班的学生所选各课程的课程号及平均成绩；SELECTCNO,AVG(GRADE)FROMSCWHERESNOLIKE‘9803’GROUPBYCNO;⑦查询选修“8105”课程的成绩高于“980302”号同学成绩的所有同学的记录；SELECT*FROMSCXWHERECNO=’8105’ANDGRATE>(SELECTGRATEFROMSCYWHERECNO=’8105’ANDSNO=’980302’ANDX.SNO=Y.SNO);⑧查询与学号为“980103”的同学同岁的所有学生的SNO,SNAME和AGE；SELECTSNO,SNAME,AGEn//FROMSTUDENTXWHEREAGE=(SELECTAGEFROMSTUDENTYWHERESNO=’980103’ANDX.SNO=Y.SNO);⑨查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；SELECTSC.CNO,SNO,GRADEFROMSC,TEACHRT,COURSEWHERETNAME=’钱军’ANDTEACHER.TNO=COURSE.TNOANDSC.CON=COURSE.CON;⑩查询选修某课程的学生人数多于20人的教师姓名；SELECTDISTINCTTNAMEFROMTEACHER,SC,COURSEWHERETEACHER.TNO=COURSE.TNOANDSC.CON=COURSE.CONGROUPBYSC.CNOHAVINGcount(SNO)>20;11查询选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程成绩的同学的SNO及“8105”课程成绩，并按成绩从高到低依次排列；SELECTSNOGRADEFROMSCXWHERECNO=’8105’ANDGRADE>(SELECTGRADEFROMSCYWHERECNO=’8245’ANDX.SNO=Y.SNO)ORDERBYGRADEDESC;12查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE；SELECTCNO,SNO,GRADEFROMSCWHERECNO=’8105’ANDGRADE>(SELECTMAX(GRADE)FROMSCWHERECNO=’8245’ANDX.CNO=Y.SNO);13列出所有教师和同学的姓名，SEX,AGE；SELECTTNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENT.SEX,STUDENT.AGEFROMTEACHER,SC14查询成绩比该课程平均成绩高的学生的成绩表；SELECT*FROMSC.Xn//GROUPBYSNOHAVINGGRADE>(SELECTAVG(GRADE)FROMSC.YWHEREX.CNO=Y.CNOGROUPBYCNO);15列出所有任课教师的TNAME和DEPT；SELECTTNAME,DEPTFROMTEACHER,COURSEWHERETEACHER.TNO=COURSE.TNO16列出所有未讲课教师的TNAME和DEPT;SELECTTNAME,DEPYFROMTEACHERWHERENOTEXISTS(SELECT*FROMCOURSEWHERETEACHER.TNO=COURSE.TNO);17列出至少有4名男生的班号；SELECTCLASSFROMSTUDENTGROUPBYCLASSHAVINGcount(SNO)>=4;18查询不姓“张”的学生记录；SELECT*FROMSTUDENTWHERESNAMENOTLIKE‘张%’;19查询每门课最高分的学生的SNO,CNO,GRADE;SELECTSNO,CNO,GRADEFROMSCGROUPBYCNOHAVINGGRADE=MAX(GRADE);20查询与“李华”同性并同班的同学SNAME；SELECTSNAMEFROMSTUDENTXWHERECLASS=(SELECTCLASSFROMSTUDENTYWHERESNAME=’李华’ANDSEX=(n//SELSCTSEXFROMSTUDENTZWHERESNAME=’李华’ANDX.SNO=Y.SNOANDY.SNO=Z.SNO));21查询“女”教师及其所上的课程；SELECTTNAME,CNO,CNAMEFROMTEACHER,COURSEWHERETEACHER.TNO=COURSE.TNO,SEX=’女’;22查询选修“数据库系统”课程的“男”同学的成绩表；SELECT*FROMSC,COURSE,STUDENTWHERESTUDENT.SNO=SC.SNOANDCOURSE.CNO=SC.CNOANDCNAME=’数据库系统’ANDSEX=’男’;23查询所有比刘涛年龄大的教师姓名，年龄和刘涛的年龄；SELECTTNAME,AGEFROMTEACHERXWHERESNAME=’刘涛’ORAGE>(SELECTAGEFROMTEACHERYWHEREX.TNO=Y.TNOANDSNAME=’刘涛’);24查询不讲授“8106”号课程的教师姓名。SELECTTNAMEFROMTEACHER,COURSEWHERETEACHER.TNO=COURSE.TNOANDCNONOTIN(8106);