您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据结构与算法 > 数据库课设-实验报告
《数据库原理》实验报告学号:姓名:实验一:SQL定义功能、数据插入1.建立教学数据库的三个基本表:S(Sno,Sname,Ssex,Sage,Sdept)学生(学号,姓名,性别,年龄,系)SC(Sno,Cno,Grade)选课(学号,课程号,成绩)C(Cno,Cname,Cpno,Ccredit)课程(课程号,课程名,先行课,学分)__________________________________________________________________createtableS040930504(Snochar(9)PRIMARYKEY,Snamechar(40),Ssexchar(2),SageSMALLINT,Sdeptchar(20));createtableSC040930504(Snochar(9),Cnochar(4),GradeSMALLINT);createtableC040930504(Cnochar(4),Cnamechar(40),Cpnochar(4),CcreditSMALLINT,);insertintoS040930504values('200215121','李勇','男',20,'CS');insertintoS040930504values('200215122','刘晨','女',19,'CS');insertintoS040930504values('20204','王敏','女',18,'MA');insertintoS040930504values('30203','张立','男',19,'IS');insertintoSC040930504values('200215121',1,92);insertintoSC040930504values('200215121',2,85);insertintoSC040930504values('200215121',3,88);insertintoSC040930504values('200215122',2,90);insertintoSC040930504values('200215122',3,80);insertintoC040930504values(1,'数据库','5',4);insertintoC040930504values(1,'数学','',2);insertintoC040930504values(1,'信息系统','1',4);insertintoC040930504values(1,'操作系统','6',3);insertintoC040930504values(1,'数据结构','7',4);insertintoC040930504values(1,'数据处理','',2);insertintoC040930504values(1,'PASCAL语言','6',4);SQLselect*fromS040930504SNOSNAMESSEXSAGESDEPT-------------------------------------------------------------200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19ISSQLselect*fromSC040930504SNOCNOGRADE-----------------------200215121192200215121285200215121388200215122290200215122380SQLselect*fromC040930504CNOCNAMECPNOCCREDIT----------------------------------------------------------1数据库541数学21信息系统141操作系统631数据结构741数据处理21PASCAL语言64_______________________________________________________2.DROPTABLE、ALTERTABLE、CREATEINDEX、DROPINDEX及INSERT语句输入数据。_______________________________________________________________________________createtablez040930504(Snochar(5),sagechar(2),Snamechar(6));insertintoz040930504values('15121','20','李勇');insertintoz040930504values('30504','20','朱猪');ALTERtablez040930504ADDs_entranceDATE;DROPtablez040930504;CREATEUNIQUEINDEXStuSnoONz040930504(Sno);DROPINDEXStuSno;_______________________________________________________________________________实验二:数据查询1.查询选修1号课程的学生学号与姓名。SQLSELECTSno,Sname2FROMS0409305043WHEREEXISTS4(SELECT*5FROMSC0409305046WHERESno=S040930504.SnoANDCno='1');SNOSNAME-------------------------------------------------200215121李勇2.查询选修课程名为数据结构的学生学号与姓名。SQLSELECTSno,Sname2FROMS0409305043WHEREEXISTS4(SELECT*5FROMSC0409305046WHERESno=S040930504.SnoANDEXISTS7(SELECT*8FROMC0409305049WHERECname='数据结构'));SNOSNAME-------------------------------------------------200215122刘晨200215121李勇3.查询不选1号课程的学生学号与姓名。SQLSELECTSno,Sname2FROMS0409305043WHERENOTEXISTS4(SELECT*5FROMSC0409305046WHERESno=S040930504.SnoANDCno='1');SNOSNAME-------------------------------------------------200215122刘晨20204王敏30203张立4.查询学习全部课程学生姓名。SQLSELECTSname2FROMS0409305043WHERENOTEXISTS4(SELECT*5FROMC0409305046WHERENOTEXISTS7(SELECT*8FROMSC0409305049WHERESno=S040930504.SnoANDCno=C040930504.Cno));SNAME----------------------------------------李勇5.查询所有学生除了选修1号课程外所有成绩均及格的学生的学号和平均成绩,其结果按平均成绩的降序排列。createviewdot(Sno,Cno,Grade)ASselectS040930504.Sno,Cno,GradefromS040930504,SC040930504whereS040930504.Sno=SC040930504.SnoandCno!=1;selectSno,avg(Grade)averagefromdotwhere60=(selectmin(Grade)fromdotxdotwheredot.Sno=xdot.Sno)groupbySnoorderbyaveragedesc;6.查询选修数据库原理成绩第2名的学生姓名。selectSname,SnofromS040930504whereSnoin(selectSnofromSC040930504whereCnoin(selectCnofromC040930504whereCname='数据库')andGradein(selectmax(Grade)fromSC040930504whereGradenotin(selectmax(Grade)fromSC040930504whereCnoin(selectCnofromC040930504whereCname='数据库'))andCnoin(selectCnofromC040930504whereCname='数据库')));7.查询所有3个学分课程中有3门以上(含3门)课程获80分以上(含80分)的学生的姓名。createviewxdot(Sno,Cno,Grade)ASselectSno,SC040930504.Cno,GradefromSC040930504,C040930504whereGrade=80andccredit=2andSC040930504.Cno=C040930504.CnoselectSnamefromS040930504whereSnoin(selectSnofromxdotgroupbySnohavingcount(*)=3);8.查询选课门数唯一的学生的学号。createviewdot(Sno,Ccount)ASselectSno,count(*)fromSC040930504groupbySno;selectSnofromSC040930504groupbySnohavingcount(*)all(selectCcountfromdotwheredot.Sno!=SC040930504.Sno);实验三:数据修改、删除1.把1号课程的非空成绩提高10%。SQLUPDATESC0409305042SETGrade=Grade*(1+0.1)3WHEREGRADEISNOTNULLANDGRADE=100ANDCNO='1';已更新1行。SQLSELECT*FROMSC040930504;SNOCNOGRADE-----------------------20021512111012002151212852002151213882002151222902002151223802.在SC表中删除课程名为数据结构的成绩的元组。SQLDELETE2FROMSC0409305043WHERECNOIN4(SELECTCNO5FROMC0409305046WHEREC040930504.CNO=SC040930504.CNOANDCNAME='数据库');已删除1行。SQLSELECT*FROMSC040930504;SNOCNOGRADE-----------------------2002151212852002151213882002151222902002151223803.在S和SC表中删除学号为95002的所有数据。SQLDELETE2FROMS0409305043WHERESNO='200215122';已删除1行。SQLDELETE2FROMSC0409305043WHERESNO='200215122';已删除2行。SQLSELECT*FROMS040930504;SNOSNAMESSSAGE-------------------------------------------------------------SDEPT--------------------200215121李勇男20CS20204王敏女18MA30203张立男19ISSQLSELECT*FROMS040930504;S
本文标题:数据库课设-实验报告
链接地址:https://www.777doc.com/doc-5336824 .html