SQLServerSQL实验与练习题参考答案

1、数据库原理与应用(SQLServer)练习题- #-实验1数据库操作1创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB,最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为testog,物理文件名为test_log.ldf,初始大小为1MB,最大尺寸为5MB，增长速度为10%。参考答案：createdatabaseTestONprimary(name=test_data,filename=d:testtest_data.mdf,size=5MB,maxsize=unlimited,filegrow

2、th=1MB)LOGON(name=test_log,filename=d:testtest_log.ldf,size=1MB,maxsize=5MB,filegrowth=10%)GO2查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性参考答案：EXECsp_helpdbtest3删除数据库：操作1.3：使用T-SQL语句删除数据库test参考答案：dropdatabaseTest实验2表操作1创建表：数据库原理与应用(SQLServer)练习题- #-数据库原理与应用(SQLServer)练习题- #-操作2.1：创建学生表表名：student说明：学生基本信息表数据库原

3、理与应用(SQLServer)练习题- -属性列数据类型长度空值列约束说明st_idnVarChar9NotNullPK学生学号st_nmnVarChar8NotNull学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fromnChar20Null学生来源st_dpidnVarChar2Null所在糸编号st_mnttinyintNull学生职务参考答案：USEtestGOCREATE(TABLEstudent(st_idnVarChar(9)prim

4、arykeyNOTNULL,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课程编号数据库原理与应用（SQLServer）练习题- -数据库原理与应用(SQLServer

5、)练习题- -cs_nmnVarChar20NotNull课程名称cs_tmintNull课程学时cs_scintNull课程学分参考答案：USEtestGOCREATETABLEcouse(csidnVarChar(4)primarykeyNOTNULL,csnmnVarChar(20)NOTNULL,cstmintNULL,cs_scintNULL)GO说明：选课表操作2.3：创建选课表表名：slt_couse属性列数据类型长度空值列约束说明cs_idnVarChar4NotNullFK课程编号st_idnVarChar9NotNullFK学生编号scoreintNull课程成绩sltda

6、tedatetimeNull选课日期参考答案：USEtestGOCREATETABLEcouse(cs_idnVarChar(4)NOTNULL,st_idnVarChar(9)NOTNULL,scoreintNULL,sltdatedatetimeNULL)GO操作2.4：创建院系信息表表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChar2NotNull系编号dp_nmnVarChar20NotNull院系名称dp_drtnVarChar8Null院系主任dt_telnVarChar12Null联系电话参考答案：USEtestGOCREATETABLEde

7、pt(dp_idnVarChar(2)NOTNULL,dp_nmnVarChar(20)NOTNULL,dp_drtnVarChar(8)NULL,dp_telnVarChar(12)NULL)GO2修改表结构：（1）向表中添加列：操作2.5：为“dept表添加“dp_count列（数据类型为nvarchar，长度为3,允许为空）参考答案：ALTERTABLEdeptADDdp_countnvarchar（3）NULL（2）修改列数据类型：操作2.6：修改“dept”表的“dp_count”列数据类型为int参考答案：ALTERTABLEdeptALTERCOLUMNdp_countintNU

8、LL（3）删除表中指定列：操作2.7：删除“dept”表的“dp_count”列参考答案：ALTERTABLEdeptDROPCOLUMNdp_count3删除表操作2.8：删除“dept”表参考答案：DROPTABLEstudent4向表中输入数据记录操作2.9:分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录实验3数据完整性1空值约束(NULL)操作3.1：将student表中的st_sex列属性更改为NOTNULL参考答案：ALTERTABLEstudentALTERCOLUMEst_nmnVarChar(8)NOTNULL2默认值约束

9、(DEFAULT)操作3.2：将student表中的st_from列默认值设置为“陕西省”参考答案：ALTERTABLEstudentADDDEFAULT陕西省FORst_from3默认值对象操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。参考答案：CREATEDEFAULTdf_todayASGetdate()GOEXECsp_bindefaultdf_today,slt_couse.sltdateGOEXECsp_unbindefaultslt_couse.sltdateGODRO

10、PDEFAULTdf_todayGO4检查约束(CHECK)操作3.4：将slt_couse表中的score列的检查约束设置为=0且=0ANDscore=2008操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩SELECTst_id,st_nm,st_sex,st_scoreFROMstudentWHERELeft(st_id,6)=0808082使用逻辑表达式表示查询条件操作6.5：查询student表中非11系的学生信息SELECT*FROMstudentWHERENOT(st_dpid=11)操作6.6：查询选修了1002号课程且成绩在60以下的学生学号S

11、ELECTst_idFROMslt_couseWHERE(cs_id=1002)AND(score75操作8.11：查询选修了2门以上课程的学生学号SELECTst_idFROMslt_couseGROUPBYst_idHAVINGCOUNT(*)2操作8.12：明细汇总年龄20的学生，并汇总学生数量、平均年龄SELECTst_nm,DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)AS年龄FROMstudentWHEREDATEPART(yy,GETDATE()-DATEPART(yy,st_birth)20COMPUTECOUNT(st_nm),AVG(

12、DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)操作8.13：按班级明细汇总成绩85分的学生，汇总学生数、均分SELECTst_nm,LEFT(st_id,6)AS班级,st_scoreFROMstudentWHEREst_score85ORDERBY班级COMPUTECOUNT(st_nm),AVG(st_score)BY班级实验9数据查询(5)连接查询操作9.1：用SQLServer形式连接查询学生学号、姓名、性别及其所选课程编号SELECTa.st_id,st_nm,st_sex,cs_idFROMstudenta,slt_cousebWHEREa.

13、st_id=b.st_idORDERBYa.st_id操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号SELECTa.st_id,st_nm,st_sex,cs_idFROMstudentaINNERJOINslt_cousebONa.st_id=b.st_idORDERBYa.st_id操作9.3：用SQLServer形式连接查询学生学号、姓名及其所选课程名称及成绩SELECTa.st_id,st_nm,cs_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=b.st_idANDb.cs_id=c.cs_idORDER

14、BYa.st_id操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩SELECTa.st_id,st_nm,cs_nm,scoreFROMslt_couseaINNERJOINstudentbONa.st_id=b.st_idINNERJOINcousecONa.cs_id=c.cs_idORDERBYb.st_id操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩SELECTa.st_id,st_nm,scoreFROMstudenta，slt_cousebWHEREa.st_id=b.st_idANDb.cs_id=1002ORDERBYb.st_id

15、操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩SELECTa.st_id,st_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=b.st_idANDb.cs_id=c.cs_idANDc.cs_nm=数据结构ORDERBYa.st_id操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名SELECTa.st_id,st_nm,scoreFROMstudentaLEFTOUTERJOINslt_cousebONa.st_id=b.st_idWHEREb.cs_idISNULLORDERBYb.st_id操作9.8：用右

16、外连接查询选修各个课程的学生学号SELECTb.cs_id,a.st_idFROMslt_couseaRightOUTERJOINcousebONa.cs_id=b.cs_idORDERBYb.cs_id实验10数据查询(6)子查询操作10.1：用子查询对各班人数进行查询(新增列)SELECTDISTINCTLEFT(a.st_id,6)AS班级,人数=(SELECTCOUNT(st_id)FROMstudentbWHERELEFT(a.st_id,6)=LEFT(b.st_id,6)FROMstudentaORDERBYLEFT(a.st_id,6)ASC操作10.2：用子查询对各课程的选课

17、人数进行查询(新增列)SELECTDISTINCTa.cs_id,人数=(SELECTCOUNT(st_id)FROMslt_cousebWHEREa.cs_id=b.cs_id)FROMslt_couseaORDERBYa.cs_idASC操作10.3：查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序通过子查询实现：使用IN关键字SELECTst_id,st_nm,st_sexFROMstudentWHEREst_idIN(SELECTst_idFROMslt_couseWHEREcs_id=1002ANDscore60)ORDERBYst_nm通过子查询实现：使用

18、比较运算符SELECTst_id,st_nm,st_sexFROMstudentaWHERE(SELECTscoreFROMslt_cousebWHEREa.st_id=b.st_idANDcs_id=1002)ANY(SELECTscoreFROMslt_couseWHEREcs_id=1002ANDLEFT(st_id,6)=070511)ANDLEFT(st_id,6)070511ANDcs_id=1002操作10.6:查询其它班比070511班任一学生的1002号课程成绩高的学生信息(ANY/ALL)SELECT*FROMslt_couseWHEREscoreALL(SELECTsco

19、reFROMslt_couseWHEREcs_id=1002ANDLEFT(st_id,6)=070511)ANDLEFT(st_id,6)070511ANDcs_id=1002操作10.7:查询大于等于60分且且比1003课程平均成绩低的学生课程信息(BetweenAnd)SELECT*FROMslt_couseaWHEREa.scoreBETWEEN60AND(SELECTAVG(b.score)FROMslt_cousebWHEREb.cs_id=1003)操作10.8：查询系主任为“赵虎”的系的所有学生信息通过子查询实现：IN运算符SELECT*FROMstudentWHEREEXISTS(SELECT*FROMdeptWHEREst_dpid=dp_idANDdp_drt=赵虎)通过子查询实现：=运算符SELECT*FROMstudentWHEREst_dpid=(SELECTdp_idFROMdeptWHEREdp_drt=赵虎)实验11数据查询(7)数据更新与子查询操作11.1：将070511班所有学生信息插入到表student01(st_id,st_nm,st_sex)INSERTI