SQLServer综合练习题1参考答案.docx
《SQLServer综合练习题1参考答案.docx》由会员分享，可在线阅读，更多相关《SQLServer综合练习题1参考答案.docx（12页珍藏版）》请在冰点文库上搜索。
SQLServer综合练习题1参考答案
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_id
nVarChar
9
NotNull
PK
学生学号
st_nm
nVarChar
8
NotNull
学生姓名
st_sex
nVarChar
2
Null
学生性别
st_birth
datetime
Null
出生日期
st_score
int
Null
入学成绩
st_date
datetime
Null
入学日期
st_from
nChar
20
Null
学生来源
st_dpid
nVarChar
2
Null
所在系编号
st_mnt
tinyint
Null
学生职务
参考答案：
USEtest
GO
CREATETABLEstudent
（
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_id
nVarChar
4
NotNull
PK
课程编号
cs_nm
nVarChar
20
NotNull
课程名称
cs_tm
int
Null
课程学时
cs_sc
int
Null
课程学分
参考答案：
USEtest
GO
CREATETABLEcouse
（
cs_idnVarChar（4）primarykeyNOTNULL,
cs_nmnVarChar（20）NOTNULL,
cs_tmintNULL,
参考答案：
USEtest
GO
CREATETABLEdept
（
dp_idnVarChar
（2）NOTNULL,
dp_nmnVarChar（20）NOTNULL,
dp_drtnVarChar（8）NULL,
dp_telnVarChar（12）NULL
）
GO
3．表中插入数据
操作3.1：
向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567
INSERTINTOdeptVALUES（'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表记录，将王小五的入学成绩改为88
UPDATEstudentSETst_score=88WHEREst_nm='王小五'
操作4.2：
修改couse表记录，将所有记录的学分改为4，学时改为64
UPDATEcouseSETcs_tm=64,cs_sc=4
操作4.3：
修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77
UPDATEslt_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表时使用列表达式：
入学成绩+400
SELECTst_id,st_nm,st_score,st_score+400ASnew_score
FROMstudent
（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*16
FROMcouse
（5）增加说明列
操作6.6：
查询dept表的系号、系名和系主任，向查询结果中插入说明列：
系号、系名和系主任
SELECT'系号:
',dp_id,'系名:
',dp_nm,'系主任:
',dp_drt
FROMdept
（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*FROMstudent
7．条件查询
（1）使用关系表达式表示查询条件
操作7.1：
查询dept表中系号为11的院系信息
SELECT*FROMdeptWHEREdp_id='11'
操作7.2：
查询student表中11系的学生学号、姓名、性别和所在系编号
SELECTst_id,st_nm,st_sex,st_dpidFROMstudent
WHEREst_dpid='11'
操作7.3：
查询student表中2008年及以后入学的学生信息
SELECT*FROMstudent
WHEREDATEPART（yy,st_date）>=2008
操作7.4：
在查询student表080808班学生的学号、姓名、性别和入学成绩
SELECTst_id,st_nm,st_sex,st_scoreFROMstudent
WHERELeft（st_id,6）='080808'
（2）使用逻辑表达式表示查询条件
操作7.5：
查询student表中非11系的学生信息
SELECT*FROMstudentWHERENOT（st_dpid='11'）
操作7.6：
查询选修了1002号课程且成绩在60以下的学生学号
SELECTst_idFROMslt_couse
WHERE（cs_id='1002'）AND（score<60）
操作7.7：
查询2007年入学的11系所有男生信息
SELECT*FROMstudent
WHEREDATEPART（yy,st_date）=2007ANDst_dpid='11'ANDst_sex='男'
操作7.8：
查询11系和12系的学生信息
SELECT*FROMstudent
WHEREst_dpid='11'ORst_dpid='12'
操作7.9：
查询11系和12系所有2007年入学的学生信息
SELECT*FROMstudent
WHERE（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*FROMstudent
WHEREst_nmLIKE'[王-张]%'
（4）使用Between…And关键字进行查询
操作7.14：
查询在1989.7.1到1990.6.30之间出生的学生信息
SELECTst_id,st_nm,st_sex,st_birthFROMstudent
WHEREst_birthBETWEEN'1981.7.1'AND'1999.6.30'
操作7.15：
查询选修了1001号课程且成绩在60到80之间的学生选课信息
SELECT*FROMslt_couse
WHEREcs_id='1001'AND（scoreBETWEEN60AND80）
（5）使用IN关键字进行查询
操作7.16：
查询11系、12系、13系的学生信息
SELECT*FROMstudentWHEREst_dpidIN（'11','12','13'）
操作7.17：
查询所有张，王，李，赵姓的学生的学号、姓名、性别
SELECTst_id,st_nm,st_sexFROMstudent
WHERELeft（st_nm,1）IN（'张','王','李','赵'）
（6）使用[NOT]NULL关键字进行查询
操作7.18：
查询所有生源为非空的学生信息
SELECT*FROMstudentWHEREst_fromISNOTNULL
操作7.19：
查询选修了1001号课程且成绩为空的学生选课信息
SELECT*FROMslt_couse
WHEREcs_id='1001'ANDscoreISNULL
8.查询排序与查询结果存储
操作8.1：
查询课程信息，按课程名称降序排序
SELECT*FROMcouseORDERBYcs_nmDESC
操作8.2：
查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序
SELECTst_id,scoreFROMslt_corse
WHEREcs_id='1001'ANDscoreISNOTNULL
ORDERBYscoreDESC
操作8.3：
查询11系学生学号、姓名和年龄，按年龄升序排序
SELECTst_id,st_nm,DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）ASage
FROMstudent
ORDERBYageASC
操作8.4：
查询学生信息，按姓名升序排序，再按系号降序排序
SELECT*FROMstudentORDERBYst_nm,st_dpidDESC
操作8.5：
创建学生表副本student01，仅保留学生学号、姓名和性别
SELECTst_id,st_nm,st_sexINTOstudent01FROMstudent
操作8.6：
查询陕西籍学生，将结果保存在新表st_shanxi
SELECT*INTOst_shanxi
FROMstudent
WHEREst_from='陕西省'
操作8.7：
查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001
SELECT*INTOslt1001FROMslt_corse
WHEREcs_id='1001'ORDERBYst_id
9.查询统计与汇总
操作9.1：
查询课程总数
SELECTCOUNT（*）FROMcouse
操作9.2：
查询选修1001号课程的学生人数
SELECTCOUNT（st_id）
FROMslt_couse
Wherecs_id='1001'
操作9.3：
查询被选修课程的数量
SELECTCOUNT（DISTINCTcs_id）FROMslt_couse
操作9.4：
查询选修070101班学生的平均入学成绩
SELECTAVG（st_score）
FROMstudent
WHERELEFT（st_id,6）='070101'
操作9.5：
查询070101001号学生选修课程的数量、总分以及平均分
SELECTCOUNT（cs_id）AS课程数量,SUM（score）AS总分,AVG（score）AS平均分
FROMslt_couse
WHEREst_id='070101001'
操作9.6：
查询选修1001号课程的学生人数、最高分、最低分和平均分
SELECTCOUNT（*）AS学生人数,MAX（score）AS最高分,
MIN（score）AS最低分,AVG（score）AS平均分
FROMslt_couse
WHEREcs_id='1001'
操作9.7：
求各个课程号和相应的选课人数
SELECTcs_id,COUNT（st_id）
FROMslt_couseGROUPBYcs_id
操作9.8：
统计各班人数
SELECTLEFT（st_id,6）AS班级,COUNT（st_id）AS人数
FROMstudent
GROUPBYLEFT（st_id,6）
操作9.9：
依次按班级、系号对学生进行分类统计人数、入学平均分
SELECTst_dpidAS系号,LEFT（st_id,6）AS班级,
COUNT（st_nm）AS人数,AVG（st_score）AS均分
FROMstudent
GROUPBYLEFT（st_id,6）,st_dpid
操作9.10：
查询选修了均分在75以上的课程号及均分
SELECTcs_idAS课程编号,AVG（score）AS均分
FROMslt_couse
GROUPBYcs_idHAVINGAVG（score）>75
操作9.11：
查询选修了2门以上课程的学生学号
SELECTst_idFROMslt_couse
GROUPBYst_idHAVINGCOUNT（*）>2
操作9.12：
明细汇总年龄<20的学生，并汇总学生数量、平均年龄
SELECTst_nm,DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）AS年龄
FROMstudent
WHEREDATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）<20
COMPUTECOUNT（st_nm）,AVG（DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth））
操作9.13：
按班级明细汇总成绩<85分的学生，汇总学生数、均分
SELECTst_nm,LEFT（st_id,6）AS班级,st_score
FROMstudent
WHEREst_score<85
ORDERBY班级
COMPUTECOUNT（st_nm）,AVG（st_score）BY班级
10.连接查询
操作10.1：
用SQLServer形式连接查询学生学号、姓名、性别及其所选课程编号
SELECTa.st_id,st_nm,st_sex,cs_id
FROMstudenta,slt_couseb
WHEREa.st_id=b.st_id
ORDERBYa.st_id
操作10.2：
用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号
SELECTa.st_id,st_nm,st_sex,cs_id
FROMstudentaINNERJOINslt_couseb
ONa.st_id=b.st_id
ORDERBYa.st_id
操作10.3：
用SQLServer形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECTa.st_id,st_nm,cs_nm,score
FROMstudenta,slt_couseb,cousec
WHEREa.st_id=b.st_idANDb.cs_id=c.cs_id
ORDERBYa.st_id
操作10.4：
用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECTa.st_id,st_nm,cs_nm,score
FROMslt_couseaINNERJOINstudentbONa.st_id=b.st_id
INNERJOINcousecONa.cs_id=c.cs_id
ORDERBYb.st_id
操作10.5：
查询选修了1002课程的学生学号、姓名及1001课程成绩
SELECTa.st_id,st_nm,score
FROMstudenta，slt_couseb
WHEREa.st_id=b.st_idANDb.cs_id='1002'
ORDERBYb.st_id
操作10.6：
查询选修了“数据结构”课程的学生学号、姓名及课程成绩
SELECTa.st_id,st_nm,score
FROMstudenta,slt_couseb,cousec
WHEREa.st_id=b.st_idANDb.cs_id=c.cs_idANDc.cs_nm='数据结构'
ORDERBYa.st_id
操作10.7：
用左外连接查询没有选修任何课程的学生学号、姓名
SELECTa.st_id,st_nm,score
FROMstudentaLEFTOUTERJOINslt_cousebONa.st_id=b.st_id
WHEREb.cs_idISNULL
ORDERBYb.st_id
操作10.8：
用右外连接查询选修各个课程的学生学号
SELECTb.cs_id,a.st_id
FROMslt_couseaRightOUTERJOINcousebONa.cs_id=b.cs_id
ORDERBYb.cs_id
升级会员 