SQL实验与练习题参考答案.docx
《SQL实验与练习题参考答案.docx》由会员分享，可在线阅读，更多相关《SQL实验与练习题参考答案.docx（30页珍藏版）》请在冰豆网上搜索。
SQL实验与练习题参考答案
实验1数据库操作
1．创建数据库：
操作1.1：
创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。
参考答案：
createdatabaseTest
ONprimary
（
name=test_data,
filename='d:
\test\test_data.mdf',
size=5MB,
maxsize=unlimited,
filegrowth=1MB
）
LOGON
（
name=test_log,
filename='d:
\test\test_log.ldf',
size=1MB,
maxsize=5MB,
filegrowth=10%
）
GO
2．查看数据库属性：
操作1.2：
使用T-SQL语句查看数据库test属性
参考答案：
EXECsp_helpdbtest
3．删除数据库：
操作1.3：
使用T-SQL语句删除数据库test
参考答案：
dropdatabaseTest
实验2表操作
1．创建表：
操作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,
cs_scintNULL
）
GO
操作2.3：
创建选课表：
表名：
slt_couse
说明：
选课表
属性列
数据类型
长度
空值
列约束
说明
cs_id
nVarChar
4
NotNull
FK
课程编号
st_id
nVarChar
9
NotNull
FK
学生编号
score
int
Null
课程成绩
sltdate
datetime
Null
选课日期
参考答案：
USEtest
GO
CREATETABLEcouse
（
cs_idnVarChar（4）NOTNULL,
st_idnVarChar（9）NOTNULL,
scoreintNULL,
sltdatedatetimeNULL
）
GO
操作2.4：
创建院系信息表：
表名：
dept
说明：
院系信息表
属性列
数据类型
长度
空值
列约束
说明
dp_id
nVarChar
2
NotNull
系编号
dp_nm
nVarChar
20
NotNull
院系名称
dp_drt
nVarChar
8
Null
院系主任
dt_tel
nVarChar
12
Null
联系电话
参考答案：
USEtest
GO
CREATETABLEdept
（
dp_idnVarChar
（2）NOTNULL,
dp_nmnVarChar（20）NOTNULL,
dp_drtnVarChar（8）NULL,
dp_telnVarChar（12）NULL
）
GO
2．修改表结构：
（1）向表中添加列：
操作2.5：
为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）
参考答案：
ALTERTABLEdeptADDdp_countnvarchar（3）NULL
（2）修改列数据类型：
操作2.6：
修改“dept”表的“dp_count”列数据类型为int
参考答案：
ALTERTABLEdeptALTERCOLUMNdp_countintNULL
（3）删除表中指定列：
操作2.7：
删除“dept”表的“dp_count”列
参考答案：
ALTERTABLEdeptDROPCOLUMNdp_count
3．删除表
操作2.8：
删除“dept”表
参考答案：
DROPTABLEstudent
4．向表中输入数据记录
操作2.9：
分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录
实验3数据完整性
1．空值约束（NULL）
操作3.1：
将student表中的st_sex列属性更改为NOTNULL
参考答案：
ALTERTABLEstudentALTERCOLUMEst_nmnVarChar（8）NOTNULL
2．默认值约束（DEFAULT）
操作3.2：
将student表中的st_from列默认值设置为“陕西省”
参考答案：
ALTERTABLEstudentADDDEFAULT'陕西省'FORst_from
3．默认值对象
操作3.3：
创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。
参考答案：
CREATEDEFAULTdf_todayASGetdate（）
GO
EXECsp_bindefaultdf_today,'slt_couse.sltdate'
GO
EXECsp_unbindefault'slt_couse.sltdate'
GO
DROPDEFAULTdf_today
GO
4．检查约束（CHECK）
操作3.4：
将slt_couse表中的score列的检查约束设置为>=0且<=100
参考答案：
ALTERTABLEslt_couseADDCHECK（score>=0ANDscore<=100）
5．规则约束对象
操作3.5：
创建规则约束对象rl_sex，用于检查性别的取值仅限于“男”和“女”，并将其绑定到student表中的st_sex列，然后取消绑定，最后删除规则约束对象rl_sex。
参考答案：
CREATERULErl_sexAS@chksex’男’OR@chksex=’女’
或
CREATERULErl_sexAS@chksexIN（’男’,’女’）
GO
EXECsp_bindrulerl_sex,'student.st_sex'
GO
EXECsp_unbindrule'student.st_sex'
GO
DROPRULErl_sex
GO
6．主键
操作3.6：
将dept表中的dp_id列设置为主键
参考答案：
ALTERTABLEdeptADDPRIMARYKEY（dp_id）
7．唯一性约束（UNIQUE）
操作3.7：
将dept表中的dp_nm列设置为唯一性约束
参考答案：
ALTERTABLEdeptADDUNIQUE（dp_nm）
8．标识列
操作3.8：
向slt_couse表中添加标识列id，第1行默认值为1，相邻两个标识列间的增量为1
参考答案：
ALTERTABLEslt_couseADDidINTIDENTITY（1,1）NOTNULL
9．外键（FOREIGNKEY）
操作3.9：
被参照表为dept，参照表为student
参考答案：
ALTERTABLEstudent
ADDFOREIGNKEY（st_dpid）REFERENCESdept（dp_id）
实验4数据更新
1．表中插入数据
操作4.1：
向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567
INSERTINTOdeptVALUES（'11','自动控制系','李其余','81234567'）
操作4.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'）
操作4.3：
向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值
INSERTINTOcouse（cs_id,cs_nm）VALUES（'1234','操作系统'）
操作4.4：
向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值
INSERTINTOslt_couse（cs_id,st_id）VALUES（'1234','070201001'）
2．修改表中数据
操作4.5：
修改student表记录，将王小五的入学成绩改为88
UPDATEstudentSETst_score=88WHEREst_nm='王小五'
操作4.6：
修改couse表记录，将所有记录的学分改为4，学时改为64
UPDATEcouseSETcs_tm=64,cs_sc=4
操作4.7：
修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77
UPDATEslt_couseSETscore=77WHEREcs_id='1234'ANDst_id='070201001'
3．删除表中数据
操作4.8：
删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除
DELETEFROMslt_couseWHEREcs_id='1234'ANDst_id='070201001'
操作4.9：
删除couse表记录，将课程号为1234的记录删除
DELETEFROMcouseWHEREcs_id='1234'
实验5数据查询
（1）——简单查询
（1）查询表中所有的列
操作5.1：
查询所有系的信息
SELECT*FROMdept
（2）查询表中指定列的信息
操作5.2：
查询所有的课程号与课程名称
SELECTcs_id,cs_nmFROMcouse
（3）在查询列表中使用列表达式
操作5.3：
在查询student表时使用列表达式：
入学成绩+400
SELECTst_id,st_nm,st_score,st_score+400ASnew_score
FROMstudent
（4）重新命名查询结果
操作5.4：
使用AS关键字为dept表中属性指定列名：
系号、系名、系主任、联系电话
SELECTdp_idAS系号,dp_nmAS系名,dp_drtAS系主任,dp_telAS联系电话
FROMdept
操作5.5：
使用"="号为couse表中属性指定列名：
课程号、课程名、学时（=cs_sc*16）、学分
SELECT课程号=cs_id,课程名=cs_nm,学分=cs_sc,学时=cs_sc*16
FROMcouse
（5）增加说明列
操作5.6：
查询dept表的系号、系名和系主任，向查询结果中插入说明列：
系号、系名和系主任
SELECT'系号:
',st_id,'系名:
',st_nm,'系主任:
',st_drt
FROMdept
（6）查询列表中使用系统函数
操作5.7：
显示所有学生的学号、姓名、性别和入学年份
SELECTst_id,st_nm,st_sex,DATEPART（yy,st_birth）AS入学年份
FROMstudent
操作5.8：
显示所有学生学号、姓名、性别和班级（学号前6位）
SELECTst_id,st_nm,st_sex,LEFT（st_id,6）AS班级
FROMstudent
（7）消除查询结果中的重复项
操作5.9：
显示所有学生班级
SELECTDISTINCTLEFT（st_id,6）AS班级FROMstudent
（8）取得查询结果的部分行集
操作5.10：
显示前5条学生记录信息
SELECTTOP5*FROMstudent
操作5.11：
显示前25%条学生记录信息
SELECTTOP25PERCENT*FROMstudent
操作5.12：
显示前n条学生记录信息，n为局部变量
DECLARE@nINT
SET@n=4
SELECTTOP@n*FROMstudent
实验6数据查询
（2）——条件查询
1．使用关系表达式表示查询条件
操作6.1：
查询dept表中系号为11的院系信息
SELECT*FROMdeptWHEREdp_id='11'
操作6.2：
查询student表中11系的学生学号、姓名、性别和所在系编号
SELECTst_id,st_nm,st_sex,st_dpidFROMstudent
WHEREst_dpid='11'
操作6.3：
查询student表中2008年及以后入学的学生信息
SELECT*FROMstudent
WHEREDATEPART（yy,st_date）>=2008
操作6.4：
在查询student表080808班学生的学号、姓名、性别和入学成绩
SELECTst_id,st_nm,st_sex,st_scoreFROMstudent
WHERELeft（st_id,6）='080808'
2．使用逻辑表达式表示查询条件
操作6.5：
查询student表中非11系的学生信息
SELECT*FROMstudentWHERENOT（st_dpid='11'）
操作6.6：
查询选修了1002号课程且成绩在60以下的学生学号
SELECTst_idFROMslt_couse
WHERE（cs_id='1002'）AND（score<60）
操作6.7：
查询2007年入学的11系所有男生信息
SELECT*FROMstudent
WHEREDATEPART（yy,st_date）=2007ANDst_dpid='11'ANDst_sex='男'
操作6.8：
查询11系和12系的学生信息
SELECT*FROMstudent
WHEREst_dpid='11'ORst_dpid='12'
操作6.9：
查询11系和12系所有2007年入学的学生信息
SELECT*FROMstudent
WHERE（st_dpid='11'ORst_dpid='12'）ANDDATEPART（yy,st_date）=2007
3．使用LIKE关键字进行模糊查询
操作6.10：
查询所有“计算机”开头的课程信息
SELECT*FROMcouseWHEREcs_nmLIKE'计算机%'
操作6.11：
查询所有由三个字组成的“王”姓学生信息
SELECT*FROMstudentWHEREst_nmLIKE'王__'
操作6.12：
查询所有课程名中包含“信息”的课程信息
SELECT*FROMcouseWHEREcs_nmLIKE'%信息%'
操作6.13：
查询学生姓名介于王姓到张姓的信息
SELECT*FROMstudent
WHEREst_nmLIKE'[王-张]%'
4．使用Between…And关键字进行查询
操作6.14：
查询在1989.7.1到1990.6.30之间出生的学生信息
SELECTst_id,st_nm,st_sex,st_birthFROMstudent
WHEREst_birthBETWEEN'1981.7.1'AND'1999.6.30'
操作6.15：
查询选修了1001号课程且成绩在60到80之间的学生选课信息
SELECT*FROMslt_couse
WHEREcs_id='1001'AND（scoreBETWEEN60AND80）
5．使用IN关键字进行查询
操作6.16：
查询11系、12系、13系的学生信息
SELECT*FROMstudentWHEREst_dpidIN（'11','12','13'）
操作6.17：
查询所有张，王，李，赵姓的学生的学号、姓名、性别
SELECTst_id,st_nm,st_sexFROMstudent
WHERELeft（st_nm,1）IN（'张','王','李','赵'）
6．使用[NOT]NULL关键字进行查询
操作6.18：
查询所有生源为非空的学生信息
SELECT*FROMstudentWHEREst_fromISNOTNULL
操作6.19：
查询选修了1001号课程且成绩为空的学生选课信息
SELECT*FROMslt_couse
WHEREcs_id='1001'ANDscoreISNULL
实验7数据查询（3）——查询排序与查询结果存储
操作7.1：
查询课程信息，按课程名称降序排序
SELECT*FROMcouseORDERBYcs_nmDESC
操作7.2：
查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序
SELECTst_id,scoreFROMslt_corse
WHEREcs_id='1001'ANDscoreISNOTNULL
ORDERBYscoreDESC
操作7.3：
查询11系学生学号、姓名和年龄，按年龄升序排序
SELECTst_id,st_nm,DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）ASage
FROMstudent
ORDERBYageASC
操作7.4：
查询学生信息，按姓名升序排序，再按系号降序排序
SELECT*FROMstudentORDERBYst_nm,st_dpidDESC
操作7.5：
创建学生表副本student01，仅保留学生学号、姓名和性别
SELECTst_id,st_nm,st_sexINTOstudent01FROMstudent
操作7.6：
查询陕西籍学生，将结果保存在新表st_shanxi
SELECT*INTOst_shanxi
FROMstudent
WHEREst_from='陕西省'
操作7.7：
查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001
SELECT*INTOslt1001FROMslt_corse
WHEREcs_id='1001'ORDERBYst_id
操作7.8：
用局部变量@stage保存学生张三的年龄
DECLARE@stageint
SELECT@stage=DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）
FROMstudent
WHEREst_nm='张三'
操作7.9：
用局部变量@name和@stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩
DECLARE@namenVarChar（8）,@stscoreint
SELECT@name=st_nm,@stscore=st_score
FROMstudent
WHERELEFT（st_id,6）='070101'
ORDERBYst_id
实验8数据查询（4）——查询统计与汇总
操作8.1：
查询课程总数
SELECTCOUNT（*）FROMcouse
操作8.2：
查询选修1001号课程的学生人数
SELECTCOUNT（st_id）
FROMslt_couse
Wherecs_id='1001'
操作8.3：
查询被选修课程的数量
SELECTCOUNT（DISTINCTcs_id）FROMslt_couse
操作8.4：
查询选修070101班学生的平均入学成绩
SELECTAVG（st_score）
FROMstudent
WHERELEFT（st_id,6）='070101'
操作8.5：
查询070101001号学生选修课程的数量、总分以及平均分
SELECTCOUNT（cs_id）AS课程数量,SUM（score）AS总分,AVG（score）AS平
升级会员 