SQL-Server2012综合练习题1---参考答案

数据库综合练习题SQLServer数据库操作1．创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。2．创建表：操作2.1：创建学生表：表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9NotNullPK学生学号st_nmnVarChar8NotNull学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fromnChar20Null学生来源st_dpidnVarChar2Null所在系编号st_mnttinyintNull学生职务参考答案：USEtestGOCREATETABLEstudent(st_idnVarChar(9)primarykeyNOTNULL,st_nmnVarChar(8)NOTNULL,st_sexnVarChar(2)NULL,st_birthdatetimeNULL,st_scoreintNULL,st_datedatetimeNULL,st_fromnVarChar(20)NULL,st_dpidnVarChar(2)NULL,st_mnttinyintNULL)GO操作2.2：创建课程信息表：表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnVarChar4NotNullPK课程编号cs_nmnVarChar20NotNull课程名称cs_tmintNull课程学时cs_scintNull课程学分数据库综合练习题参考答案：USEtestGOCREATETABLEcouse(cs_idnVarChar(4)primarykeyNOTNULL,cs_nmnVarChar(20)NOTNULL,cs_tmintNULL,cs_scintNULL)GO操作2.3：创建选课表：表名：slt_couse说明：选课表属性列数据类型长度空值列约束说明cs_idnVarChar4NotNullFK课程编号st_idnVarChar9NotNullFK学生编号scoreintNull课程成绩sltdatedatetimeNull选课日期参考答案：USEtestGOCREATETABLEslt_couse(cs_idnVarChar(4)NOTNULL,st_idnVarChar(9)NOTNULL,scoreintNULL,sltdatedatetimeNULL)GO操作2.4：创建院系信息表：表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChar2NotNull系编号dp_nmnVarChar20NotNull院系名称dp_drtnVarChar8Null院系主任dt_telnVarChar12Null联系电话参考答案：USEtestGOCREATETABLEdept(dp_idnVarChar(2)NOTNULL,dp_nmnVarChar(20)NOTNULL,dp_drtnVarChar(8)NULL,dp_telnVarChar(12)NULL数据库综合练习题)GO3．表中插入数据操作3.1：向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567INSERTINTOdeptVALUES('11','自动控制系','李其余','81234567')操作3.2：向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期为1990年9月9日，系号为11，其余字段为NULL或默认值INSERTINTOstudent(st_id,st_nm,st_sex,st_birth,st_dpid)VALUES('070201001','王小五','男','1990.9.9','11')操作3.3：向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值INSERTINTOcouse(cs_id,cs_nm)VALUES('1234','操作系统')操作3.4：向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值INSERTINTOslt_couse(cs_id,st_id)VALUES('1234','070201001')4．修改表中数据操作4.1：修改student表记录，将王小五的入学成绩改为88UPDATEstudentSETst_score=88WHEREst_nm='王小五'操作4.2：修改couse表记录，将所有记录的学分改为4，学时改为64UPDATEcouseSETcs_tm=64,cs_sc=4操作4.3：修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77UPDATEslt_couseSETscore=77WHEREcs_id='1234'ANDst_id='070201001'5．删除表中数据操作5.1：删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除DELETEFROMslt_couseWHEREcs_id='1234'ANDst_id='070201001'操作5.2：删除couse表记录，将课程号为1234的记录删除DELETEFROMcouseWHEREcs_id='1234'6.简单查询（1）查询表中所有的列操作6.1：查询所有系的信息SELECT*FROMdept（2）查询表中指定列的信息操作6.2：查询所有的课程号与课程名称SELECTcs_id,cs_nmFROMcouse（3）在查询列表中使用列表达式操作6.3：在查询student表时使用列表达式：入学成绩+400SELECTst_id,st_nm,st_score,st_score+400ASnew_scoreFROMstudent（4）重新命名查询结果操作6.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话SELECTdp_idAS系号,dp_nmAS系名,dp_drtAS系主任,dp_telAS联系电话FROMdept操作6.5：使用=号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分数据库综合练习题SELECT课程号=cs_id,课程名=cs_nm,学分=cs_sc,学时=cs_sc*16FROMcouse（5）增加说明列操作6.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任SELECT'系号:',dp_id,'系名:',dp_nm,'系主任:',dp_drtFROMdept（6）查询列表中使用系统函数操作6.7：显示所有学生的学号、姓名、性别和入学年份SELECTst_id,st_nm,st_sex,DATEPART(yy,st_birth)AS入学年份FROMstudent操作6.8：显示所有学生学号、姓名、性别和班级(学号前6位)SELECTst_id,st_nm,st_sex,LEFT(st_id,6)AS班级FROMstudent（7）消除查询结果中的重复项操作6.9：显示所有学生班级SELECTDISTINCTLEFT(st_id,6)AS班级FROMstudent（8）取得查询结果的部分行集操作6.10：显示前5条学生记录信息SELECTTOP5*FROMstudent操作6.11：显示前25%条学生记录信息SELECTTOP25PERCENT*FROMstudent7．条件查询（1）使用关系表达式表示查询条件操作7.1：查询dept表中系号为11的院系信息SELECT*FROMdeptWHEREdp_id='11'操作7.2：查询student表中11系的学生学号、姓名、性别和所在系编号SELECTst_id,st_nm,st_sex,st_dpidFROMstudentWHEREst_dpid='11'操作7.3：查询student表中2008年及以后入学的学生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)=2008操作7.4：在查询student表080808班学生的学号、姓名、性别和入学成绩SELECTst_id,st_nm,st_sex,st_scoreFROMstudentWHERELeft(st_id,6)='080808'（2）使用逻辑表达式表示查询条件操作7.5：查询student表中非11系的学生信息SELECT*FROMstudentWHERENOT(st_dpid='11')操作7.6：查询选修了1002号课程且成绩在60以下的学生学号SELECTst_idFROMslt_couseWHERE(cs_id='1002')AND(score60)操作7.7：查询2007年入学的11系所有男生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)=2007ANDst_dpid='11'ANDst_sex='男'数据库综合练习题操作7.8：查询11系和12系的学生信息SELECT*FROMstudentWHEREst_dpid='11'ORst_dpid='12'操作7.9：查询11系和12系所有2007年入学的学生信息SELECT*FROMstudentWHERE(st_dpid='11'ORst_dpid='12')ANDDATEPART(yy,st_date)=2007（3）使用LIKE关键字进行模糊查询操作7.10：查询所有“计算机”开头的课程信息SELECT*FROMcouseWHEREcs_nmLIKE'计算机%'操作7.11：查询所有由三个字组成的“王”姓学生信息SELECT*FROMstudentWHEREst_nmLIKE'王__'操作7.12：查询所有课程名中包含“信息”的课程信息SELECT*FROMcouseWHEREcs_nmLIKE'%信息%'操作7.13：查询学生姓名介于王姓到张姓的信息SELECT*FROMstudentWHEREst_nmLIKE'[王-张]%'（4）使用Between…And关键字进行查询操作7.14：查询在1989.7.1到1990.6.30之间出生的学生信息SELECTst_id,st_nm,st_sex,st_birthFROMstudentWHEREst_birthBETWEEN'1981.7.1'AND'1999.6.30'操作7.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息SELECT*FROMslt_couseWHEREcs_id='1001'AND(scoreBETWEEN60AND80)（5）使用IN关键字进行查询操作7.16：查询11系、12系、13系的学生信息SELECT*FROMstudentWHEREst_dpidIN('11','12','13')操作7.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别SELECTst_id,st_nm,st_sexFROMstudentWHERELeft(st_nm,1)IN('张','王','李','赵')（6）使用[NOT]NULL关键字进行查询操作7.18：查询所有生源为非空的学生信息SELECT*FROMstudentWHEREst_fromISNOTNULL操作7.19：查询选修了1001号课程且成绩为空的学生选课信息SELECT*FROMslt_couseWHEREcs_id='1001'ANDscoreISNULL8.查询排序与