SQL Server SQL实验与练习题参考答案文档格式.docx

SQL Server SQL实验与练习题参考答案文档格式.docx

《SQL Server SQL实验与练习题参考答案文档格式.docx》由会员分享，可在线阅读，更多相关《SQL Server SQL实验与练习题参考答案文档格式.docx（20页珍藏版）》请在冰豆网上搜索。

说明

st_id

nVarChar

9

NotNull

PK

学生学号

st_nm

8

学生姓名

st_sex

2

Null

学生性别

st_birth

datetime

出生日期

st_score

int

入学成绩

st_date

入学日期

st_from

nChar

20

学生来源

st_dpid

所在系编号

st_mnt

tinyint

学生职务

USEtest

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

操作2.2：

创建课程信息表：

couse

课程信息表

cs_id

4

课程编号

cs_nm

课程名称

cs_tm

课程学时

cs_sc

课程学分

CREATETABLEcouse

cs_idnVarChar（4）primarykeyNOTNULL,

cs_nmnVarChar（20）NOTNULL,

cs_tmintNULL,

cs_scintNULL

操作2.3：

创建选课表：

slt_couse

选课表

FK

学生编号

score

课程成绩

sltdate

选课日期

cs_idnVarChar（4）NOTNULL,

st_idnVarChar（9）NOTNULL,

scoreintNULL,

sltdatedatetimeNULL

操作2.4：

创建院系信息表：

dept

院系信息表

dp_id

系编号

dp_nm

院系名称

dp_drt

院系主任

dt_tel

12

联系电话

CREATETABLEdept

dp_idnVarChar

（2）NOTNULL,

dp_nmnVarChar（20）NOTNULL,

dp_drtnVarChar（8）NULL,

dp_telnVarChar（12）NULL

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（）

EXECsp_bindefaultdf_today,'

slt_couse.sltdate'

EXECsp_unbindefault'

DROPDEFAULTdf_today

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（’男’,’女’）

EXECsp_bindrulerl_sex,'

student.st_sex'

EXECsp_unbindrule'

DROPRULErl_sex

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'

）

操作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（'

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='

ANDst_id='

3．删除表中数据

操作4.8：

删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除

DELETEFROMslt_couseWHEREcs_id='

操作4.9：

删除couse表记录，将课程号为1234的记录删除

DELETEFROMcouseWHEREcs_id='

实验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

（6）查询列表中使用系统函数

操作5.7：

显示所有学生的学号、姓名、性别和入学年份

SELECTst_id,st_nm,st_sex,DATEPART（yy,st_birth）AS入学年份

操作5.8：

显示所有学生学号、姓名、性别和班级（学号前6位）

SELECTst_id,st_nm,st_sex,LEFT（st_id,6）AS班级

（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='

操作6.2：

查询student表中11系的学生学号、姓名、性别和所在系编号

SELECTst_id,st_nm,st_sex,st_dpidFROMstudent

WHEREst_dpid='

操作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='

操作6.6：

查询选修了1002号课程且成绩在60以下的学生学号

SELECTst_idFROMslt_couse

WHERE（cs_id='

1002'

）AND（score<

60）

操作6.7：

查询2007年入学的11系所有男生信息

WHEREDATEPART（yy,st_date）=2007ANDst_dpid='

ANDst_sex='

操作6.8：

查询11系和12系的学生信息

WHEREst_dpid='

ORst_dpid='

12'

操作6.9：

查询11系和12系所有2007年入学的学生信息

WHERE（st_dpid='

）ANDDATEPART（yy,st_date）=2007

3．使用LIKE关键字进行模糊查询

操作6.10：

查询所有“计算机”开头的课程信息

SELECT*FROMcouseWHEREcs_nmLIKE'

计算机%'

操作6.11：

查询所有由三个字组成的“王”姓学生信息

SELECT*FROMstudentWHEREst_nmLIKE'

王__'

操作6.12：

查询所有课程名中包含“信息”的课程信息

%信息%'

操作6.13：

查询学生姓名介于王姓到张姓的信息

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（'

'

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

ANDscoreISNULL

实验7数据查询（3）——查询排序与查询结果存储

操作7.1：

查询课程信息，按课程名称降序排序

SELECT*FROMcouseORDERBYcs_nmDESC

操作7.2：

查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序

SELECTst_id,scoreFROMslt_corse

ANDscoreISNOTNULL

ORDERBYscoreDESC

操作7.3：

查询11系学生学号、姓名和年龄，按年龄升序排序

SELECTst_id,st_nm,DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）ASage

ORDERBYageASC

操作7.4：

查询学生信息，按姓名升序排序，再按系号降序排序

SELECT*FROMstudentORDERBYst_nm,st_dpidDESC

操作7.5：

创建学生表副本student01，仅保留学生学号、姓名和性别

SELECTst_id,st_nm,st_sexINTOstudent01FROMstudent

操作7.6：

查询陕西籍学生，将结果保存在新表st_shanxi

SELECT*INTOst_shanxi

WHEREst_from='

操作7.7：

查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001

SELECT*INTOslt1001FROMslt_corse

ORDERBYst_id

操作7.8：

用局部变量@stage保存学生张三的年龄

DECLARE@stageint

SELECT@stage=DATEPART（yy,GETDATE（））-DATEPART（yy,st_birth）

WHEREst_nm='

张三'

操作7.9：

用局部变量@name和@stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩

DECLARE@namenVarChar（8）,@stscoreint

SELECT@name=st_nm,@stscore=st_score

WHERELEFT（st_id,6）='

070101'

ORDERBYst_id

实验8数据查询（4）——查询统计与汇总

操作8.1：

查询课程总数

SELECTCOUNT（*）FROMcouse

操作8.2：

查询选修1001号课程的学生人数

SELECTCOUNT（st_id）

FROMslt_couse

Wherecs_id='

操作8.3：

查询被选修课程的数量

SELECTCOUNT（DISTINCTcs_id）FROMslt_couse

操作8.4：

查询选修070101班学生的平均入学成绩

SELECTAVG（st_score）

操作8.5：

查询070101001号学生选修课程的数量、总分以及平均分

SELECTCOUNT（cs_id）AS课程数量,SUM（score）AS总分,AVG（