数据库实验

1实验三建表、修改表、删除表1.建立数据库jxgl在jxgl数据库中建立学生表student,课程表course,选修表sc，建表过程有如下方式1）以图形界面操作2）以SQL操作（以下脚本可直接在查询分析器中执行）CreateTableStudent(SnoCHAR(5)NOTNULLPRIMARYKEY(Sno),SnameVARCHAR(20),SageSMALLINTCHECK(Sage=15ANDSage=45),SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女'),SdeptCHAR(2));CreateTableCourse(CnoCHAR(2)NOTNULLPRIMARYKEY(Cno),CnameVARCHAR(20),CpnoCHAR(2),CcreditSMALLINT);CreateTableSC(SnoCHAR(5)NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESStudent(Sno),CnoCHAR(2)NOTNULL,GradeSMALLINTCHECK((GradeISNULL)OR(GradeBETWEEN0AND100)),PRIMARYKEY(Sno,Cno),CONSTRAINTC_FFOREIGNKEY(Cno)REFERENCESCourse(Cno));INSERTINTOStudentVALUES('98001','钱横',18,'男','CS');INSERTINTOStudentVALUES('98002','王林',19,'女','CS');INSERTINTOStudentVALUES('98003','李民',20,'男','IS');INSERTINTOStudentVALUES('98004','赵三',16,'女','MA');INSERTINTOCourseVALUES('1','数据库系统','5',4);INSERTINTOCourseVALUES('2','数学分析',null,2);INSERTINTOCourseVALUES('3','信息系统导论','1',3);INSERTINTOCourseVALUES('4','操作系统原理','6',3);INSERTINTOCourseVALUES('5','数据结构','7',4);INSERTINTOCourseVALUES('6','数据处理基础',null,4);INSERTINTOCourseVALUES('7','C语言','6',3);INSERTINTOSCVALUES('98001','1',87);INSERTINTOSCVALUES('98001','2',67);INSERTINTOSCVALUES('98001','3',90);INSERTINTOSCVALUES('98002','2',95);INSERTINTOSCVALUES('98002','3',88);2课程表Course（课程号Cno,课程名Cname,先修课号Cpno,学分Ccredit）学生表Student（学号Sno,姓名Sname,年龄Sage,性别Ssex,所在系Sdept）学生选课表SC（学号Sno,课程号Cno,成绩Grade)3实验四Select查询操作练习（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表示下列查询：①检索年龄大于23岁的男学生的学号和姓名；selectSno,SnamefromStudentwhereSage'23'ANDSsex='男';②检索至少选修一门课程的女学生的姓名；selectSnamefromStudent,SCwhereSsex='女'ANDStudent.Sno=SC.SnogroupbyStudent.Snamehavingcount(*)=1;或者SelectSnameFromStudentWhereSsex='女'ANDSnoin(selectsnofromSCgroupbysnohavingcount(*)=1);③检索王同学不学的课程的课程号；selectCnofromCoursewhereCourse.Cnonotin(selectCnofromSC,StudentwhereSC.Sno=Student.SnoANDSnameLIKE'王%');④检索至少选修两门课程的学生学号；selectDISTINCTStudent.SnofromStudent,SC4WHEREStudent.Sno=SC.SnoGROUPBYStudent.SnoHAVINGCOUNT(*)=2;⑤检索全部学生都选修的课程的课程号与课程名；SELECTCno,CnamefromCoursewherenotexists(select*fromstudentwherenotexists(select*fromSCwhereSC.sno=Student.SnoANDSC.Cno=Course.Cno))或者假设所有学生只有两人SELECTCno,CnamefromCourseWHERECourse.Cnoin(selectCnofromSCgroupbySC.Cnohavingcount(Sno)=(selectcount(*)fromStudent));SELECTCno,CnamefromCourseWHERECourse.Cnoin(selectCnofromSCgroupbySC.Cnohavingcount(Sno)=2);5⑥检索选修了所有3学分课程的学生学号。selectdistinctStudent.SnofromStudent,SCwhereexists(select*fromCoursewhereCcredit='3'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno);(2)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询：①统计有学生选修的课程门数；selectcount(distinctSC.Cno)FROMSC;②求选修4号课程的学生的平均年龄；SELECTavg(Student.Sage)fromStudent,SCwhereStudent.Sno=SC.SnoANDCno='4';SELECTavg(Student.Sage)as平均年龄fromStudent,SCwhereStudent.Sno=SC.SnoANDCno='3';③求学分为3的每门课程的学生平均成绩；6SELECTavg(SC.Grade)fromCourse,SC,StudentwhereStudent.Sno=SC.SnoANDCourse.Ccredit='3'groupbySC.Cno用groupby语句以课程号分组注意，如果程序是这样的：SELECTavg(SC.Grade)fromCourse,SC,StudentwhereStudent.Sno=SC.SnoANDCourse.Ccredit='3'ANDCourse.Cno=SC.Cno;只显示一门课程的成绩！④统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECTCno,count(Sno)fromSCGROUPBYCnoHAVINGCount(Sno)3orderbycount(sno)DESC,Cno;SELECTCno,count(Sno)as选修人数fromSCGROUPBYCnoHAVINGCount(Sno)1orderbycount(sno)DESC,Cno;⑤检索学号比“王林”同学大而年龄比他小的学生姓名；SELECTSnameFROMStudentwhereSno(selectSnofromStudentwhereSname='王林')7ANDSage(selectSagefromStudentwhereSname='王林');或者也可以selectX.snamefromStudentasX,StudentasYwhereX.SnoY.SnoANDX.SageY.SageANDY.Sname='王林';⑥检索姓名以“王”开头的所有学生的姓名和年龄；selectSname,SagefromStudentwhereStudent.Snamelike'王%';注意：like那里不能用等号“=”⑦在SC中检索成绩为空值的学生学号和课程号；SELECTSno,CnofromSCwhereGradeisnull注意：isnull那里不能用等号“=”⑧求年龄大于女学生平均年龄的男学生姓名和年龄；SELECTSname,SagefromStudentwhereSage(selectavg(Sage)fromstudentwhereSsex='女')ANDSsex='男';⑨求年龄大于所有女学生年龄的男学生姓名和年龄；SELECTSname,SagefromStudentwhereSage(SELECTMAX(Sage)8fromStudentwhereSsex='女')ANDSsex='男';⑩检索所有比“王林”年龄大的学生姓名、年龄和性别；SELECTSname,Sage,SsexfromStudentwhereSage(selectSagefromStudentwhereSname='王林')⑾检索选修“2”课程的学生中成绩最高的学生的学号；SELECTSno,GradefromSCwhereGrade=(selectMAX(Grade)fromSCwhereCno='2');注意：不能写成Grade=MAX(Grade)的形式，因为不存在！Count,min,avg也是⑿检索学生姓名以及所选修课程的课程号和成绩；selectSname,Cno,GradefromSC,StudentwhereStudent.Sno=SC.Sno;⒀检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。选择4门以上selectsno,sum(Grade)sumfromSCwheregrade=60ANDSnoin(selectSno9fromSCgroupbySnohavingcount(Sno)4)groupbysnoorderbysum(grade)desc注意：选择2门课（包括两门）selectsno,sum(Grade)fromSCwheregrade=60ANDSnoin(selectSnofromSCgroupbySnohavingcount(Sno)=2)groupbysnoorderbysum(grade)desc再注意：Selectsno,sum(grade)FromSCWheregrade=60Groupbysnohavingcount(sno)1Orderbysum(grade)desc因为输入的数据分数都大于60，所以结果与上面的例子相同。但是，当分数小于60时，有课不及格的同学的学号会被删除，统计不完整。