资源描述：
《sql_server上机实验_答案_华中科技大学》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、实验二4.createdatabasestudbSp_helpdb5.alterdatabasestudbmodifyfile(name=studb,size=5MB,maxsize=20MB,filegrowth=1MB)7.ALTERDATABASEstudbMODIFYNAME=student_db8.DROPDATABASEstudent_db实验三5.USEstudents_dbGOCREATETABLEgrade(学号char(4),课程编号char(4),分数decimal(5))
2、8.USEstudentdbGOINSERTgradeVALUES(‘0004’,’0001’,’80’)9.USEstudentdbGOALTERTABLEcurriculumALTERCOLUMN课程编号char(4)NOTNULL10.USEstudentdbGOALTERTABLEgradeALTERCOLUMN分数real11.USEstudentdbGOALTERTABLEstudent_infoALTERCOLUMN姓名。。。。。。不会12.USEstudentdbGODELETE
3、FROMgradeWHERE学号='0004'13.USEstudentdbGOUPDATEgradeSET分数=90WHERE学号='0003'and课程编号='0005'14.USEstudentdbGOALTERTABLEgradeADD备注VARCHAR(20)NULL15.USEstudentdbGODROPTABLEgrade实验四2（1）select学号,姓名,出生日期fromstudent_info（2）select姓名,家庭住址fromstudent_infowhere学号='
4、0002'（3）select学号,姓名fromstudent_infowhere性别='男'3（1）select学号,分数fromgradewhere分数between80and90（2）selectavg(分数)fromgradewhere课程编号='0003'（3）select课程编号,count(课程编号)as人数fromgradegroupby课程编号（4）select姓名,出生日期fromstudent_infoorderby出生日期desc（5）select学号,姓名fromstud
5、ent_infowhere姓名like'张%'4（1）SELECT姓名,出生日期FROMstudent_infoWHERE性别=(SELECT性别FROMstudent_infoWHERE姓名='刘卫平')（2）SELECT学号,姓名,性别FROMstudent_infoWHEREstudent_info.学号IN(SELECT学号FROMgradeWHERE课程编号IN('0002','0005'))（3）SELECT课程编号,分数FROMgradeWHERE学号='0001'AND分数>AN
6、Y(SELECT分数FROMgradeWHERE学号='0002')（4）SELECT课程编号,分数FROMgradeWHERE学号='0001'AND分数>ALL(SELECT分数FROMgradeWHERE学号='0002')5（1）SELECTstudent_info.学号,姓名,分数FROMstudent_info,gradeWHEREstudent_info.学号=grade.学号AND分数BETWEEN80AND90（2）SELECTstudent_info.学号,姓名,分数FROM
7、student_infoINNERJOINgradeONstudent_info.学号=grade.学号INNERJOINcurriculumON课程名称='C语言程序设计'（3）SELECTstudent_info.学号,student_info.姓名,curriculum.课程名称,grade.分数FROMstudent_infoINNERJOINgradeONstudent_info.学号=grade.学号and性别='男'INNERJOINcurriculumoncurriculum.课
8、程编号=grade.课程编号（4）SELECTstudent_info.学号,max(grade.分数)FROMstudent_infoINNERJOINgradeONstudent_info.学号=grade.学号groupbystudent_info.学号（5）SELECTstudent_info.学号,sum(grade.分数)FROMstudent_infoleftouterJOINgradeONstudent_info.学号=grade.学号groupbystudent_info.学号
